Send Personalised Bulk Emails with Attachments from Excel Using VBA

This is related to an earlier hack published (see Hack 4: Email a document from Excel using VBA), for sending one report to several users.

For this use case, you have a list of users, and each user has a specific report that must be sent to them. In our example, there are 8 business regions, and each region has a manager and a specific performance report. The performance reports have been saved as Excel files on the work system, and the list of managers with their emails are maintained in an Excel database.

For this instance, there are 8 managers. However, if there were several managers and you had to send each manager's report to them in a unique email, you would need to initiate a new Outlook email for each regional manager and painstakingly attach each one's report to their email, thus taking time. This hack shows how to get this work done under 5 minutes or less.

Step 1: In your worksheet, develop (if you haven't one already) a new tab in which you would store the names of your email recipients, their email addresses, the subject of your email, the body of your email message, and the list of attachments. This can look like the snapshot below.

(If you want to know how I was able to extract the First names from the list of Recipients, click here)

Step 2: Open a new workbook, access the Visual Basic Editor window (File>Developer>Visual Basic or use keyboard shortcut Alt+F11).

Note: If you cannot find the Developer Tab on your Excel ribbon, then you need to enable it from the Options menu. Click File>Options>Customize Ribbon, and check the box next to the “Developer” option.

Step 3: In the Visual Basic Editor window, go to Insert>Module, and in the new window that opens, paste the code below:

Sub EmailWorkbook()

Dim OutlookApp As Outlook.Application
Dim OutlookMail As Object
Dim xlBook As Workbook
Dim xlSheet As Worksheet

For i = 2 To 8 'adjust these numbers to match the beginning and the ending rows of your recipient table

Set OutlookApp = New Outlook.Application
Set OutlookMail = OutlookApp.CreateItem(0)
Set xlBook = ActiveWorkbook
Set xlSheet = ActiveWorkbook.Sheets("list")  'change "list" to the name of your Excel worksheet

With OutlookMail

source_file = "C:\Users\madamjeunsoke\Desktop\performance reports\" & Cells(i, 4)  'change the bolded section to the file path of the folder in which the performance reports are saved

.To = xlSheet.Cells(i, 2).Value
.Subject = xlSheet.Range("A11").Value

.Body = "Dear " & xlSheet.Cells(i, 3).Value & "," & Chr(10) & Chr(10) & xlSheet.Range("A14").Value

.Attachments.Add source_file
    
    
.Display 'change this to .Send if you want it to send without opening the outlook email for you
End With

Set OutlookMail = Nothing
Set OutlookApp = Nothing

Next i

End Sub

To run this code, go to Macros (or keyboard shortcut Alt+F8) and run the code: