Send Email from an Excel File using VBA Macro and Outlook

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. However, it has many other useful features too. In this article, I am going to show you how to send emails from an Excel file using VBA macro and Office Outlook.

Send Email from Excel using VBA and Outlook

Using VBA, you can write small programs that will transform Excel worksheets into a very dynamic application. I’ll show you how to automate Office Outlook using VBA. Automation is a process where an application gets access to methods and properties of another application.

Must Read: How to Parse Outlook Emails and Show it in Excel Worksheet using VBA

To get access to Outlook methods and properties, we have to create an instance of Outlook. To initialize the Outlook application, we will use the CreateObject function to create an object of the application. You know how to initialize, don’t you?

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

Important Note: Before reading this article further, 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.

Before we start writing our VBA code, we’ll first add a Command Button (an ActiveX Control) on the Excel sheet. The button’s click event will trigger the send mail procedure. You can have access to ActiveX Controls from the Developer tab.

Also Read: Find and Highlight Duplicate Values in Excel using VBA (macro)

You can find the Developer tab on the top of your Excel sheet, usually next to the View tab. If the Developer tab is not available, then you have to add it to the top tabs, manually.

Click the Developer tab, in the Controls group find Insert, and click it. It will show you a list of Form Controls and ActiveX Controls, click the Command Button under ActiveX Controls, and add it to the Excel sheet (sheet1).

You can now place the command button anywhere on the sheet. Inside the Controls group, find Design Mode (next to Insert) and click it. This will allow you to edit the button control on the sheet itself. Right click the button, find CommandButton Object tab and choose Edit. Change the name of the button to Send Mail.

ActiveX Command Button in Excel

Remember: Anytime you want to change the name or any other property of the CommandButton, first choose the Design Mode and make necessary changes.

Display Message using .Display Property

Finally, we will write our program to send emails from Excel. Double click the command button, and you see the CommandButton1_Click() procedure, where you will add the code. In the top menu find Tools and choose References…

In the References dialog box, find Microsoft Outlook 12.0 Object Library (higher version) and click the check box and press OK.

Option Explicit

Private Sub CommandButton1_Click()
    On Error GoTo ErrHandler
    
    ' SET Outlook APPLICATION OBJECT.
    Dim objOutlook As Object
    Set objOutlook = CreateObject("Outlook.Application")
    
    ' CREATE EMAIL OBJECT.
    Dim objEmail As Object
    Set objEmail = objOutlook.CreateItem(olMailItem)

    With objEmail
        .to = "webadmin@encodedna.com"
        .Subject = "This is a test message from Arun Banik"
        .Body = "Hi there"
        .Display   		' DISPLAY MESSAGE.
    End With
    
    ' CLEAR.
    Set objEmail = Nothing:    Set objOutlook = Nothing
        
ErrHandler:
    '
End Sub

In the above code, we are creating two objects.

01) Using the CreateObject method, we have initialized Outlook. Now, we can access the email properties with the CreateItem() method.

02) The second object, objEmail, will create an Outlook item. This will give access to properties such as to, body and subject etc.

We are still not sending the email. What exactly would the code do?

Look at the code carefully, you will see the .Display property. This will show the message in the Outlook application, with all the necessary parameters. You can now see the To address, with the Subject and Body. Click the Send button to email the message to its address.

This is a good way to check the properties and its contents that we have assigned in our VBA code, before mailing it. Once convinced with our code, we will add the .send property, to email the message at the click of the button.

Send Message using .Send Property

All you need to do is change the .Display property with .Send property.

With objEmail
    .to = "webadmin@encodedna.com"
    .Subject = "This is a test message from Arun"
    .Body = "Hi there"
    .Send  		' SEND THE MESSAGE.
End With

Add Attachments, CC and BCC in Excel

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

With objEmail
    .To = "webadmin@encodedna.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

Also Read: How to add Hyperlinks in Excel using VBA macro?

Conclusion

Now you know how to send emails from an Excel file using VBA and Outlook. Here we also learned how to add Attachments, Blind Carbon Copy or BCC etc. using Excel VBA. It is very simple. However, I recommend you to explore other properties and methods and do not forget to share your experience it with us here.

Thanks for reading.

Previous - How to Add Hyperlinks in Excel using VBA macroNext - VBA Split() Function – Split Cell Values with Carriage Returns to Multiple Columns using VBA Macro



Like this Article? Subscribe now, and get all the latest articles and tips, right in your inbox.

Enter your email id

Delivered by FeedBurner
Tweet this article Google+

Related Posts:

Join our Google Plus Community and be a part of a discussion!