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