Loop through query and send email everyday at 12AM

Private Sub Form_Timer()
Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String
 
Set MyDb = CurrentDb()
Set rsEmail = MyDb.OpenRecordset("passport_expiry_15days", dbOpenSnapshot)
 
With rsEmail
        .MoveFirst
        Do Until rsEmail.EOF
            If IsNull(.Fields(12)) = False Then
            Set email = CreateObject("CDO.Message")
email.Subject = "Passport Expiry Notification 15 days"
email.From = "HR System <hrms@domain.com>"
email.To = .Fields(12)
email.TextBody = "Your passport will expire on " & .Fields(4)
email.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "admin"
email.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "admin"
email.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = "hrms@domain.com"
email.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mail.domain.com"
email.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
email.Configuration.Fields.Update
email.Send
Set email = Nothing
 MsgBox (.Fields(12))
            End If
            .MoveNext
        Loop
End With
Set MyDb = Nothing
Set rsEmail = Nothing
End Sub

Select allOpen in new window