This article will detail different examples of Custom - User Defined Reports that you can pull into Microsoft Word and Excel using Mail Merge. 

In order to create a Mail Merge, you must have CLIPxe with customers and jobs loaded up in Microsoft Word, Excel, or Microsoft Office.

In our examples, we are using Microsoft Office 2013.

This will outline a simple Mail Merge to all my clients for a marketing letter.
First, click Reports>Custom Reports> then User Defined List.

Click "Customer and Job" under "Choose Data Base Set", and then click "New."

Give the report a name and then click the "Ok" button.

Click "Fields."

Choosing the "Fields" on this screen will apply those fields to the report or letter.
Click "Return" once you are finished choosing the Fields. 

Click "Condition."

We will choose a condition that applies to certain customers in our CLIP database since our customer numbers are greater than 1000.
​
Under Step 1, choose the field "CU.Customer Number." Double left click or press "enter."
​
In step 2, click "Is Greater Than." Type in 999 since in our example we want to include all customers and our customer numbers start with 1000 or greater.
You can skip step 4 and click "Use This Condition" at the bottom. 

On the "User Defined List" window, click "Make List" towards the bottom.
Click "Yes" to save your settings.

On the Report Output menu, choose Microsoft Word and then choose "Form Letter" and "Proceed."

If you already have a saved merge document for use with this list, then you can open it. However, since this is an example we are starting a blank document.
Click "No."

If you get an error about ODBC drivers please install the drivers - click here to download.
​
Now you will be in Microsoft Word with the merge information all ready for modification. Type out the generic items from your letter. 

When you get to the place in the document where the customer's name and address go, choose it from "Insert Merge Field" under the Mailings section in Microsoft Word.

Click "Preview Results" to see how the merge fields will look in your document.

Type out the rest of the marketing letter, while adding the fields that you need to personalize it to each customer. Click the arrow button to the right of "Preview Results" in order to view each customer one at a time. 

Once you have detailed information in your merge document and are ready to print. Click "Finish and Merge."

This will give you the option to "Edit Individual Documents", "Print Documents", or "Send Email Messages." Make sure you save this as a template for later.

Mail Merge using CLIP's power to filter and export in order to limit our marketing letter to only our "Active Customers."

This will be a more complex mail merge that uses CLIP's ability to filter the customers down to only the active ones and use an existing merge template.

Click "New"and give it a name. Click "Fields."
You can use the same fields as the mail merge in the first section of the article since this setup is similar. Click "Condition", and choose "CU.Customer Status" field for step 1. 

Double click or press "enter."

Under step 2, click "Equals."
Under Step 3, type "ACTIVE" (in all capital letters).
Press "Enter" on your keyboard and then click "Use This Condition."
Since we do not need any other conditions at the moment, click the "Make List" button.

Save the settings. On "Report Output", choose the Microsoft Word Document. "Form Letter" should now be selected.
Click "Proceed."
When CLIP asks you if you want to open as an existing document, click "Yes."

Choose the Microsoft Word Document that you want to use for this mail merge.

Now, let us go back and create the same merge letter but now only for customers that have had no activity in the last year.
​
For this, we can use the same setup as before.
However, now we will change the "Condition" to specify customers that have had activity.

In the "Condition" generator, click "CU.Last Activity Date" for step 1.

Click "Is Less Than."
​In step 3, enter a date in the last year (i.e. 01/01/2013). This will essentially find customers whose activity date is older than a year ago.

Click Use This Condition> Make List> and Output to Microsoft Word (similar to the last Mail Merge).
This gives you a letter that is ready to go out to all of your customers in your list that have not been active since last year. You could enhance this to include any condition that we have previously used by clicking "Add Another Condition."

Mail Merge that uses information from CLIP customers and the first job for word to show pricing.

This will be a more complex mail merge that includes the price for all customers with a certain job number. Using CLIP to export the information to Microsoft Excel in order to format and calculate the information and pull it into Microsoft to format it into a nice proposal.

Create a new list by pressing the "New" button.

Give the report a name. Click the "Ok" button.
Make sure "Customer and Job" is selected under "Choose Data Base Set."
Click "Fields" to choose the fields you want.

Choose the fields you want. For this example, we will choose three new job fields. The fields we have listed above are the ones necessary for getting the name and address along with the job name, minimum, maximum, and charge per job. 

Click the "Return" button once you've finished selecting the fields you want.

Click the "Condition" button and enter the condition for active customers.

Your screen should look similar to the screen above.
Click "And" under step 4 or click "Add Another Condition" towards the bottom. Click the "JB.Job Number" field under step 1 and double click or press "enter."

Click "Equals", and then enter the job number. In our example, it will be "1".
Click "Use This Condition" once finished.

Click the "Make List" button. Click "Yes" to save the settings.
On "Report Output", choose Microsoft Word Document, and then "Form Letter." Click "Proceed."
When CLIP asks you if you want to open as an existing document, click "Yes."

Choose the same document that you used earlier in this article.

Click the "OK" button. 

Microsoft Word will open up with the same document we have been working on with new merge field information. Make sure to use "Save As" to save this report in a different location that is easy to access. 

After "Preview Results" is clicked, it will look like the screen below:

Now, we will export it to Microsoft Excel to change some columns and format it for numbers.
Click "Make List" on the same report, and then choose Report Output to Microsoft Excel.

Click "Proceed."
​CLIP will ask where you want to save the Microsoft Excel Document. If you accept the default, you can always "Save" the document in Microsoft Excel later.

Click "Yes" to open Excel with the new file.

Once Microsoft Excel opens, click the row for amount and then click the drop-down menu in order to switch that column to "Currency."

Create or select a new column. Select the first cell in that column then change the name to "Annual."
​
For this example, we want to take the amount times the maximu to get an amount over the maximum cuts. 

Enter a formula on the second cell "=I2*K2*".

Press "Enter" after adding the formula. Now left click and drag the little box in the lower right hand corner of the cell and pull it down until it fills that formula into all cells in that row.

Format the new row with the formula as "Currency."
​
Save the Excel Sheet and close it. Open the mail merge marketing letter document that was created earlier in Microsoft Word. 

Under the Mailings section, click "Select Recipients."

Now, click the "Use an Existing List" option.

Open the Microsoft Excel document that we created earlier.

Once you have located the Microsoft Excel file from earlier, click "Open."

Click "OK" to confirm the data source.

Click "OK" to confirm the table.

The Microsoft Word Document will display the new mail merge field based on the file we imported using Microsoft Excel.

Insert the new field and format the document as needed to create a marketing letter.

Again, click "Preview Results" after formatting the document.
Now click the "Finish & Merge" button to "Edit Individual Documents" for accuracy. Click "Print Documents."

This completes a rough proposal and gives a good example of how to use Microsoft Word and Excel together to complete a mail merge. 

Mail Merge that uses the "Create Merge" function to create an interesting proposal

We will create a mail merge that uses the "Create Merge" function in CLIP to create a proposal that shows various services being offered.
​
Since "Create Merge" will create the field names based on the job numbers, the only way we know what field to use in Microsoft Word is based on us knowing that job number 1 is the main mowing or service job.

All Proposal services should be in the first 10-20 job slots with consistency throughout.

If you look at the pattern jobs above, everyone of these services are the same Job Number across each customer in the database. This should be the same as your program for this to work properly. The point of this is to be able to get the system to automatically produce proposals with little or no input from you, the operator.

From the main CLIP screen click Reports>Custom Reports> then Create Merge.

Choose a directory you want to store the merge.

Click "Change Target Directory" if you want to change where you want the merge to be saved.
Click "Create Merge" to begin.
Click "Create Microsoft Word Mail Merge Document" in order to create the merge for use with Microsoft Word in mind.

Choose the "Fields" from the customer and job that you will need for your proposal. The "Fields" will be the information you want the proposal to show. Double click on the fields you want to choose, and click "Return" when finished.

When done with the fields, the "Condition Generator" will appear.
In this example, we will choose "CU.Customer Status" equals "Active."
Once you click "Use This Condition", you will need to enter what job number to start with and how many jobs you want included.

This will include the job number and however many jobs you want the proposal to show. Click "Proceed" and then CLIP will ask if you want to print the structure of the merge.

If you click "Yes", it will ask you to print this out. If you checked off the option to "Create Microsoft Word Mail Merge Document", it will open Microsoft Word with the merge fields ready to be applied to a proposal.
If you click "No", you will want to open the merge file in Microsoft Excel.
Find where you previously saved the Merge.dbf file.

Open Microsoft Excel and click "Open" or "Open Other Workbooks" (depending on your version).

Find the Merge.dbf file and click "Open."

By using formulas in Microsoft Excel, you can add new columns and create new numbers to use in Microsoft Word.
We went over one example of this in section 3 above. 

Save and close the Microsoft Excel document when you have finished your changes. Open your proposal document in Microsoft Word.

Open Merge.dbf file that we previously opened in Microsoft Excel.

If Microsoft Word asks you to "Confirm the Data Source", select the second option which will connect our Merge.dbf with the proposal document. From here, you can enter merge fields into the document and finish the proposal.