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.
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.
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.
Add a Button Control
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.
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 for example).
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.
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
We will now write the macro (a program) to send emails from our Excel workbook.
Double click the command button (which we have added on our worksheet, see above), 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 (or any higher version like 16.0 etc.), 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 = "firstname.lastname@example.org" .Subject = "This is a test message from Arun Banik" .Body = "Hi there" .Display ' Display the message in Outlook. End With ' CLEAR. Set objEmail = Nothing: Set objOutlook = Nothing ErrHandler: ' End Sub
01) Object objOutlook: Using the CreateObject() function, I have initialized Outlook. I can now access the 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.
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 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 = "email@example.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 = "firstname.lastname@example.org" .CC = "email@example.com" .BCC = "firstname.lastname@example.org" .Subject = "This is a test message from Arun" .Body = "Hi there" .Attachments.Add ("e:\report.doc") .Send End With
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 VBA. It is very simple. Try working with other properties too.
Thanks for reading. ☺