Send Email from GMail VBA (Visual Basic Applications) example

Send Email from GMail VBA (Visual Basic Applications) example

CDO

Collaborative Data Object is a library offered by Microsoft which allows sending emails using SMTP. In this article we will dive into various methods and properties offered by CDO library in VBA and see a complete code example to send emails from GMail.

Step 1: user needs to add reference for Microsoft CDO for Windows 2000 Library which is available in cdosys.dll as shown below:

  1. Navigate Tools menu in Visual Basic Editor
  2. Click on References…
  3. Find and check Microsoft CDO for Windows 2000 Library
  4. Say OK

CDO.Message

Represents a mail object having following properties and methods which can be configured:

  1. Configuration: Message exposes configuration object which has various properties as:
    • Fields: it is a collection which supports various properties to configure SMTP related properties
  2. Subject: takes user input to set mail subject in string format
  3. From: refers to account from which email will be sent from
  4. To: refers to recipients to whom mail will be sent
  5. CC: list of recipients which will be kept in Carbon Copy of the mail
  6. BCC: list of recipients which will be kept in Blind Carbon Copy of the mail
  7. Update: is a method which commits changes made to the property into Message object
  8. AddAttachment: it is a method which allows adding external files into the mail as attachment
  9. CreateMHTMLBody: takes string inf html format and embedded into the email body
  10. TextBody: plan text body of the mail
  11. Send: a method which sends email

Code example

Sub SendEmailUsingGmail(UserFromAccount As String, UserPwd As String, UserTo As String, strSubject As String, strMailBody As String)
	Dim NewMail As CDO.Message
	'Early Binding
	Set NewMail = New CDO.Message
	
	'Enable SSL Authentication
	NewMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True

	'Make SMTP authentication Enabled=true (1)
	NewMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1

	'Set the SMTP server and port Details
	'To get these details you can get on Settings Page of your Gmail Account

	NewMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"

	NewMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465

	NewMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

	'Set your credentials of your Gmail Account
	'Username
	NewMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = UserFromAccount
	'Password
	NewMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = UserPwd

	'Update the configuration fields
	NewMail.Configuration.Fields.Update
	 
	'Set All Email Properties

	With NewMail
		  .Subject = strSubject
		  .From = UserFromAccount
		  .To = UserTo
		  .CC = "a@gmail.com;b@gmail.com;c@gmail.com"
		  .BCC = "d@gmail.com"
		  .Fields("urn:schemas:mailheader:disposition-notification-to") = "myemail@gmail.com"
		  .Fields("urn:schemas:mailheader:return-receipt-to") = "myemail@gmail.com"
		
		' Set importance or Priority to high
		  .Fields("urn:schemas:httpmail:importance") = 2
		  .Fields("urn:schemas:mailheader:X-Priority") = 1
		'
		' Request read receipt
		.Fields("urn:schemas:mailheader:return-receipt-to") = "a@gmail.com"
		.Fields("urn:schemas:mailheader:disposition-notification-to") = "a@gmail.com"
		'
		' Update fields
		.Fields.Update
		
		'body text can be taken from stored file
		.TextBody = "file://Yourcomputer/YourFolder/Week2.xls"
		
		'Add attachements
		.AddAttachment ("C:\Users\jj\Desktop\MyAttCancel.xlsx")
		
		'send a complete webpage, instead of HTMLBody or TextBody use
		.CreateMHTMLBody strMailBody
		
		'Plan text body
		.TextBody = strMailBody
	
	End With
	
	'Send mail
	NewMail.Send

	'Set the NewMail Variable to Nothing
	Set NewMail = Nothing
End Sub

Please leave your comments or queries under comment section also please do subscribe to out blogs to keep your self upto date.

Leave a Reply

Your email address will not be published.