Send email from Excel using VBA

Tired of writing tons of similar emails to your recipients? Preparing a personalized newsletter in Excel? Sending an email from Excel is a useful feature in case you need email notifications or you are building a mailing list in Excel. Below find a quickly drafted method for sending emails from your Outlook application. The prerequisite is to have Outlook Exchange installed and configured to some default email account. The procedure will then leverage this account to send emails. Similarly this can be use to send emails from VBscript – just remove any variable data type declarations and you should be good to go (e.g. “Dim outApp” instead of “Dim outApp as Object”).

Send email from Excel

The code below will utilize your Outlook application to created an Mail item, and send the email.

Sub SendEmailFromOutlook(body As String, subject As String, toEmails As String, ccEmails As String, bccEmails As String)
    Dim outApp As Object
    Dim outMail As Object
    Set outApp = CreateObject("Outlook.Application")
    Set outMail = outApp.CreateItem(0)

    With outMail
        .to = toEmails
        .CC = ccEmails
        .BCC = bccEmails
        .subject = subject
        .HTMLBody = body
        .Send 'Send the email
    End With

    Set outMail = Nothing
    Set outApp = Nothing
End Sub

Tweak: Save email in Outlook from Excel

What if you do not want to send the email, but instead just send a draft to finish or send later? Replace Send with Save in the above code snippet as show below:

 With outMail
        .to = toEmails
        .CC = ccEmails
        .BCC = bccEmails
        .subject = subject
        .HTMLBody = body
        .Save 'Instead of send
    End With

Tweak: Display draft instead of sending an email

An additional modification of the procedure could be to simply display the email draft instead of sending/saving it. Replace Send with Display in the above code snippet as show below. You will be presented with the draft email message which you can tinker with and send by yourself.

 With outMail
        .to = toEmails
        .CC = ccEmails
        .BCC = bccEmails
        .subject = subject
        .HTMLBody = body
        .Display 'Instead of send/save
    End With

Adding attachments to email from Excel

Sometimes text/html is not the only content you want to share with your recipients. What about adding some attachments to the email? Just add the code snippet below to the original code snippet above:

Dim filePath as String
filePath = ("C:\someFile.txt")
outMail.Attachments.Add filePath 'Send some text file

'Attach the current Workbook
outMail.Attachments.Add ActiveWorkbook.FullName

Summary

Sending emails from Excel can save you a lot of time. This is a very useful example of Excel automation. I commonly see sending emails from Excel being used in these scenarios:

Managing email distribution lists from Excel
Sending notifications emails from Excel
Creating draft emails

Additional tip: send Excel as PDF

As most of you know Excel is great for preparing reports. Charts, numbers etc. You can of course create a text message with this data. However sometimes that is simply not as convenient as sending an image/pdf and distributing these PDF reports to recipients. Let’s extend our snippet further to send a pdf Excel report with just the output instead of your entire workbook.

Dim ws as Worksheet
Set ws = Activesheet
ws.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=myFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

More examples

Above I tried to focus on the most common examples of sending emails via Excel. There are definitely more scenarios. I think Ron de Bruin did a great job of listing most of them. Check his website here.

Related posts: