Macro to Send Email from Excel VBA

In this article we will provide you step by step guide to send mails with attachments from Outlook automatically using Excel VBA. We can access Outlook methods by enabling outlook scripting in VBA.

Step 1: Add a reference to Outlook Object Library.

• In Visual Basic Editor Window, Go to Tools > References

• Now in References box add reference of “Microsoft Outlook 16.0 Object Library” to the project. Then click OK. Now we have added reference to Outlook Object Library.

Step 2: Creating a sub procedure in VBA: write the below given code snippet in VB editor.

Sub Mail_Sending_example()

End Sub

Step 3: Declaring & Instantiating Outlook Application Object: The below given code snippet declares objOL_app as Outlook application object and creates an instance of it.

'Declaring Outlook Application Object
Dim objOL_app As Outlook.Application
'Creating Instance of Declaring Outlook Application Object
Set objOL_app = New Outlook.Application

Step 4: Declaring & Instantiating Outlook Mail Item Object: The below given code snippet declares objOL_app as Outlook mail item object and creates an instance of it.

'Declaring Outlook Mail Item Object
Dim objOL_MailItem As Outlook.MailItem
'Creating Instance of Declaring Outlook Mail Item Object
Set objOL_MailItem = objOL_app.CreateItem(olMailItem)

Step 5: Providing the receivers of mail: You must be aware of To, CC and BCC fields of Outlook. The below given code snippet supplies To, CC and BCC fields. Put the mail Ids of receivers in To, CC and BCC properties as per your requirements.

objOL_MailItem.To = "[email protected]"
objOL_MailItem.CC = "[email protected]"
objOL_MailItem.BCC = "[email protected]"

Step 6: Providing the Subject of mail: Using below given code snippet, provide the Subject of mail.

objOL_MailItem.Subject = "Test Mail Subject".

Step 7: Writing Mail Body content of mail: Using .Body property of objOL_MailItem , provide to mail body content that you want to send. Use VbNewLine VBA constant to insert a new line. Here is the code snippet.

objOL_MailItem.Body = "Hi Receiver," & vbNewLine & vbNewLine & "Please find test report in attachment" & _
                     vbNewLine & vbNewLine & _
                     "Regards," & vbNewLine & _
                     "XYZ Sender"

Step 8: Attaching the attachment to be sent: Provide the full path of the attachment to be sent by using below given code snippet.

objOL_MailItem.Attachments.Add "D:\Test Report.xlsx"

Step 9: Adding Importance to Mail Item: In outlook you can set mail importance as Normal, Low and High. The same you can define using VBA automation using below given code snippet. Use whichever importance you want to set. If you do not want to set importance level, you can omit this line of code.

objOL_MailItem.Importance = olImportanceHigh
'objOL_MailItem.Importance = olImportanceLow
'objOL_MailItem.Importance = olImportanceNormal

Step 10: Sending Mail: Now our mail is ready to be sent to mentioned mail IDs and we can send it by using Send method as given below.

objOL_MailItem.Send

Here is the entire code.

Sub Mail_Sending_example()

'Declaring Outlook Application Object
Dim objOL_app As Outlook.Application
'Creating Instance of Declaring Outlook Application Object
Set objOL_app = New Outlook.Application
'Declaring Outlook Mail Item Object
Dim objOL_MailItem As Outlook.MailItem
'Creating Instance of Declaring Outlook Mail Item Object
Set objOL_MailItem = objOL_app.CreateItem(olMailItem)  

objOL_MailItem.To = "[email protected]"
objOL_MailItem.CC = "[email protected]"
objOL_MailItem.BCC = "[email protected]"

objOL_MailItem.Subject = "Test Mail Subject"

objOL_MailItem.HTMLBody = "Hi Receiver," & vbNewLine & vbNewLine & "Please find test report in attachment" & _
                     vbNewLine & vbNewLine & _
                     "Regards," & vbNewLine & _
                     "XYZ Sender"
                     
                     
objOL_MailItem.Attachments.Add "D:\Test Report.xlsx"

objOL_MailItem.Importance = olImportanceHigh
'objOL_MailItem.Importance = olImportanceLow
'objOL_MailItem.Importance = olImportanceNormal

objOL_MailItem.Send
                     
End Sub

Follow the given steps and debug the code. Check your sent items. You will find that a mail has been sent to mentioned mail IDs with the mentioned subject and mail body.