VBA Send Emails from Excel through Gmail

This tutorial will show you how to send emails from Excel through Gmail using VBA.

Adding the Microsoft CDO Reference

To allow Excel to send email through Gmail you first have to add the CDO reference library to your Excel project.

In the VBA Editor, click on Tools, References.

Search for the Microsoft CDO Reference, and click OK.

Sending an Email via GMail

To send an Email from Excel via Gmail you would first need to declare a CDO object (Dim gMail as CDO.Message).

Secondly, you would need to configure the SSL Authentication for your message in your code.  This means that you need to set SMTP server and Port Details in order for your email to go through correctly, as well as setup your username and password.

In the code snippet below, this is what you would need at the beginning of your Excel function.  You need an Email Recipient (strTo), and a Subject (strSubject) but the CC and the Body of the email can be optional variables.

Function CreateEmail(strTo As String, strSubject As String, Optional strCC As String, Optional strBody As String)

'create a CDO object 
   Dim gMail As CDO.Message 
   Set gMail = New CDO.Message

'Enable SSL Authentication 
   gMail.Configuration.Fields.Item _ 
   ("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True

'Make SMTP authentication Enabled=true (1) 
   gMail.Configuration.Fields.Item _ 
   ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1

'Set the SMTP server and port Details 
   gMail.Configuration.Fields.Item _ 
   ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
   gMail.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
   gMail.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 

'Set your username and password for your own gmail account here
    gMail.Configuration.Fields.Item _ 
   ("http://schemas.microsoft.com/cdo/configuration/sendusername") = "johnsmith@gmail.com" 
   gMail.Configuration.Fields.Item _ 
   ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "*********" 

'Update the configuration fields 
   gMail.Configuration.Fields.Update

Once that is done, you can create your email and complete the function.

'set the email properties and the file to send 
   With gMail 
      .Subject = "Write subject here" 
      .From = "stitchmel@gmail.com" 
      .To = strTo 
      .TextBody = strBody 
   End With 
'to send the mail 
   gMail.Send 
End Function

You can then call your function with the following code

Sub SendEmail
'create the body of the email
Dim strText as string
   strText = "Good morning.  Hope you are well - this is a test email"
'populate the arguments of the function - leave CC blank so put a comma as a placeholder
   CreateEmail("jim.smith@gmail.com", "Test Email", , strText)
End sub

Sending a Workbook via GMail

To send a workbook as an attachment  via GMail, you can create a function much like the one above with a few additions.

You need to add  code to select the workbook, and then you need to attach the selected file to the email.

We will use the Microsoft Office FileDialog command to select the file to send, and then use the AddAttachment property of the email to add the file as an attachement.

Function SendWorkbook(strTo As String, strSubject As String, Optional strCC As String, Optional strBody As String) As Boolean
On error goto eh:

'create a CDO object
   Dim gMail As CDO.Message
   Set gMail = New CDO.Message

'Enable SSL Authentication
   gMail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True

'Make SMTP authentication Enabled=true (1)
   gMail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1

'Set the SMTP server and port Details
   gMail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
    gMail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
   gMail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

'Set your username and password for your own gmail account here
   gMail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/sendusername") = "johnsmith@gmail.com"  
   gMail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "*********"

'Update the configuration fields
   gMail.Configuration.Fields.Update

'select the file to send with the microsoft file dialog box
   Dim strFileToSend As String
   Dim dlgFile As FileDialog
   Dim strItem As Variant
   Dim nDlgResult As Long
   Set dlgFile = Application.FileDialog(msoFileDialogFilePicker)
'filter for excel and csv files only
   dlgFile.Filters.Add "Excel Files", "*.csv; *.xls; *.xlsx; *.xlsm"
   nDlgResult = dlgFile.Show
   If nDlgResult = -1 Then
      If dlgFile.SelectedItems.Count > 0 Then
         For Each strItem In dlgFile.SelectedItems
             strFileToSend = strItem
         Next strItem
      End If
   End If
'set the email properties and the file to send
   With gMail
     .Subject = "Write subject here"
     .From = "stitchmel@gmail.com"
     .To = strTo
     .TextBody = strBody
'add the selected file as an attachment
     .AddAttachment strFileToSend
   End With
'to send the mail
   gMail.Send
   SendWorkbook= True
   Exit Function
eh:
   SendWorkbook = False
End Function

If the Configuration port 25 does not work you might get an error – “The transport failed to connect to the server”   Change the port to 25 and try again.

The function above can be called using the procedure below.

Sub SendMail()
   Dim strTo As String
   Dim strSubject As String
   Dim strBody As String
'populate variables
   strTo = "jon.smith@gmail.com"
   strSubject = "Please find finance file attached"
   strBody = "some text goes here for the body of the email"
'call the function to send the email
   If SendWorkbook(strTo, strSubject, , strBody) = true then
      Msgbox "Email creation Success"
   Else
      Msgbox "Email creation failed!"
   End if
End Sub

You will be asked to select the File you want to send.   This code is restricted to Excel files due to the filter being  “*.csv; *.xls; *.xlsx; *.xlsm” – however, you can use this code within Word as well, and amend your filter accordingly.

Select the File, and Click Open.  The code may take while to run – if you mail is sent successfully, you will get the following message.

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!


Learn More!