How to Apply Macro to Send Email from Excel with Attachment

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

VBA Code to Develop Macro to Send Email from Excel with Attachment


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)

Declaring Necessary Objects to Develop the Macro to Send the Email with the Attachment

⧪ 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."

Inserting Email Properties to Develop the Macro to Send the Email with the Attachment

⧪ 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

Sending the Email to Develop the Macro to Send the Email with the Attachment

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

VBA Code to Develop Macro to Send Email from Excel with Attachment

Read More: Macro to Send Email from Excel (5 Suitable Examples)


Similar Readings


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.

Opening VBA Window to Develop the Macro to Send the Email with the Attachment

⧪ Step 2: Enabling the Necessary References

Go to Tools > References in the toolbar above. Click on References.

Enabling Necessary References to Develop the Macro to Send the Email with the Attachment

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.

Putting the VBA Code to Develop the Macro to Send the Email with the Attachment

⧪ Step 5: Running the Code

Click on the Run Sub / UserForm tool from the toolbar above.

Running the Macro to Develop the Macro to Send the Email with the Attachment

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.


Related Articles

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo