How many times have you forgotten to send an email? How many times did you think that you clicked send and the email did not come out of your mailbox? In this article I’m gonna show You how to automatically send en email using Excel.
Email communication is fundamental in office work. In today’s world almost nobody is using traditional letter. The most popular way, apart of SMS and other mobile ways of communication, is electronic mail. Often it is your proof of work.
But why send email from Excel?
To send your report immediately after creation and not forget to do this!
Do full automation of your reporting tasks! If You don’t have to analyze the report before sending, why not do this automatically?
Let’s go to the coding
First of all, You need to check the Outlook references, because You are in Excel application. In this case it is Microsoft Outlook 16.0 Object Library.
Then You can declare Outlook application and your email as object.
Dim outlookApp As Outlook.Application Dim OutMail As Object
Also 2 string variables needed for recipient’s email address and report full file path.
Dim email_address As String, report_file As String email_address = "Email address" report_file = "Report full file path"
After that set the Outlook and the email.
Set outlookApp = Outlook.Application Set OutMail = outlookApp.CreateItem(0)
And now the only thing left is to fulfill the email properties and methods.
With OutMail .To = email_address .CC = "" .BCC = "" .Subject = "Test email title" .Body = "Hello there" .Attachments.Add report_file .Send End With
Of course You can add recipients to CC and BCC.
If You want to add as attachment this workbook, instead of report_file put ActiveWorkbook.FullName or ThisWorkbook.FullName.
Also if You want to try it out instead of immediate sending You can just display prepared email. Replace .Send for .Display.
Now it’s time for whole code. Putting all together it looks something like this:
Option Explicit Sub sendemail() Dim outlookApp As Outlook.Application Dim OutMail As Object Dim email_address As String, report_file As String email_address = "Email address" report_file = "Report full file path" Set outlookApp = Outlook.Application Set OutMail = outlookApp.CreateItem(0) With OutMail .To = email_address .CC = "" .BCC = "" .Subject = "Test email title" .Body = "Hello there" '.Attachments.Add report_file 'ActiveWorkbook.FullName .Display '.Send End With End Sub
And this is it! Now You should know how to automatically send email using Excel. You’ll never forget about sending the report or anything You’ll need to send from now. Simple, but really useful.