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
An Overview of the Macro 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.
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)
⧪ 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]"
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."
⧪ 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
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
Send Email to an Address in a Cell with Excel Macro: 2 Suitable Methods
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.
Read More: Macro to Send Email from Excel
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.
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.
Read More: Excel Macro to Send Email Automatically
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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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 in the comment section below.
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
