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.
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