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.


You can check the draft email saved in your Outlook.

Table of contents
Key Takeaways
- 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.
- 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.
- 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.
- 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.

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

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).

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.”

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.

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

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

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

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

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

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.

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.

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

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

Step 12: Save the module and click on Run.

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.

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.”

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.

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

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

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

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

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

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

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.

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.

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

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.

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.”

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.

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

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.

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

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

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

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

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

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.

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.

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

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

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.

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
- Ensure you have a working Outlook installation on your computer to use VBA Outlook functionalities.
- Check the security settings in Outlook to prevent potential issues with sending emails programmatically.
- Always handle errors and exceptions in VBA when working with Outlook to provide a better user experience and prevent unexpected behavior.
- Be cautious when automating emails to avoid unintentional mass email sending.
Frequently Asked Questions (FAQs)
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.
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.
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.
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.
Recommended Articles
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 –
Leave a Reply