Working with Microsoft Word mail merge templates in Sage CRM

The Sage CRM Blog

Articles of general interest about Sage CRM, the community, add-ons and general news!

Working with Microsoft Word mail merge templates in Sage CRM

  • Comments 8
  • Likes

Background

We are mindful that some of our customers are working with their own pre-existing templates in Microsoft Word, and do not want to remove the ability to perform mail merges with these templates.

However, while it is possible to perform merges with pre-existing Word templates, it is not possible to edit them via the Sage CRM rich text editor interface.

The objective of this document is to highlight the benefits of the Sage CRM mail merge feature and to offer some potential solutions to the issues described. 

Mail Merge in Sage CRM

Sage CRM's Mail Merge feature is designed to allow all users to efficiently create merged documents from a number of Internet Browsers, without the need for a browser specific plug-in, and with data from any entity within CRM, including custom entities.

All of these key objectives were achieved.

  • In Sage CRM mail merge is a server-side process, which means you no longer need to install an ActiveX plug-in to perform mail merges.
  • The mail merge process is cross-browser compatible, which means you can now perform mail merges when working with Sage CRM in Mozilla Firefox, Google Chrome, or Apple Safari.
  • In a significant development, it is now possible to perform mail merges from newly created custom entities. Previously it was possible only to merge to the predefined set of core entities, which was restrictive and hampered some customer's business processes. The only simple prerequisite is that the newly created entity must have the Has Library and Has Communications check boxes selected.
  • Many of the issues that were returned to us concerned the mail merge feature's reliance on Microsoft Word. End users simply found Word's native mail merge feature too complex and not intuitive enough to set up templates. To get around this, we did a lot of work around making the Sage CRM merge fields available to all users in Sage CRM's rich text editor. All users can now create new templates without the requirement to have MS Word installed
  • In addition to this, a great deal of effort went into making nested items available from the context of Quote, Order and Opportunity entities. This means that users can now quickly and easily create templates that include line items, without having to have expert knowledge of Microsoft Word or the Sage CRM merge field infrastructure for child items!
  • As stated earlier, it is still possible to use existing Microsoft Word templates to perform merges but due to the complexity of converting Word documents to HTML the ability to edit these templates "on the fly" was removed.

 

Converting Microsoft Word Templates to HTML

Users can convert their pre-existing templates to HTML, and then re-upload them to the Sage CRM server. Although this introduces some additional overhead, frontloading this effort will result in users being able to enjoy the many benefits of working with the new Sage CRM mail merge feature with their pre-existing Word templates converted to HTML.

Note: When working with the Google Chrome or Mozilla Firefox browsers, it is possible to upload multiple documents/images at the same time.

To convert Word documents to HTML:

1. Open the Word template you want to convert.

2. Select File | Save As and select Web Page (*.htm, *.html) from the Save As Type drop-down menu.

Save As Web Page

3. Navigate to the location in Windows Explorer where you saved the HTML version of the template, right-click it and open it with a text editor program - something as simple as Notepad is fine.

 Open with Notepad

4. In the text editor, replace the merge field tags (« and ») with « and » respectively.

Replace with «

Replace with »

5. In Sage CRM, upload any images that are in the Word template to the Sage CRM server.

To do this:

i. Navigate to the entity from which you want to carry out the merge and select New | Mail Merge.

ii. On the Select Or Create a New Template page, click the Add Local Template / Image button.

iii. Click the Browse button, and select the image file(s) you want to upload.

iv. Click Save. The image file you uploaded is now available for you to reference when you have uploaded your converted HTML template.

6. Now you can follow the same process to upload your converted HTML template.

i. On the Select Or Create a New Template page, click the Add Local Template / Image button.

ii. Click the Browse button, and select the template file(s) you want to upload.

iii. Click Save. The template file you uploaded is now available for you to reference when you have uploaded your converted HTML template.

You can now perform a mail merge using the uploaded HTML template.

To do this:

1. Click on the hyperlink of the newly converted template. The template will open in Sage CRM's rich text editor.

2. Add the image reference to the template where you would like the image to display. For example, if you want the logo to display on the top left of the merged document enter <img src="panoplylogo.png"> in the top left of the Sage CRM rich text editor.

Converted template in Sage CRM

You can now edit the template "on the fly" and perform your mail merge as before.

Comments
  • This is very useful. however, I found that tables dont convert too well. While we can continue to use Word templates, it looks like the quotes templates with a nested table with a list of items only works in the HTML template. Is this correct? I have Word quotes templates from prior versions that have been working very well. Do I have to convert them to HTML?

  • Hi Nathalie - If you have nested items in your quote templates, you may need to tweak them a little to make them work in SP2. This is because the old Microsoft Word quote templates were hard coded and dependent on the plug-in.

    To display nested items in a quote (or order) template in SP2, a specific structure is required - If you want to include a table with nested (child) item merge fields, you need to insert start and end tags for both the main table (Quotes) and the child table (Quote Items). You must also include a TableStart and TableEnd tag in the same template section, table row or table cell. For example, if you want a row of quote item data to display, the first column in that row must include a TableStart:QuoteItems tag, and the final column must include a TableEnd:QuoteItems tag.

    A basic example would be something like:

    «TableStart:Quotes»

    «quot_createddate»

    Reference: «quot_reference»

    To:

    «pers_firstname»«pers_lastname»

    «comp_name»

    «addr_address1»

    «addr_address2»

    «addr_address3»

    Items Quoted                          Item Name                  Price                                                                                  Quantity                 Sub Total

    «TableStart:QuoteItems» «prod_description» «quit_quotedprice_CID»«quit_quotedprice» «quit_quantity» «quit_quotedpricetotal_CID»«quit_quotedpricetotal» «TableEnd:QuoteItems»

    Total Cost: «quot_grossamt_CID»«quot_grossamt»

    «TableEnd:Quotes»

    For more information on creating new templates in Microsoft Word and creating new quote or order templates, please refer to

    community.sagecrm.com/.../DT_CreateNewWordTemplate.htm

    and

    community.sagecrm.com/.../DT_CreateQuoteOrder.htm

    I hope this helps,

    Philip

  • Hi Philip, Would you by any chance have a template of a Word document with nested tables for a quote updated for version 7.1SP2? When I try and add the table tags to Word, it doesnt work. Thanks

  • I have actually found a sample Word quote in the default library folder that the service pack populates, not where my usual templates are. So I will try and work out why my version doesn't work. Thanks for your help.

  • I have just started to convert my client's Word Merge documents and thought I'd start with a new template to make sure everything is working.  I cannot get the Merge document to open from the communication or document tab.  It also looks like the document is being filed in the user library as opposed to the company library where you would expect to find it.  Are there more specific instructions for setting this up to work?

  • Hi Nathalie - sorry I haven't replied sooner, but I have only returned to the office today. It sounds like you have managed to sort your issue yourself, but please let me know if ou have any further questions.

    Thanks

    Philip

  • Hi Laura - sorry I haven't replied sooner, but I have only returned to the office today after a holiday.

    I have just performed a merge to double check everything was working OK.

    Have you made sure when performing your merge that the Create Communication And Save Merged Document check box is selected?

    When you have selected that check box and clicked Continue, you can enter details on the Save Merged Document as a Communication page and click Save to make sure your merged document is saved as a communication.

    Then to vew the merged document from the Communications tab, select the All option in the Status filter to see your communication, then click on the paperclip icon on the farthest left column.

    To vew the merged document from the documents tab, click the paperclip icon on the farthest left column.

    Thanks for pointing out the issue with the document being filed in the user library as opposed to the company library. This is an issue we have resolved and the fix will be included the next 7.1 patch in a couple of weeks' time.

    Hope that helps,

    Thanks

    Philip

  • Hi Philip,

     any chance you could look at this

    community.sagecrm.com/.../9873.aspx

    issue with mail merge?

    Basically create a custom field on opportunity, add it to vmailmergeopportunity, and try use that custom field in a mail merge. not working n 2 installs of crm72 for us.

    Regards

    CRM Together