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

Last updated: 22nd August 2022

👉   You may like this... How to Email Multiple Tables in Excel as HTML table in Body using VBA?

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.

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

2) Or, 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 above (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.

👉 Related article: How to parse Outlook emails and show it in your Excel worksheet using VBA

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 = "arunbanik21@rediffmail.com"      ' Add your email too to check if it has send the email. 
        .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 = "arunbanik21@rediffmail.com"
    .Subject = "This is a test message from Arun Banik"
    .Body = "Hi there"
    ' .Send
    .Display   		' Display the message in Outlook.
End With

👉 Now you can send emails from your Excel worksheet with Table in Body. See this post.

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.

👉 Do you know you can send emails automatically on special occasions from Excel to multiple recipients? Check this out.

Add Attachments, CC and BCC in Excel

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

With objEmail
    .To = "arunbanik21@rediffmail.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

👉 Related article: How to send emails to Multiple recipients from your Excel workbook using VBA and Outlook.

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.

Happy coding. ☺

← PreviousNext →