Automatically send email if criteria exists when database is launched

yes,  I generally do this on the Form Load event of my splash form

Create the query that queries the table, something like:

SELECT ccEmpEmailAddresses, EmailReminderSent, EmailSentDate
 FROM yourTable
WHERE [AnniversaryDate] < Date()
AND [EmailReminderSent] = False

Then in the load event, create a recordset based on this query and loop through the recordset and use the DoCmd.SendObjects method to create an email to each email address.  After each email is sent, update the [EmailReminderSent] and [EmailSentDate] fields before moving on to the next record in the recordset.

Ok.  I have the query designed and when I run it I get the right records.  But I do not know how to create a recordset based on this query and loop through the recordset and use the DoCmd.SendObjects method to create an email to each email address.  After each email is sent, update the [EmailReminderSent] and [EmailSentDate] fields before moving on to the next record in the recordset.

Private sub Form_Load:

   Dim strSQL as string
   Dim rs as DAO.Recordset
   Dim strEmail as string, strSubject as string, strMsg as string

   strSubject = "Enter your subject here"
   strMsg = "Enter your message body here"
   set rs = currentdb.querydefs("YourQueryName").openrecordset
   While not rs.eof

        strEmail = rs!ccEmpEmailAddresses
        docmd.SendObject acSendNoObject,,, strEmail,,,strSubject, strMsg, false
        rs.Edit
        rs![EmailReminderSent] = true
        rs![EmailSentDate] = Date()
        rs.Update
        rs.movenext
    Wend

    rs.close
    set rs  = nothing

End Sub

Select allOpen in new window

So far... PERFECT.  But is there a way to get rid of the attached message that is popping up before each email is sent?
warning.jpg

That is a remnant of Microsoft Office Security.  I use a program called vbMapi  but that costs about $100 and also involves modifying the code above.

There are other methods, search EE on "Access Sendobject security warning", but I've found the above to be the most effective.

I am testing this code as suggested previously.  But now I need to add to the body of the email, the customer name and invoice number which I have added in the query as the 4th and 5th fields in the query.  What do I have to do to this code to make that happen?

Private Sub Form_Load()

   Dim strSQL As String
   Dim rs As DAO.Recordset
   Dim strEmail As String, strSubject As String, strMsg As String

   strSubject = "Subject copy to be written"
   strMsg = "Need to get customer name and invoice number here."
   Set rs = CurrentDb.QueryDefs("qryFindRecordsForAutoEmail").OpenRecordset
   While Not rs.EOF

        strEmail = rs!ccEmpEmailAddresses
        DoCmd.SendObject acSendNoObject, , , strEmail, , , strSubject, strMsg, False
        rs.Edit
        rs![EmailReminderSent] = True
        rs![EmailSentDate] = Date
        rs.Update
        rs.MoveNext
    Wend

    rs.Close
    Set rs = Nothing

End Sub

And yes, the body of the email is no longer static but now has to change with each email sent depending on the record.

Am I even close?  Here is what I have but when the form loads I get error:   Invalid use of null and this line of code is highlighted in yellow:

strEmail = rs!ccEmpEmailAddresses


My attempt:

Private Sub Form_Load()

   Dim strSQL As String
   Dim rs As DAO.Recordset
   Dim strEmail As String, strSubject As String, Customer As String, InvoiceN As String, strToAddress As String

   strSubject = "The afore mentioned customer's contract is up for renewal. Please arrange to send the invoice."
   Set rs = CurrentDb.QueryDefs("qryFindRecordsForAutoEmail").OpenRecordset
   While Not rs.EOF

        Customer = rs!Customer
        InvoiceN = rs!InvoiceN
        strEmail = rs!ccEmpEmailAddresses
        DoCmd.SendObject acSendNoObject, , , strToAddress, strEmail, , strSubject, "Customer: " & [Customer] & " - Invoice Number: " & [InvoiceN], False
        rs.Edit
        rs![EmailReminderSent] = True
        rs![EmailSentDate] = Date
        rs.Update
        rs.MoveNext
    Wend

    rs.Close
    Set rs = Nothing

End Sub

That error impils that the email address is missing.

You might want to:

1.  either change the query to exclude those records where the email address is missing, or
2.  Add an if statement right after the While, something like:

   While Not rs.EOF

        if IsNull(rs!ccEmpEmailAddresses) Then
              docmd.sendObject acSendNoObject,,,stevel13@whatever,, "Customer without email", "Customer: " & rs!Customer & " is missing email address!", false
        Else
            Customer = rs!Customer
            InvoiceN = rs!InvoiceN
            strEmail = rs!ccEmpEmailAddresses
            DoCmd.SendObject acSendNoObject, , , strToAddress, strEmail, , strSubject, "Customer: " & [Customer] & " - Invoice Number: " & [InvoiceN], False
            rs.Edit
            rs![EmailReminderSent] = True
            rs![EmailSentDate] = Date
            rs.Update
        end if
        rs.MoveNext
    Wend

Select allOpen in new window

This would send you an email (make sure your email is correct) to indicate that each customer where the email is blank, as a reminder to update those records in your table.

I'm sure I'm making this more difficult than it need be.  But here is my code now.  I had to add a field to the code (EmailAddress) which is supposed to be the "To:" address.  When I launch the database I get an error, "Item not found in this collection" and when I debug, the line "ToEmail = rs!EmailAddress" is highlighted in yellow.

Here's the code currently: (Note than I have commented some lines out for now)...

Private Sub Form_Load()
   Dim strSQL As String
   Dim rs As DAO.Recordset
   Dim strEmail As String, strSubject As String, Customer As String, InvoiceN As String, strToAddress As String, ToEmail As String

   strSubject = "The afore mentioned customer's contract is up for renewal. Please arrange to send the invoice."
   Set rs = CurrentDb.QueryDefs("qryFindRecordsForAutoEmail").OpenRecordset


   While Not rs.EOF

        'If IsNull(rs!ccEmpEmailAddresses) Then
              'docmd.sendObject acSendNoObject,,,stevel13@whatever,, "Customer without email", "Customer: " & rs!Customer & " is missing email address!", false
        'Else
            Customer = rs!Customer
            InvoiceN = rs!InvoiceN
            strEmail = rs!ccEmpEmailAddresses
            ToEmail = rs!EmailAddress
            'DoCmd.SendObject acSendNoObject, , , strToAddress, strEmail, , strSubject, "Customer: " & [Customer] & " - Invoice Number: " & [InvoiceN], False
            DoCmd.SendObject acSendNoObject, , ToEmail, strToAddress, strEmail, , strSubject, "Customer: " & [Customer] & " - Invoice Number: " & [InvoiceN], False
            rs.Edit
            rs![EmailReminderSent] = True
            rs![EmailSentDate] = Date
            rs.Update
        'End If
        rs.MoveNext
    Wend
    rs.Close
    Set rs = Nothing

End Sub

The field was missing from the query.