Emailing report sections

  • Thread starter Thread starter Biss
  • Start date Start date
B

Biss

Access 2007

I have a report that I sorted bye sales groups, In each sales group are the
details.

I need to email to each group their part of the report. I would even be ok
with coping and pasting the groups.

So far I have run into a blank wall..

Thanks in advance

Bob
 
Biss said:
Access 2007

I have a report that I sorted bye sales groups, In each sales group are
the details.

I need to email to each group their part of the report. I would even be
ok with coping and pasting the groups.

So far I have run into a blank wall..
Click to expand...

Filter the report so that it returns data for only one group and Email them
that report. Repeat for all the other groups.

All of this can be automated if you are interested in pursuing that
 
Rick thanks so much for your advice, so obvious that I didn't think of it.

How would you suggest automating this process as some week there are more or
less groups than the week before..

Bob
 
Biss said:
Rick thanks so much for your advice, so obvious that I didn't think of it.

How would you suggest automating this process as some week there are more
or less groups than the week before..
Click to expand...

There are almost as many strategies for that as there are Access developers.
Suggesting one for someone else to use is mostly determined by their skill
level and specific requirements.

Are you using SendObject to send the Emails?
Are you familiar with writing VBA code?

One of the headaches with Access is that reports only allow an on-the-fly
filter when using the OpenReport method of DoCmd. You cannot easily apply
such a filter when using OutputTo or SendObject. Working around that is the
first thing to solve for something like this.

One of the simplest work-arounds is to exploit a behavior Access has which
is that processing a report that is already opened in preview mode (with a
filter applied) will cause that same filter to be applied to the new
process. So if you open a report with...

DoCmd.OpenReport "SalesReport",acViewPreview,,"SalesGroup = 'GroupName'"

....and then call SendObject on that same report, the report attached to your
Email will also be filtered on that same group name.

Presumably you would be pulling the value for the "GroupName" filter from
somewhere rather than hard-coding it. One way to do that would be to create
a Recordset in your code that would return all the group names that had
activity on the particular week being processed. If possible that same
Recordset could include the Email address to use for that group. You would
then loop through that Recordset processing each report.

(overly simplified pseudo-example)

dim db as Database
dim rs as DAO.Recordset
dim sql as String

Set db = CurrentDB
sql = "SQL that returns list of Group Names and Email addresses"
Set rs = db.OpenRecordset(sql,dbOpenSnapshot)

Do Until rs.EOF
DoCmd.OpenReport "SalesReport",acViewPreview,,"SalesGroup = '" &
rs!GroupName & "'"
DoCmd.SendObject acSendReport,"SalesReport",acFormatSNP,rs!EmailAddress
DoCmd.Close acReport,"SalesReport"
Loop

The above should be enough to get you started (or at least produce some more
questions). My overall advice is to map out all the steps that need to
happen for the entire process and then work out how to do each piece in
isolation. Stitching the steps together into a single process that does
everything is fairly simple once you have them all worked out.
 
I sell some code for batch reporting and emailing.
http://www.rptsoftware.com/products/email/

For example you can call our function to create a report (filtered on the
fly)
Result = RPT_CreateSingleFile("rptExample",
"C:\Reports\Report1.pdf","PDF","WHERE Group = 'Marketing'")

or you can populate our tblreportqueue table with report requests and it
will make tons of files.

The code also does email templates (with merge fields etc...).

The basic approach for filtering a report is similar to what Rick mentions,
we just wrapped it all up in some nice functions.
It will also create a query on the fly to base your report off if you want
to get advanced.

Also we capture the error if the email did not go out (example: no email
address for this group).

You get all source code,
Might help so I thought I would mention,
Mark Andrews
RPT Software
http://www.rptsoftware.com
http://www.donationmanagementsoftware.com
 
Back
Top