How To Apply Macro To Send Email From Excel With 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

Step 1- Setting the Necessary Objects

In the code, the necessary objects need to be set:

• 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

The code must include the Email properties: receiver address, cc address, bcc address, Email subject and Email body.

The following are sample Emails.

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)

• Attach your file. Here, Attachment.xlsx from the address F:\ExcelDemy.

Attached_File = "F:\ExcelDemy\Attachment.xlsx" MyMail.Attachments.Add Attached_File

Step 4: Sending the Email

• Send the Email.

MyMail.Send

The complete VBA code will be:

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: Excel Macro to Send Email Automatically

Developing a Macro to Send an Email with an Attachment

Step 1: Opening the VBA Window

Press ALT + F11 to open the Visual Basic window.

Step 2 – Enabling the Necessary References

• Go to Tools > References in the toolbar. Click References.

The References dialog 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 Module. A new module will open.

Step 4: Entering the VBA Code

• Enter the VBA code in the module.

Step 5 – Running the Code

• Click the Run Sub / UserForm tool.

The code will run.

• Sign in the Outlook App.

• Enter your Email address and password.

The Email will be sent from your Email address automatically.

Read More: Send Email from Excel VBA without Outlook

Download Practice Workbook

Download this practice workbook to exercise.

Send Email with Attachment.xlsm

Related Articles

• Send Reminder Email Automatically from an Excel Worksheet Using VBA
• VBA to Generate Multiple Lines in Email Body in Excel
• Excel Macro: Send Email to an Address in Cell
• Macro to Send Email from Excel
• 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

Get FREE Advanced Excel Exercises with Solutions!