Sending an Email with VBA Based on Address from Specific Cell In a Filtered Table

[mamamia93]

Hello All,

I hope you are all doing well.

I need assistance with a little project I have been working. Every month, I reach out to custodians concerning accounts I manage. I have a sheet that contains a table with all the accounts and their respective custodians in a single table. I filter by column E, and based on the custodian, I copy and paste the table with the information related to the respective custodian in the body of the email. This is a tedious process, so I attempted to create a macro that prepares and formats the email to my liking.

There is just one issue. If you take a look at the logic below the email address used is pulled from the email address located in column F. My initial thought was to filter the custodian I want, and when I press the button to trigger the macro, it would search the first cell in column F (F2) and inserts in the "To" field in the email. So, if I choose Bank of America, it works great because the email is in cell F2, and the Macro pulls that email correctly. The problem occurs when I filter for a different custodian. I thought the macro will look in that same area and pull the proper email address for the specific custodian. Because I filter the table, the custodian I am filtering is essentially in another cell, so it does not pull from what I filtered by. For example, let's say I filter for State Street in column E, despite the table looking like it is in the same place as Bank of America it is essentially in F22, so the macro does not pull the email for State Street. It instead pulls the email address for Bank of America in cell F2. Is there a way to direct the macro to look in the area rather than the specific cell reference to pull the email I want to send to thus limiting the issue if I filter the table?

Private Sub CommandButton1_Click()

    Dim Sht As Excel.Worksheet
    Set Sht = ThisWorkbook.ActiveSheet
 
    [COLOR=rgb(184, 49, 47)]Recip = [F2].Value & "; "[/COLOR]

    Dim rng As Range
    Set rng = Sht.Range("A2:F26")
        rng.Copy

    Dim OutApp As Object
    Set OutApp = CreateObject("Outlook.Application")

    Dim OutMail As Object
    Set OutMail = OutApp.CreateItem(0)

    Dim vInspector As Object
    Set vInspector = OutMail.GetInspector
 
    Dim wEditor As Object
    Set wEditor = vInspector.WordEditor
 
       

    With OutMail
        .TO = Recip
        .CC = ""
        .Subject = "STIF Vehicle Confirmation" & " - " & [[COLOR=rgb(184, 49, 47)]E2].Value[/COLOR]
        .display
       
         wEditor.Paragraphs(1).Range.Text = "Hello All," & Chr(11) & Chr(11) & "I hope this email finds you all doing well." & Chr(11) & Chr(11) & _
         "Can you please confirm if the below STIF vehicle details are accurate for the accounts below? If the vehicle has changed, can you please confirm the new STIF vehicle name and CUSIP?" & vbCrLf
       
       
         wEditor.Paragraphs(2).Range.Paste

    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Internall Account	External Account	Vehicle Name	CUSIP	Custody	Email
13723​	64055​	Jamila Eastwood	NTP6PHMTM	Bank of America	Bank of America@Bank.com
30618​	26995​	Simran Oconnell	UNSVS7R4P	Bank of America	Bank of America@Bank.com
10692​	48027​	Emanuel Nava	QNIT0TUFE	Bank of America	Bank of America@Bank.com
41981​	26261​	Jordana Andrade	5349CHX4C	Bank of America	Bank of America@Bank.com
87960​	42732​	Autumn Alston	APUEW77YR	Bank of America	Bank of America@Bank.com
32445​	84960​	Faris Mitchell	6BQUG9O5P	Bank of America	Bank of America@Bank.com
51825​	88508​	Mylo Payne	CNYKL32AI	Bank of America	Bank of America@Bank.com
61999​	52633​	Peyton Read	V5OSE0AA1	Bank of America	Bank of America@Bank.com
83340​	26943​	Malakai Espinosa	ZA64HVWOV	Citi	Citi@Bank.com
32576​	94372​	Mylee Pena	0MIJB173U	Citi	Citi@Bank.com
43076​	19463​	Hadley Cisneros	GHKSC9HCM	JP Morgan	JP Morgan@Bank.com
68555​	38814​	Mylie Floyd	USO6DBRG5	JP Morgan	JP Morgan@Bank.com
72812​	23583​	Neal Rowland	K3DSAHMGV	JP Morgan	JP Morgan@Bank.com
19525​	17266​	Uma Drew	VETY20RJP	JP Morgan	JP Morgan@Bank.com
44003​	41899​	Sia Finnegan	BFLZY5K5H	JP Morgan	JP Morgan@Bank.com
30421​	19778​	Wilma Humphries	SECLN65HA	JP Morgan	JP Morgan@Bank.com
93326​	87271​	Ashraf Hamer	NWP2MM2KX	Goldman Sachs	Goldman Sachs@Bank.com
28515​	64051​	Zayyan Trevino	URVFFI7KW	Goldman Sachs	Goldman Sachs@Bank.com
49077​	79988​	Charly Lucas	0RNOT6XQQ	Goldman Sachs	Goldman Sachs@Bank.com
57629​	61994​	John-James Huber	OJZOX32U1	Goldman Sachs	Goldman Sachs@Bank.com
35576​	58643​	Oluwatobiloba Frye	547HVSOEA	State Street	State Street@Bank.com
79816​	38204​	Darrel Appleton	DZXSJHP8N	State Street	State Street@Bank.com
70330​	58820​	Bea Mcgill	KTU9BG09R	State Street	State Street@Bank.com
69074​	31198​	Cordelia Reid	LBT1CA44L	State Street	State Street@Bank.com
13432​	81314​	Yara Kirkpatrick	QU3M1MJDS	State Street	State Street@Bank.com
-	-	-	-	-	-

 

[daverunt]

Hi,

Not quite following your post.
I think you just need to find the first visible cell of the filtered content, which could be any row.

If that is the case, try the following: The first line should grab the first row number of the filtered rows. Then we use Cells(RowIndex,ColumnIndex).Value to get the filtered email address.

VRow = ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 1).Row

Recip = Cells(VRow, 6).Value

 

[mamamia93]

Hi,

Not quite following your post.
I think you just need to find the first visible cell of the filtered content, which could be any row.

If that is the case, try the following: The first line should grab the first row number of the filtered rows. Then we use Cells(RowIndex,ColumnIndex).Value to get the filtered email address.

VRow = ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 1).Row

Recip = Cells(VRow, 6).Value

That is pretty much what I am looking to do. I will add and say the table is not the only table on the sheet. This table happens to sit below another table. Will this still work? Secondly, where in my code do you advise I insert the code you provided?

Best,

 

[daverunt]

Hi,

I would have thought it applicable to the filtered rows but TBH I don't know. Try it on a file copy.
Both lines instead of Recip=F2.value

 

[mamamia93]

Hi,

I would have thought it applicable to the filtered rows but TBH I don't know. Try it on a file copy.
Both lines instead of Recip=F2.value

I am getting an object variable or block variable not set error. Do I need to declare these variables?

 

[daverunt]

Don't know why you would get that error.
That isn't a problem with declaration.

Check the code.

 Dim Sht As Excel.Worksheet
 Set Sht = ThisWorkbook.ActiveSheet

    VRow = ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 1).Row

    Recip = Cells(VRow, 6).Value

    Dim rng As Range
   Set rng = Sht.Range("A2:F26")

 

[mamamia93]

The below code is what I am using. I can paste in in VBA and it will run once fine with no issues. The second time I run the code when filtering the table with a different parameter, the code give me a Run-Time Error 91.

Private Sub CommandButton1_Click()
    Dim Sht As Excel.Worksheet
    Set Sht = ThisWorkbook.ActiveSheet
 
    Recip = Worksheets("STIF Report").AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 6) ----!
  
    Custody = Worksheets("STIF Report").AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 5) ----!
  
    Dim rng As Range
    Set rng = Sht.Range("B43:D85")
        rng.Copy
  
    Dim OutApp As Object
    Set OutApp = CreateObject("Outlook.Application")
 
    Dim OutMail As Object
    Set OutMail = OutApp.CreateItem(0)
 
    Dim vInspector As Object
    Set vInspector = OutMail.GetInspector
  
    Dim wEditor As Object
    Set wEditor = vInspector.WordEditor
    
    With OutMail
        .TO = Recip
        .CC = ""
        .Subject = "STIF Vehicle Confirmation" & " - " & Custody ----!
        .display
        
         wEditor.Paragraphs(1).Range.Text = "Hello All," & Chr(11) & Chr(11) & "I hope this email finds you all doing well." & Chr(11) & Chr(11) & _
         "Can you please confirm if the below STIF vehicle details are accurate for the accounts below? If the vehicle has changed, can you please confirm the new STIF vehicle name and CUSIP?" & vbCrLf
        
         wEditor.Paragraphs(2).Range.Paste
    End With

 

[daverunt]

On which line does the error occur.
List the exact steps you take to get the error.
Do you have a better example of your worksheet including the additional table.
Which column/row does your filtered range start in.

I don't have any issues filtering the columns. However my worksheet is hacked together only using the information you posted.