The Aspose Word Mail Merge Plugin enables QBO 3 create Word, PDF, and RTF documents based on Microsoft Word document templates. Business users use Word to construct the layout and map the appropriate data elements to their appropriate location using Word’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 QBO power user to complete the process.

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

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

For example:

[

Dear <>,

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

]

However, in more complex documents, 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 document 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, with sections I & II in separate sections as well.

To ensure text and data placement consistency when building these sections, utilize Word’s table feature and configure each cell appropriately.  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 (9×8).  This is calculated by counting the number of rows in the section, then finding the highest number of elements found in any given row of the section, as shown using red numbers in the next two images:

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 count of rows and columns that were 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, place your cursor over the edge of the cell box you would like to resize (the mouse arrow icon will change to the resize icon), then click and hold while dragging to create 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 by clicking on a cell that should be merged, then while holding down the left mouse button, drag in the direction of the cells you would like to be me merged together.  Now right-click on the highlighted cells, and select “Merge Cells”.  In the example below, the first row was doubled in height, and all cells were 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.

Broker Price Opinion (BPO)
CLIENT LOAN NUMBER:		INSPECTION TYPE		Drive-By		Interior		DATE INSPECTED
1st ALT TRACKING #		HOUSE APPEARS:		Occupied		Vacant/Secured			Vacant/Unsecured
2nd ALT TRACKING #				BORROWER/OWNER
PROPERTY ADDRESS:				CLIENT NAME:
CITY, STATE, ZIP				COMPLETED BY:
FIRM NAME				FAX NO:
PHONE NO.:				PARCEL NUMBER:

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

	Broker Price Opinion (BPO)
CLIENT LOAN NUMBER:			INSPECTION TYPE		Drive-By		Interior		DATE INSPECTED
1st ALT TRACKING #			HOUSE APPEARS:		Occupied		Vacant/Secured			Vacant/Unsecured
2nd ALT TRACKING #					BORROWER/OWNER
PROPERTY ADDRESS:					CLIENT NAME:
CITY, STATE, ZIP					COMPLETED BY:
FIRM NAME					FAX NO:
PHONE NO.:					PARCEL NUMBER:

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.

	Broker Price Opinion (BPO)
CLIENT LOAN NUMBER:			INSPECTION TYPE		Drive-By		Interior		DATE INSPECTED
1st ALT TRACKING #			HOUSE APPEARS:		Occupied		Vacant/Secured			Vacant/Unsecured
2nd ALT TRACKING #					BORROWER/OWNER
PROPERTY ADDRESS:		27442 Portola Pkwy #350			CLIENT NAME:			Quandis, Inc.
CITY, STATE, ZIP					COMPLETED BY:
FIRM NAME					FAX NO:
PHONE NO.:					PARCEL NUMBER:

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.

III. COMPETITIVE CLOSED SALES

ITEM

SUBJECT

COMPARABLE NUMBER 1

COMPARABLE NUMBER 2

COMPARABLE NUMBER 3

Address

City, State Zip

Proximity to Subject

*Proximity  Miles

*Proximity   Miles

*Proximity  Miles

Type of Sale

Sale Price

Price/Gross Living Area

Data Source

Property Type

Original List $

List Date

Sale Date

Total DOM

VALUE ADJUSTMENTS

DESCRIPTION

DESCRIPTION

+(-) Adjustment

DESCRIPTION

+(-) Adjustment

DESCRIPTION

+(-) Adjustment

Mastering the process of laying out the Word document to match the PDF sample may appear to be challenging, but with practice, creating new PDF templates and managing existing ones will become routine.

As soon as the Word document has been assembled, a Document Template must be created in the QBO application.  The Document Template will be tied to a database query that returns the data elements which are then 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.

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:
   1. Navigate to the Dashboard of the object you are working with, in this example, you would navigate to Home >> Foreclosure
   2. Search for a file that exists in the system using the “Search” box in the top right of the screen, or click on any value in the “Summary” Panel that returns valid data
   3. Click on the hyperlink to access the case
   4. In the URL, the numbers following “ID=” represent the ID for this case.  Copy these numbers, as they will be used to generate test data for your Document Template

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 above, 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 that you created when following Section 2.  In our example above, we created “Foreclosure Referral Package”:

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”).  Do not change the name of the file.

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”), and locate the Sample Data file that was generated by the system.  Assuming you did not change the name of it when saving it in a previous step, it will be an Excel file that contains the name of the Document Template (our example was named “Foreclosure Referral Package) and end with “SampleData.xls” or “SampleData.xlsx”.  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”.

You will be directed back to the Word Document.  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.  Please refer to Section 1, as well as Word’s help section for assistance with additional formatting.  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”, NOT “.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.  Should you have any questions, please contact your Quandis Representative.