VBA Outlook

Article byJeevan A Y
Edited byJeevan A Y
Reviewed byDheeraj Vaidya, CFA, FRM

We have seen VBA in Excel and how we automate our tasks in Excel by creating macros. In Microsoft Outlook, we also have a reference for VBA and can control Outlook using VBA. It makes our repeated tasks in Outlook easier to automate. Like Excel, we need to enable the Developer feature to use VBA in Outlook.

VBA Outlook

The beauty of VBA is we can reference other Microsoft objects like PowerPoint, Word, and Outlook. We can create beautiful presentations. We can work with Microsoft word documents. Finally, we can send the emails as well. Yes, you heard it right. We can send emails from Excel. It sounds awkward but, at the same time, puts a smile on our faces as well. This article will show you how to work with Microsoft Outlook objects from excel using VBA codingVBA CodingVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more. Read on.

VBA Outlook

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Outlook (wallstreetmojo.com)

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

How do we Reference Outlook from Excel?

Remember, Outlook is an object. Therefore, we need to set the reference to this in the object reference library. To set the Outlook object to reference, follow the below steps.

Step 1: Go to Visual Basic Editor.

Step 2: Go to Tools > Reference.

Reference Outlook 1

Step 3: In the below references, object library, scroll down, and select “MICROSOFT OUTLOOK 14.0 OBJECT LIBRARY.”

Check the “MICROSOFT OUTLOOK 14.0 OBJECT LIBRARY” box to make it available for Excel VBA.

Reference Outlook 1-1

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 the outlook app. For this, we need to write VBA codes. Then, follow the below steps to send the emails from Outlook.

You can download this VBA Outlook to Excel Template here – VBA Outlook to Excel Template

Step 1: Create a sub procedure.

Code:

Option ExplicitOption ExplicitVBA option explicitly makes a user mandatory to declare all the variables before using them; any undefined variable will throw an error while coding execution. We can enable it for all codes from options to require variable declaration.read more

Sub Send_Exails()

End Sub
VBA Outlook Step 1

Step 2: Define the variable as VBA Outlook.Application.

Code:

Option Explicit

Sub Send_Exails()

  Dim OutlookApp As Outlook.Application

End Sub
VBA Outlook Step 2

Step 3: The above variable reference the VBA Outlook application. In Outlook, we need to send emails, so define another variable as Outlook.MailItem.

Code:

Option Explicit

Sub Send_Exails()

  Dim OutlookApp As Outlook.Application
  Dim OutlookMail As Outlook.MailItem

End Sub
VBA Outlook Step 3

Step 4: Now, both variables are object variables. We need to set them. First, set the variable “OutlookApp” as New Outlook.Application.

Code:

Sub Send_Exails()

  Dim OutlookApp As Outlook.Application
  Dim OutlookMail As Outlook.MailItem

  Set OutlookApp = New Outlook.Application

End Sub
VBA Outlook Step 4

Step 5: Now, set the second variable, “OutlookMail,” as below.

Set OutlookMail=OutlookApp.CreateItem(olMailItem)

Code:

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
VBA Outlook Step 5

Step 6: Now, using With statement access VBA Outlook Mail.

Code:

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
VBA Outlook Step 6

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: Inside the statement, we can see the IntelliSense list by putting a dot.

VBA Outlook Step 7

Step 8: First, select the body format as olFormatHtml.

Code:

With OutlookMail
    .BodyFormat = olFormatHTML

End With
VBA Outlook Step 8

Step 9: Now display the email.

Code:

With OutlookMail
  .BodyFormat = olFormatHTML
  .Display
End With
VBA Outlook Step 9

Step 10: We need to write the email in the body of the email. For this, select HtmlBody.

Code:

With OutlookMail
  .BodyFormat = olFormatHTML
  .Display
  .HTMLBody = "Write your email here"
End With
VBA Outlook Step 10

Below is the example of the body of the email writing.

Step 11

Step 11: We need to mention the receiver’s email ID after writing the email. For this access, “To.

Step 12

Step 12: Next, mention for whom you want to CC the email.

Step 13

Step 13: Now, mention the BCC email ids,

Step 14

Step 14: Next, we need to mention the subject of the email we are sending.

Step 15

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

Step 16: Finally, send the email by using the Send method.

Step 17

Now, this code will send the email from your VBA outlook mail. Use the below VBA code to send emailsVBA Code To Send EmailsWe can use VBA to automate our mailing feature in Excel to send emails to multiple users at once. To use Outlook features, we must first enable outlook scripting in VBA, and then use the application method.read more from your outlook.

To use the below code, you must set the object reference to “MICROSOFT OUTLOOK 14.0 OBJECT LIBRARY” under the object library of Excel VBA.

The library is called early binding by setting the reference to the object. We need to set the reference to the 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. It makes writing code difficult because you need to be sure of what you are writing in terms of technique and spelling.

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 breaksLine BreaksLine break in excel means inserting a new line in any cell value. To insert a line break, press ALT + Enter. As we insert a line break, the cell's height also increases as it represents the data.read more b/w two lines
    .To = "[email protected]"
    .CC = "[email protected]"
    .BCC = "[email protected];[email protected]"
    .Subject = "Test mail"
    .Attachments = ThisWorkbook
    .Send
  End With

End Sub

Recommended Articles

This article has been a guide to VBA Outlook. Here, we learn how to send emails from Outlook using VBA codes, examples, and a downloadable template. Below are some useful Excel articles related to VBA: –