Email Reports to Multiple Recipients using VBA

In this post I will show you how to email reports (as attachments) to various recipients via Outlook using VBA. It is a less commonly-used function of VBA to control other Windows processes beyond Excel, but no less powerful. Once we have the Macro in place, we will be able to:

1),Send territory-level reports to corresponding territories. Each territory will only receive one email with one report attached.

2), Send each email with customized Subject line and body message showing, for example, territory name.

Before I show the code, we need email addresses for all territories as shown in the following snapshot:

Add the following Macro to your workbook. Download the example here.

The code above calls an Outlook object and then create a new mail item. Once that's done, we can change the properties of that item (such as .To, .Subject, .Body) to send an email to a specific address with specific messages. Finally, .Attachments.Add method is used to attach the currently opened workbook with the outgoing email.

Several things to keep in mind:

1), Outlook must be open and running when you execute the Macro.

2), Outlook may give a security warning every time an email is sent. Readjust the security settings in your Outlook to disable that message.

3), To add a line break in the body of the message, use vbCrLf as part of the string.