Selective emailing

[Chuck]

I have a database that has a table in it with employee information (name,
dob, email, etc). This is joined to a table that has tasks that are
assigned to each individual that has a recurring date. For example, Joe
must take an annual CPR class, Bob needs to take his Hearing Test, Ann must
take her Eye Exam. The task table keeps the completed date in it, while the
form that displays it calculates the next due date (using dateadd). So, as
simple as this is designed, here is my issue. I need to be able to generate
a form that shows only the employees that are late with their tasks (can be
multiple employees on the same task) and not selecting individuals that are
within limits. Once this is displayed I need to select a command button
that will email these people to inform them that they are late with their
tasks. I would like to see where I can email everyone in one shot instead
of separate emails. The task table can have multiple tasks in it so each
task has to be analyzed and be part of this late report. Any suggestions?

[Pat]

You mention a preference of emailing everyone at once. By doing this, you
might consider is whether you want each person on the recipient list to see
what everyone else is overdue for.

That said, the simplest way (IMO) to send a quick and dirty email on an
NT-based machine (NT, 2K, XP) is with CDONTS. The machine must be running
the SMTP service and have access to the internet. You will also need to add
a reference to the CDO library within Access. After this, it's as simple
as:

Dim oEMail As New CDONTS.EMail

oEMail.From "an*****@anydomain.com"
oEMail.To "yo*******@yourdomain.com"
oEMail.BodyFormat = CdoBodyFormatText
oEMail.Body = "Insert some useful text here"
oEMail.Importance = CdoHigh
oEMail.AttachFile "C:\filename.txt"
oEMail.Send

A great overview of the many options for email with Access is at Tony Toews'
website:
http://www.granite.ab.ca/access/email.htm

HTH
Pat

"Chuck" <cj*****@hvc.rr.com> wrote in message
news:ML********************@twister.nyc.rr.com...

I have a database that has a table in it with employee information (name,
dob, email, etc). This is joined to a table that has tasks that are
assigned to each individual that has a recurring date. For example, Joe
must take an annual CPR class, Bob needs to take his Hearing Test, Ann must take her Eye Exam. The task table keeps the completed date in it, while the form that displays it calculates the next due date (using dateadd). So, as simple as this is designed, here is my issue. I need to be able to generate a form that shows only the employees that are late with their tasks (can be multiple employees on the same task) and not selecting individuals that are within limits. Once this is displayed I need to select a command button
that will email these people to inform them that they are late with their
tasks. I would like to see where I can email everyone in one shot instead
of separate emails. The task table can have multiple tasks in it so each
task has to be analyzed and be part of this late report. Any suggestions?

[Alan Webb]

Chuck,
Finding late tasks is something SQL should do nicely. docmd.sendobject is a
built-in call to the local system's e-mail software to send a message with
an attached object from Access. There is an option to sendnoobject, giving
you the ability to just send the e-mail. So, the algorythm goes something
like:

Private Function NagUsers() as long
'define error handling, if any.

'Get list of late users

'For each late user, concantenate thier e-mail address to a recipient list,
skipping any that don't have an address.

'Call docmd.sendobject with the sendnoobject option and using the previously
created recipient list.

'Clean up anything created to complete the task.

'Exit function

'Error handling would go here.

End Function

"Chuck" <cj*****@hvc.rr.com> wrote in message
news:ML********************@twister.nyc.rr.com...

I have a database that has a table in it with employee information (name,
dob, email, etc). This is joined to a table that has tasks that are
assigned to each individual that has a recurring date. For example, Joe
must take an annual CPR class, Bob needs to take his Hearing Test, Ann must take her Eye Exam. The task table keeps the completed date in it, while the form that displays it calculates the next due date (using dateadd). So, as simple as this is designed, here is my issue. I need to be able to generate a form that shows only the employees that are late with their tasks (can be multiple employees on the same task) and not selecting individuals that are within limits. Once this is displayed I need to select a command button
that will email these people to inform them that they are late with their
tasks. I would like to see where I can email everyone in one shot instead
of separate emails. The task table can have multiple tasks in it so each
task has to be analyzed and be part of this late report. Any suggestions?