Send email msg 2 days before project due date

  • Thread starter Thread starter Tiffany
  • Start date Start date
T

Tiffany

How do I:

Create an automatic email notification 2 days prior to a
record's "due date" (this is a project tracking database
and i need to notify user of which projects are DUE in 2
days)

Thank you so much for any assistance you can give!

Tiffany
 
Tiffany said:
How do I:

Create an automatic email notification 2 days prior to a
record's "due date" (this is a project tracking database
and i need to notify user of which projects are DUE in 2
days)

Thank you so much for any assistance you can give!

Tiffany
Click to expand...

It seems to me that there are two parts to this problem, Tiffany: (1)
automatically identify, on a daily basis, those projects that will be
due two days from now (and that haven't yet had a notice sent, and (2)
actually send the e-mail for each such project. Part 2 is actually
pretty easy, provided that your Access version is up to date on its
service packs (the unpatched version of Access 2000 had a bug in the
SendObject method). Part 1 requires a little thought.

First, you must recognize that the database must be open in order for
the code to run. So to make this work at all, you have to open the
database at least once a day. Second, how you handle the scheduling
will depend on whether the database is opened at least once a day, or
whether it is left open all the time or overnight. If it's opened at
least once a day (and closed overnight), then you can use a startup form
or autoexec macro to run the code. If the database is left open, on the
other hand, you need to use the Timer event of some form that is always
open to run your code at least once a day.

Either way, I would add a yes/no field named "NotificationSent" to your
Projects table, and give it a default value of False. Now you need a
query that will select the project records that should have an e-mail
sent, and include the necessary fields. I don't know your table or
field names, of course, but I can imagine a query with SQL like this:

SELECT
ProjectID,
ProjectName,
EmailAddress,
DueDate,
NotificationSent
FROM Projects
WHERE (NotificationSent = False)
AND EmailAddress Is Not Null
AND (DateDiff("d", Date(), DueDate) <= 2);

Suppose this query is saved under the name
"qryProjectsForNotifications". Then your code (run by whatever
scheduling mechanism seems appropriate, might be embodied in a procedure
like this:

'----- start of example code ("air code") -----
Public Sub SendProjectDueNotifications()

' *** Error-handling to be added by Tiffany ***

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("qryProjectsForNotifications")

With rs
Do Until .EOF

DoCmd.SendObject acSendNoObject, _
To:=!EmailAddress, _
Subject:="Notice: Project Due", _
MessageText:="--- Automated Notice ---" & vbCrLf &
vbCrLf & _
"Project '" & !ProjectName & "' is due on " & _
Format(!DueDate, "short date") & ".", _
EditMessage:=False

.Edit
!NotificationSent = True
.Update

.MoveNext
Loop
.Close
End With

Set rs = Nothing

End Sub
'----- end of example code -----
 
Back
Top