Wiki

Case Status
Log In

Wiki

 
Microsoft Office Mail Merge Us…
  • RSS Feed

Last modified on 11/27/2013 9:59 AM by User.

Tags:

Microsoft Office Mail Merge Using OASIS

Overview

This document shows how to access the OASIS contact database from Microsoft Access and Word to perform a "mail merge" for marketing.

Installing Software

Most Microsoft Office installations are 32-bit, not 64-bit. For this reason, you must install the 32-bit version of OASIS. Also, the ASAODBC driver must be the driver kind used to connect to OASIS.

Data in OASIS

First, the contacts have to be in OASIS. However, a nice touch is to add user search tags. Once set up, the user may be found during the mail merge:

 

Creating the Access Database

We first create the access database to help identify the contacts that will be sent a message through the mail merge.

First, create the access database. Then link the tables from OASIS. To do this, click on "External Data" then "ODBC Database":

Select "Link to the data source by creating a linked table" and press "OK":

Select the tab "Machine Data Source" and then select "oasis". (If you do not see the "oasis" data source, then you are not using 32-bit OASIS, or your copy of Microsoft office is 64-bit. Another possibility is you are not using the “ASAODBC” driver kind to run OASIS, but are using “Sybase” instead.)

Press "OK".

Select the tables to link to. For this example, we will access the contact header, contact phone, contact attachment, and customer header records. Press "OK":

You will be asked to select the columns used to uniquely identify a record in the OASIS database. Highlight the columns listed for the tables as show below (the picture shows the contact attachments table):

Contact Header (CHSC_CONTACTS):

                    ·         DBID

                    ·         ContactID

Contact Phone (CHSC_CONTACT_PHONE):

                    ·         DBID

                    ·         ContactID

                    ·         Sequence

Contact Attachments (CHSC_CONTACT_ATTACHMENTS)

                    ·         DBID

                    ·         ContactID

                    ·         AttachmentID

Customer Header (CHSC_DISTRIBUTORS)

                    ·         DistributorID

Next, we need to create a query for the contact showing the phone number and customer role. To do this, select "Create" then "Query Design":

In the tables tab, select the contact header, contact phone, and customer header tables the press "Add," then press cancel.
 

Link the tables together by clicking, dragging, and then dropping the "DBID" or "ContactID" icons from one table to the next, as shown. The DistributorID should auto link.
 

Next, double click on the following columns in the related tables:

Contact Phone (CHSC_CONTACT_PHONE)

                    ·         KIND – key ‘EMail’ in the Criteria row at the bottom as shown

                    ·         PHONENUMBER

Contact Header (CHSC_CONTACTS)

                    ·         DBID

                    ·         CONTACTID

                    ·         COMPANYNAME

                    ·         TITLE

                    ·         FIRSTNAME

                    ·         MIDDLENAME

                    ·         LASTNAME

Customer Header (CHSC_DISTRIBUTORS)

                    ·         ROLE

Close the query by clicking on the "x" across from "Query 1" – click "yes" to save the query and name it "Contacts With Email".

Creating a List Query

Next, we are able to create a query to only select the contacts on our "Candy" list – the contacts in OASIS where we have selected the "Candy" check box as shown in the original screen above.

Begin by selecting "Create" then "Query Design":

This time, add the contact attachments table on the tables tab (then press add), then select the query we created above in the queries tab. Press add and then close.

Link the columns "DBID" and "ContactID" between the two boxes. 

Now, add the following columns by double clicking on them in each table:

Contact Attachments (CHSC_CONTACT_ATTACHEMNTS)

                    ·         TAGKEY – key in the name “Candy” – or the search tag name you used in OASIS

                    ·         TAGVALUE – key in the value to search for. ‘Y’ for yes/no check boxes

Contacts with Email query

                    ·         Add all of them you wish to see in the mail merge process

Click on the "x" across from the title "Query 1", and save the query with a name like "Candy List".

Close out of Microsoft Access.

Setting up the mail merge

There exists a lot of options to mail or document merge from Microsoft Office. This example just creates a form letter for everyone on the list.

First, create the document as you would like to send it out:

Next, you will want to swap out the typed text with data from OASIS. To do this, select "Mailings", then under "Select Recipients" select "Use Existing List":

Select the access database created above. You will be asked which query to use for the contact list:

Select Candy list (or the list you named previously). Press OK:

Replace the names and addresses with entries under "Insert Merge Field" at the top of the screen. Once complete, press "Preview Results" button to see what the data would look like if printed.

Example

The ZIP file below contains an example document (DOCX) and Access Database (ACCDB).

Marketing.zip