I'm trying to email multiple worksheets to multiple recipients contained in a column on a sheet named "Directory". The email address is contained on the 7th column of the sheet.
I've written this code using several pieces of code from different sources. It only emails the first sheet if finds and then it gives me an error when it goes back through the loop. The error occurs on the code-line: "With WBA.Sheets(AAO).MailEnvelope". The error I get is a Run-time error. It says: "Method 'MailEnvelope' of object '_Worksheet' failed" I don't know what's wrong.
Sub EmailingMacro()
Set WBA = ActiveWorkbook
FinalRow = WBA.Sheets("Directory").Cells(Rows.Count, 1).End(xlUp).Row + 1
'This code speeds up the macro
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With
For i = 2 To FinalRow
AAO = WBA.Sheets("Directory").Cells(i, 1).Value
If SheetExists(AAO) Then
emailaddr = WBA.Sheets("Directory").Cells(i, 1).Offset(0, 7).Value
With WBA.Sheets(AAO).MailEnvelope
.Item.to = emailaddr
.Item.Subject = "CD & BS Audit Results"
.Item.body = "Errors found during audit are attached."
.Item
.Item.send
End With
End If
Next i
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
Function SheetExists(ByVal AAO As String) As Boolean
On Error Resume Next
SheetExists = Len(Sheets(AAO).Name)
End Function
These are the tabs I have on the workbook:
"Directory", "New Errors", "Producers", "AI", "BM", ...., "WS"
I want the macro to look in the directory Column A and find the offices that have a worksheet... "AI", "BM", etc. and use the email in the directory to email only that sheet to them. This is a sample of the names on Column A. Some repeat so I want to email the sheet to both recipients. You can see below that I want to email the sheet named "AI" to both email addresses in Column 7 of the same row.
AI
AI
AV
BE
BG
BM
BO
BR
Bookmarks