Learn To Send Outlook Emails Using Excel VBA [Tutorial]

Home

Note: This webpage is designed to accompany the Youtube video posted above. The video offers detailed explanation on all topics; while this webpage will serve as a repository for the code presented in the video.

This tutorial will cover various aspects such as how to connect to Outlook through Excel VBA to multiple scenarios for attaching files to the email. The last section of code shows how to loop over data and send multiple emails with one attachment each.

Blank Email Template

To send an email via Outlook and to access the various features of Outlook, we need to create a reference to the Outlook Object library.

In the VB Editor, go to Tools, then References:

You shouldn’t be able to see any reference to an Outlook library.

We need to search and select the Outlook library. Scroll down all the way till you see Microsoft, and then keep scrolling till you find Microsoft Outlook X.X Object Library. The version used in this tutorial is 16.0. Yours might be different. It shouldn’t matter. Select the library and Press OK.

Now, you are ready to create an email template and access its various properties and methods. First, we create the email object. And then, we can call the display method of that email object to display a blank email template.

Sub Basic_Email_Vanilla()

Dim oApp As Outlook.Application
Set oApp = New Outlook.Application
Dim oMail As Outlook.MailItem
Set oMail = oApp.CreateItem(olMailItem)

oMail.Display

‘ We will not set objects to Nothing in the subsequent codes, 
‘ as VBA drops the object from memory when we exit the sub.

Set oApp = Nothing
Set oMail = Nothing

End Sub

Email in Plain Text Format

There are 2 parts to creating an email. First, create the email object and next, fill the properties and methods. Plain text email will not display logos and formatting. Hence, we will use HTML format for subsequent videos.

Sub Basic_Email_w_Properties_Plain_Text()
Dim oApp As Outlook.Application
Set oApp = New Outlook.Application
Dim oMail As Outlook.MailItem
Set oMail = oApp.CreateItem(olMailItem)

With oMail
    .BodyFormat = olFormatPlain
    .Display
    .To = "[email protected]"
    .CC = ""
    .BCC = ""
    .Subject = "Test Email"
    .Body = "Hi There," & vbNewLine & _
            "This is a Test Email." & vbNewLine & _
            "Regards," & .Body
    '.Send
    .Display
End With
End Sub

Email in HTML Format

Sub Basic_Email_w_Properties_HTML()

Dim oApp As Outlook.Application
Set oApp = New Outlook.Application
Dim oMail As Outlook.MailItem
Set oMail = oApp.CreateItem(olMailItem)

With oMail
    .Display
    .To = "[email protected]"
    .CC = ""
    .BCC = ""
    .Subject = "Test Email"
    .HTMLBody = "Hi There,<br>" & _
            "This is a Test Email.<br>" & _
            "Regards," & .HTMLBody
    '.Send
    .Display
End With

End Sub

Early Binding/ Late Binding

Early Binding allows us to access to Intellisense so that we don’t have to type code from memory. Code shown in Code 1 Section follows Early Binding i.e. set a reference to the Outlook library and create our Email directly as an Outlook object. However, your macro may not work if someone else tries to run it, if they don’t have the same version of Outlook. To avoid this, we would need to make our code version independent as shown below. Tip: When creating your own macro, build the code using Early Binding and then, convert it to Late Binding at the end.

Sub Basic_Email_Lat_Binding()

Dim oApp As Object
Set oApp = CreateObject("Outlook.Application")
Dim oMail As Object
Set oMail = oApp.CreateItem(0)

oMail.Display

End Sub

Add Current Workbook as Attachment

Use ActiveWorkbook.FullName to get the file path of the current workbook you are working on. This code will just attach the last saved version of your current workbook. To attach the a copy that includes any changes made since, use the code in the next section.

Sub Add_Attachment_Current_Workbook()

Dim oApp As Outlook.Application
Set oApp = New Outlook.Application
Dim oMail As Outlook.MailItem
Set oMail = oApp.CreateItem(olMailItem)

Dim thisFilePath As String
thisFilePath = ActiveWorkbook.FullName

With oMail
    .Display
    .To = "[email protected]"
    .CC = ""
    .BCC = ""
    .Subject = "Test Email"
    .HTMLBody = "Hi There,<br>" & _
            "This is a Test Email.<br>" & _
            "Regards," & .HTMLBody
    .Attachments.Add thisFilePath
    '.Send
    .Display
End With

End Sub

Add COPY OF Current Workbook as Attachment

We can save a copy of the current workbook in a temporary folder which can be accessed using the Environ() function. Since we source the file path using the Environ() function, we just need to add the current workbook name using ActiveWorkbook.Name. Always check if the file exists using Dir() function and remember to delete off the file once the email has been sent, using Kill() function.

Sub Add_Attachment_Copy_Of_Current_Workbook()

Dim oApp As Outlook.Application
Set oApp = New Outlook.Application
Dim oMail As Outlook.MailItem
Set oMail = oApp.CreateItem(olMailItem)

Dim thisFilePath As String
thisFilePath = Environ("temp") & "\" & ActiveWorkbook.Name

If Dir(thisFilePath) = "" Then
    ActiveWorkbook.SaveCopyAs thisFilePath
    Else
    Kill thisFilePath
    ActiveWorkbook.SaveCopyAs thisFilePath
End If

With oMail
    .Display
    .To = "[email protected]"
    .CC = ""
    .BCC = ""
    .Subject = "Test Email"
    .HTMLBody = "Hi There,<br>" & _
            "This is a Test Email.<br>" & _
            "Regards," & .HTMLBody
    .Attachments.Add thisFilePath
    '.Send
    .Display
End With

Kill thisFilePath
End Sub

Attach File from Folder

The most common scenario will be to attach files stored in the Windows file directory. You can attach any type of file e.g. Excel, PDF, Powerpoint etc. as long as you specify the full file path. To attach multiple files, you’ll need to repeat the Attachments.Add method.

Sub Add_Attachment_of_Existing_File()

Dim oApp As Outlook.Application
Set oApp = New Outlook.Application
Dim oMail As Outlook.MailItem
Set oMail = oApp.CreateItem(olMailItem)

Dim newFilePath As String
newFilePath = "C:\Youtube\Outlook Tutorial 01\Outlook\Invoices\MB10023.pdf"

If Dir(newFilePath) = "" Then
    MsgBox "File doesn't exist"
    Exit Sub
End If

With oMail
    .Display
    .To = "[email protected]"
    .CC = ""
    .BCC = ""
    .Subject = "Test Email"
    .HTMLBody = "Hi There,<br>" & _
            "This is a Test Email.<br>" & _
            "Regards," & .HTMLBody
    .Attachments.Add newFilePath
    '.Send
    .Display
End With

End Sub

Attach Worksheet from Current Workbook

You can also, attach a worksheet as a file by saving the worksheet as a separate Excel file. For this, we can copy the source worksheet and paste it into a new workbook.

Sub Add_Attachment_Worksheet()

Dim oApp As Outlook.Application
Set oApp = New Outlook.Application
Dim oMail As Outlook.MailItem
Set oMail = oApp.CreateItem(olMailItem)

Dim newFilePath As String
newFilePath = Environ("temp") & "\" & "Datafile.xslx"

If Dir(newFilePath) <> "" Then
    Kill newFilePath
End If

Dim wbDest As Workbook
Set wbDest = Workbooks.Add

wsExport.Copy After:=wbDest.Worksheets("sheet1")

wbDest.SaveCopyAs newFilePath
wbDest.Close savechanges:=False

With oMail
    .Display
    .To = "[email protected]"
    .CC = ""
    .BCC = ""
    .Subject = "Test Email"
    .HTMLBody = "Hi There,<br>" & _
            "This is a Test Email.<br>" & _
            "Regards," & .HTMLBody
    .Attachments.Add newFilePath
    '.Send
    .Display
End With

Kill newFilePath

End Sub

Read Email Properties from Worksheet

A common scenario is where you have data such as email Id, attachment file name etc. saved in your excel file and you want to send emails based on that information. In this code, we will learn how to read one line of data from a worksheet and send one email using that info.

Sub Basic_Email_Read_Properties_From_Worksheet()

Dim oApp As Outlook.Application
Set oApp = New Outlook.Application
Dim oMail As Outlook.MailItem
Set oMail = oApp.CreateItem(olMailItem)

Dim attachPath As String
attachPath = wsData.Range("H1").Value & "\"

Dim emTo As String, emFirstName As String, emBody As String, emAttach As String

emTo = wsData.Range("A2").Value
emFirstName = wsData.Range("B2").Value
emBody = "Hi " & emFirstName & ",<br><br>" & _
        "Please find our latest statement attached.<br><br>" & _
        "Regards,"
emAttach = wsData.Range("D2").Value
emAttach = attachPath & emAttach

With oMail
    .Display
    .To = emTo
    .CC = ""
    .BCC = ""
    .Subject = "Statement of Overdue Invoices (Food Jar Co)"
    .HTMLBody = emBody & .HTMLBody
    .Attachments.Add emAttach
    .Display
End With

End Sub

Create Separate Sub to Send Email

As our code becomes bigger or more complex, we should consider splitting our code into separate sub procedures to improve readability and maintenance. Here, we will create a sperate private sub procedure to send emails. We will assign our dynamic email properties as variables in the main sub-procedures pass these as parameters into our private email sub.

Sub Send_Email_from_Seperate_Sub()

Dim emTo As String, emFirstName As String
emTo = wsData.Range("A2").Value
emFirstName = wsData.Range("b2").Value

Call Send_Email(emTo, emFirstName)


End Sub

Private Sub Send_Email(psTo As String, psFirstName As String)

Dim oApp As Outlook.Application
Set oApp = New Outlook.Application
Dim oMail As Outlook.MailItem
Set oMail = oApp.CreateItem(olMailItem)

Dim psBody As String
psBody = "Hi " & psFirstName & ",<br><br>" & _
        "Please find our latest statement attached.<br><br>" & _
        "Regards,"

With oMail
    .Display
    .To = psTo
    .CC = ""
    .BCC = ""
    .Subject = "Statement of Overdue Invoices (Food Jar Co)"
    .HTMLBody = psBody & .HTMLBody
    .Display
End With

End Sub

Send Multiple Emails with One Attachment Each

Here, we will combine what we have learned so far to send separate emails to multiple recipients based on data that we read from our worksheet.

Sub Send_Mulitple_Emails_Single_Attach()
Application.ScreenUpdating = False

Dim attachPath As String
attachPath = wsData.Range("H1").Value & "\"

Dim lrow As Long
lrow = wsData.Range("a1").CurrentRegion.Rows.Count

Dim emTo As String, emFirstName As String, emAttach As String
Dim i As Long

For i = 2 To lrow
    emTo = wsData.Range("a" & i).Value
    emFirstName = wsData.Range("b" & i).Value
    emAttach = wsData.Range("d" & i).Value
    emAttach = attachPath & emAttach
    Call Send_Email2(emTo, emFirstName, emAttach)
Next i

Application.ScreenUpdating = True
End Sub


Private Sub Send_Email2(psTo As String, psFirstName As String, psAttach As String)

Dim oApp As Outlook.Application
Set oApp = New Outlook.Application
Dim oMail As Outlook.MailItem
Set oMail = oApp.CreateItem(olMailItem)

Dim psBody As String
psBody = "Hi " & psFirstName & ",<br><br>" & _
        "Please find our latest statement attached.<br><br>" & _
        "Regards,"

With oMail
    .Display
    .To = psTo
    .CC = ""
    .BCC = ""
    .Subject = "Statement of Overdue Invoices (Food Jar Co)"
    .HTMLBody = psBody & .HTMLBody
    .Attachments.Add psAttach
    '.Send
    .Display
End With

End Sub