Do I Really Need to Use 54 Macros to Sent Separate report PDFs ?

[ArtW]

HI, am pretty new at this and don't have any VBA training but I am develoing a 2007 MS Access database for folks at work and could use a sanity check on what I am doing.

The dB will produce many reports based on different division and branch workload. At present to send a report as a PDF file I create a macro to Send Object from MS Access ribbon. I choose Report, select report name, choose PDF format and add description for the subject line of the mail window. The macro works fine and produces the PDF file of each record as I like. In doing so I'm up to over 50 macros that send out reports as PDF files.

Question: I assume their is a much easier way. What am I doing wrong ?

Thank you for your response ! Art

 

[pbaldy]

Likely not (in answer to the thread title). Are the 54 macros for 54 different reports, or different data selections for a single report? For starters, I'd use VBA code rather than macros, as it's more flexible. For the first you can use a combo box or something for the user to select a report, and then use that combo for the report argument of SendObject. For the second, base the report on a parameter query or use this technique:

http://www.granite.ab.ca/access/email/reporttomultiplerecipients.htm

 

[ArtW]

Thanks I have one report format and produces the same report which is sorted by different offices for their specific info. Don't know much about VBA. The 2007 MACROs work but it is time consuming to do a macro for each individual report. Thanks again ! Art