How to Automate Emails From Excel?
Get macro to send email from Excel & email Excel file as attachment using VBA.
In this page, you will learn and get code for 2 different things. Be it a professional Organization announcement, Promotional offer or a Valentine greeting email, these codes will come handy.
- How do I attach an Excel file to an email?
- How to send automated emails from Excel?
If you are looking to send a email blast to multiple recipients from Excel, then get the VBA code in this page.
Auto Email Excel File as Attachment
To attach an Excel file to an Email, follow these instructions.
- Open Excel file & Click on Office Ribbon
- Click Send option
- Choose E-Mail option (keyboard Shortcut – “Alt + F + D + E”)
- Enter recipients email address, subject, body.
- Excel file will be attached automatically.
- Click Send
This is how it is done manually. In case if you want to automate this process, then use the below code. There are 2 variants of same process has been given.
'How do I attach an Excel file to an Email? Sub Attach_Excel_File_To_An_Email() 'Method 1: Attach Current Excel Workbook to Email & Display Application.Dialogs(xlDialogSendMail).Show 'Method 2: Attach Excel file to Email & Send to the Recipient ThisWorkbook.SendMail "Author@officetricks.com", "Subscribe me to Officetricks.com", "True" End Sub
Send Automatic Email From Excel with Attachment
This code can be used in sending email blast or mass email from Excel. Not only plain email, you can also add attachment with this code to all the emails you send from Excel.
Open a new Excel workbook, Press alt + F11 and copy paste this code. When you call this module, pass three parameters – recipients email address, subject , email content.
'How to send Email from Excel? Sub Send_Automatic_Email_From_Excel(ToAddr As String, ToSubj As String, ToMsg As String) 'Click Tools -> References -> Microsoft Outlook nn.n Object Library Dim oExcelEmailApp As Object Set oExcelEmailApp = Outlook.Application.CreateItem(0) 'VBA Create email With oExcelEmailApp .To = ToAddr .CC = "" .BCC = "" .Subject = ToSubj .Body = ToMsg .Attachments.Add ThisWorkbook.FullName .Display 'to send automatic mail from excel instead of .display use .send End With Set oExcelEmailApp = Nothing End Sub
When you copy this code, sometime the double quotes & single quotes are modified to different symbols. Just verify & change those symbols before you execute the code.
Also, add a reference to Microsoft Outlook Object Library from Excel VB editor -> Tools -> References option. If this reference is not added, then it will result in a run time error.