How to Send Emails from Excel Using VBA (With Recipient List & Body)

Here's a quick macro module you can add to your Excel document to directly email the document to a list of recipients straight from the Excel application. I have placed comments (indicated by the text sections on the code that start with the character ') so that it is easy to understand what each section stands for, and know where you can make modifications to suit your own needs.

Step 1: In your workbook, create a new tab in which you would store the names of your email recipients, their email addresses, the subject of your email, and the body of your email message. This can look like the snapshot below.

(to see how to extract first names from a name list, learn the LEFT and FIND formula approach — extract first names from a name list using LEFT and FIND)

This tab can be hidden if you don't wish for others to see the content. Simply right-click on it, and select "Hide". To unhide, select any of the visible tabs, right-click and select "Unhide".

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 9

Set OutlookApp = New Outlook.Application
Set OutlookMail = OutlookApp.CreateItem(0)
Set xlBook = ActiveWorkbook
Set xlSheet = ActiveWorkbook.Sheets("Sheet2")

With OutlookMail
.To = xlSheet.Cells(i, 2).Value
.CC = "myemail@COMPANY.com"
.Subject = xlSheet.Range("A12").Value
.Body = "Hello " & xlSheet.Cells(i, 3).Value & "," & Chr(10) & Chr(10) & xlSheet.Range("B12").Value

.Attachments.Add xlBook.FullName

.Display
End With

Set OutlookMail = Nothing
Set OutlookApp = Nothing

Next i

End Sub

Step 4: To run this macro from the front-end of the workbook, place a Command button (from the Developer Tab) on your preferred spot on your sheet and connect it to the module as shown below:

When you run the code, it will prepare all the individual email messages to each recipient listed and open each in a separate window. Thus if you listed 9 recipients, 9 windows will pop up on your screen.

This method will allow you to review each email and add some personal messages. If you want to skip this step and send all the messages immediately, remove the line ".Display" and replace with ".Send"

EXPLANATION OF EACH CODE LINE IN THE ABOVE MACRO

Sub EmailWorkbook()

1. Dim OutlookApp As Outlook.Application 'we are declaring a variable called OutlookApp that we will call the MS Outlook Application in
2. Dim OutlookMail As Object 'we are declaring an object called OutlookMail that we will call the new Outlook Mail item in
3. Dim xlBook As Workbook 'we are declaring a workbook called xlBook that would signify the Excel workbook we want to send as an email attachment
4. Dim xlSheet As Worksheet 'we are declaring a worksheet in our workbook called xlSheet that refers to where the email addresses, email recipients, subject, message body we want to use.

5. For i = 2 To 9 'this is the beginning of a For-Next loop, it tells the code to loop through all the email addresses we listed and send them individual messages using the parameters within the loop. The numbers 2 to 9 indicate the row numbers of the emails and recipients we want to send the message to - we skip 1 as that contains headers. Adjust this to suit your own data length

6. Set OutlookApp = New Outlook.Application 'we fill the object we declared above as discussed earlier 
7. Set OutlookMail = OutlookApp.CreateItem(0)  'we fill the object we declared above as discussed earlier
8. Set xlBook = ActiveWorkbook ' we fill the object we declared above as discussed earlier
9. Set xlSheet = ActiveWorkbook.Sheets("Sheet2") 'we fill the object we declared above as discussed earlier. "Sheet2" is the name of the sheet containing the email information on my own workbook - rename this to match the name of the sheet on your own workbook

10. With OutlookMail 'the With statement helps to modify the contents and properties of whatever object is in front of it. In the code lines below, we are declaring what we want the new Outlook Mail item to have and contain
11.  .To = xlSheet.Cells(i, 2).Value 'this sets the "To" in the new email as the content of the cell found in row i, column 2 of the email information sheet (xlSheet)
12.  .CC = "myemail@COMPANY.com" 'this is if you want to copy yourself in the email. Change this to your own email address. You can also set a blind copy using the line .BCC
13.  .Subject = xlSheet.Range("A12").Value 'this sets the "Subject" of the email to what is in Cell $A$12 of the xlSheet
14.  .Body = "Hello " & xlSheet.Cells(i, 3).Value & "," & Chr(10) & Chr(10) & xlSheet.Range("B12").Value 'the email will start with 'Hello' and the first name of the recipient, then have two lines of space (Chr10 is used to do this), and then the message body.
15.  .Attachments.Add xlBook.FullName 'this is to attach the active workbook to the email. 
16.  .Display  'this is to show the new email item in a new window when all of the above has been done. Alternatively you can use .Send to email the message directly

17.   End With  'this closes the With statement

18. Set OutlookMail = Nothing  'this empties out the OutlookMail object to prepare it for the next recipient
19. Set OutlookApp = Nothing   'this empties out the OutlookMail object to prepare it for the next recipient

Next i

End Sub