I need to email 131 reports from Access to 131 different project managers. However, the reports will need to be updated by the recipients. Ideally I would have liked to produce the report in Word so they could update the relevant sections but the report is based on at least 3 fairly complex queries. I can export a report with subreports from Access to Word but lose all the formatting this way. I have noticed Steve Arbaugh's suggestion to a similar question to use PDF files but these can't be updated by recipients without Acrobat Writer... any ideas?
I've been tearing my hair out trying to think of different ways to do this, it's driving me mad!!!
Here's a suggestion - format the reports for Excel then email them using Excel. Then, instructions can be sent as to how to record changes, so that when they return the email spreadsheet, you can easily pick up the changes via automation. Automation with Excel is not very difficult, and there should be plenty of resources both online and in the various access books to help with this.
Could you set up a document in Word that pulls information from your Access query via a Mail Merge? You could then set up Fill-In fields and Text Form Fields that your managers could adjust as neccessary?
Originally posted by saross
Tried that too! Two of the queries will pull up several records for each project and so these can't be merged into one query...
Ok, done it!! You can use Visual Basic within Word to write macros that open and query databases in Access for you using SQL statements. By placing bookmarks on the word document you can format the outputted data.