Email Workbook as an Attachment – Excel VBA Macro

The functionality in Excel to send a workbook via email as an attachment is fantastic. However if you find yourself using this feature often, you may have a desire to automate or speed up the process.

I have been asked this many times in my Excel VBA classes so thought it would be beneficial to share some code to accomplish this.

This Excel VBA Macro will attach the current workbook as an attachment to an email and send it. It will add some text to the subject line and body of the email also.

This code can be adapted to your own needs. I will explain how to do this and the lines you will need to change. There are also a few things to look out for that may cause you problems.

This macro should work for any email service. I have used it with my GMail and my Yahoo accounts successfully. The code may just need some tweaking to work for your own situation.

How to Adapt the Code to my own Needs

This Excel VBA code uses CDO to send the email from Excel. There are a few techniques to send email through Excel using VBA. Some advantages to the CDO approach include;

• It uses a SMTP server so you are not restricted to using Outlook. It should work with any mail program.
• You will not receive dialog boxes containing warning messages.
• You can send any file you like such as a PDF or PowerPoint file.

To get the macro to work for you, you will need to change the following lines of code.

Edit the SMTP server information e.g. smtp.gmail.com or smtp.mail.yahoo.co.uk in this line.

.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = ""

Use your own email address to test the code in this line.

.To = "[email protected]"

Enter the name and email address that you are sending from in the line below.

.From = """Your Name"" <[email protected]>"

Excel VBA Code to Email Workbook as an Attachment

Download the Email Workbook as an Attachment file.

Sub Email_Workbook()
    Dim Msg As Object
    Dim Conf As Object
    Dim msgBody As String
    Dim ConfFields As Variant
    Dim wb As Workbook
    Dim FilePath As String
    Dim FileName As String
   
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    Set wb = ActiveWorkbook
   
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Please select a location to save the workbook"
        .AllowMultiSelect = False
        .Show
   
        If .SelectedItems.Count = 0 Then
            MsgBox "You did not select a folder"
        Exit Sub
    Else
        FilePath = .SelectedItems(1) & "\"
    End If
   
End With
   
    FileName = wb.Name
    wb.SaveCopyAs FilePath & FileName
   
    Set Msg = CreateObject("CDO.Message")
    Set Conf = CreateObject("CDO.Configuration")
    Conf.Load -1    ' CDO Source Defaults
    Set ConfFields = Conf.Fields
    With ConfFields
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        'Enter the username and password of your email account below
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "username here"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password here"
        'Edit the SMTP server below e.g. smtp.gmail.com or smtp.mail.yahoo.co.uk
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = ""
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
        .Update
    End With
    msgBody = "Hi" & vbNewLine & vbNewLine & _
              "Please find the Excel workbook attached."
    With Msg
        Set .Configuration = Conf
        'Add the email address to whom to send the email below
        .To = "[email protected]"
        .CC = ""
        .BCC = ""
        .From = """Your Name"" <[email protected]>"
        .Subject = "The Macro Worked. Yay!!"
        .TextBody = msgBody
        .AddAttachment FilePath & FileName
        .Send
    End With
   
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

Possible Problems and Solutions

If you receive the ‘Authentication Required Error’ or the ‘Server Rejected your Response’ error (shown below) then the following 3 lines of code will need to be used.

Enter the appropriate username and password for your email account.

.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "username here"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password here"

If you receive the ‘transport failed to connect to the server’ error, try changing the SMTP port from 465 to 25, and try adding the line of code below.

.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True

If you are using GMail like I do for one of my accounts, then you may need to enable the less secure apps. You can do this by using the link below.
https://www.google.com/settings/security/lesssecureapps

New to Excel VBA and Want to Know More?

Our online Excel VBA course will take you from beginner to a savvy Excel VBA operator in just 11 hours of lessons.

Support is available throughout the course for any questions you may have, and all workbooks are provided to work through.

Find out more about the online Excel VBA course.