At present my job (in Pensions) partly consists of me creating Retirement Packs and posting them out for Members
The creation of Retirement Packs can take about 5 / 10-mins to produce (manually entering the Members Personal Details and Retirement Details in the relevant fields throughout the template Document). At the moment its not taking too much time as my Department is new (consists of just myself), yet is part of a pilot project which when it properly goes live will seriously increase not just my workload but will see me employing additional hands to help with the new workload.
Basically at present I have to manually copy and paste the data from the Database that we use on to the Microsoft Word template before sending.
Here's the help (advise) I need... I've found a way (a very quick way) to export all the Database data into a single Microsoft Excel Spreadsheet (which remains on a hidden worksheet). Then on the live sheet I've managed to create something (using VLOOKUPS) where I can enter an individual Reference Number and pull that Member's information without having to hunt for it OR having to manually copy and paste.
What I want to do next is to be able to click a button in the Spreadsheet which automatically loads the relevant Microsoft Word Template and automatically runs a mail merge which pulls the selected Member Data into the relevant fields (without having to do those steps manually).
Can this be done (I'm guessing a lot of VBA will be required)... Or is there another similar approach that can be taken that I've not thought of?
0
Comments
Can't think of any other way to do that to be honest.
I would use a Macro but I don't really remember how to set one up, I would have thought a mail merge would be interlinked with your database.
If it's not, then you might need someone to do some development work for you, which would be a whole different kettle of fish
To be honest though, all that's needed is to make sure your source is right on the Word doc. Not sure a macro will save much time.
But at the moment the risk that the manual input creates is huge, other checks are thankfully in place to ensure this doesnt happen.
When I worked in Contract Production whilst in the Recruitment industry though there was a programme like what I want to create (its where I've got my idea from)... Hell even if I get it to work I swear I'm going to struggle to get sign off past the type of people who wont be keen on the idea because they didnt think it up themselves!!
Alternatively go with Dave Mehmets tried and trusted formula, or get yourself a nice little diary.
As previously said by others you'll need a little programming knowledge for this but VBA is one of the easier ones to get in to.
If you don't already have them then you'll need to set up your word document as templates with merge field references. Google mail merge with your version of Word for a how to guide if you're not sure. This sound simple but there are techniques to avoid layout corrupting from say different length of addresses.
Make sure your Excel data extract contains headings and given them meaningful names...it makes life much easier and the mail merge template easier to create.
You can then start on the VBA code to open the ms word application and create a new doc based on the template.
Once you have it up and running make sure you run it alongside your manual process or double check your created letters as much as possible. As much praise you'll get for saving time, you'll get in double the amount of shit if it goes wrong. I love automation but I see too many people just assuming it works and not checking the outcome.
Couple of other thing....add as many comments to the code as you need to explain the program. Plus run the tool on someone else's PC - a developers favourite excuse is "well it worked on my PC".
Good luck
Based on you having a MS Access DB containing all the data you don't even need to manually export anything
1. Link the data from your MS Access database into a blank MS Excel spreadsheet using Data > From Other Sources. Once linked you can right click and 'refresh' every time your database updates without you having to do anything. In fact you can even set this to refresh every time you open the spreadsheet so the data is always upto date when you open it. This will mean your data will always be in MS Excel but will also mean you don't need to copy and paste from Access and also won't need to use the vlookups, roughly how many records are you managing?
2. Assuming you have a record in each row on your spreadsheet i'm pretty sure you can then set a mail merge up using the following process https://support.office.com/en-gb/article/Mail-merge-using-an-Excel-spreadsheet-858c7d7f-5cc0-4ba1-9a7b-0a948fa3d7d3
3. Record step 2 using a macro and then create a button on your toolbar which then runs this macro anytime.