Complex Excel-Word mail merge solution needed

[damianjmcgrath]

Hello,

I have been tasked with a job at work to look into possible ways of speeding up one of our processes.

At the moment, we have to write letters to customers reminding them to complete certain sections of a form that they always seem to forget to complete, and make sure we chase for these sections every 10 days or so.

There is a spreadsheet set up which logs all these cases, logs what section each customer is missing, what day the next chasing letter is due and which member of our team has been allocated to write out.

Each member of our team goes into this spreadsheet daily, sees what letters are due that day, sees if any of them have been allocated to them, and then goes off and writes the letters.

As there may be 10-20 letters due each day, and writing a chasing letter by scratch takes 5 minutes or so, that's potentially 1 hour and a half or so wasted each day.

The spreadsheet looks roughly like:

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 104px"><COL style="WIDTH: 49px"><COL style="WIDTH: 65px"><COL style="WIDTH: 101px"><COL style="WIDTH: 101px"><COL style="WIDTH: 101px"><COL style="WIDTH: 101px"><COL style="WIDTH: 65px"><COL style="WIDTH: 136px"><COL style="WIDTH: 136px"><COL style="WIDTH: 106px"><COL style="WIDTH: 148px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: left">Title</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: left">Initials</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: left">Surname</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: left">Address Line 1</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: left">Address Line 2</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: left">Address Line 3</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: left">Address Line 4</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: left">Postcode</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: left">Reference</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: left">Section Chasing For</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: left">Team Member</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: left">Date Next Chaser Due</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: left">Mr</TD><TD style="TEXT-ALIGN: left">D J</TD><TD style="TEXT-ALIGN: left">McGrath</TD><TD style="TEXT-ALIGN: left">1 The Street</TD><TD style="TEXT-ALIGN: left">2 Village</TD><TD style="TEXT-ALIGN: left">3 City</TD><TD> </TD><TD style="TEXT-ALIGN: left">BA1 2AA</TD><TD style="TEXT-ALIGN: left">G1900/111</TD><TD style="TEXT-ALIGN: left">Section 1</TD><TD style="TEXT-ALIGN: left">Ronald Jenkins</TD><TD style="TEXT-ALIGN: left">01/03/2009</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: left">Miss</TD><TD style="TEXT-ALIGN: left">M L</TD><TD style="TEXT-ALIGN: left">Taylor</TD><TD style="TEXT-ALIGN: left">2 The Street</TD><TD style="TEXT-ALIGN: left">3 Village</TD><TD style="TEXT-ALIGN: left">5 City</TD><TD> </TD><TD style="TEXT-ALIGN: left">BA2 311</TD><TD style="TEXT-ALIGN: left">G1214/1121</TD><TD style="TEXT-ALIGN: left">Section 4</TD><TD style="TEXT-ALIGN: left">Cat Jones</TD><TD style="TEXT-ALIGN: left">03/03/2009</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: left">Ms</TD><TD style="TEXT-ALIGN: left">A J </TD><TD style="TEXT-ALIGN: left">McGrath</TD><TD style="TEXT-ALIGN: left">4 The Street</TD><TD style="TEXT-ALIGN: left">5 Village</TD><TD style="TEXT-ALIGN: left">9 City</TD><TD> </TD><TD style="TEXT-ALIGN: left">B3 211</TD><TD style="TEXT-ALIGN: left">6754611231</TD><TD style="TEXT-ALIGN: left">Section 2</TD><TD style="TEXT-ALIGN: left">Dave Smith</TD><TD style="TEXT-ALIGN: left">05/09/2009</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4



I think a way to speed this up would be to use some sort of Mail Merge system.

Ideally, there would be a command button on the Excel spreadsheet or some VBA code that can be run from the Macro menu.


This code should run through the range of members, and do the following:

• See which ones are due today (where the Date Next Chaser Due = Today's date).
• For those cases only, open the relevant Word template (for example, there will be a Section 1 template, Section 2 template, etc), and populate it with the relevant details for each member.
• Print.

For, in pseudo-code, something like:

For each row in Range A2 to L4
      If L(row number) = Todays Date AND If J(row number) = Section 1 Then
            Open Section 1 Word Template
            Populate Word Template with members details so if there are 4 
            members that match this criteria, then the Word document will
            have 4 pages
            Print Word Document
            Close Word Document
and so forth checking for Section 2, 3, 4, etc.

I suspect that using something like the code above, it would be best to open one template, populate it, print it and then close it before opening another template.


I know this is a massive thing I'm trying to, so if anyone has any other comments, suggestions, hints or advice, I'd be very appreciative of it.


Thanks very much!

 

[damianjmcgrath]

I know this is a complicated problem, but hopefully someone will have some advice to give me.


Thanks!

 

[John_w]

You don't really need to have Excel populate mail merge fields in a Word document to achieve your requirements. Instead, you could use a normal Word mail merge main document with a Microsoft/MS Query which selects records from your Excel workbook based on the 'Section Chasing For' column and 'Date Next Chaser Due' column. You need 4 queries and 4 main documents - one for each section.

Manually opening a main document set up in this way would run the mail merge and create a new document which can be printed out. Of course, the new document would be empty if there are no records with 'Date Next Chaser Due' = Today's date. This is where you can use VBA within Excel to open and print a main document only if 'Date Next Chaser Due' = Today's date so that you don't print an empty document.

I hope the above makes sense.

The next steps are:

1. Create Microsoft/MS Query for Section 1. In Excel 2003, a query is created by clicking Data - Import External Data - New Database Query. The query reads records from your Excel data workbook with the WHERE clause: WHERE (`Sheet1$`.Section='Section 1') AND (`Sheet1$`.`Date Due`=DATE()).
2. Create mail merge main Word document for Section 1. For the data source, use the query created in Step 1. Put required mail merge fields in the document. Save and close document.
3. Test mail merge by opening main document.
4. Repeat above steps for Sections 2-4.

Once the manual mail merge is working use a bit of VBA Excel code to automate it.