The beauty of VBA is we can reference other Microsoft object like PowerPoint, Word, and Outlook. We can create beautiful presentations, we can work with Microsoft word document and finally, we can send the emails as well. Yes, you heard it right, we can send emails from excel itself. This sounds awkward but at the same time puts a smile on our face as well. In this article, I will show you how to work with Microsoft Outlook object from excel using VBA coding. Read on…
How do we Reference Outlook from Excel?
Remember Outlook is an object and we need to set the reference to this in the object reference library. To set the Outlook object to reference follow below steps.
Step 1: Go to Visual Basic Editor.
Step 2: Go to Tools > Reference.
Step 3: In the below references object library scroll down and select “MICROSOFT OUTLOOK 14.0 OBJECT LIBRARY”.
Check the box of “MICROSOFT OUTLOOK 14.0 OBJECT LIBRARY” to make it available for Excel VBA.
Now we can access the VBA Outlook object from excel.
Write a Code to Send Emails from VBA Outlook from Excel
We can send the emails from excel through outlook app. For this, we need to write VBA codes. Follow the below steps to send the emails from Outlook.
Step 1: Create a sub procedure.
Option Explicit Sub Send_Exails() End Sub
Step 2: Define the variable as VBA Outlook.Application.
Option Explicit Sub Send_Exails() Dim OutlookApp As Outlook.Application End Sub
Step 3: The above variable reference to the VBA Outlook application. In the outlook, we need to send emails, so define another variable as Outlook.MailItem.
Option Explicit Sub Send_Exails() Dim OutlookApp As Outlook.Application Dim OutlookMail As Outlook.MailItem End Sub
Step 4: Now both the variables are object variables. We need to set them. First set the variable “OutlookApp” as New Outlook.Application.
Sub Send_Exails() Dim OutlookApp As Outlook.Application Dim OutlookMail As Outlook.MailItem Set OutlookApp = New Outlook.Application End Sub
Step 5: Now set the second variable “OutlookMail” as below.
Sub Send_Exails() Dim OutlookApp As Outlook.Application Dim OutlookMail As Outlook.MailItem Set OutlookApp = New Outlook.Application Set OutlookMail = OutlookApp.CreateItem(olMailItem) End Sub
Step 6: Now using With statement access VBA Outlook Mail.
Sub Send_Exails() Dim OutlookApp As Outlook.Application Dim OutlookMail As Outlook.MailItem Set OutlookApp = New Outlook.Application Set OutlookMail = OutlookApp.CreateItem(olMailItem) With OutlookMail End With End Sub
Now we can access all the items available with email items like “Body of the email”, “To”, “CC”, “BCC”, “Subject” and many more things.
Step 7: Now inside the with the statement we can see the IntelliSense list by putting a dot.
Step 8: First select the body format as olFormatHtml.
With OutlookMail .BodyFormat = olFormatHTML End With
Step 9: Now display the email.
With OutlookMail .BodyFormat = olFormatHTML .Display End With
Step 10: Now we need to write the email in the body of the email. For this select HtmlBody.
With OutlookMail .BodyFormat = olFormatHTML .Display .HTMLBody = "Write your email here" End With
Below is the example of the body of the email writing.
Step 11: After writing the email we need to mention the email id of the receiver. For this access “To”.
Step 12: Next mention for whom you want to CC the email.
Step 13: Now mention the BCC email id’s,
Step 14: Next thing is we need to mention the subject for the email we are sending.
Step 15: Now add attachments. If you want to send the current workbook as an attachment then use the attachment as This workbook
Step 16: Finally send the email by using the Send method.
Now, this code will send the email from your VBA outlook mail. Use below code to send emails from your outlook.
To use the below code you must set the object reference to “MICROSOFT OUTLOOK 14.0 OBJECT LIBRARY” under object library of Excel VBA
By setting the reference to the object library is called early binding. The reason why we need to set the reference to object library because without setting the object library as “MICROSOFT OUTLOOK 14.0 OBJECT LIBRARY” We cannot access the IntelliSense list of VBA properties and methods. This makes the writing of code difficult because you need to be sure of what you are writing in terms of technique and spellings.
Sub Send_Emails() 'This code is early binding i.e in Tools > Reference >You have check "MICROSOFT OUTLOOK 14.0 OBJECT LIBRARY" Dim OutlookApp As Outlook.Application Dim OutlookMail As Outlook.MailItem Set OutlookApp = New Outlook.Application Set OutlookMail = OutlookApp.CreateItem(olMailItem) With OutlookMail .BodyFormat = olFormatHTML .Display .HTMLBody = "Dear ABC" & "<br>" & "<br>" & "Please find the attached file" & .HTMLBody 'last .HTMLBody includes signature from the outlook. '<br> includes line breaks b/w two lines .To = "email@example.com" .CC = "firstname.lastname@example.org" .BCC = "email@example.com;firstname.lastname@example.org" .Subject = "Test mail" .Attachments = ThisWorkbook .Send End With End Sub
This has been a guide to VBA Outlook. Here we learn how to send emails from Outlook using VBA codes along with examples and download template. Below are some useful excel articles related to VBA –