Uploaded October 2020 | Updated November 2026, 1 day ago
How to send reminder mail from Excel using Gmail SMTP server with CDO and VBA.
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 NOT EQUAL "" 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 = "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
NOTE: Replace 'NOT EQUAL' with angular brackets.
How to send reminder mail from Excel using Gmail SMTP server with CDO and VBA.
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 NOT EQUAL "" 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 = "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
NOTE: Replace 'NOT EQUAL' with angular brackets.

![Using Activex Controls SQL VBA to Analyze Excel Data
How to use activex controls like combo-boxes and command buttons, SQL and VBA to analyze Excel worksheet data super fast automatically.
Heres the complete VBA code:
Option Explicit
Public conn As New ADODB.Connection
Public myrs As New ADODB.Recordset
Public strSQL As String
Public Sub OpenDB()
If conn.State = adStateOpen Then conn.Close
conn.ConnectionString = Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ= & _
ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name
conn.Open
End Sub
Public Sub closeRS()
If myrs.State = adStateOpen Then myrs.Close
myrs.CursorLocation = adUseClient
End Sub
Option Explicit
Private Sub cmdClear_Click()
clear the data
cboVehicleModel.Clear
cboRegion.Clear
cboCustomerType.Clear
Sheet2.Visible = True
Sheet2.Select
Range(dataset).Select
Range(Selection, Selection.End(xlDown)).ClearContents
End Sub
Private Sub cmdDisplayData_Click()
populate data
strSQL = SELECT * FROM [Sheet1$] WHERE
If cboVehicleModel.Text Not Equal To Then
strSQL = strSQL & [Vehicle Model]= & cboVehicleModel.Text &
End If
If cboRegion.Text Not Equal To Then
If cboVehicleModel.Text Not Equal To Then
strSQL = strSQL & AND [Region]= & cboRegion.Text &
Else
strSQL = strSQL & [Region]= & cboRegion.Text &
End If
End If
If cboCustomerType.Text Not Equal To Then
If cboVehicleModel.Text Not Equal To Or cboRegion.Text Not Equal To Then
strSQL = strSQL & AND [Customer Type]= & cboCustomerType.Text &
Else
strSQL = strSQL & [Customer Type]= & cboCustomerType.Text &
End If
End If
If cboVehicleModel.Text Not Equal To Or cboRegion.Text Not Equal To Or cboCustomerType.Text Not Equal To Then
now extract data
closeRS
OpenDB
myrs.Open strSQL, conn, adOpenKeyset, adLockOptimistic
If myrs.RecordCount Greater Than 0 Then
Sheet2.Visible = True
Sheet2.Select
Range(dataset).Select
Range(Selection, Selection.End(xlDown)).ClearContents
Now putting the data on the sheet
ActiveCell.CopyFromRecordset myrs
Else
MsgBox No matching records found!, vbExclamation + vbOKOnly
Exit Sub
End If
getting the total revenue using Query
If cboVehicleModel.Text Not Equal To And cboRegion.Text Not Equal To And cboCustomerType.Text Not Equal To Then
strSQL = SELECT SUM ([Sheet1$].[Cost]) FROM [Sheet1$] WHERE ((([Sheet1$].[Vehicle Model]) = & cboVehicleModel.Text & ) And & _
(([Sheet1$].[Region]) = & cboRegion.Text & ) And (([Sheet1$].[Customer Type]) = & cboCustomerType.Text & ));
closeRS
OpenDB
myrs.Open strSQL, conn, adOpenKeyset, adLockOptimistic
If myrs.RecordCount Greater Than 0 Then
Range(I6).CopyFromRecordset myrs
MsgBox The total revenues from & cboVehicleModel.Text & in & cboRegion.Text & from & cboCustomerType.Text & were & Range(I6).Value, vbExclamation + vbOKOnly
Else
Range(I6).Clear
MsgBox The total revenue could not be retrieved!, vbExclamation + vbOKOnly
Exit Sub
End If
End If
End If
End Sub
Private Sub cmdUpdate_Click()
strSQL = Select Distinct [Vehicle Model] From [Sheet1$] Order by [Vehicle Model]
closeRS
OpenDB
cboVehicleModel.Clear
myrs.Open strSQL, conn, adOpenKeyset, adLockOptimistic
If myrs.RecordCount Greater Than 0 Then
Do While Not myrs.EOF
cboVehicleModel.AddItem myrs.Fields.Item(Vehicle Model)
myrs.MoveNext
Loop
Else
MsgBox No unique vehicle models found!, vbCritical + vbOKOnly
Exit Sub
End If
strSQL = Select Distinct [Region] From [Sheet1$] Order by [Region]
closeRS
OpenDB
cboRegion.Clear
myrs.Open strSQL, conn, adOpenKeyset, adLockOptimistic
If myrs.RecordCount Greater Than 0 Then
Do While Not myrs.EOF
cboRegion.AddItem myrs.Fields.Item(Region)
myrs.MoveNext
Loop
Else
MsgBox No unique regions found!, vbCritical + vbOKOnly
Exit Sub
End If
strSQL = Select Distinct [Customer Type] From [Sheet1$] Order by [Customer Type]
closeRS
OpenDB
cboCustomerType.Clear
myrs.Open strSQL, conn, adOpenKeyset, adLockOptimistic
If myrs.RecordCount Greater Than 0 Then
Do While Not myrs.EOF
cboCustomerType.AddItem myrs.Fields.Item(Customer Type)
myrs.MoveNext
Loop
Else
MsgBox No unique customer types found!, vbCritical + vbOKOnly
Exit Sub
End If
End Sub Using Activex Controls SQL VBA to Analyze Excel Data](https://i.ytimg.com/vi/D0rNN9KyQZg/mqdefault.jpg)





![Automatically Find First and Last Working Day in Month
How to automatically find first and last working day in any month using VBA.
Below is the recorded and optimized code:
Option Explicit
Sub FirstLastWorkingDayInMonth()
FirstLastWorkingDayInMonth Macro
How to find the first and last working day in a month given any date of the month
Range(A2).Select
ActiveCell.FormulaR1C1 = First working Day
Range(C2).Select
ActiveCell.FormulaR1C1 = _
=WORKDAY(DATE(YEAR(R[-1]C),MONTH(R[-1]C),1)-1,1,RC[2]:R[3]C[2])
Range(C2).Select
Selection.NumberFormat = m/d/yyyy
Range(E2).Select
Selection.ClearContents
Range(E2).Select
ActiveCell.FormulaR1C1 = 1-Jan-2020
Range(A3).Select
ActiveCell.FormulaR1C1 = Last Working Day
Range(C3).Select
ActiveCell.FormulaR1C1 = _
=WORKDAY(DATE(YEAR(R[-2]C),MONTH(R[-2]C)+1,1)-1,R[-1]C[2]:R[2]C[2])
Range(C3).Select
ActiveCell.FormulaR1C1 = _
=WORKDAY(DATE(YEAR(R[-2]C),MONTH(R[-2]C)+1,1),-1,R[-1]C[2]:R[2]C[2])
Range(C3).Select
Selection.NumberFormat = m/d/yyyy
Range(C1).Select
ActiveCell.FormulaR1C1 = 1/15/2020
Range(E3).Select
ActiveCell.FormulaR1C1 = 1/31/2020
Range(E3).Select
ActiveCell.FormulaR1C1 = 1/26/2020
Range(K3).Select
Range(A2) = First Working Day
Range(C2) = =WORKDAY(DATE(YEAR(R[-1]C),MONTH(R[-1]C),1)-1,1,RC[2]:R[3]C[2])
Range(C2).NumberFormat = m/d/yyyy
Range(A3) = Last Working Day
Range(C3) = =WORKDAY(DATE(YEAR(R[-2]C),MONTH(R[-2]C)+1,1),-1,R[-1]C[2]:R[2]C[2])
Range(C3).NumberFormat = m/d/yyyy
End Sub Automatically Find First and Last Working Day in Month](https://i.ytimg.com/vi/EFE_PULYBKg/mqdefault.jpg)


