Excel VBA Outlook Function

We work on lots & lots of Excel files on a daily basis and we send to many users on a daily basis. We write the same Message in the email daily and send that excel file. This gives us the scope of the automation of this task. You heard it right. This task of writing an email and sending the file can be automated with the help of VBA. The reason is that VBA can use a reference with different Microsoft Objects like outlook, word, PowerPoint, paint, etc.

So we can send the email with the help of VBA. Now I am sure you all will be excited to know how we can send an email with the help of VBA.

We will learn in this article on how to use the Outlook as Microsoft object from excel using VBA coding and how we can send an email including an attachment with the help of VBA.

How to Use Excel VBA Outlook Function?

To use VBA Outlook function, we need to do two things.

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

Reference Microsoft Outlook Object from Object Library in VBA.
Write VBA code to send emails in the VBA module.

#1 – Reference of Outlook from Excel

As you know Outlook is an object and we need to provide a reference to Outlook object. So there is an Object reference library in VBA which we need to use for reference.

Follow the below steps to use the Object Reference Library.

Step 1: In the Developer Tab click on Visual Basic to open the VB Editor.

Step 2: Go to Tools and then select References as shown in the below screenshot.

Step 3: Scroll down in the Reference Object library and select “Microsoft Outlook 16.0 Object Library” to make it available for Excel VBA.

Depending on the Microsoft office, the Outlook version may be different. In our case, it is 16.0. You can use “Microsoft Outlook 14.0 Object Library” if that is the version shown in your computer.

Check the box of Microsoft Outlook as shown in the above screenshot. Now we can use the Microsoft Outlook object in Excel VBA.

This process of setting the reference to “MICROSOFT OUTLOOK 16.0 OBJECT LIBRARY” is known as Early Binding. Without setting the object library as “MICROSOFT OUTLOOK 16.0 OBJECT LIBRARY” we cannot use the IntelliSense properties and methods of VBA which makes writing the code difficult.

#2 – Write a Code to Send Emails from VBA Outlook from Excel

Follow the below steps to write the VBA code to send email from outlook from Excel.

Step 1: Create a Sub Procedure by naming the macro. We will name macro as “send_email” as shown in the below screenshot.

Code:

Option Explicit

Sub Send_email()

End Sub

Step 2: Define the variable as Outlook. Application as shown in the below screenshot. This is the reference to the VBA Outlook Application.

Code:

Option Explicit

Sub Send_email()

Dim OutlookApp As Outlook.Application

End Sub

Step 3: We need to send an email in Outlook so define another variable as “Outlook.Mailitem” as shown in the below screenshot.

Code:

Option Explicit

Sub Send_email()

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

End Sub

Step 4: In the previous steps we have defined the variable now we need to set them.

Now set the first variable “Outlook Application” as “New Outlook.Application” as shown in the below screenshot.

Code:

Option Explicit

Sub Send_email()

Dim OutlookApp As Outlook.Application
Dim OutlookMail As Outlook.MailItem
Set OutlookApp = New Outlook.Application

End Sub

Step 5: Now set the Second Variable “Outlook Mail” with the below code.

Code:

Option Explicit

Sub Send_email()

Dim OutlookApp As Outlook.Application
Dim OutlookMail As Outlook.MailItem
Set OutlookApp = New Outlook.Application
Set OutlookMail = OutlookApp.CreateItem(olMailItem)

End Sub

Step 6: We can now use the VBA Outlook using the “With” statement as shown in the below screenshot.

Code:

Option Explicit

Sub Send_email()

Dim OutlookApp As Outlook.Application
Dim OutlookMail As Outlook.MailItem
Set OutlookApp = New Outlook.Application
Set OutlookMail = OutlookApp.CreateItem(olMailItem)
With OutlookMail

End Sub

We now have all the access to Email items like “To”, “CC”, “BCC”, “subject”, ” Body of the email” and Many more items.

Step 7: Inside the “With” statement, we can see a list by putting a dot which is known as “Intellisense List”.

Step 8: First select the body format as olFormatHtml as shown in the below screenshot.

Code:

With OutlookMail
  .BodyFormat = olFormatHTML

End Sub

Step 9: Select “.Display” to display the mail as shown in the below screenshot.

Code:

With OutlookMail
  .BodyFormat = olFormatHTML
  .Display

End Sub

Step 10: Select “.HTMLbody” to write the email as shown in the below screenshot.

Code:

With OutlookMail
  .BodyFormat = olFormatHTML
  .Display
  .HTMLBody = "write your email here"

End Sub

We need to remember a few things while writing the email in VBA code.

“<br>” is used to include line breakup between two lines. To add signature in the email, you need to enter “& .HTMLbody”

See below example on how to write the mail in VBA.

Code:

With OutlookMail
  .BodyFormat = olFormatHTML
  .Display
  .HTMLBody = "Dear ABC" & "<br>" & "Please find the attached file" & .HTMLBody

End Sub

Step 11: Now we need to add the receiver of the email. For this, you need to use “.To”.

Code:

.To = "[email protected]"

Step 12: If you want to add someone in “CC” & “BCC”, you can use “.CC” and “.BCC” as shown in the below screenshot.

Code:

.CC = "[email protected]"
.BCC = "[email protected]"

Step 13: To add a subject for the email that we are sending, we can use “.Subject” as shown in the below screenshot.

Code:

.Subject = "TEST MAIL"

Step 14: We can add our current workbook as an attachment in the email with the help of “.Attachment” Property. To do that first declare a variable Source as a string.

Code:

Dim source_file As String

Then use the following code to attach the file in the email.

Code:

source_file = ThisWorkbook.FullName
.Attachments.Add source_file

Here ThisWorkbook is used for the current workbook and .FullName is used to get the full name of the worksheet.

Step 15: Now the last code is to finally send the email for which we can use “.send”. But make sure to close the With and Sub procedure by “End with” and “End Sub” as shown in the below screenshot.

So the code is finally ready to send an email. We need to just run the macro now.

Step 16: Run the code by hitting F5 or Run button and see the output.

Final Full code

So below is the final code on how to send an email with the help of VBA Outlook.

Code:

Option Explicit

Sub Send_email()

Dim OutlookApp As Outlook.Application
Dim OutlookMail As Outlook.MailItem
Dim source_file As String
Set OutlookApp = New Outlook.Application
Set OutlookMail = OutlookApp.CreateItem(olMailItem)
With OutlookMail
  .BodyFormat = olFormatHTML
  .Display
  .HTMLBody = "Dear ABC" & "<br>" & "Please find the attached file" & .HTMLBody

  .To = "[email protected]"
  .CC = "[email protected]"
  .BCC = "[email protected]"
  .Subject = "TEST MAIL"

  source_file = ThisWorkbook.FullName
  .Attachments.Add source_file
  .Send

End With
End Sub

Example of VBA Outlook Function

Suppose there is a Team Leader and he wants to send a daily email for follow up of each member’s activity. The email will be like this.

“Hi Team,

Request you to kindly share your actions on each of your follow up items by 11 AM today.

Thanks & Regards,

Unknown

“

Follow the steps mentioned above for referencing the Microsoft Object and writing the VBA coding or you can just modify the code accordingly.

So with all the code remaining same, we need to change few things in the code be like Email ID of the receiver, Subject, Body of the email and there will be no attachment in the email.

Below is the modified code we are using to write this email.

Code:

Sub Send_teamemail()

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 = "Hi Team " & "<br>" & "<br>" & "Request you to kindly share 
your actions on each of your follow up items by 8 PM today." & .HTMLBody

.To = "[email protected];[email protected];[email protected]"
.Subject = "Team Follow Up"
.Send

End With
End Sub

After running the macro, you will see the mail has been sent automatically from your outlook.

Things to Remember

First, make sure you have installed Microsoft Outlook in your computer and you have login into your account.
Make sure that the box for Microsoft Outlook in Object Library reference is always checked. The code will not run and throw an error if it is not checked.
Defining variables and setting variables in very important in VBA coding. Without Variables, a code will not work.
Make sure that if you want to add signature in the mail, first you should have at least one signature already created in outlook.
Always use “<br>” to enter line gaps in the mail.

Quiz Result
Total Questions	Correct Answers	Wrong Answers	Percentage

Excel VBA Outlook Function

How to Use Excel VBA Outlook Function?

#1 – Reference of Outlook from Excel

#2 – Write a Code to Send Emails from VBA Outlook from Excel

Example of VBA Outlook Function

Things to Remember

Recommended Articles

Follow us!

APPS

Blog

Courses

Email