Send Email (with Attachments) from Excel using VBA and Outlook

Last updated: 06th May 2024

We often use Microsoft Office Outlook application to manage emails, contacts etc. from home and office. It is arguably one of the safest and secure ways to manage confidential emails. It has many useful features. Here in this article, I am going to show you how to send emails from Excel step-by-step using VBA and Outlook.

Although you can send and receive emails from Outlook directly, you can actually automate the process using a simple macro.

Get Access to Microsoft Outlook in Excel using VBA

There are two ways you can access Outlook properties and methods using VBA in Excel.

1) You can add a reference of Outlook object in your VBA project (This is called EarlyBinding.)

Follow these steps to add a reference of the Outlook object in your VBA project

In the top menu find Tools and choose References….

In the References dialog box, find Microsoft Outlook 16.0 Object Library, click the Check-box and press OK.

Microsoft Outlook 16.0 object library reference

Note: If you are using Office 2007, add Microsoft Outlook 12.0 Object library.

2) By creating an instance of Outlook using CreateObject() method. (This is called LateBinding.)

To get access to Outlook methods and properties, you'll have to first create an instance of Outlook in VBA. Next, initialize Outlook using CreateObject().

Dim objOutlook as Object
Set objOutlook = CreateObject("Outlook.Application")

I am using the 2nd method in my example.

Remember: You must first configure Microsoft Office Outlook in your computer. Else, the code example that I am going to show you here will not produce the desired result.

Send email from Excel

Copy the macro in your VBA editor.

Option Explicit

Private Sub send_email

    ' Use this code if you have added a Reference of Outlook in your VBA project.
    '    Dim objOutlook As Outlook.Application
    '    Set objOutlook = Outlook.Application
    '
    '    Dim objEmail As Outlook.MailItem
    '    Set objEmail = objOutlook.CreateItem(olMailItem)

    Dim objOutlook As Object        ' Set outlook application object.
    Set objOutlook = CreateObject("Outlook.Application")
    
    ' Create email object.
    Dim objEmail As Object
    Set objEmail = objOutlook.CreateItem(olMailItem)

    With objEmail
        .to = "xyz@email.com"   ' add a real email address.
        .Subject = "This is a test message from Arun Banik"
        .Body = "Hi there"
        .Send   		' Send the message.
    End With
    
    ' Clear objects.
    Set objEmail = Nothing:    Set objOutlook = Nothing
End Sub

If everything is right, then it will send an email with a subject and a message saying "Hi there".

As you can see, I have created two objects (objOutlook and objEmail), one for outlook application and another for creating email.

01) Object objOutlook: Using the CreateObject() function, I have initialized Outlook. I can now access email properties with the CreateItem() method.

02) Object objEmail: Using this object, I'll create an Outlook item. This will give access to properties such as to, body and subject etc.

Using .Display property to display message before sending email

Here's another important feature that you can use. You can actually display or see the email message like body, subject, attachements etc. in Outlook before sending the email.

To do this simply comment the .Send property and add .Display property. Please remember, the properties are case-sensitive.

With objEmail
    .to = "xyz@email.com"
    .Subject = "This is a test message from Arun Banik"
    .Body = "Hi there"
    ' .Send
    .Display   		' Display the message in Outlook.
End With

This will now open Ms-Outlook application and shows the message with all the parameters. You can see the To address, with the Subject and Body. Click the Send button (in outlook) to email the message to its address.

Add Attachments, CC and BCC in Excel

Similarly, you can test with other important properties such, CC, BCC, Attachments etc.

With objEmail
    .To = "xyz@email.com"
    .CC = "arun@mail.com"
    .BCC = "arun@hotmail.com"
    .Subject = "This is a test message from Arun"
    .Body = "Hi there"
    .Attachments.Add ("e:\report.doc")
    .Send
End With

Conclusion

Now you know how to send emails from Excel using VBA and Outlook. Here we also learned how to add Attachments, Blind Carbon Copy (or BCC), CC etc., using VBA. It is very simple. Try working with other properties too.

← PreviousNext →