Receive new articles directly in your inbox!! Register your email address

How to send emails with attachments to multiple recipients

Often we need to send multiple emails to different people with a customised attachment for each recipient. Excel VBA has the solution

One of the most valuable features of Excel VBA is its ability to communicate with other Microsoft applications, such as Outlook email. This allows users to automate tasks that would otherwise be time-consuming and repetitive, such as sending out mass emails

This post describes how to write Excel VBA code to send emails to recipients in a spreadsheet, and attach files that are located in the paths listed next to each recipient.

The steps to build the code are the following

1. Improve speed and declare variables

2. Start the loop

3. Create the email object

4. Compose the email, attach the relevant document and send

5. Full code

1. Improve speed and declare variables

The first two lines of code are used to optimize speed of execution. This is done by disabling screen updating and automatic calculation. The next lines are used to define the variables that will be used throughout the code.

Sub send_emails()

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual

Dim i As Integer, lstrw As Integer

Dim olApp As Object, olMail As Object

Dim filetosend As String, email As String

Dim msg As String, subj As String

2. Start the loop

The email addresses and the file path of the documents to be attached are reported in a sheet called "Mailing" The code will loop through the email addresses and will identify the corresponding file path

The below picture shows how the sheet "Mailing" has to be set up before running the code

The sheet "Mailing" is selected, the last used row is identified and the For Loop starts running.

The counter (i) starts at 2 as the first row contains column headers

Sheets("Mailing").Select

lstrw = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lstrw

3. Create the email object

Each time the loop is executed the Outlook and email objects are created as shown below

Set olApp = CreateObject("Outlook.Application")

Set olMail = olApp.CreateItem(0)

Each variable is given a specific value and then it is used to populate the relevant email fields

- email: String Variable containing the the email address reported in column A of sheet "Mailing"

- filetosend: String Variable containing the path to the file to be attached. It is reported in column B of the sheet "Mailing"

- msg: String Variable containing the text to be reported in the body of the email. The vbNewLine function creates a new line

- subj: String variable containing the subject of the email.

email = Cells(i, 1)

filetosend = Cells(i, 2)

msg = "Hi" & vbNewLine & "Please find attached your file" & vbNewLine & vbNewLine & "Best Regards"

subj = "Information for you"

4. Compose the email, attach the relevant document and send

Each property of the mail object receives the corresponding variable and the email is sent to the recipient

With olMail

.to = email

.subject = subj

.body = msg

.attachments.Add filetosend

.send

End With

The outlook object and email object are then deleted and the loop moves to the next row repeating the same instructions.

Once the loop is over the screen updating and the automatic calculation are reestablished

Set olApp = Nothing

Set olMail = Nothing

Next i

Application.ScreenUpdating = True

Application.Calculation = xlCalculationAutomatic

End Sub