Automatically send email if criteria exists when database is launched

In an Access database there is a table named tblInvoicingMaster.  In this table are four fields.  One is named AnniversaryDate (date field), another is named ccEmpEmailAddresses (text field), and the 3rd one is named EmailReminderSent which is a yes/no field and the 4th one is EmailSentDate (date field).

What I want to make happen if it is possible is when the database is launched, have emails sent automatically.  The criteria for sending the emails would be:

1) The checkbox field, EmailReminderSent = false
2) The current date is equal to or greater than the AnniversaryDate

When the email is sent each one must have a static subject line and a static email body text. Also when the email has been sent the field EmailReminderSent has to be flagged as true so the email doesn't get sent again any time the database is launched and also the 4th field in the table needs to be populated with the date the email was sent.  Somehow this code would have to loop through all the records that meet the criteria.

Is this possible?
SteveL13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
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.
0
SteveL13Author Commented:
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.
0
Dale FyeCommented:
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

Open in new window

0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

SteveL13Author Commented:
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
0
Dale FyeCommented:
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.
0
SteveL13Author Commented:
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
0
SteveL13Author Commented:
And yes, the body of the email is no longer static but now has to change with each email sent depending on the record.
0
SteveL13Author Commented:
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
0
Dale FyeCommented:
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

Open 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.
0
SteveL13Author Commented:
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
0
Dale FyeCommented:
The "Item not found in this collection" error means that you are referring to a field that does not exist in the recordset you are referencing.

You really need to be consistent in naming your variables.  I always preface string variables with 'str'.

In your SendObject statement you are missing a comma (there should be 3 commas between acSendNoObject and ToEmail.  

You also used three email addesses for To, CC, and BCC, but I don't see where you are assigning a value to strToAddress.

DoCmd.SendObject acSendNoObject, , ,  ToEmail, strToAddress, strEmail,

Can you post the SQL you are using for query: qryFindRecordsForAutoEmail

Does that query return the records you want it to, with all of the data you want?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveL13Author Commented:
The field was missing from the query.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.