Return to VBA Code Examples

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.

vba outlook add reference

Search for the Microsoft CDO Reference, and click OK.

vba gmail cdo reference

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.

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

You can then call your function with the following code

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.

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.

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.

vba gmail select file

 

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 gmail success

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! vba save as


Learn More!