Send Email from Excel with Attachment using vba

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.

1. How do I attach an Excel file to an email?
2. 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.

1. Open Excel file & Click on Office Ribbon
2. Click Send option
3. Choose E-Mail option (keyboard Shortcut – “Alt + F + D + E”)
4. Enter recipients email address, subject, body.
5. Excel file will be attached automatically.
6. 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.