Unanswered: Triggering an Automatic Email Response based on Dates Generated By Query
I would like to create a query that would do the following give me all dates that occurred within the last six months triggered by a date entered into table which when reached it would trigger an email being sent. To explain more detail I am working on a funeral home database. (Which seems like a never ending but I am learning so much so I want to keep building it). Once I enter the date of death field name (DOD) I want query to all deaths that happen prior to past six months from current date. Which would trigger and email that would notify me to send a follow up letter for family. How would I achieve this any ideas?
I have created a query using Names and DOD with the following Criteria <Date()-180 and it does generate the desired clients. However, I have no clue how to make this query trigger email and once email is sent to remove that client next time query is run. Would love to create an executable file that would generate each time database is loaded.
christyxo posted this, however, I am not sure how to make this work or how to implement it.
Private Sub Cmd_Reminder_Click()
Create a query that has the list of DOD for the day. (or when)
docmd.TransferSpreadsheet 'to a file
use it below:
Click Send button to run the email code:
Public Function EmailOutlook(ByVal pvTo, ByVal pvSubj, ByVal pvBody, ByVal pvFile) As Boolean
Dim oApp As Outlook.Application
Dim oMail As Outlook.MailItem
On Error GoTo ErrMail
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(olMailItem)
.To = pvTo
.Subject = pvSubj
If Not IsNull(pvBody) Then .Body = pvBody
.Attachments.Add pvFile, olByValue, 1, "attached file"
EmailOut = True
Set oMail = Nothing
Set oApp = Nothing
MsgBox Err.Description, vbCritical, Err