Rifat Hassan
While working with Exel files in our day-to-day lives, we often need to send an Email containing the file as an attachment. In this article, I’ll show how you can develop a Macro to send an Email from Excel with an attachment.
VBA Code to Send Email from Excel with Attachment (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 = "[email protected]"
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 VBA Code to Send Email from Excel with Attachment (Step-by-Step Analysis)
So, without further delay, let’s go to our main discussion today. We’ll break down the VBA code step-by-step to learn how to send an Email with an attachment from an Excel Macro in detail.
⧪ 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 Email Properties
Next, we’ve to fix the Email properties inside the code. These include the receiver address, the cc address, the bcc address (If any), the Email subject, and the Email body.
Here I’ve used 3 imaginary Emails for examples. You put these according to your needs.
MyMail.To = "[email protected]"
MyMail.CC = "[email protected]"
MyMail.BCC = "[email protected]"
MyMail.Subject = "Sending Email with VBA."
MyMail.Body = "This is a Sample Mail."
⧪ Step 3: Attaching the Required File (Optional)
Then we’ll attach our required file (If there is any). Here I’ll attach an xlsx file called Attachment.xlsx from the address F:\ExcelDemy of my PC.
You change it according to your needs.
Attached_File = "F:\ExcelDemy\Attachment.xlsx"
MyMail.Attachments.Add Attached_File
⧪ Step 4: Sending the Email
Finally, we’ll send the Email.
MyMail.Send
So the complete VBA code will be:
⧠VBA Code:
Sub send_Email_complete()
Dim MyOutlook As Object
Set MyOutlook = CreateObject("Outlook.Application")
Dim MyMail As Object
Set MyMail = MyOutlook.CreateItem(olMailItem)
MyMail.To = "[email protected]"
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
Read More: Macro to Send Email from Excel (5 Suitable Examples)
Similar Readings
- Send Reminder Email Automatically from an Excel Worksheet Using VBA
- How to Send Email Automatically When Condition Met in Excel
- [Solved]: Share Workbook Not Showing in Excel (with Easy Steps)
- How to Send Email from Excel List (2 Effective Ways)
- How to Send an Editable Excel Spreadsheet by Email (3 Quick Methods)
Developing a Macro to Send Email with the Attachment
We’ve seen the step-by-step analysis of the code to send the Email with the attachment.
Now let’s see how to use the code to send the Email.
⧪ Step 1: Opening the VBA Window
Press ALT + F11 on your keyboard to open the Visual Basic window.
⧪ Step 2: Enabling the Necessary References
Go to Tools > References in the toolbar above. Click on References.
The References dialogue box will open. Check Microsoft Office 16.0 Object Library in case it’s unchecked.
⧪ Step 3: Inserting a New Module
Go to Insert > Module in the toolbar. Click on Module. A new module called Module1 (or anything else depending on your past history) will open.
⧪ Step 4: Putting the VBA Code
This is the most important step. Insert the given VBA code in the module.
⧪ Step 5: Running the Code
Click on the Run Sub / UserForm tool from the toolbar above.
The code will run. First, you have to sign up for the Outlook App if are not already signed in.
During the sign-up process, you have to input your Email address and password.
Once you are done with the sign-up process, the Email will be sent from your Email address automatically.
Things to Remember
- Make sure the path mentioned in the code to attach the file is a valid one on your computer. Otherwise, it’ll show an error.
- Make sure that the Email addresses provided in the code are valid.
- 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 from Excel with an attachment. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.
