Send Google Mail From Excel VBA Macro

In a previous post we saw how to send emails from Excel via Outlook and from a Microsoft email account. We could use the same method to send email with Gmail by adding the Gmail account to Outlook and specifying to send using that account. However, if we don’t want to use Outlook at all, we can also send an email from Excel via a Gmail account using the Collaboration Data Objects (CDO) API for Windows. In this post we see a macro that sends an email from Excel using a Gmail account.

Macro code:

 
 Sub SendGmailFromExcel()
 
 Dim CDOmail As Object, CDOconfig As Object, fields As Variant
 Set CDOmail = CreateObject("CDO.Message")
 Set CDOconfig = CreateObject("CDO.Configuration")
 CDOconfig.Load -1
 
 Set fields = CDOconfig.fields
 
 With fields
 .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
 .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
 .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
 .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
 .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
 .Item("http://schemas.microsoft.com/cdo/configuration/sendusername")="email@gmail.com"
 .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "xxxxxxxxxx"
 .Update
 End With
 
 With CDOmail
     Set .Configuration = CDOconfig
     .From = " yourgmail@gmail.com"
     .To = "recipientemail@domain.com"
     .Subject = "test"
     .TextBody = "This is a test email sent from Excel via Gmail"
     .Send
 End With
 
 End Sub
 

Macro explained:

• First of all we need to add the Microsoft CDO for Windows 2000 library. We declare CDOmail and CDOconfig as objects and fields as a variant.
• Then we create the CDO message and configuration objects that are assigned to the respective variables and load the CDO configuration.
• We need to target certain CDO configuration fields to setup the connection with the SMTP server. These are the steps to setup the connection:
• Enable SSL Authentication
• Specify SMTP server (smtp.gmail.com)
• Specify SMTP port, which can be 465 or 25
• Enable SMTP authentication
• Indicate to use default settings to send the message
• Add sender Google email address
• Add Google account app password
• IMPORTANT: We need to setup the Google account with 2-factor authentication to use this method. Then you can get an app password (not the same as your Google account password) going to Google account -> Security -> 2-step authentication to generate an app password for mail application.
• Finally, we assign that CDO configuration to the CDO message object and specify the sender and recipient email, subject, body, to send the email from Excel via the Gmail account

This is how we Send Google Mail From Excel using VBA macros.

Other examples:

• How to Export Range As Picture with Excel VBA macros
• How to Sort Data With Custom List with Excel VBA macros
• How to Add Slicers To Table Headers with Excel VBA macros