Sending Emails for Incomplete Action Items

[MattS]

I have a table of action items assigned to each person with a date it is due.
The table is simple. The fields are
[AssignedBy][DateAssigned][AssignedTo][ActionRequired][DateDue][DateCompleted][CompletedComments].
I have the field [AssignedTo] setup with a lookup to another table that has
[ContactName][ContactEmail]. I have the field save the email in the action
item table. I want setup something that will send emails to the [AssignedTo]
people that have action items that are not completed. I have made a macro
that emails people using a field from a form but it exported a whole form
which is close but not what I need. I hope can explain this. I want an email
sent to each person for each action item and not show records that they are
not listed in. I would love to set it up on a timer but a button that
generates the emails would work too.

 

[Dale Fye]

Matt you could set this up on a timer using a Timer event on a form.
However, if you are using Outlook as your email software you will not be
able to do this unattended, but should be able to do it in a command button.

First, create a form and put a textbox (txt_AssignedTo) and a command button
on it. Because SendObject will not allow you to filter a report when you
send it, you must create the report so that it only generates the data for a
single AssignedTo person at a time. To do this, put code similar to the
following in the click event of the command button.

Private Sub cmd_OverdueEmail_Click

Dim strSQL as string
Dim rs as dao.recordset

strSQL = "SELECT DISTINCT AssignedTo, ContactEmail " _
& " FROM Table1 INNER JOIN Table2 " _
& " ON Table1.AssignedTo = Table2.AssignedTo " _
& "WHERE DateDue < Date() " _
& " AND DateCompleted IS NULL"
set rs = currentdb.openrecordset(strSQL)

While not rs.eof

Me.txt_AssignedTo = rs("AssignedTo")
docmd.SendObject acSendReport, "ReportName", , rs("ContactEmail"),
, , _
"Overdue Actions", "Attached report provides info on
overdue actions", false
rs.movenext
Wend

rs.close
set rs = nothing

End Sub

Now, create a report based on a query that is similar to the following:

SELECT AssignedTo, ContactName, ContactEmail, ActionRequired, DateDue
FROM table1 INNER JOIN table2 ON Table1.AssignedTo = Table2.AssignedTo
WHERE DateDue <= Date()
AND DateComplete IS NULL
AND AssignedTo = Forms![YourForm].txt_AssignedTo

The critical aspect of this is that the query has to referenct the textbox
on your form.

HTH
Dale