Preparing a Mail Merge document

Owned by Jodie Miners
Last updated: Mar 08, 2016

I am not sure there is a bigger time-sink in Salesforce than preparing Mail Merge documents. (Nope, not even Email Templates - with Mail Merge you have the extra hassle of dealing with the documents). 

I'm just concentrating on Word documents here but you can do really cool things with Excel, Powerpoint or even Google Docs.  

Steps to create a mail merge document:

  • Create the shell of your document, have an idea where you want the fields to go. Types of fields you may have in your Word document.
    • Fields directly from the record in Salesforce
    • Fields from related records in Salesforce
    • Formulas on the record in Salesforce
    • Image fields from documents uploaded to Salesforce
    • Paragraphs of text that are conditional in Word depending on a field in Salesforce
    • More advanced word fields
  • Create any formula fields on the main Object in Salesforce
    • This is a balancing act - do you want the logic in the Word document or in Salesforce. I think have all the logic in Salesforce and the end result in Word. 
      • Eg you need a clause to appear in Word if the Type is A, B or C. 
        • Create a formula field in Salesforce named TypeClause
          • I ALWAYS add a note in the Description field to note that this field exists only for the Merge Documents - eg CONGA or MERGE
          • Type = Checkbox
          • Formula OR(ISPICKVAL(Type,"A"),ISPICKVAL(Type,"B"),ISPICKVAL(Type,"C))
        • Add a Word Field to your document (ctrl +F9)
          • IF <<TypeClause>>="True" "The contents of your para" "" (ensure you enter TypeClause correctly depending on which app you are using. 
  • If using Conga, create a Report Type to include all the related fields that you want to use. Custom Report Types are way more useful than creating formula fields for every related field you want to include on your merge document.
    • Or use SOQL Queries - they are more robust, less prone to people modifying them or deleting them, and you need Queries if you are working with Partner or Customer communities documents. 
  • If using Drawloop, set up the relationships in the Loop setup screen for all the related Objects.
  • Create documents records for your images, and then add the image URLs to fields on the record. 
    • Eg you want a conditional logo to appear on the document based on what product it is. 
      • In the product object, add a new field for image file. 
      • Add the externally valid URL to the image file field. 
      • Add that image field to your Report Type (Conga). 
      • Add the image field into your word document (it's different syntax for Conga or Drawloop). 
      • Ensure that the image size is the exact size to fit within the margins of your word document. 
  • Add all the correct Merge fields into your document
    • I like colouring all the fields until the document is complete (yep, it is blurred out). 

  • If using Drawloop, upload your template, if using Conga, you can test before you upload your template. 
  • If using Conga, create your button. 
  • If using Drawloop create your Loop setup and your button. 
  • Set up the record in Salesforce to have all the test data in it
  • Test Test and Test again. 
  • Test with different Salesforce records 
  • Rinse and repeat! 

Merge Field Tricks

A snippet of the above document. 

Note Show Field Codes and Show Formatting Marks are turned on - DO NOT TRY TO WORK WITH MAIL MERGE DOCUMENTS WITHOUT THESE SETTINGS TURNED ON!

Note the extensive use of Word IF Fields. 

Basically I set up a checkbox field or formula in my object to determine if a section of text should display on the document. 

I use Word Fields to say should this text be shown. Then other field within the merge field that will be displayed. 

Note that the Word Merge Fields are joined together with no break, and the line breaks are within the merge field. This ensures no gaps in your content document when it displays. It is easier to deal with each field on it's own line first, then smoosh all the fields together. 

Modifying the Mail Merge document

You are testing, and you found that you just need one conditional merge field to create an extra paragraph of text in the Word document. This is where the time sink starts. 

Conga

  • Navigate to the object that you need to create the formula field on.
  • Create the formula field. 
    • Make the field visible, 
    • Ensure the field is showing the right value for the right conditions. 
  • Go to the main record that you are testing.
  • Click the Conga button to get to the template builder. 
    • Oops, you forgot. It needs to be on the Report Type because it's in a related object from your main object. 
    • Note this part may be replaced with modifying the SOQL query, but it is just as annoying.
  • Go to Report Types
    • Modify the Report Type to include the new field. 
    • Place the new field into the right section on the report type. 
    • Change the name of the field on the Report Type to make more sense
    • Save the report Type. 
  • Go to the Report 
    • Drag and drop the new field onto the report. 
    • Save the report. 
    • If you want to test the report at this stage, then
      • Modify the report criteria to bring back a valid record, rather than an empty parameter. 
      • Run the report
      • Be sure you don't save the report this time. 
  • Go to the main record that you are testing.
  • Click the Conga button
    • Wait about a minute for the Conga popup window to go through all it's configuration
    • Click Tools & Settings
    • Click Template Builder 
    • Wait about a minute for Conga to display the Template Builder window. 
    • Pfaff about in the template builder window to find the field you added to your Report Type.
    • (or download the View Data workbook, but then you give up using that because it puts spaces at the end of the field name when you paste it into Word and you have to type the 4 braces yourself, whereas the copy from the template builder puts both sets of braces in and puts a space AFTER the field so you can quickly add new fields).  
    • Click on the Copy button (you have given up using the drag and drop feature because it is so cumbersome and is only useful for table data). 
  • Open your Word document 
    • View field codes (alt+F9)
    • Find the location you want to put the new field. 
    • Paste the new field in. 
  • Save your word document. 
  • Go back to the template builder. 
  • Click the back button (why oh why oh why can't you have both open at the same time!). 
  • Choose Local Template Tab
    • Click Select File
    • Browse to your file you just saved. 
    • Click Open
  • Click Preview
    • Wait about a minute for Conga to merge and download the document. 
  • Open the document from the downloads tab at the bottom of your browser. 
    • Click Enable Editing
    • Turn off view field codes (alt +F9)
  • Find the field that you just added. 
  • Let out a muffled scream when you realise it's not quite right and you have to start the whole process all over again. 
  • Find about 10 other errors in the document that you are yet to fix. 
  • Rinse and repeat, oh about 1000 times until you get it right. 
  • Then test it as the user who needs to run it to find out that they can't run it or the field is not visible for them.
    • Go into profiles and update profiles so that user can see the field. 

Drawloop

  • Navigate to the object that you need to create the formula field on.
  • Create the formula field. 
    • Make the field visible, 
    • Ensure the field is showing the right value for the right conditions. 
  • Go to the DDP in the Loop app that you need to modify. 
    • Click on the document name to open up the document record
    • Download the Word file related to that DDP
  • On the DDP record, open the Field Tagger
    • Find the field in the field tagger
  • Open your Word document 
    • Find the location you want to put the new field. 
    • Paste the new field in. 
  • Save your word document. 
  • Go back to the document record
    • Click Replace Document
    • Upload the document. (Noting that if any automated documents go out at this time they might be incorrect because you are modifying a document in a live environment and there is no other way to test it other than create a whole new DDP). 
  • Go to the main record that you are testing.
    • Click the LOOP button
    • Choose the download option (ensure you have a download option in your LOOP setup just for this occurrence)
    • Wait quite a few mins for the download to occur. 
  • Open the generated document.
  • Find the field that you just added. 
  • Let out a muffled scream when you realise it's not quite right and you have to start the whole process all over again. 
  • Find about 10 other errors in the document that you are yet to fix. 
  • Rinse and repeat, oh about 1000 times until you get it right. 
  • Then test it as the user who needs to run it to find out that they can't run it or the field is not visible for them.
    • Go into profiles and update profiles so that user can see the field.