How to run mail merge using contact report

This page explains how to run a mail merge using a custom FBO One Contact report and Word 2010 (also possible with older versions of Word).

In FBO One custom values can be generated to differentiate between products, contacts, aircraft types etc. Usually custom values are used for price agreements, but for the mail merge they will be used to determine to which customers the mailing will be sent. In this how-to we will create a custom value for sending greeting cards. Next we will use a custom Contact report to run a mail merge in Word 2010.

To do this, you are required to complete the following steps:

  1. Define the custom property ‘Send Greeting card’ and the related list of custom values. This list of values should be a single value ‘Yes’
  2. Tag all contacts that you want to send a Greeting card with the custom value ‘send Greeting card: Yes’.
  3. Define a custom contact report that lists all contact details relevant to print an address label, as well as the custom Greeting Card property
  4. Run the new report
  5. Use Excel to filter for the value ‘Yes’ in the ‘Greeting card’ column
  6. Export the filtered list to a new Excel file
  7. Create a mail merge session in Microsoft Word to print address labels
  8. Use word to merge the Excel file with the address details with the letter or label layout, and print all the labels

 Below, we illustrate the steps that you need to take in FBO One. The steps that you need to take in Excel and MS Word are also illustrated using screen shots.

Step 1: Creating the custom values

Select a descriptive name, which in this case could be ‘Greeting Card’ or ‘KeyClient’. Link this custom value to the[ contacts|MAN:Contact (Company)] that you wish to send a Greeting card to. Thereafter every year you are only required to run a contact report and filter the Greeting card value to find the required list. To do this go to the administration menu, click ‘Custom values’ and click “Add new. Select a clear name for the Custom property such as ‘Greeting card’. Complete the fields according to the picture below and click save:

 Next click 'Add new' under Custom values and create two values, 'Yes' and 'No'

Finally link the contact table to the custom property. This way the custom values can be selected in the 'edit contact' screen. If you want you can add a warning if no custom value is selected when editing a contact:

Step 2: Adding the custom value to the contacts

We are required to add this custom value to the concerned contacts, those that we wish to send a greetings card. Go to the contacts page, add the custom value by clicking on the ‘Edit organization’ as shown in the example below show below.

 

 

Step 3: Creating the custom Contact report

To be able to use excel as a source for the mail merge we need to create and configure a Contact report. To do this go to the administrations menu and select ‘Reports’ followed by ‘Add new’.

 

When creating a report always ensure they are clearly named and has a clear description. This will allow fellow colleagues to be more efficient. We will call this report ‘Greeting card report’. Under the ‘Queries to be executed’ sub title, Click ‘Add new’ and select the correct query which in this case is ‘Contacts’. Now we must start to generate the desired columns by adding the ‘Custom property’ column which must have the relevant ‘Custom property’ which in this case is Greeting Card, as shown in the example.

 

We now need to start generating the other desired columns which may include the following;

  1. Custom properties – which we have just created
  2. First name
  3. Last name
  4. Name
  5. Parent organization
  6. Address 1
  7. Address 2
  8. Zipcode
  9. City
  10. Country code

As you may wish to filter by suppliers, operators, trip support providers and debtors you may also require:

  1. Is Debtor
  2. Is Operator
  3. Is Trip support provider
  4. Is Supplier

You should have a result similar to the example below. Note that the ‘Column offset’ starts at 0 and represents column A, 1 is column B, 2 is column C and so on.

To test the report, go to the ‘Report screen’ and select the ‘Greeting card report’. Run the report, you will find the first column to be ‘Custom property’. Select the top left cell and click ‘Filter’. Use the filter on the required columns be it the ‘Greeting card’ column or any other i.e Is Supplier, Is Debtor etc. 

 

 

Step 4: Running the mail merge

 Start Word 2010, navigate to Mailings tab, and from 'Start Mail Merge' options, click 'Step by Step Mail Merge Wizard'.

This will bring up 'Mail Merge' dialog at the side of the Word document window, here you can choose what type of document you want to create, select 'Letters' for this example and 'click 'Next:Starting document' from the bottom of the dialog screen.

In this step, you will be provided with different options to create Letters, we will choose a simple procedure by selecting by template. Click 'Select template'.

This will bring up the 'Select template dialog', choose a suitable template. We will be using the 'Black Tie Letter' template. Of course you can create your own template. Click OK to continue.

In the next step, you can choose different options to import recipient list, we will select an existing list, the Greeting card report. Hit 'Browse' to import the recipients list.

The 'Select Table' dialog will appear, now choose the worksheet that holds the data, usually this is Sheet1$. The first row contains column headers, so enable the 'First row of data contains column headers' option, and click OK.

 You will see the worksheet in the Mail Merge Recipients dialog. Here you can filter<a id="KonaLink6" href="http://www.addictivetips.com/microsoft-office/word-2010-mail-merge/"></a> down the recipients on the basis of the Greeting card custom value and/or any other property by clicking on the arrow next to the columnheader. Click 'OK' to proceed further.

Now navigate to 'Mailings' tab, and click 'Address Block'.

This will open the 'Insert Address Block' dialog box. From 'Specify address elements', choose the format you wish to show recipient name in. Beneath 'Preview' you can see a preview of the changes that affect the format. Under 'Correct problems', click Match Fields to insert new fields in the letter, as we want to include the address of the recipient.

The 'Match Fields' dialog will appear, now for Company select Name, for Country Country code and so on. Match all required fields with the column names from the Greeting card report. Click OK to continue.

You will see the information is added in to the format in the preview window. Click *OK *to continue.

 

Now we will customize the greeting message, for that navigate to the 'Mailings' tab, and click 'Greeting Line'.

In the 'Insert Greeting Line' dialog, you can choose the desired greeting format from 'Greeting Line format'. Under 'Greeting line for invalid recipient names' choose the format for unknown recipients. Under 'Preview', it show the preview of of the changes you made. Click OK to continue.

Use 'Insert Merge Field' to add extra fields to the template. If you just want to print labels, the template could look like this:

Next click 'Next: Write your Letter' in the 'Mail merge' dialog box and write your message. When finished click 'Next: Preview your letters'. If everything is alright click 'Next: Complete the merge' and click 'Edit individual letters'. This will bring you to the 'Merge to New Document' dialog. Select 'All' to view all the recipients. Click OK to show the letter of each recipient.

And with this final step the mail merge is complete.

 

See also

How to create a custom report

Reports introduction

Queries: Contacts

How to: create a template

Fields: Report