Microsoft Word Mail Merge Functionality in Metastorm BPM 9.x

Tony Wheeler

The information in this article applies to:

Product: Metastorm BPM
Version: 9.x

 

Issue

 

Metastorm BPM (MBPM) version 7.x provided Microsoft Word mail merge functionality out-of-the-box. MBPM version 9.x does not have this built-in functionality.

 

Resolution

 

Use the Open XML SDK 2.0 for Microsoft Office provided by Microsoft in a server side script to create and manipulate Open XML Word documents.

       

In past versions of MBPM, the native %Print function allowed for the merge of a Word document on the server with information from the current process. This was useful when you wanted to “stamp” information onto a pre-built Word template. In more advanced scenarios, you could even use VB Script to automate the Word application using COM Interop.

 

In MBPM version 9, you can still do these things and much more by leveraging the power of the full .NET framework. In fact, you can now extend MBPM’s functionality using the widely adopted C\# language, while tapping into the robust APIs provided by Microsoft, other vendors, or even open source projects.

 

In MBPM version 7.6, automating the Word application on the server worked well in certain scenarios, but Microsoft does not recommend or support it. This Microsoft KB article describes some of the issues when automating the Word application on the server: http://support.microsoft.com/kb/257757.

 

Beginning with Word 2007, Microsoft recommends that you use the Open XML spec to build and manipulate Word documents. Using Open XML has the benefit of not requiring Word to be installed on the server, is much more scalable, and also avoids the issues outlined in the Microsoft knowledge base article.

 

In order to perform a pseudo mail merge, we’re going to use a simple “find-and-replace" pattern. This is a classic technique often used in HTML, but equally effective in Word, now that we have access to the new Open XML object model and the XML-based Word file format (.docx). This was not possible under the old binary Word (.doc) format. To see the internals of the new format you can simply change any Word document’s extension from .docx to .zip and open the zip file in Windows Explorer.

 

1. Install the Open XML SDK 2.0 for Microsoft Office: http://www.microsoft.com/downloads/en/details.aspx?FamilyId=C6E744E5-36E9-45F5-8D8C-331DF206E0D0&displaylang=en

 

Installed with the SDK are 2 DLLs that we’re interested in for this example:

 

DocumentFormat.OpenXml.dll (installed with the SDK)

 

WindowsBase.dll (found in …\\Program Files\\Referenced Assemblies\\ or …\\Program Files (x86)\\Referenced Assemblies\\)

 

2. Find those 2 DLLs and copy both of them into the following 3 locations:

 

..\\ \\Metastorm\\BPM\\Deployment\\CustomLib              

..\\ \\Metastorm\\BPM\\Designer\\CustomLib              

..\\ \\Metastorm\\BPM\\Engine\\dotnetbin 

 

Each location represents a different context that needs to have access to the DLLs (so these locations could possibly be on different machines depending on your environment)… one location for deployment, another location for the Designer, and finally a location for the engine.

 

3. Activate DocumentFormat.OpenXml.dll in the Designer by going to Options > Scripting > Namespaces (just to be safe, you should check all namespaces that start with DocumentFormat). You can also activate “All Functions” under the Intellisense tab if you like.

 

As an aside, the above steps are required if you wish to use ANY custom external DLL.

 

You should now have access to the OpenXml class library for creating and manipulating Word documents in the Designer.

 

4. In your solution, create a new server-side script, name it “WordExample”, and add Using statements:

 

using System.IO;

using DocumentFormat.OpenXml.Packaging;

 

The following demonstrates a server-side script method that accepts the path to the Word template, the path to the output file, the text to find in the template, and the text to replace it with:

 

        // Search and replace content in a Word document.

        public static void SearchAndReplace(string wordTemplatePath, string wordOutputPath, string findString, string replaceString)

        \{

            //Create instance of our template in the output location

            File.Copy(wordTemplatePath, wordOutputPath, true); //overwrite = true

            //Using automatically takes care of variable scope

            using (WordprocessingDocument wordOutputDoc = WordprocessingDocument.Open(wordOutputPath, true))

            \{

                string docText = null;

                using (StreamReader sr = new StreamReader(wordOutputDoc.MainDocumentPart.GetStream()))

                \{

                    docText = sr.ReadToEnd();

                \}

                //Swap the text out -- This could be "Merge" fields in the document

                docText = docText.Replace(findString, replaceString);

 

                using (StreamWriter sw = new StreamWriter(wordOutputDoc.MainDocumentPart.GetStream(FileMode.Create)))

                \{

                    sw.Write(docText);

                \}

            \}

        \}

 

 We call this method in a Code Activity anywhere in our solution by using the following:

 

WordExample.SearchAndReplace(@C:\\Program Files\\Metastorm\\BPM\\Web\\Docs\\LoremIpsum.docx, @C:\\Program Files\\Metastorm\\BPM\\Web\\Docs\\LoremIpsumNEW.docx, "\[REPLACETEXT\]", "New Text!!!");

 

As long as your template document has well-defined “merge” fields, you can build any number of documents by finding your “merge” fields within the output doc’s MainDocumentPart stream, replacing them with your own text values, and then writing the new stream back out. Note that you do not need to create a special template using Word's mail merge feature. You just need to create a document where the "merge" fields can be clearly specified in the Code Activity. Also note that the account the BPM engine runs under needs write access to the destination folder.   

 

Of course this is just a simple example. You can extend this by generalizing it for any business object passed in, looping for and treating each business object property like a key/value pair. This has the benefit of reuse. Alternatively you could create a separate SearchAndReplace-type method with custom/conditional business logic to perform before the replace function for each of your templates. This is a very powerful and flexible way to create mailers, letterhead, or any type of customized form letter in your business process.

 

The Open XML SDK 2.0 for Microsoft Office even gives you the tools to create any Office document from scratch, including Excel files, although some find this level of control too granular and prefer to use one of the commercial or open-source wrappers for the Open XML SDK. Note that Metastorm Support does not cover the usage (code) of 3rd party assemblies beyond getting the namespace to appear in the Designer, provided the root namespace of the assembly is marked accessible by external assemblies.

  

The attached solution expects the above-mentioned DLLs to be in their proper locations and the included template to be at: C:\\Program Files\\Metastorm\\BPM\\Web\\Docs\\LoremIpsum.docx (your paths may vary as long as they match the paths specified in the SearchAndReplace method call). This docx file contains the merge field “\[REPLACETEXT\]”.  The Code Activity on the command button on the form calls the server script and passes in the source, destination, and text to find as well as the replacement text. After clicking the command button, your “merged” document will be available in the wordOutputPath location.