Sending Gmails using Excel VBA

 

Bottom Line:

Learn how to send Gmails using Excel VBA, with attachments and body text, to predefined or dynamic list of recipients. Important aspects such as configuration changes, that need to be done in Gmail and Visual Basic Editor , for the code to work as expected, are also covered here.

Note:

Macro Template and other useful links related to Gmail configuration changes are included within/at the end of the post. Please make use of them.

 Skill Level: Beginner

Sample File: VbaToSendGmail.xlsm

Prerequisite:

1. 1. Basic VBA Hands-on knowledge
   2. Gmail configuration changes
   3. Enabling Microsoft CDO for Windows 2000 Library
      

 

1. Basic VBA Hands-on knowledge:

Assuming you already have some basic knowledge on how to enable Developer option, accessing different objects in Visual Basic Editor and basics programming knowledge, providing some useful links relevant to the topic.

Please go through the below links to quickly brush up the concepts:

Getting started with Excel VBA Developer

VBA Resources

Microsoft: Getting started with VBA in Office

 

2. Gmail configuration changes:

One of the most important aspect, for this tool to work, is to enable “Less secure app access” in Gmail account settings from which you are willing to send emails.

Gmail Less Secure App Access Link

 

3. Enabling Microsoft CDO for Windows 2000 Library:

Other important step, that need to done before jumping into writing/using the code, is to enable the “Microsoft CDO for Windows 2000 Library” from :

VBA Editor Window Tools References

Code:

	Private Sub GmailVBA_Click()
	'********************************
	'# Msgbox to confirm if user updated required details in the code
	a = MsgBox("Have you updated the code with your Gmail Credentials", 4, "Choose options")
	' Assume that you press No Button
	If a = 6 Then
	MsgBox ("you are proceeding to the code execution")
	Else
	MsgBox ("Please provide the required details in the code & try again")
	Exit Sub
	End If
	'********************************
	Dim objMessage
	'Assign CDO Obj to Variable
	Set objMessage = CreateObject("CDO.Message")
	'Declare To Address,Body and Attachment Variables
	Dim Toadd, Body, Attch As String
	'Assign To Address,Body and Attachment Variables from Sheet1
	Toadd = ThisWorkbook.Worksheets(1).Range("A2").Value
	Body = ThisWorkbook.Worksheets(1).Range("B2").Value
	Attch = ThisWorkbook.Worksheets(1).Range("C2").Value
	'********************************
	'# Provide Required Gmail parameters
	objMessage.Subject = "Test email from Gmail using Excel VBA | " & Date & " | " & Time
	'Update your Gmail. Should be enabled with Less Secure App Access
	objMessage.From = "***@gmail.com"
	'List defined in the Sheet1 of this Workbook. Can be customised
	objMessage.To = Toadd
	'Text defined in the Sheet1 of this Workbook. Can be customised
	objMessage.TextBody = Body
	'From the path defined in the Sheet1 of this Workbook. Can be customised
	objMessage.AddAttachment Attch
	'********************************
	'# Define Gmail configurations here
	objMessage.Configuration.fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
	'Name or IP of Remote SMTP Server
	objMessage.Configuration.fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
	'Type of authentication, NONE, Basic (Base64 encoded), NTLM
	objMessage.Configuration.fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
	'Same as From address
	objMessage.Configuration.fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "***@gmail.com"
	'Your Gmail password
	objMessage.Configuration.fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "*******"
	objMessage.Configuration.fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
	'Server port (typically 25)
	objMessage.Configuration.fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
	'Use SSL for the connection (False or True)
	objMessage.Configuration.fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
	objMessage.Configuration.fields.Update
	objMessage.Send
	'********************************
	'Disable if you do not want to Save Workbook
	ThisWorkbook.Save
	End Sub

view raw SendGmailVba.vbs hosted with ❤ by GitHub