How to send an email using Excel

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.

    .Attachments.Add ActiveWorkbook.FullName

Also if You want to try it out instead of immediate sending You can just display prepared email. Replace .Send for .Display.

    .Display

Full code

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

Summary

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.