How To Send Basic Outlook Email [Excel VBA]

In this tutorial, we will create a simple procedure to send an outlook email from excel using VBA.

Note: We will be looking at how to send an email in HTML format which is most common format used for office communications.

There are 3 steps to achieve this.

  1. Add a Reference to Outlook
  2. Create an Email Object
  3. Fill in the Email Details and Send

Send is a method of the email object. So, we will send the email in Step 3.

Before we continue, please also check out our detailed Youtube tutorials  on sending Outlook emails via Excel VBA.

  1. Tutorial 1: Learn To Send Outlook Emails
  2. Tutorial 2: Handle Errors While Sending Outlook Emails
  3. Tutorial 3: Send Outlook Emails with Multiple Attachments

Step 1: Add a Reference to Outlook

In Excel VBA, 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.

Let’s go to Tools, and then, References.

Set VBA Tools Reference for Sending Outlook Emails

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

Set VBA Reference Outlook Object Library for Sending Outlook Emails

Now, to find the Outlook reference, lets scroll down till we first see Microsoft, and lets keep scrolling till we find the Outlook Object Library. My version is 16. Yours might be different. It doesn’t matter though. Lets select this and press OK to exit out.

Set VBA Reference Outlook Object Library for Sending Outlook Emails

Step 1 is done.

A quick side note: Intellisense

The biggest advantage of setting references is that we get access to Intellisense which makes it much easier to write code. Intellisense is basically autocomplete.

Step 2: Create an Email Object

The step is split into 2 parts. First, we will connect to Outlook by creating a new instance of the Outlook Application. You can imagine this as before creating an Outlook Email, we first need to open Outlook.

' Connect to Outlook
Dim oApp As Outlook.Application
Set oApp = New Outlook.Application

And then, we will use this new instance of Outlook to create an email object.

' Create an email Object
Dim oMail As Outlook.MailItem
Set oMail = oApp.CreateItem(olMailItem)

And that’s our email object. Which is nothing but a blank email template. And we now have full access to all its methods and properties.

Step 3: Fill in the Email Details and Send

We can start manipulating the various properties of the email such as To, Subject, Body etc. Since there are multiple properties, we can use the With Statement.

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

The Send method is commented out. Once you are satisfied that the email is displaying correctly, you can remove Display and comment out the .Send method

Also, the Add attachment method is commented out. Let’s see how to add attachments. We need to provide the full path of the file that we want to attach.

To attach the current workbook, use the FullName property of this Workbook.

.Attachments.Add ThisWorkbook.FullName

To attach a file from a folder, provide the full folder path.

.Attachments.Add "C:\Youtube\Outlook Tutorial 01\Outlook\Invoices\MB10023.pdf"

To attach multiple files, you will need to repeat the add method.

.Attachments.Add "C:\Youtube\Outlook Tutorial 01\Outlook\Invoices\MB10023.pdf"
.Attachments.Add "C:\Youtube\Outlook Tutorial 01\Outlook\Invoices\MB10024.pdf"

We can use a for loop for multiple attachments, where we iterate over a range of files and attach each one. But, we will leave that for another session.

Lastly, as good practice, you could empty the email and outlook objects on exit to clear memory space.

Set oApp = Nothing
Set oMail = Nothing

This is not necessary though and VBA will do that for you when you exit of the procedure.

Full Code to Send Email with Current Workbook as Attachment

Sub BasicEmail()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Step 1: Add a Reference to Outlook
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Tools -> References -> Microsoft Outlook X.X Object Library
    
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Step 2: Create an Email Object
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Connect to Outlook
Dim oApp As Outlook.Application
Set oApp = New Outlook.Application
' Create an email Object
Dim oMail As Outlook.MailItem
Set oMail = oApp.CreateItem(olMailItem)

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Step 3: Fill in the Email Details and Send
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
With oMail
    .To = "[email protected]"
    .CC = ""
    .BCC = ""
    .Subject = "Test Email"
    .HTMLBody = "Hi There,<br>" & _
            "This is a Test Email.<br>" & _
            "Regards,"
    '.Attachments.Add ThisWorkbook.FullName
    '.Send
    .Display
End With

Set oApp = Nothing
Set oMail = Nothing

End Sub

Related Posts