This ebook contains instructions and code samples to help you easily send emails to a list of email recipients. The three scenarios covered are emails containing simple text, including a unique attachment or containing an element of spreadsheet data.
Not many people realize that it’s possible to send information directly from Excel to individual email accounts. It effectively works like a ‘mail merge’ but can be achieved from Excel using a little Visual Basic code (VBA).
This ebook will explain the process you need to follow to link Excel with Outlook.
Note: A similar process can be achieved by linking Excel with Gmail or Hotmail; however it’s more difficult and also requires passwords to be incorporated into the code so security may be an issue, we are not going to consider these options in this ebook.
We will look at three different scenarios:
- How to send personalized email to a list of people from Excel
- How to send an email including an attachment
- How to send a specific range of data from a worksheet within an email
- Must have the developer tab
- Must have outlook
Step 1: Adding the Developer Tab into your Excel spreadsheet
Before we begin, we need to add the Developer tab into the current worksheet.
- Right Click anywhere on the ribbon
- Select Customize the Ribbon.
- From the options available in the right-hand window, ensure the Developer option is selected and click OK.
This will add the Developer tab into the top menu of your workbook as shown.
Going forward, when you save your workbook, you will need to save it as a .xlsm file (Excel Macro-Enabled Workbook) to ensure the VBA code is saved.
Step 2: Create an email to a list of names in Excel
With the set up for this code, your sheet must have
- the name that you would like to address the email to in column A and
- their corresponding email address in column B
The list of recipients can be as long as needed. The code will look for the first empty cell in the name field, and then it will stop, so ensure that you have no empty lines in the list.
Creating the Initial Code to Send Email via Outlook from Excel
Go to the Developer tab on your Excel Workbook and Click on the Visual Basic Icon.
Ensure that the Current workbook is selected in the Visual Basic for Applications window that opens, as shown below.
From the Menu bar, select Insert then Module.
The code you need to enter into the Module screen is shown below, but first a few explanations:
- All variables need to be declared, using the “Dim” statement and Allocating a data type (e.g. Dim edress As String) Sets the variable “edress” to be a string data type that we can use later in the module.
- X = 2 informs the program that the data starts on row 2, and is used subsequently to move to the next row (row 1 holds the field names/column titles)
- The code follows a “Do While” command, the code between this command and the “Loop” command further down will execute until the logic next to the “Do while” command is true and we hit a blank row, at which point the program will move past the Loop command.
- X = x + 1 ensures that the loop moves to the next value
- Using a single quote before text ensures that these descriptions are “commented out” this is a really useful way to include notes about what each step of your program is trying to achieve so it is easier to understand and follow.
Please note: In the code, the .send Command is commented out. This is a good practice technique and means that you can display and review your messages effectively to test them. Once you are happy with the outcome, remove the “ ‘ “ in front of the send command so the emails can actually be distributed.
This the basic outline for sending emails from Excel to Outlook. In the later examples, we will be starting with this code and making changes and additions.
Sub Send_email_fromexcel() Dim edress As String Dim subj As String Dim message As String Dim outlookapp As Object Dim outlookmailitem As Object Dim x As Integer Dim name As String X = 2 Do While Sheet1.Cells(x, 1) <> “” ‘calling outlook Set outlookapp = CreateObject(“Outlook.Application”) Set outlookmailitem = outlookapp.createitem(0) Applocation.DisplayAlerts = FALSE ‘defining your email address, your name, And your subject edress = Sheet1.Cells(x, 2) Name = Sheet1.Cells(x, 1) Subj = “Up Coming Excel Webinar” ‘making the email With outlookmailitem .To = edress .cc = “” .bcc = “” .Subject = subj .body = “Hello” & “ ” & vbCrLf & Name & vbCrLf & “Announcing a New Webinar” & vbCrLf & “Best Regards” .display ‘.send End With edress = “” X = x + 1 Loop ‘clear your fields Set outlookapp = Nothing Set outlookmailitem = Nothing End Sub
Use F8 to run your code in debug mode and go through step by step to ensure that it is correct and the email output appears as you intend.
When you have checked your code, click the little blue arrow on the top of your Visual Basic Screen or press F5 to run it.
If you have keyed this in correctly the result should produce emails similar to the one below.
Moving on from our initial basic example, we’re going to look at including a personalized attachment in the email.
Creating an email with a different attachment for each email is an effective method of sending out statements each month – for example, where each recipient needs to receive the information that is relevant to themselves.
Make the task easier by collating all the files that need to be attached in the same folder.
In the “A” column, we have email addresses, in the “B” column, we have subj and in the “C” column, we have the name of the file that we would like to attach.
Please note VBA is case sensitive, so you need to get the name of the file exactly right. Any type of file can be attached in this way, so you can send .pdf, .xls, .docs etc.
Click in the address bar of the Windows explorer file folder to display the path to that folder. Copy that path and when you get to the relevant place in the code ensure you define the path as…
Path = “place the copied address between the quotation marks \”
Ensure that the end of the address for the path has \”
The code follows the same structure as our earlier example with an additional section to include the attachment to the email.
You will notice there are a few more Variables needed this time that are declared with “Dim” at the top of the code with the others, these are needed in order to build up the correct path to the file for the attachment for each of the emails in the list,
Sub Send_email_fromexcel() Dim edress As String Dim subj As String Dim message As String Dim x As Integer Dim outlookapp As Object Dim outlookmailitem As Object Dim myAttachments As Object Dim path As String Dim attachment As String Dim filename As String X = 2 Do While Sheet1.Cells(x, 1) <> “” Set outlookapp = CreateObject(“Outlook.Application”) Set outlookmailitem = outlookapp.createitem(0) Set myAttachments = outlookmailitem.Attachments ‘define your path For the attachment Path = “c:\Users\Barb\Documents\statements\” edress = Sheet1.Cells(x, 1) subj = Sheet1.Cells(x, 2) filename = Sheet1.Cells(x, 3) Attachment = path + filename Outlookmailitem.To = edress Outlookmailitem.cc = “” Outlookmailitem.bcc = “” Outlookmailitem.Subject = subj Outlookmailitem.body = “Please find your statement attached” & vbCrLf & “Best Regards” myAttachments.Add (attachment) Outlookmailitem.display Outlookmailitem.send ‘clear your email address edress = “” X = x + 1 Loop ‘clear your fields Set outlookapp = Nothing Set outlookmailitem = Nothing End Sub
The difficulty is trying to present the excel data in an easy to read format and potentially include headings and an element of formatting.
To make it easier to read, it’s a good idea to:
- add borders all-around your data (you can find the borders option from the home tab, font grouping, select all borders)
- format the heading row with a background color
NB: You should do this in the spreadsheet manually before using the code to copy it across.
How the Excel sheet might look
The code for this has a few changes from the previous examples and is going to call up some Word VBA using the xlinpsect and pageEditor Objects as per the code below. The Wordeditor is what enables us to paste with format instead of just plain text.
Before we write the code we will also need to know which cells we want to copy from our spreadsheet, in this example the range of D3:E12. The important bit here is to remember to put double quotation marks and brackets around the cell range as shown in the code below.
You will notice there is a .display done before and after the WordEditor code, both of these are needed for this to work.
Sub senddatawithborders () Dim edress As String Dim message As String Dim outlook As Object Dim newEmail As Object Dim pageEditor As Object Dim xinspect As Object Set outlook = CreateObject(“Outlook.Application”) Set newEmail = outlook.CreateITem(0) With newEmail .to = Sheet1.Range(“A2”).Text .cc = “” .bcc = “” .Subject = “Data” .Body = “Pleas Find requested information” & vbCrlF & “Best Regards” .display Set xInspect = newEmail.getInspector Set pageEditor = xlinspect.wordEditor Sheet1.range (“D3:E12”).Copy pageEditor.Application.Selection.Start = Len(.Body) pageEditor.Application.Selection.end = pageEditor.Application.Selection.start pageEditor.Application.Selection.PasteAndFormat (wdFormatPlainText) .display .send Set PageEditor = Nothing Set xInspect = Nothing End With Set newEmail = Nothing Set outlook = Nothing End Sub
- It is difficult to get any text to display below your data. The excerpt from the excel spreadsheet is normally displayed underneath the text you programmed to include.
- Automatic signatures don’t seem to be displayed in the email when excel data is included using this method