Word Mail Merge Guide

Quandis PDF Builder

A PDF construction tool is available in the Quandis Version 3.0 and higher platform.  The tool allows a business user to utilize Microsoft Word to construct the layout and map the appropriate data elements to their appropriate location using Microsoft's Mail Merge feature.  This guide will walk you through the construction of the word document, which includes best practices, and data mapping.  Once the word document has been assembled, a few simple front end configuration steps are required by a Quandis PM/BA to complete the process.

Section 1: Layout

Having the proper layout of the PDF is important, especially when this PDF is being delivered to a client, or other entity, such as a court.

When only simple blurbs of text such as paragraphs are being used in the PDF, simply type out the paragraphs and/or sentences within the Word document, leaving spaces for the data elements to be mapped.  For example:

[

Dear <<First Name, Last Name>>,

We have received your letter requesting a Short Payoff for the property located at <<Property Address>>, in the amount of <<Payoff Amount>>.  ...

]

However, in more complex PDFs, where multiple data elements will be mapped in various locations throughout the PDF and need to be properly aligned, the following instructions should be used.

It is recommended that you break the PDF out into sections, which will assist with positioning text and fields appropriately.  For example, the following screenshot should be broken out into three sections; The header information should be broken out into its own section, and sections I & II should be in separate sections as well.

When building these sections, utilize Word's table feature, and configure each cell appropriately.  This will ensure text and data placement consistency.  We will need to calculate the proper size of the table.  For the first section in the example provided above, we will need 8 rows:

And 9 columns.  This is calculated by finding the highest number of elements found in any given row of the section, in the first section of the example above, rows 2 and 3 will both require 9 columns:

You may find that elements that will remain static in size can be combined, such as columns 4 & 5 as well columns 6 & 7.  However, we will keep them separated for this example.  In the Word Ribbon (Toolbar for Office versions prior to 2007), select "Insert", then "Table", and select the rows and column count that was just calculated (9 X 8).  The maximum size table that word allows to be inserted is 10 X 9, however, additional rows and columns can be added afterward.

Which will appear like this:

If additional columns are needed, right click anywhere in the table and select "Insert Columns to the Left" or "Insert Columns to the Right".

If additional rows are needed, follow the same instructions, except this time select "Insert Rows Above" or "Insert Rows Below".

We can now begin configuring the table by resizing rows and columns, as well as splitting and merging cells.  To resize rows and columns, mouse over the border of the row/column you would like resized, then click and drag until it is at the desired size.  To split cells, right click on the cell that needs to be split and select "Split Cells", then enter the number of columns and/or rows that should be contained within that cell.  To merge cells, highlight the cells that should be merged, right click on the highlighted cells, and select "Merge Cells".  In the example below, the first row was doubled in height, and all cells merged together, then the cells in the remaining rows were resized to fit the text for the first section of the sample PDF.

We can now begin entering the static text in the appropriate cells.  Use Word's text formatting (size, font, alignment, etc) to match the sample PDF.

 Static images can also be embedded.  For example, if a logo was needed in the top-left area of the page, first row could be split into two cells, and the image simply copied and pasted, then resized to fit appropriately.

 We will now remove all borders from the table, and underline the appropriate cells.  Underlining can be handled two ways.  If the width of the underlining should match the width of the text, then highlight the cell and select the underline icon ("U" in 2007 and later versions of Word).  If the width of the underlining should be static, right click on the cell and select "Borders and Shading", then select the bottom underline option.

Both underline methods are displayed below.

Even though the borders are no longer displayed, resizing rows, columns and cells, as well as splitting and merging cells can still be conducted.

In some scenarios, such as comparable grids in BPOs, borders will be required.  An example of this is provided below.

Mastering the process of laying out the Word document to match the PDF sample may take a little time, but once you have reached a high level of comfort, creating new PDF templates and managing existing ones will become very simple.

Section 2: Create the QBO Document Template

As soon as the Word document has been assembled, an Document Template must be created in the QBO application.  The Document Template will be tied to a database query that will return the data elements which will be mapped to their appropriate locations throughout the PDF, which is detailed in the next section.  If the database query already exists, this process will take only a few minutes.  However, if there is no query readily available, assembling one to meet the needs of the data requirements can take anywhere from minutes to hours, depending on the data needed.

Within the QBO application, navigate to Design >> Documents:

In the "Document Templates" Panel, select "+ New Template" from the Options Dropdown menu:

For the purpose of this document, only the Document Template, Applies To, Method, Plugin and Transform data entry fields will be used.  All other fields can be ignored.  Enter the name of the Template and select what object this Document Template will apply to.  Business logic can be applied to determine what your Template will apply to.  Examples:

·         If the document will be a Foreclosure Referral package, then "Applies To" will be set to "Foreclosure".

·         If the document will be a PDF of BPO results, then "Applies To" will be set to "Valuation"

Select "AsposeWord" from the "Plugin" dropdown list.  A field for "Transform" will be presented.  This is where the completed Mail Merge Word document will be uploaded.

The values in the "Method" dropdown will generally match the value in the "Applies To" dropdown.  Once a Method is selected, an "Operation" dropdown will be presented.  "Select" will be the appropriate selection from this dropdown, except when a custom query is needed, as described above.  Once an Operation selection is made, a "Parameters" text box will be presented, this will be left blank.

Click "Save"

Creating the QBO Document Template is a process that is currently handled by a Quandis Project Manager/Business Analyst.

Section 3: Data Mapping

The mapping of the data will utilize the Mail Merge functions of Microsoft Word, which obtains its Merge Fields from an Excel spreadsheet produced by the QBO application.

In order to produce the Excel spreadsheet that will be referenced by the Word document, you will need to obtain an ID for a valid test case.  There are a couple of ways to obtain this information:

1.       Contact your Quandis Representative

2.       Follow these steps:

a.       Navigate to the Dashboard of the object you are working with, in this example, you would navigate to Home >> Foreclosure

Navigate back to the Document Templates page (Design >> Documents).  In the "Summary" Panel click on the link for the object to which your new Template applies.  In our example, it is "Foreclosure":

The "Document Templates" Panel will refresh and display all Templates that apply to that object.  Click on the link for the new Template:

In the Options menu of the "Summary" Panel, select "Create Test Data"

In the dialogue window that opens, paste the case ID obtained in the prior steps and click "Generate"

This will produce the Excel spreadsheet required for the Mail Merge function.

Open the spreadsheet and select File >> Save As, then select either "Excel Workbook" or "Excel 97-2003 Workbook", and save it to a location you will easily remember (for example, "Desktop").

Open the Word document that was assembled in the first section (Layout).  Within the "Mailings" menu, click on "Select Recipients", then "Use Existing List...".

In the dialogue window that opens, browse to the location where you saved the file (our example used "Desktop"), click on the file and click "Open" (or you can simply double-click on the file).

In the next dialogue window that opens, ensure that the checkbox for "First row of data contains column headers" is selected, then click on "OK".

Locate the first section that was reserved for the Merge Field and delete the text that was entered to reserve that space.

Leaving the cursor at its current location (in the example above, it would be prior to the comma following "Dear "), select "Insert Merge Field" from the "Mailings" menu, and locate the appropriate field to be populated.  Note: Determining the appropriate field may require referencing the Excel spreadsheet and locating sample data within it.  Your Quandis Representative will also be able to provide appropriate field mapping.

The text will now contain the Merge Field you selected, wrapped in "<<>>".

Continue working through the document, updating all reserved sections with their appropriate Merge Fields.  When working with tables, as described in Section 1, you will simply insert the Merge Field into the appropriate cell.  Additional formatting may be needed, as inserting the Merge Field may resize the cell/row/column.  A few examples of cell formatting have been provided below.

Auto resized on insert:

Cell width resized manually after insert:

"AutoFit to Contents" applied:

"Wrap text" removed and "AutoFit" applied:

Once all Merge Fields have been inserted, and all formatting applied, save the final version as a "Word 97-2003 Document" (file extension ".doc") or "Word Document" (file extension ".docx") and upload it to the Document Template in the QBO application.  As with the Excel spreadsheet, be sure to save it to a location that is easy for you to remember, for example, "Desktop".

Navigate to the Document Template that was previously created, and in the "Summary" Panel, select Options >> Edit

Select the Upload icon in the Transform field

Browse to the Word Document's location (our example used "Desktop"), click on the file, then click "Open" (or simply double-click on the file).

When the file finishes uploading, click "Save".

A test can be run by selecting "Generate" from the "Options" menu in the "Summary" Panel.

This concludes the Quandis Aspose Word PDF Builder instructional guide.  If there are any questions, please contact your Quandis Representative.