none
Word 2007 VBA Mail Merge - Multiple Templates RRS feed

  • Question

  • We have a Word Wizard written in Word Basic 2.0 (I think) that will look at the first column in the data source and then select the corresponding template to merge the data.  Each record is a separate merge that is sent to the printer.  (If I have 10 records then it will produce a merge and print for each record rather than one large merge with 10 records.)  I have been trying to update this program but I cannot.  I will run under Word 2007, but it is slow and not really editable.  I am not against using Access to accomplish this, but I am not that familiar with Access (but willing to try).  Anyone have any ideas or can anyone direct me to creating a mail merge that will use multiple templates?  There is only one data source (tab delimilited text file) with about 70 different templates.  I hope this makes sense.  Thanks.
    Tuesday, February 1, 2011 5:19 PM

Answers

  • 1. Does the Wizard definitely get each template to perform a regular Word mailmerge (with 1 record per merge)? If it actually does its own replacement of placeholders by data, that would need a different approach.

    2. Does each template use the tab delimited text file as its data source (just setting the start and end records to be the same, perhaps), or does the Wizard create a new one-record data file (perhaps with a header) for each merge? If it is the latter, can the records in the file have varying numbers of fields depending on the template?

    3. Do the templates currently expect a separate header file?

    (Not critical info: 4. Does the first field in the data source contain the name of the template, or just a code (perhaps a number) that is used to determine the name of the template?)

    FWIW I would probably aim for a solution where a VBA macro processed the data source and created new ones for each Template (there are various ways you could do that), but that might mean changes to all the templates (to look in a different place for the data source than they do now). If you need to avoid changing the templates you need to say.


    Peter Jamieson
    • Edited by Peter Jamieson Tuesday, February 1, 2011 6:23 PM 5->4
    • Marked as answer by Bruce Song Wednesday, February 9, 2011 9:40 AM
    Tuesday, February 1, 2011 6:23 PM

All replies

  • 1. Does the Wizard definitely get each template to perform a regular Word mailmerge (with 1 record per merge)? If it actually does its own replacement of placeholders by data, that would need a different approach.

    2. Does each template use the tab delimited text file as its data source (just setting the start and end records to be the same, perhaps), or does the Wizard create a new one-record data file (perhaps with a header) for each merge? If it is the latter, can the records in the file have varying numbers of fields depending on the template?

    3. Do the templates currently expect a separate header file?

    (Not critical info: 4. Does the first field in the data source contain the name of the template, or just a code (perhaps a number) that is used to determine the name of the template?)

    FWIW I would probably aim for a solution where a VBA macro processed the data source and created new ones for each Template (there are various ways you could do that), but that might mean changes to all the templates (to look in a different place for the data source than they do now). If you need to avoid changing the templates you need to say.


    Peter Jamieson
    • Edited by Peter Jamieson Tuesday, February 1, 2011 6:23 PM 5->4
    • Marked as answer by Bruce Song Wednesday, February 9, 2011 9:40 AM
    Tuesday, February 1, 2011 6:23 PM
  • I apologize, I did not see that anyone had responded to this question.  I have somewhat figured out how this works.  The data file has a header then several rows of information (one line per...) The first column (three characters) is the DocCode (heading).  The macro then creates a temp data file using the original header and row 1 to EOF.  It pulls the template based on the DocCode and then performs a Mail Merge using that template and the temp data file.  This new merged document is sent to the printer and the macro loops to the second row of the original data file, process repeats through the end of the file.  Once I understood how it worked, I was able to change it do a degree.  It does run much smoother now.  At this point I need to figure out how to manipulate the printer to use different card stock when processing a row that has a DocCode of '00'.  Regarding your option for solution, I am interested in researching various ways to do the originally requested task.  Can you steer me in the right direction for either of these things?  Thank you.
    Tuesday, March 29, 2011 3:27 PM
  • > At this point I need to figure out how to manipulate the printer to use different card stock when processing a row that has a DocCode of '00'.

    If the card stock is associated with a different paper source (e.g. tray) from the other paper types you are using, and you only ever use one printer, then I would guess that you ought to be able to set up the template to use that particular tray, save the template, and that /may/ be enough. If that doesn't work, the most you should have to do is programmatically set the paper source after loading the template. e.g. use ActiveDocument.PageSetup.FirstPageTray

    However, you have to know the appropriate printer tray values for the tray you want to use on the printer you want to use. There are some generic "wdPaperTray" values, but you'll probably need to use trial and error on different printers to discover what they mean, and you will probably be able to use values outside the enumeration.

    > Regarding your option for solution, I am interested in researching various ways to do the originally requested task

    There are many possibilities depending on the resources available (user/development/maintenance), who needs to own each task (e.g. are layouts defined by programmers, from time to time, or by users, every day?), and what the technological options are (e.g. in the Microsoft world, Word is good at layout, but perhaps SQL Server Reporting Services is a better framework if there are "reports" to be defined by programmer types and distributed to various users).

    As far as Word is concerned, Word MailMerge has many limitations, but a critical one in this case is that the process assumes that you take a single mail merge main document and a single data source, then merge it to a single destination (e.g. a particular printer). Further, the data source is expected to have the same number of columns of data in each row.  Although there is a way to get MailMerge to produce completely different letters for each record in a data source (using INCLUDETEXT fields), it's not something I would recommend unless there is no other way. In other words, the process itself is quite inflexible. AFAICS the process you already have is intended to overcome those limitations - and in fact it sounds as if it does it in a similar way to the one that I was suggesting - but still uses Word Mail Merge to produce each output. That certainly has its advantages because it means that end users can construct a layout using standard Word MailMerge features. If you try to "roll your own" Mail Merge, you immediately have to consider stepping outside standard Word features and either ensure that whoever maintains the system as a whole knows how to create a layout that can be merged, or provide custom UI that lets users do it /and/ ensure that they understand how to do that.

    However, if you do not want to use MailMerge, in Word 2007/2010 (but not Mac Word) you can consider using plain text Content Controls instead of MERGEFIELD fields to construct your layout, and you can either populate those fields using VBA and the object model, or by associating the controls with Custom XML Parts. if you have to work with Word 2003 or earlier, or Mac Word, then you can consider using Word DOCVARIABLE fields to insert data. Every approach has its gotchas.



    Peter Jamieson
    Tuesday, March 29, 2011 4:46 PM
  • We have a Word Wizard written in Word Basic 2.0 (I think) that will look at the first column in the data source and then select the corresponding template to merge the data.  Each record is a separate merge that is sent to the printer.  (If I have 10 records then it will produce a merge and print for each record rather than one large merge with 10 records.)  I have been trying to update this program but I cannot.  I will run under Word 2007, but it is slow and not really editable.  I am not against using Access to accomplish this, but I am not that familiar with Access (but willing to try).  Anyone have any ideas or can anyone direct me to creating a mail merge that will use multiple templates?  There is only one data source (tab delimilited text file) with about 70 different templates.  I hope this makes sense.  Thanks.

    Hi Jason!

    Can you share your solution to the problem descrobed above?

    I am facing a similar problem.

     

    Thanks.

    Wednesday, April 27, 2011 11:58 AM
  • This is not something that you could do with mail merge.  It could be done however by having { DOCVARIABLE varname } fields in each of the templates and then use code to open and iterate through the data determining the template to be used for each record, creating a new document based on that template and setting the values of variables in that document to the values from the fields in the current record, then updating the fields in the document to display those value, then save and close the document before moving to the next record and repeating the process.


    Hope this helps.

    Doug Robbins - Word MVP,
    dkr[atsymbol]mvps[dot]org
    Posted via the Community Bridge

    "nitson" wrote in message news:82022f7e-1f36-4eab-954b-cdb3a51da975@communitybridge.codeplex.com...

    We have a Word Wizard written in Word Basic 2.0 (I think) that will look at the first column in the data source and then select the corresponding template to merge the data.  Each record is a separate merge that is sent to the printer.  (If I have 10 records then it will produce a merge and print for each record rather than one large merge with 10 records.)  I have been trying to update this program but I cannot.  I will run under Word 2007, but it is slow and not really editable.  I am not against using Access to accomplish this, but I am not that familiar with Access (but willing to try).  Anyone have any ideas or can anyone direct me to creating a mail merge that will use multiple templates?  There is only one data source (tab delimilited text file) with about 70 different templates.  I hope this makes sense.  Thanks.

    Hi Jason!

    Can you share your solution to the problem descrobed above?

    I am facing a similar problem.



    Thanks.


    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    Wednesday, April 27, 2011 7:58 PM
  • Hi Jason, Do you mind to share the code for this solution? I'm facing the same problem and I'm fairly new to VBA, specially the part "The macro then creates a temp data file". In my case, I have an Excel file where the first column is the name of the "DocCode" and the rest of the columns represent the values of the fields in the template.

    Thanks,

    Luis


    Luis

    Tuesday, May 15, 2012 7:58 PM
  • Luis,

    I am sorry but I do not get on here very often.  Do you still need help with this? I can post the code if this board allows it.

    Jason

    Thursday, August 2, 2012 3:27 PM