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: