Sending Reminder from Excel Using Gmail with CDO

How to send reminder mail from Excel using Gmail SMTP server with CDO and VBA. CDO (Collaboration Data Objects) is a Microsoft technology that is designed to simplify the creation of messaging applications.

Watch the video below:

Send Reminder Using Gmail with CDO (Collaboration Data Objects)

Here’s the complete macro code:
Option Explicit

Sub SendEmailUsingGmailWithCDO()
Dim NewMail As CDO.Message
Dim mailConfig As CDO.Configuration
Dim fields As Variant
Dim msConfigURL As String
Dim cell As Range

‘early binding

Set mailConfig = New CDO.Configuration

' load all default configurations
mailConfig.Load -1

Set fields = mailConfig.fields

'Set All Email Properties
For Each cell In Sheets("Sheet1").Columns("D").Cells.SpecialCells(xlCellTypeConstants)
    If cell.Offset(0, 2).Value <> "" Then
        If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 2).Value) = "yes" Then
        Set NewMail = New CDO.Message
With NewMail
    .From = ""
    .To = cell.Value
    .CC = ""
    .BCC = ""
    .Subject = "Payment Reminder"
    .TextBody = "Dear " & cell.Offset(0, -3).Value & " " & cell.Offset(0, -2).Value & " " & cell.Offset(0, -1).Value & vbNewLine & vbNewLine & _
                    "Reminder: Kindly arrange to pay the due amount." & vbNewLine & vbNewLine & _
                    "Dinesh Takyar"
    .AddAttachment "C:\invoices\payment-due-september-2020.xlsx"
End With

msConfigURL = ""

With fields
    .Item(msConfigURL & "/smtpusessl") = True
    .Item(msConfigURL & "/smtpauthenticate") = 1
    .Item(msConfigURL & "/smtpserver") = ""
    .Item(msConfigURL & "/smtpserverport") = 465
    .Item(msConfigURL & "/sendusing") = 2                 'Send using default setting
    .Item(msConfigURL & "/sendusername") = ""
    .Item(msConfigURL & "/sendpassword") = "my password"
    .Update                                               'Update configuration fields
End With
NewMail.Configuration = mailConfig
        End If
    End If
Next cell

'Release memory
Set NewMail = Nothing
Set mailConfig = Nothing

End Sub

How to send reminder mail from Excel using Gmail SMTP server with CDO and VBA
How to send reminder mail from Excel using Gmail SMTP server with CDO and VBA.

Published by

Dinesh Kumar Takyar

Welcome to! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: For a structured Excel VBA training course online you can visit:

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.