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:

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 = "[email protected]"
    .To = cell.Value
    .CC = "[email protected]"
    .BCC = "[email protected]"
    .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 = "http://schemas.microsoft.com/cdo/configuration"

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

'Release memory
Set NewMail = Nothing
Set mailConfig = Nothing

End Sub