Sending Email Using Excel VBA in Outlook – A Step-by-Step Guide

Sending emails through VBA (Visual Basic for Applications) in Microsoft Outlook can be a powerful and efficient way to automate your email communication. Whether you need to send routine reports, notifications, or any other type of email, you can streamline the process using VBA. In this article, we’ll walk you through the process of sending an email using VBA in Outlook.

Prerequisites

Before you begin, ensure that you have Microsoft Outlook installed on your computer and that your Outlook email account is properly configured. You’ll also need to have a basic understanding of VBA.

Setting Up the VBA Code

Here’s the VBA code to send an email through Outlook:

Sub SendEmailUsingSMTP()
Dim OutApp As Object
Dim OutMail As Object

' Create an Outlook application object
Set OutApp = CreateObject("Outlook.Application")
' Create a new mail item
Set OutMail = OutApp.CreateItem(0)

' Configure the email properties
With OutMail
.To = "[email protected]"
.CC = "[email protected]"
.BCC = "[email protected]"
.Subject = "Subject of your email"
.Body = "This is the body of your email."
.Attachments.Add "C:\Path\To\Attachment\File.txt" ' Add attachments if needed

' Send using SMTP
.Send
End With

' Clean up the objects
Set OutMail = Nothing
Set OutApp = Nothing
End Sub

Let’s break down what this code does:

1. We declare two objects, OutApp and OutMail, to represent the Outlook application and the email item, respectively.
2. We create an Outlook application object using CreateObject("Outlook.Application").
3. We create a new email item using OutApp.CreateItem(0). The 0 parameter specifies that we’re creating a mail item.
4. We configure the email properties such as the recipient’s email address, CC, BCC, subject, body, and attachments. Make sure to replace the placeholder values with your actual email details.
5. We send the email using .Send.
6. Finally, we clean up by setting the objects to Nothing.

Running the VBA Code

To run the VBA code, follow these steps:

1. Open Microsoft Outlook.
2. Press Alt + F11 to open the VBA editor.
3. In the VBA editor, go to “Tools” > “References” and make sure that “Microsoft Outlook xx.x Object Library” is selected.
4. Create a new module or use an existing one for your VBA code.
5. Copy and paste the code into the module.
6. Replace the placeholder values with your actual email details.
7. Run the SendEmailUsingSMTP subroutine by pressing F5 or executing it from the VBA editor’s Run menu.

Troubleshooting

If the email isn’t sent, refer to the troubleshooting steps mentioned earlier in this article to identify and resolve the issue.

Sending emails through VBA in Outlook can save you time and effort, especially when you need to automate repetitive email tasks. The provided code is a simple example, but you can extend it to suit your specific needs, whether it’s sending personalized emails to a list of recipients or automating email notifications from your applications. With a little knowledge of VBA, you can harness the power of Outlook to streamline your email communication.