Excel Macro: Send Email to an Address in Cell (2 Easy Ways)

In this article, I’ll show you how we can develop a Macro to send an email to an address from a cell in a worksheet in Excel.


Excel Macro to Send Email to an Address in a Cell (Quick View)

Sub Send_Email_with_Attachment()

Dim MyOutlook As Object
Set MyOutlook = CreateObject("Outlook.Application")

Dim MyMail As Object
Set MyMail = MyOutlook.CreateItem(olMailItem)

MyMail.To = Range("B3").Value

MyMail.CC = "[email protected]"
MyMail.BCC = "[email protected]"

MyMail.Subject = "Sending Email with VBA."
MyMail.Body = "This is a Sample Mail."

Attached_File = "F:\ExcelDemy\Attachment.xlsx"
MyMail.Attachments.Add Attached_File

MyMail.Send

End Sub

VBA Code to Develop an Excel Macro to Send an Email to an Address in Cell


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


An Overview of the VBA Code to Send Email to an Address in a Cell

Here we’ve got a worksheet in an Excel workbook that contains 1 email address in cell B3 for sending an email to.

Data Set Excel Macro to Send Email to an Address in a Cell

Our objective is to develop a Macro to send an email to this address.

First, we’ll see the step-by-step analysis of the VBA code to accomplish this.

⧪ Step 1: Setting the Necessary Objects

At the very outset of the code, we have to set the necessary objects required in this code. They are:

  • An Outlook Object
  • A Mail Object
Dim MyOutlook As Object
Set MyOutlook = CreateObject("Outlook.Application")

Dim MyMail As Object
Set MyMail = MyOutlook.CreateItem(olMailItem)

Setting Necessary Objects Excel Macro to Send Email to an Address in a Cell

⧪ Step 2: Inserting the Destination Address

Next, we have to the address to send the email to. Here it’s in cell B3 of our active worksheet.

MyMail.To = Range("B3").Value

⧪ Step 3 (Optional): Inserting the Additional Addresses

Next, we have to insert the additional addresses into the code. They are the cc address and the bcc address. I’ve added 2 imaginary emails here.

MyMail.CC = "[email protected]"
MyMail.BCC = "[email protected]"

Inserting Addresses Excel Macro to Send Email to an Address in a Cell

This is optional. You can always omit it if it’s not necessary.

⧪ Step 4: Inserting the Mail Subject and the Mail Body

Next, we’ll insert the mail subject and the mail body into the code.

MyMail.Subject = "Sending Email with VBA."
MyMail.Body = "This is a Sample Mail."

Inserting Mail Subject and Mail Body Excel Macro to Send Email to an Address in a Cell

⧪ Step 5 (Optional): Attaching Any File

Again this is also optional. If you want to attach any file to the mail, you can attach it here.

Attached_File = "F:\ExcelDemy\Attachment.xlsx"
MyMail.Attachments.Add Attached_File

⧪ Step 6: Sending the Mail

Finally, we’ll send the file.

MyMail.Send

Sending Email Excel Macro to Send Email to an Address in a Cell

So, the complete VBA code will be:

VBA Code:

Sub Send_Email_with_Attachment()

Dim MyOutlook As Object
Set MyOutlook = CreateObject("Outlook.Application")

Dim MyMail As Object
Set MyMail = MyOutlook.CreateItem(olMailItem)

MyMail.To = Range("B3").Value

MyMail.CC = "[email protected]"
MyMail.BCC = "[email protected]"

MyMail.Subject = "Sending Email with VBA."
MyMail.Body = "This is a Sample Mail."

Attached_File = "F:\ExcelDemy\Attachment.xlsx"
MyMail.Attachments.Add Attached_File

MyMail.Send

End Sub

VBA Code to Develop an Excel Macro to Send an Email to an Address in Cell


2 Suitable Methods to Send Email to an Address in a Cell with Excel Macro

1. Developing a Macro to Send Email to a Single Address in a Cell

Actually, we have done this already. We’ve developed a VBA code to send an email to a single address from an Excel cell.

If you run this code, a Microsoft Outlook window will open. If you are not logged in there, it’ll ask you to log in there.

After completing the formalities, an email will be sent to the mentioned email address.


2. Developing a Macro to Send Email to Multiple Address in Multiple Cells

Now we’ll develop a Macro to send emails to multiple addresses from multiple cells from a worksheet.

Here we have 5 email addresses in range B3:B8 of the worksheet.

Multiple Addresses to Develop Excel Macro to Send Email to an Address in a Cell

To develop a VBA code to send an email to each of the addresses, we have to use a for-loop here.

The VBA code will be:

VBA Code:

Sub Send_Email_to_Multiple_Addresses_in_Cells()

Addresses = Range("B3:B7")

Dim MyOutlook As Object
Set MyOutlook = CreateObject("Outlook.Application")

Dim MyMail As Object
Set MyMail = MyOutlook.CreateItem(olMailItem)

For i = 1 To Addresses.Rows.Count
    For j = 1 To Addresses.Columns.Count
        MyMail.To = Addresses.Cells(i, j).Value
        MyMail.CC = "[email protected]"
        MyMail.BCC = "[email protected]"
        MyMail.Subject = "Sending Email with VBA."
        MyMail.Body = "This is a Sample Mail."
        Attached_File = "F:\ExcelDemy\Attachment.xlsx"
        MyMail.Attachments.Add Attached_File
        MyMail.Send
    Next j
Next i

End Sub

Output:

Run this code, and you’ll be again asked to log in to Microsoft Outlook if you are not logged in there already. After completing the formalities, you’ll find an email sent to each of the addresses.


Things to Remember

  • All the email addresses that I’ve used in this article are imaginary ones. You make sure to use valid addresses in your code.
  • It may take some time to complete the sign-up process within the Outlook App if you are there for the first time. So, have patience and wait till the process ends.

Conclusion

Therefore, this is the process to develop a Macro to send an email to an address in a cell in Excel. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo