We need to automate the following reporting function. Currently, we receive 5 different emails (with subject lines we establish) each containing an MS Word attachment. These five attachments (each has a randomly-generated file name) are opened and their contents are copied to a consolidated report file, also in Word. We then save the report.
We need to automate the process of opening the attachments and copying over the information.
We've attached one of the reports sent via email as well as one of the consolidated reports for review.
It would probably be best to set up a wizard that would open up our master template and ask which consolidated report we wanted to generate...the selection would look up the emails whose subjects correspond with that report (the subjects are numbered "XXX 1", "XXX 2" where XXX is the report name), pull the attachments out in order and copy the data into our master template. It would also populate the header with the correct report name.
From there we could review and save it manually, which is fine because we tend to write notes in certain sections.
Lastly, we need to be able to add a new report to this wizard as the need arises, either through an editor (with supporting documentation) or another wizard...and we need to be able to write narrative in the Word document, or copy in Excel files manually as needed (so the template can't be locked).