How to Send an Email to an Address in a Cell using an Excel Macro- 2 Methods

This is an overview:

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

The sample worksheet contains 1 email address in B3. To send an email to this address:

Step 1- Setting the Necessary Objects

Set the necessary objects:

An Outlook Object
A Mail Object

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

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

Step 2 – Inserting the Destination Address

Use the address to send the email to. Here, B3.

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

Step 3 (Optional) – Inserting Additional Addresses

Insert additional addresses into the code: cc address and bcc address. 2 random addresses were added here.

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

Step 4 – Inserting the Mail Subject and the Mail Body

Insert the mail subject and the mail body into the code.

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

Step 5 (Optional): Attaching Any File

Attach a file.

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

Step 6: Sending the Mail

This sends the email.

MyMail.Send

The complete VBA code is:

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

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

Method 1 – Developing a Macro to Send an Email to a Single Address in a Cell

Use the VBA code above to send an email to a single address.
Run the code and a Microsoft Outlook window will open. (you need to be logged in)

The email will be sent to the email address.

Read More: Macro to Send Email from Excel

Method 2 – Developing a Macro to Send an Email to Multiple Addresses in Multiple Cells

There are 5 email addresses in B3:B8.

The VBA code is:

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