VBA – Page 2 – Coding Basics

Bottom Line:

Learn how to send automated e-mails from Gmail using Excel VBA, included with some Attachments and Body Text, to predefined or dynamic list of receivers. Other 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.

Note:

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

Skill Level: Beginner

Prerequisite:

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

1. Basic VBA Hands-on knowledge:

I assume that you already have some basic knowledge on how to enable Developer option in Excel, different objects in Visual Basic Editor and coding basics.

You can 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:

Sub GmailVBA()

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

'@@ Define Mail parameters and configurations here
objMessage.Subject = "Test email from Gmail using Excel VBA  | " & Date & " | " & Time
objMessage.From = "YOUR GMAIL" '@@ Gmail from which you willing to send email '@@ (Should be enabled with Less Secure App Access)
objMessage.To = Toadd '@@ List defined in the Sheet1 of this Workbook. Can be Hard coded
objMessage.TextBody = Body '@@ Text defined in the Sheet1 of this Workbook. Can be Hard coded
objMessage.AddAttachment Attch '@@From the path defined in the Sheet1 of this Workbook. Can be Hard coded
objMessage.Configuration.fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objMessage.Configuration.fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com" 'Name or IP of Remote SMTP Server
objMessage.Configuration.fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1 'Type of authentication, NONE, Basic (Base64 encoded), NTLM
objMessage.Configuration.fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "GMAIL ID" '@@ same as in From Address (Your UserID on the SMTP server)
objMessage.Configuration.fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "*******" 'Your password on the SMTP server '@@ Replace ******* with your password
objMessage.Configuration.fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
objMessage.Configuration.fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465 'Server port (typically 25)
objMessage.Configuration.fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True 'Use SSL for the connection (False or True)
objMessage.Configuration.fields.Update

'@@ Send Command
objMessage.Send

'@@ Disable if you do not wish to Save Workbook
ThisWorkbook.Save

End Sub

Tag: VBA

Step-by-step: Send automated e-mails from Gmail using Excel VBA