Rifat Hassan
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
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.
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 = "Pe[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
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.
Read More: Macro to Send Email from Excel (5 Suitable Examples)
Similar Readings
- How to Send Email from Excel List (2 Effective Ways)
- [Solved]: Share Workbook Not Showing in Excel (with Easy Steps)
- How to Send Email Automatically When Condition Met in Excel
- Send Reminder Email Automatically from an Excel Worksheet Using VBA
- How to See Who Is in a Shared Excel File (With Quick Steps)
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: How to Send Multiple Emails from Excel Spreadsheet (2 Easy Methods)
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.
Related Articles
- How to Send Email If Conditions Met in Excel (3 Easy Methods)
- How to Send Automatic Email from Excel to Outlook (4 Methods)
- Automatically Send Emails from Excel Based on Cell Content (2 Methods)
- How to Send Excel File to Email Automatically (3 Suitable Methods)
- Send Email from Excel with Body Using a Macro (with Easy Steps)
- How to Send an Editable Excel Spreadsheet by Email (3 Quick Methods)
