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]

Re: Selective emailing

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 "anyname@anydom ain.com"
oEMail.To "youremail@your domain.com"
oEMail.BodyForm at = CdoBodyFormatTe xt
oEMail.Body = "Insert some useful text here"
oEMail.Importan ce = CdoHigh
oEMail.AttachFi le "C:\filename.tx t"
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" <cjr1958@hvc.rr .com> wrote in message
news:MLG2c.3345 $tP6.2692417@tw ister.nyc.rr.co m...[color=blue]
> 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[/color]
must[color=blue]
> take her Eye Exam. The task table keeps the completed date in it, while[/color]
the[color=blue]
> form that displays it calculates the next due date (using dateadd). So,[/color]
as[color=blue]
> simple as this is designed, here is my issue. I need to be able to[/color]
generate[color=blue]
> a form that shows only the employees that are late with their tasks (can[/color]
be[color=blue]
> multiple employees on the same task) and not selecting individuals that[/color]
are[color=blue]
> 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?
>
>[/color]

[Alan Webb]

Re: Selective emailing

Chuck,
Finding late tasks is something SQL should do nicely. docmd.sendobjec t 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.sendobjec t 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" <cjr1958@hvc.rr .com> wrote in message
news:MLG2c.3345 $tP6.2692417@tw ister.nyc.rr.co m...[color=blue]
> 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[/color]
must[color=blue]
> take her Eye Exam. The task table keeps the completed date in it, while[/color]
the[color=blue]
> form that displays it calculates the next due date (using dateadd). So,[/color]
as[color=blue]
> simple as this is designed, here is my issue. I need to be able to[/color]
generate[color=blue]
> a form that shows only the employees that are late with their tasks (can[/color]
be[color=blue]
> multiple employees on the same task) and not selecting individuals that[/color]
are[color=blue]
> 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?
>
>[/color]