email multiple reports from one report

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.

"Russell" wrote:

> 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.
>
>
> "LeVlo" <LeVlo DeleteThis @discussions.microsoft.com> wrote in message
> news:39A2CFD3-5425-48E5-B593-1E70686339DC@microsoft.com...
> >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.
>
>
>

On Aug 22, 5:26 pm, LeVlo <Le....RemoveThis@discussions.microsoft.com> wrote:
> 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 add a
> new sales rep.  We are using Windows XP and Office 2007.  I write only very
> limited code.

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?

"FMS Development Team" wrote:

> On Aug 22, 5:26 pm, LeVlo <Le....RemoveThis@discussions.microsoft.com> wrote:
> > 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 add a
> > new sales rep. We are using Windows XP and Office 2007. I write only very
> > limited code.
>
> 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
>

LeVlo <LeVlo.TakeThisOut@discussions.microsoft.com> wrote:

>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.

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/

On Sep 16, 10:44 am, LeVlo <Le....TakeThisOut@discussions.microsoft.com> wrote:
> FMS Development Team - Are you saying that the capability does not exist
> within Access 2007 itself?
>
>
>
> "FMS Development Team" wrote:
> > On Aug 22, 5:26 pm, LeVlo <Le....TakeThisOut@discussions.microsoft.com> wrote:
> > > 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 add a
> > > new sales rep.  We are using Windows XP and Office 2007.  I write only very
> > > limited code.
>
> > 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- Hide quoted text -
>
> - Show quoted text -

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?

"Tony Toews [MVP]" wrote:

> LeVlo <LeVlo.TakeThisOut@discussions.microsoft.com> wrote:
>
> >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.
>
> 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/
>