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