Linking Outlook to Excel to produce Mail outs

Barb Henderson

0 comments

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

For all of these scenarios, the basic requirements for sending email through outlook are

  • 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

To send email using this method, you will need to compile a simple list of names and email addresses in an excel spreadsheet.

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.

What the code looks like

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.

Attaching unique files to individual emails from a list in Excel

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.

Your excel sheet needs to look like this…

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.

How to determine the path to your file

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,

What the code looks like

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

What the Email will look like

Include data from an excel sheet into the emails

Rather than send an entire excel file as an attachment to a personalized email, you may only want to include a section or element of the data contained within the spreadsheet.

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

Create an email including data from Excel

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

What the email will look like

Difficulties with this solution

  • 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

0000-00-00 00:00:00


Leave a Reply

Your email address will not be published. Required fields are marked

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}