January 16, 2021

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 = "takyardinesh@gmail.com"
    .To = cell.Value
    .CC = "diya@exceltrainingvideos.com"
    .BCC = "guptao@exceltrainingvideos.com"
    .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") = "takyardinesh@gmail.com"
    .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

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.

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.