Full Guide to SharePoint Mail Merge with Documents, Templates, Lists and Quick Parts

​

Quick Intro Introduction to Mail-merge

The idea of mail-merging is old. Yet it's relevance today is continues to be paramount in the modern day business.

 

Mail merge is about efficiency and productivity. You may have a boilerplate document used frequently in your everyday workflows that needs to be customized for an individual recipient. This document template could be a contract, letter, proposal, RFP or any other type of reusable document. The challenge is that opening and editing the contents of each document to tailor it to the right recipient by changing the name, address, project name, or any myriad number of fields multiple times is a very time consuming process for something you do so often.

 

Mail-merge was the magic solution that allowed a template document to be automatically filled in with all the right data about the recipient, etc.. all in the right places in the document very quickly.

 

Today there are many tools that can help you do this. In this post will focus on how SharePoint can be a great plat form for your document merge needs and explore uses cases and tools that can help you accomplish this.

 

Before We Dive in, Meet the Two Mail Merge Use Cases

Many may feel like they need mail-merge on SharePoint documents, but the truth is you may fit into one of those two categories that are slightly different.

 

1. Use Case 1 - I want to create one or more documents for a single recipient.
2. Use Case 2 - I want to create the same type of document to multiple recipient.

 

May sound confusing. But here is the difference.

 

1. Use Case 1 - I want to create one or more documents for a single recipient.
   An example of this is if you are creating a project for a client and you have a predefined number of documents that come with every project, (The scope of work, the functional spec, the project time-line, etc..) there could be tens of those documents per project. You ideally would put input the users/client's data into a form and all those supporting documents get populated.
   
   
2. Use Case 2 - I want to create the same type of document to multiple recipient.
   An example here would be that you are sending out invoices for the month. You have one invoice template, but you just want to generate an invoice for each customer. In this case one template -> multiple customers.
   
   

 

Both of use cases are great document merge applications. Try to identify which you fit in, because the solutions may differ.

 

 

 

 

First Let's See how SharePoint Can hold Mail Merge Data

 

SharePoint fundamental building blocks are sites and lists. A site is hold a collection of lists and lists can hold data (sort of like a database or even a file share). SharePoint lists are dynamic because you can add as many columns / metadata / fields to the list as you need. You can add "First Name" to a text field., or "Due Date" to be a Date and Time Field., etc.. Let's look at this example:

 

Here's an example:

 

​

A document library here has some contracts and forms along with some extra fields and columns such as "Primary Contact Name", "Primary Phone" and "Address"

 

Updating this information direclty in SharePoint is very quick. Wouldn't it be awesome if we can have the data that we update in SharePoint show up in the document in the right place automatically?

 

YES it would. Keep Reading.

 

Microsoft Word and Quick Parts Merge Fields

There is a great feature in Word that allows you to put fields on a page. This may be known to many, but how do we put a SharePoint field into a Microsoft Word document?

 

The answer is simple, if the document is in a SharePoint library that already has fields, like the projects library above, then those fiels will magically appear automatically under Quick Parts when you try to add fields. In fact you will see other document native fields along with the SharePoint fields as well.

 

​

 

When you insert it into the document, it will look like this:

 

​

 

The beauty of this now is that those fields are tied to the SharePoint field. If you modify it from the document it will change in SharePoint and if you modify it from SharePoint it will change in the document.

 

The Benefits are clear:

 

1. You can quickly add template documents and populate them without ever opening the document itself. Quick Edit feature allows to quickly modify fields across all documents. For example, if you have 30 documents, you can drag the name quickly across of them and all the documents will update with that "contact name" field in the quick parts.
2. Automation is now possible. You may have work flow that updates a field in your document library, then will in turn update the document content itself. This is useful if you have to generate say a receipt or an agreement to sign, etc..

 

Draw Backs

 

1. Inital Investment:
   Of course there is the initial investment of building the template and populating the quick parts fields in there. We would expect that. This is more of an initial investment than a hurdle.
2. Tabular Data Deficiency:
   There are no repeating rows functionality. Say for example you want to populate a section of document with the list of contacts from another SharePoint list. There are some workarounds to this below lookup fields and calculated columns.
3. List Data to Document Deficiency:
   Use case 2, where you're trying to generate say invoice for multiple clients is not easy. No mechanism of feeding this data from an external source into the document.
4. End user Friendliness
   There are still areas of improvement for use case 1 and one and challenges.
   1. As an end user, creating a new project folder with all supporting documents still, seems like quiet a number of steps to complete and populate. More on a solution to that below.

 

Solution to the Tabular Data Deficiency

Let us say that you have a template document for Project Plan. This project plan should list all the team members of that project. You have the list of all your contacts in a separate SharePoint list. Ideally you would want to select which contacts are part of this project and then their info would show up in the Word document. This information would include their Name, Last name, email, phone number, and the company they work with.

 

SharePoint is challenged when it comes to that. Let me explain.

 

The closest solution is to create a lookup column on your document library that points to the list of contacts. However when you insert that column in the document library the contact will only show the First Name or whatever field you chose only. It will NOT be able to show the other fields for that contact such as their email, phone number, company.

 

To work around that issue, you can create a calculated column on the SharePoint document library that pull in all the data you need. For example:

 

The calculated column would like

 

=[FirstName]&" "&[LastName]&"-"&[email]&"-"&[Company]

 

The result will show in the field as

 

John Doe - john.doe@gmail.com - ABC Company

 

And that will work.

 

Of course the formatting is not as clean. Its not quiet tabular and listing those users under each other like that might look a bit untidy. But at least you can capture that information.

 

Here's more on Calculated Field Formulas from Microsoft

 

 

A Turn Key Solution for Use Case 2 - Populating many documents for single client

 

We talked about SharePoint and how you can create fields in document libraries and make those surface in the Word document itself using QuickParts. We talked about how a great use case for that is:

 

1. If you started a new project with a client and need to create a new folder for that project and collected all the supporting documents that go along with it such as project plan, agreements, checklists, etc..
2. For each one of those documents you want the Client's name, Contact info, address, to show up in the headers, etc..

 

Ideally your work flow would be like this:

 

1. Open a page in SharePoint to create a project / client / etc..
2. Enter the new clients information
3. The folder is created, all the documents are populated from templates
4. All the templates are populated with the information about the client

 

.. and magically all your tedious work is done. 2 minutes to provision the folder and all supporting documents and their core info.

 

The out of the box SharePoint document merge fields, don't quiet give you that full turn key solution. You still have to go through many screens to create the folder copy the files, rename files, edit ht fields for each file and could take 20 minutes if not more.

 

For something that we do so often there is a great solution called Tru Copy (available free in the Office 365 SharePoint Store) that allows us to do just the steps outline above quickly.

1. You can quickly create templates and reuse them anywhere. This could be proposals, agreements, etc..
2. You have a one page that will past the entire folder and its supporting documents to your destination location
3. You cam complete the new client's forms directly from that same page.

 

Let's take a deeper dive.

 

Let's say we have a Project Document Library, where each folder represents a project.

 

​

 

Inside Each project we have a number of documents needed for our project. For simplicity we have 2 in this case

​

 

With Tru Copy we can provision projects very quickly. Here's how. Tru Shows up at the top of you document library ribbon.

 

​

 

To insert a project we click Paste. We Click on the Templates Tab to find the templates we have available. The templates tab shows all items in your sites that has the word "Template" in it. So to create new templates you just need to create your template anywhere in SharePoint and make sure it has the work "Template".

 

​

 

Last thing is we check the option

 

We Specify a New Folder Name

​

 

 

Then we specify all the client details that go along with the template and then all the information from the form will be automatically populated into the template documents inside the projects folder.

 

​

That is it. You were able to create a project folder for a new client with all the needed files and fields pre-populated using a mail-merge feature.

 

Give it a try: Tru Copy

 

 

Use Case 2 - Creating a Merged documents form a data source such as SharePoint List

 

Imagine now having one Master List , of say your customers, and would like to create a document or an invoice for from this pre-existing data.

 

One option you have here is to use a workflow and associate it with the list. Still there is much setup and configuration needed. here, but here is the gist of it.

 

You create a workflow the copies the data from one list to another. So you would have two lists one containing the source merge data and the other a document library with the final output.

 

Both those lists would have matching fields in a way. So the workflow would copy First Name, Last Name, Email, Company from the source list to the document library and the document library can of course have those fields mapped inside the document itself as QuickParts.

 

The workflow would also need to copy a source document template as well.

 

This workflow is something you can do in SharePoint Designer.

 

If you are interested in more details on those steps please leave a comment below.

 

Conclusion

Mail Merge in SharePoint using Microsoft Word, Quick Parts is a great tool that can help you improve the efficiency of your end users workflow. Boiler plate templates for contracts, agreement, Legal documents, proposals, RFPs can be stored in SharePoint and user interface of SharePoint leverage to provide forms to complete those forms. Tools like Tru Copy Paste can also be valuable as turn key solutions for Boiler plate template management.

 

Also share any other use cases you may have with SharePoint mail merge with documents and etc..