How To Create A Macro In Outlook For Email

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 VBA 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…

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

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.

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 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                  VBA 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

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

Code:

            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.

Code:

            Option Explicit            Sub            Send_Exails()            Dim            OutlookApp            As            Outlook.Application            Dim            OutlookMail            As            Outlook.MailItem            End Sub

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

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

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

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.

Code:

            With            OutlookMail     .BodyFormat = olFormatHTML            End With

Step 9: Now display the email.

Code:

            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.

Code:

            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 ids,

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 the below VBA code to send emails We 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.

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                  Line 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 = "abc@gmail.com"     .CC = "sdf@gamil.com"     .BCC = "hello@gamil.com;hi@gmail.com"     .Subject = "Test mail"     .Attachments = ThisWorkbook     .Send            End With            End Sub