Develop a Macro to send an email to an address in a cell.
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:
Analysis of the VBA code:
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
Develop a Macro to send emails to multiple addresses in multiple cells.
There are 5 email addresses in B3:B8.
Use a for-loop.
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
Run the code. Log in to Microsoft Outlook.
The email is sent to the addresses.
Read More: Excel Macro to Send Email Automatically
Download Practice Workbook
Download the practice workbook.
Related Articles
- Send Reminder Email Automatically from an Excel Worksheet Using VBA
- Send Email from Excel VBA without Outlook
- VBA to Generate Multiple Lines in Email Body in Excel
- How to Apply Macro to Send Email from Excel with Attachment
- How to Send Email from Excel with Body Using a Macro
- Print to PDF and Email Using VBA in Excel
- How to Use Excel VBA to Paste Range into Email Body
