WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Outlook

VBA Outlook

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

We have seen VBA in excel and how we automate our tasks in excel with creating macros, in Microsoft Outlook we also have a reference for VBA and using which we can control outlook using VBA, this makes our repeated tasks in outlook easier to automate, and similar to 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 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…

VBA Outlook

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 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 box of “MICROSOFT OUTLOOK 14.0 OBJECT LIBRARY” 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. 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 Explicit

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 to the VBA Outlook application. In the 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.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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: Now, inside the with 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: Now, 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: After writing the email, we need to mention the email id of the receiver. 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 thing is we need to mention the subject for 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 emails 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.

By setting the reference to the object, the 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 = "abc@gmail.com"
    .CC = "sdf@gamil.com"
    .BCC = "hello@gamil.com;hi@gmail.com"
    .Subject = "Test mail"
    .Attachments = ThisWorkbook
    .Send
  End With

End Sub

Recommended Articles

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

  • Excel VBA ThisWorkbook
  • VBA ArrayList
  • VBA For Next Loop
  • List of String Functions in VBA
  • VBA Write Text File
21 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Outlook to Excel Template

Special Offer - VBA Training Course (6 courses, 35+ hours video) View More