Loop to export report and email to each person

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database that prepares data to notify our clients if there was any
microbiological growth on their samples. The database will look for a
non-null value in [strCount]. It will then pull in the [strProjectManager],
[strClient], [strSampleName], etc. This is used at the data for
[rptClientEmails], which I currently preview, export to Word, see which
project managers are listed, then manually e-mail the document. The report is
groups by [strProjectManager], then [strClient], then [dtmCollectionDate],
with about 5 fields at the detail level.

Its possible to have multiple samples for a given client, and multiple
clients for a given project manager.

What I would like to do is have some code that runs the query, selects a
project manager, run the query / report with only that project manager’s
data, export to Word (rich text), e-mail the report, then move on to the next
project manager and repeat.
 
charliesangels said:
What I would like to do is have some code that runs the query, selects a
project manager, run the query / report with only that project manager’s
data, export to Word (rich text), e-mail the report, then move on to the next
project manager and repeat.
Click to expand...

So you need two queries. One by sample and another with the sample's
project managers on it.

For sample recordset looping logic see
http://www.granite.ab.ca/access/email/recordsetloop.htm

So you will have the loop for the project manager(s) records that
match that sample inside the loop for the sample.

Within the second loop you will do your work of sending the emails,
etc, etc.

Now why use Word? Why not create the report and then make a PDF file
from it? That would be less hassle I would think.
A2000ReportToPDF is an Access 2000 database containing a function to
convert Reports and Snapshot files to PDF documents. No PDF Printer
driver is required.
http://www.lebans.com/reporttopdf.htm

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
Back
Top