Last updated: 22nd August 2022
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 dynamically using VBA and Outlook.
👉 You may like this... VBA code to send email from excel with table in body
Although you can send and receive emails from Outlook directly, you can actually automate the process using a simple macro.
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 in VBA. 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.
👉 You may also like this:
How to parse Outlook emails and show it in your Excel worksheet using VBA
Add Outlook Reference
First, we need add a reference of the Outlook object in VBA. 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.
We will now write the macro to send an email from our Excel workbook.
Option Explicit Private Sub send_email Dim objOutlook As Object Set objOutlook = CreateObject("Outlook.Application") Dim objEmail As Object Set objEmail = objOutlook.CreateItem(olMailItem) With objEmail .to = "" .Subject = "This is a test message from Arun Banik" .Body = "Hi there" .Send End With 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 = "" .Subject = "This is a test message from Arun Banik" .Body = "Hi there" .Display 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 = "" .CC = "" .BCC = "" .Subject = "This is a test message from Arun" .Body = "Hi there" .Attachments.Add ("e:\report.doc") .Send End With
👉 I am sure you will like
this: 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. ☺
← PreviousNext →