Visit my Youtube Channel
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