VBA Outlook

What is VBA Outlook?

VBA Outlook refers to integrating Microsoft Outlook with VBA, a programming language used in various Microsoft Office applications. With VBA, you can automate tasks and perform operations within Outlook, such as sending emails, managing appointments, handling contacts, and accessing mailbox data. This powerful combination allows users to enhance productivity and customize Outlook functionalities according to their needs.

Let us look at an example. Here, the VBA code uses the Outlook application to send a basic test VBA Outlook email. It initializes the Outlook application using the “VBA Outlook.Application”, creates a new email item using “CreateItem(olMailItem),” and then sets the recipient’s email address, subject, and body text within the With block. The “.Send” method is used to send the email immediately.

VBA Outlook Intro.jpg
VBA Outlook Intro - Invalid email.jpg

You can check the draft email saved in your Outlook.

VBA Outlook Intro - Outlook.jpg
Key Takeaways
  1. VBA provides a powerful way to automate tasks and interact with Microsoft Outlook, allowing users to send emails, manage appointments, and access mailbox data from within Excel.
  2. You can create new email items, and set recipients, subject, body, and attachments programmatically. The .Send method sends emails immediately, while .Display allows previewing the email before sending.
  3. Understanding the Outlook Object Model is essential for using VBA with Outlook. Declare and initialize objects like VBA Outlook.Application and VBA Outlook MailItem to interact with Outlook’s functionalities.
  4. Properly managing objects and memory is crucial. Release objects (Set obj = Nothing) after usage to avoid memory leaks and ensure efficient code execution.

How do we Reference Outlook from Excel?

To reference Outlook from Excel, follow these steps:

Step 1: Open your Excel workbook.

Step 2: Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.

VBA Outlook - Step 2

Step 3: In the VBA editor, click Tools in the menu and select References.

VBA Outlook - Step 3.jpg

Step 4: In the “References – VBAProject” dialog box, scroll down and locate “Microsoft Outlook XX.X Object Library” (where XX.X represents the version number).

VBA Outlook - Step 4.jpg

Step 5: Check the box next to “Microsoft Outlook XX.X Object Library” to enable the reference.

Step 6: Click “OK” to apply the reference and close the dialog box.

Now, you can access VBA Outlook objects and functionalities in your Excel VBA project.


Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Write a Code to Send Emails from VBA Outlook from Excel

Here’s a basic code example to send an email using VBA Outlook from Excel:

Step 1: In a new module, create a subroutine named “SendEmailFromOutlook.”

VBA Outlook - send mail - Step 1.jpg

Step 2: Declare and initialize variables “outlookApp” and “outlookMail” as objects of the “VBA Outlook.Application” and “Outlook.MailItem” classes, respectively. These variables will be used to work with Outlook from within Excel VBA.

VBA Outlook - send mail - Step 2.jpg

Step 3: Create a new instance of the Outlook application using New Outlook.Application. It allows us to interact with Outlook.

VBA Outlook - send mail - Step 3.jpg

Step 4: Create a new email item using “outlookApp.CreateItem(olMailItem)”. The “olMailItem” constant represents a new email message.

VBA Outlook - send mail - Step 4.jpg

Step 5: Start a “With” block to access the properties of the outlookMail object and set the email properties.

VBA Outlook - send mail - Step 5.jpg

Step 6: Set the recipient’s email address using “.To = “recipient@example.com”.” Replace the email address with the desired recipient’s address.

VBA Outlook - send mail - Step 6.jpg

Step 7: Set the email’s subject using “.Subject = “Test Email.” Modify the subject to suit your specific email content.

VBA Outlook - send mail - Step 7.jpg

Step 8: Set the email body text using “.Body = “This is a test email sent from VBA Outlook.”. Customize the body text to convey your desired message.

VBA Outlook - send mail - Step 8.jpg

Step 9: Use .Send to send the email immediately. It will send the email without displaying a preview. If you want to preview the email before sending it, use .Display instead of .Send.

VBA Outlook - send mail - Step 9.jpg

Step 10: Release the “outlookMail” object from memory by setting outlookMail = Nothing. This step ensures proper memory management and avoids potential memory leaks.

VBA Outlook - send mail - Step 10.jpg

Step 11: Release the “outlookApp” object from memory by setting outlookApp = Nothing. This step ensures proper memory management and closes the Outlook application.

VBA Outlook - send mail - Step 11.jpg

Step 12: Save the module and click on Run.

VBA Outlook - send mail - Step 12.jpg

Step 13: Once you click on “Run” the code is triggered and it will send an email to the mentioned recipients.

Note: If you have not added an account, the email will be stored in the outbox in Outlook.

VBA Outlook - send mail - Step 13.jpg

Here is the complete code:

Sub SendEmailFromOutlook()
Dim outlookApp As Outlook.Application
Dim outlookMail As Outlook.MailItem
‘ Create a new Outlook application
Set outlookApp = New Outlook.Application
‘ Create a new email item
Set outlookMail = outlookApp.CreateItem(olMailItem)
‘ Set email properties
With outlookMail
.To = “recipient@example.com”
.Subject = “Test Email”
.Body = “This is a test email sent from VBA Outlook.”
‘ Uncomment the next line to add an attachment
‘.Attachments.Add “C:\Path\To\Attachment.pdf”
.Send ‘ Use .Display instead of .Send to preview the email before sending
End With
‘ Release objects from memory
Set outlookMail = Nothing
Set outlookApp = Nothing
End Sub

Examples

Example #1: Sending an Email with an Attachment

In this example, we send an email with an attachment using the VBA Outlook Attachments.Add method.

Step 1: In the new module, start by creating a new subroutine, “SendEmailWithAttachment.”

VBA Outlook - Example 1 - Step 1.jpg

Step 2: Declare and initialize variables “outlookApp” and “outlookMail” as objects of the VBA Outlook.Application and Outlook.MailItem classes, respectively. It allows us to work with Outlook from within Excel VBA.

VBA Outlook - Example 1 - Step 2.jpg

Step 3: Create a new instance of the VBA Outlook application using New Outlook.Application. It allows us to interact with Outlook.

VBA Outlook - Example 1 - Step 3.jpg

Step 4: Create a new email item using outlookApp.CreateItem(olMailItem). The olMailItem constant represents a new email message.

VBA Outlook - Example 1 - Step 4.jpg

Step 5: Start a With block to access the properties of the outlookMail object and set the email properties.

VBA Outlook - Example 1 - Step 5.jpg

Step 6: Set the recipient’s email address using .To = “recipient@example.com.” Replace the email address with the desired recipient’s address.

VBA Outlook - send mail - Step 6.jpg

Step 7: Set the subject of the email using .Subject = “Email with Attachment.” Modify the subject to suit your specific email content.

VBA Outlook - Example 1 - Step 6.jpg

Step 8: Set the email body text using .Body = “Please find the attached file.”. Customize the body text to convey your desired message.

VBA Outlook - Example 1 - Step 8.jpg

Step 9: Add an attachment to the email using .Attachments.Add “C:\Path\To\Attachment.pdf”.

Replace the path with the actual file path of the attachment you want to include to send a VBA Outlook attachment.

VBA Outlook - Example 1 - Step 9.jpg

Step 10: Send the email using .Send. It will send the email immediately. If you want to preview the email before sending it, use .Display instead of .Send.

VBA Outlook - Example 1 - Step 10.jpg

Step 11: Release the objects from memory by setting outlookMail = Nothing and outlookApp = Nothing. This step ensures proper memory management.

VBA Outlook - Example 1 - Step 11.jpg

Step 12: Save the macro and click on Run.

Once you execute the macro, it triggers the VBA Outlook code and sends an email to the mentioned email address with attachments.

VBA Outlook - Example 1 - Step 12.jpg

Here is the complete code:

Sub SendEmailWithAttachment()
Dim outlookApp As Outlook.Application
Dim outlookMail As Outlook.MailItem
Set outlookApp = New Outlook.Application
Set outlookMail = outlookApp.CreateItem(olMailItem)
With outlookMail
.To = “recipient@example.com”
.Subject = “Email with Attachment”
.Body = “Please find the attached file.”
.Attachments.Add “C:\Users\Vikram\Desktop\Test email Outlook.txt”
.Send
End With
Set outlookMail = Nothing
Set outlookApp = Nothing
End Sub

Example #2: Sending Multiple Emails Using a Loop

In this example, we use a loop to send multiple emails to different recipients that are stored in the Excel worksheet:

Step 1: In the new module, start by creating a new subroutine, “SendMultipleEmails.”

Example 2 - Step 1.jpg

Step 2: Declare and initialize variables outlookApp and outlookMail as objects of the Outlook.Application and Outlook.MailItem classes, respectively. Also, declare recipientList as a Range object and recipientCell as a Range object to loop through the email addresses.

Example 2 - Step 2.jpg

Step 3: Create a new instance of the Outlook application using New Outlook.Application.

Example 2 - Step 3.jpg

Step 4: Set recipientList to the range containing recipients’ email addresses. This example assumes the email addresses are in column A, starting from row 2.

Example 2 - Step 4.jpg

Step 5: Start a For Each loop to iterate through each recipientCell in recipientList.

Example 2 - Step 5.jpg

Step 6: Create a new email item using outlookApp.CreateItem(olMailItem) for each recipient.

Example 2 - Step 6.jpg

Step 7: Start a With block to access the properties of the outlookMail object and set the email properties.

Example 2 - Step 7.jpg

Step 8: Set the recipient’s email address using .To = recipientCell.Value. The email address is taken from the current cell in the loop.

Example 2 - Step 8.jpg

Step 9: Set the subject of the email using .Subject = “Test Email”. Modify the subject to suit your specific email content.

Example 2 - Step 9.jpg

Step 10: Set the email body text using .Body = “This is a test email sent from VBA Outlook.”. Customize the body text to convey your desired message.

Example 2 - Step 10.jpg

Step 11: Send the email using .Send. It will send each email immediately. If you want to preview each email before sending it, use .Display instead of .Send.

Example 2 - Step 11.jpg

Step 12: Release the outlookMail object from memory by setting outlookMail = Nothing at the end of each loop iteration.

Example 2 - Step 12.jpg

Step 13: Release the outlookApp object from memory by setting outlookApp = Nothing after the loop completes. This step ensures proper memory management.

Example 2 - Step 13.jpg

Step 14: Save the macro and click Run.

Once you execute the code, the VBA Outlook macro will send the emails to all the recipients’ email IDs stored in the Excel files ranging from A2 to A25.

Note: if you have not added an account to Outlook, the emails will be stored in the outbox.

Example 2 - Step 14.jpg

Here is the complete code:

Sub SendMultipleEmails()
Dim outlookApp As Outlook.Application
Dim outlookMail As Outlook.MailItem
Dim recipientList As Range
Dim recipientCell As Range
Set outlookApp = New Outlook.Application
Set recipientList = ThisWorkbook.Sheets(“Sheet1”).Range(“A2:A25”) ‘ Assuming recipients’ email addresses are in column A from row 2 to 5
For Each recipientCell In recipientList
Set outlookMail = outlookApp.CreateItem(olMailItem)
With outlookMail
.To = recipientCell.Value
.Subject = “Test Email”
.Body = “This is a test email sent from VBA Outlook.”
.Send
End With
Set outlookMail = Nothing
Next recipientCell
Set outlookApp = Nothing
End Sub

Important Things To Note

  1. Ensure you have a working Outlook installation on your computer to use VBA Outlook functionalities.
  2. Check the security settings in Outlook to prevent potential issues with sending emails programmatically.
  3. Always handle errors and exceptions in VBA when working with Outlook to provide a better user experience and prevent unexpected behavior.
  4. Be cautious when automating emails to avoid unintentional mass email sending.

Frequently Asked Questions (FAQs)

1. How to Copy an Excel Range to Outlook Using VBA?

You can use VBA to copy an Excel range and paste it into an email body in Outlook. First, copy the range data to the clipboard using the Copy method, and then create a new Outlook email item. Finally, use the HTMLBody property of the email item to paste the range data in HTML format into the email body.

2. Why is VBA Outlook Not Working?

Common reasons for VBA Outlook not working include:
Missing or incorrect Outlook references.
Incorrect Outlook security settings.
Issues with email properties or attachments.

Ensure you have the correct Outlook reference and handle potential security prompts. Double-check your code for any syntax errors or missing properties.

3. How Do I Add a Signature in Outlook VBA?

VBA does not provide a direct method to access Outlook signatures. However, you can use the HTMLBody property to manually add the signature as HTML code in the email body. Retrieve the signature from the current user’s Outlook settings and include it in the HTMLBody property along with your email content.

4. How to Download Attachments from Outlook VBA?

To download attachments from Outlook using VBA, you can loop through the Attachments collection of a VBA Outlook item (e.g., email) and save each attachment to a specified folder using the SaveAsFile method.

Download Template

This article must help understand VBA Outlook with its formulas and examples. You can download the template here to use it instantly.

Guide to VBA Outlook. Here we learn to send mail by writing VBA code using Outlook application from excel with examples & downloadable excel template. You can learn more from the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *