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