Automating Emailed Reports from Access

[rhmorrison]

Hi,

I have a database to track appointments for consultants. What I need
to do is run a report for each consultant and email their individual
reports to them. I'd like to be able to do this from one command
button on the main form. Any ideas how I would do this?

Thanks
r.

 

[Dale Fye]

The way I have done this in the past is to:

1. Add an unbound textbox to your form (you can hide it).
2. Create your report, and have the query that supports it based on that
unbound text box (consultant).
3. Then, in the code behind the Click event of your command button, create
a recordset of consultants (SELECT ConsultantID, C.email from Consultants C
INNER JOIN ConsultantSchedule CS ON C.ConsultantID = CS.ConsultantID WHERE
CS.ApptDate = #1/15/2008#)

4. Loop through the consultants, populate the unbound textbox with the
ConsultantID retrieve in this recordset
5. Inside the loop, use SENDOBJECT to send the report to the consultant.
Depending on which security updates you have, and what your email software
is, you will probably get a security warning for each of the SendObject
messages. Although I've recently seen a message on my machine that allows me
to set a timeframe where I can use multiple SendObject commands and only get
the security warning once.

HTH
Dale

 

[Tom van Stiphout]

On Sun, 13 Jan 2008 21:56:37 -0800 (PST), (e-mail address removed)
wrote:

DoCmd.SendObject is the simplest way to send a report with
appointments. Your report would use a query that "looks back" on a
form to see for which ConsultantID the report would be generated.
Once you have that, just execute that in a loop over all consultants.
In the loop you would also set that "look back field" to the current
ConsultantID.

-Tom.