Loop through query and send email everyday at 12AM

MAS
MAS used Ask the Experts™
on
I used this code to loop through a query and send email but I get this error.
Is there anyone can assist me?
test.JPG

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

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
Try determining whether this is failing on a particular setting or on any/all, by moving the Update (or temporarily commenting out some of the items), so that you are beginning with one setting and progressively adding more.
Top Expert 2014

Commented:
If you comment the statement, does the code work?
MASEE Solution Guide - Technical Dept Head
Most Valuable Expert 2017

Author

Commented:
-->If you comment the statement, does the code work?
No
@mbizup
I tested line by line with no luck
Since you are setting the port, the values that need to be set are SendUsing and SMTPServerPort. Try inserting this code to Line 24 (before setting the port):
oMailConfig.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

Open in new window

The valid values for SendUsing  are:
  • 1 CDO.cdoSendUsingPickup,
  • 2 CDO.cdoSendUsingPort, and
  • 3 CDO.cdoSendUsingExchange.
MASEE Solution Guide - Technical Dept Head
Most Valuable Expert 2017

Author

Commented:
Yes this did the trick. Thank you MlandaT.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial