How To Send Email Templates In 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.

👉 Here's an example, How to parse Outlook emails and show it in your Excel worksheet using VBA
Parsing Outlook emails 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.

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.

Please read this too ... 👉 A simple VBA macro to find and highlight Duplicate values in Excel
Find and highlight duplicates in Excel

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               Dim objOutlook As Object     Set objOutlook =            CreateObject("Outlook.Application")               Dim objEmail As Object     Set objEmail = objOutlook.CreateItem(olMailItem)      With objEmail         .to = "arunbanik21@rediffmail.com"         .Subject = "This is a test message from Arun Banik"         .Body = "Hi there"         .Display                 End With               Set objEmail = Nothing:    Set objOutlook = Nothing          ErrHandler:      End Sub

I have created two objects (objOutlook and objEmail) in the above macro.

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 = "arunbanik21@rediffmail.com"     .Subject = "This is a test message from Arun"     .Body = "Hi there"     .Send             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 = "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

👉 Here's another exmaple, I am sure you will like. How to send emails to Multiple recipients from your Excel workbook using VBA and Outlook.

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 VBA. It is very simple. Try working with other properties too.

Thanks for reading. ☺

← Previous Next →