Automatic email alerts

[Ecogambler]

Hi people, I wonder if anyone out there can give me some advice. I have a
database where users input various requests that have deadlines to be met.
What I would like to happen is for an email to be automatically sent when the
deadline is approaching, say one week in advance of the deadline for example.

Any ideas would be greatly appreciated!

Ecogambler

 

[Dale Fye]

I would add a Yes/No field (AlertSent) to the table. You might want to make
this a date/time field if you want to know when the alert was sent.

Then have a command button or some other technique (maybe in the
applications startup procedure) that generates a recordset of those suspenses
that are approaching that have not been completed, which also have not
received an alert:

SELECT E.EmpLastName, E.EmpFirstName, E.EmpEmail,
P.ProjectName, P.SuspDate
FROM tbl_Projects P INNER JOIN tbl_Employees E
ON P.ProjLead = E.EmpID
WHERE DateDiff("d", Date(), P.SuspDate) < 8
AND AlertSent <> -1

Then loop through the recordset, create a message that is a reminder of the
upcoming suspense, and send that message to the individual using the
Docmd.SendObject method.

Once you have sent the message, update the AlertSent field of the record to
True, and then continue with the next record.

HTH
Dale