email multiple reports from one report

  • Thread starter Thread starter LeVlo
  • Start date Start date
L

LeVlo

I want to be able to send many reports with separate data to separate email
addresses. Currently I have written the 100+ queries and created the 100+
reports, each with the parameter that I think I should somehow be able to
change "dynamically?" Can I create one report on one query that will take
each set of results and send it to each of the 100+ recipients? I add to the
list of 100+ a couple of times a week. In TblLeadSource, the 100+ codes are
Like "RP*". Each stands for a sales rep. I email each sales rep a report on
their submissions using a macro that outputs each report to Outlook. The
need to enter each email address is not a bad problem. I am more worried
about having to add a new query, report, and macro step every time we add a
new sales rep. We are using Windows XP and Office 2007. I write only very
limited code.
 
Can you answer this please..
Are the 100+ queries the exact same except for 1 paramater (i.e. the
repnumber)?
Can you post the exact details of the query and structure of the tables
involved and I will suggest a code based approach to use.

You will need to delve into a bit of SQL and vbasic at some time.
 
Yes, the 100+ queries are exactly the same except for 1 parameter. Here is
one of them.
SELECT TblRepLeadUpload.LeadSource, TblRepLeadUpload.FirstContactDate,
TblRepLeadUpload.CompanyName, TblRepLeadUpload.RepLeadStatus
FROM TblRepLeadUpload
WHERE (((TblRepLeadUpload.LeadSource)="RP-BANKS"))
ORDER BY TblRepLeadUpload.LeadSource, TblRepLeadUpload.RepLeadStatus;

I'm not sure what I should tell you about the tables.
TblRepLeadUpload.BusinessPhone is the primary key. The table is related to
three other tables by TblRepLeadUpload.RepLeadUploadID. The data in
TblRepLeadUpload.LeadSource are members of a list in TblLeadSource, which is
not related, but is the place I create the new codes such as RP-BANKS.
TblRepLeadUpload and the three related tables are cleared every morning near
the beginning of the macro series.
 
I want to be able to send many reports with separate data to separateemail
addresses.  Currently I have written the 100+ queries and created the 100+
reports, each with the parameter that I think I should somehow be able to
change "dynamically?"  Can I create one report on one query that will take
each set of results and send it to each of the 100+ recipients?  I add to the
list of 100+ a couple of times a week.  In TblLeadSource, the 100+ codes are
Like "RP*".  Each stands for a sales rep.  Iemaileach sales rep a report on
their submissions using a macro that outputs each report to Outlook.  The
need to enter eachemailaddress is not a bad problem.  I am more worried
about having to add a new query, report, and macro step every time we adda
new sales rep.  We are using Windows XP and Office 2007.  I write only very
limited code.
Click to expand...

Hi LeVio,

The 100 queries sounds very painful. There's definitely a much better
way to save you time to run and support this need.

Our Total Access Emailer program is designed for this situation. If
you have a report that can be filtered for each recipient, Total
Access Emailer can email each person their own subset of the data or
report.

For instance, if your table contains each sales rep's email address,
name, and code to filter the report, you can easily create an email
blast that uses that table (or a query off that table) and send a
separate email to each person with their name in the subject or
message, and filter the report to the sales rep's specific data.
Depending on what you choose, the data can be embedded in the message
or attached as a separate file. In Access 2007, it can attach the
Access report as a PDF file.

Total Access Emailer runs interactively as an Access add-in. It also
has a programmatic interface to let you launch it in VBA.

You can download a 30 day demo/trial version. For more info vsit
http://www.fmsinc.com/MicrosoftAccess/Email.asp

Good luck!

Luke Chung
FMS, Inc.
http://www.fmsinc.com

P.S. Visit our Micosoft Access Help Center for more resources:
http://www.fmsinc.com/MicrosoftAccess/help.html
 
FMS Development Team - Are you saying that the capability does not exist
within Access 2007 itself?
 
LeVlo said:
I want to be able to send many reports with separate data to separate email
addresses. Currently I have written the 100+ queries and created the 100+
reports, each with the parameter that I think I should somehow be able to
change "dynamically?" Can I create one report on one query that will take
each set of results and send it to each of the 100+ recipients? I add to the
list of 100+ a couple of times a week. In TblLeadSource, the 100+ codes are
Like "RP*". Each stands for a sales rep. I email each sales rep a report on
their submissions using a macro that outputs each report to Outlook. The
need to enter each email address is not a bad problem. I am more worried
about having to add a new query, report, and macro step every time we add a
new sales rep. We are using Windows XP and Office 2007. I write only very
limited code.
Click to expand...

100 queries? Yikes. However the below solution will require some
knowledge of VBA and will be hard to get running if you haven't much
experience in VBA.

Create a query based on the Sales Rep so it has the 100 or so records
in it. Then you need to loop through that query for each Sales Rep.
For sample recordset logic see
http://www.granite.ab.ca/access/email/recordsetloop.htm

Then you need some means within that loop of selecting only that sales
rep on the report. For a page on how to print a report for a single
record and how to generate reports to attach to emails see the
Emailing reports as attachments from Microsoft Access page at
http://www.granite.ab.ca/access/email/reportsasattachments.htm

Now you need a method of creating the report. I would suggest PDF and
using Lebans code. 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

Finally you need to send the reports as emails. Microsoft Access
Email FAQ http://www.granite.ab.ca/access/email.htm

By now you may decide that the FMS solution Luke mentions may be a lot
less work than the above.

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/
 
FMS Development Team - Are you saying that the capability does not exist
within Access 2007 itself?














- Show quoted text -
Click to expand...

One can do all sorts of things within Access. Unfortunately, it takes
a lot of expertise and effort to create and test a solid solution.

From the reception we've received from the Access community, our Total
Access Emailer program offers tremendous value at a fraction of the
cost it would take to write yourself.

There are definitely some features of Total Access Emailer that would
be difficult to create in Access VBA. For instance, it supports the
ability to use an HTML file as the body of an email including the
ability to embed graphics directly in the email (or you can use
references to the graphics on a public web site). This lets you use
tools like FrontPage to format your messages very nicely, then use
Total Access Emailer to customize portions of the message with data
from your data source.

We also use SMTP to bypass the limitations of Outlook/MAPI so you can
send multiple emails without violating security settings.

Try the demo (http://www.fmsinc.com/Products/Emailer/conftrial.asp)
and you'll see how easily it works. With Total Access Emailer you can
focus on the creative/custom part of what you need to deliver while
leveraging the power of Access tables, queries, and reports for
emails.

I think you'll be quite pleased with it. Had we had a similar tool to
solve our email needs, we wouldn't have bothered to create Total
Access Emailer.

Luke Chung
FMS, Inc.
http://www.fmsinc.com

P.S. Visit our Micosoft Access Help Center for more resources:
http://www.fmsinc.com/MicrosoftAccess/help.html
 
Thanks for the response. Do you have a suggestion for learning some basic
VBA; a book, a class, a training?
 
LeVlo said:
Thanks for the response. Do you have a suggestion for learning some basic
VBA; a book, a class, a training?
Click to expand...

Try the Access <insert your version> Program for Dummies book. I have
the A97 version and it looks fairly decent to me.

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