Send Email With Different Body From Excel to Multiple Recipients Using VBA

Written by Kasper Langmann

Automating routine tasks can significantly enhance productivity and efficiency in any work environment. One such task is sending emails to multiple recipients with different email bodies. This can be time-consuming if done manually, but with the power of Visual Basic for Applications (VBA) in Excel, you can automate this process. This tutorial will guide you through the process of using VBA to send emails with different bodies to multiple recipients.

Understanding VBA in Excel

Visual Basic for Applications (VBA) is a programming language developed by Microsoft. It is primarily used for automating tasks in Microsoft Office applications, including Excel. VBA allows you to create macros, which are sequences of commands that perform specific tasks. In Excel, you can use VBA to automate repetitive tasks, manipulate data, and interact with other Office applications.

One of the powerful features of VBA in Excel is its ability to interact with other applications, such as Microsoft Outlook. This feature allows you to automate tasks that involve both Excel and Outlook, such as sending emails with different bodies to multiple recipients.

Getting Started with VBA

Before you can use VBA in Excel, you need to enable the Developer tab in the Excel Ribbon. This tab gives you access to the VBA editor and other developer tools. To enable the Developer tab, right-click anywhere on the Excel Ribbon, select Customize the Ribbon, and then check the Developer option.

Once the Developer tab is enabled, you can access the VBA editor by clicking on the Visual Basic button. The VBA editor is where you write and edit your VBA code.

Creating a Macro to Send Emails

Now that you have a basic understanding of VBA in Excel, let’s create a macro that sends emails with different bodies to multiple recipients. The first step is to open the VBA editor and insert a new module. A module is a container for VBA code. To insert a new module, click on the Insert menu in the VBA editor, and then select Module.

Once the new module is inserted, you can start writing your VBA code. The code for sending emails with different bodies to multiple recipients involves several steps, including setting up the Outlook application, creating the email items, and looping through the recipients list.

Setting Up the Outlook Application

The first step in the VBA code is to set up the Outlook application. This involves creating an instance of the Outlook application and setting up the namespace. The namespace is the container for all the objects and collections in Outlook. The code for setting up the Outlook application is as follows:

Dim OutlookApp As Outlook.Application
Dim OutlookNamespace As Namespace

Set OutlookApp = New Outlook.Application
Set OutlookNamespace = OutlookApp.GetNamespace("MAPI")

Creating the Email Items

The next step in the VBA code is to create the email items. This involves creating an instance of the MailItem object for each email. The MailItem object represents an email message in Outlook. The code for creating the email items is as follows:

Dim Mail As Outlook.MailItem

Set Mail = OutlookApp.CreateItem(olMailItem)

Looping Through the Recipients List

The final step in the VBA code is to loop through the recipients list. This involves reading the recipients and their corresponding email bodies from an Excel range and sending the emails. The code for looping through the recipients list is as follows:

Dim Recipients As Range
Dim Cell As Range

Set Recipients = ThisWorkbook.Sheets("Sheet1").Range("A1:B10")

For Each Cell In Recipients
    With Mail
        .To = Cell.Value
        .Body = Cell.Offset(0, 1).Value
        .Send
    End With
Next Cell

Conclusion

Automating the process of sending emails with different bodies to multiple recipients can save you a lot of time and effort. With the power of VBA in Excel, you can create a macro that performs this task with just a few lines of code. This tutorial has provided you with a step-by-step guide on how to create such a macro.

Remember that VBA is a powerful tool that can automate many other tasks in Excel. With a bit of practice and exploration, you can use VBA to significantly enhance your productivity and efficiency in Excel.