Mail Merge
  • 18 Nov 2021
  • 6 Minutes to read
  • Contributors
  • Dark
    Light
  • PDF

Mail Merge

  • Dark
    Light
  • PDF

Introduction

Mail merge allows the merging of data into a pre-formatted letter designed in Microsoft Word. The letter may reference values such as a student’s name, address, major or tracking item.

Set the Merge Folders Paths

The merge folders are where Campus Café will find the templates and output the results. This one-time step is required before using the mail merge feature.

  1. Navigate to Admin > Custom Control Maintenance 
  2. Locate the ProgramId MAILMERGE
  3. Take note of parameter 1, which specifies the path to the Word Templates, change the specific folder if desired
  4. Take note of parameter 2, which specifies the path to the outputted Word templates, change the specific folder if desired

Verify the Merge Folders Exist

This one-time step is required before using the mail merge feature and will only need to be repeated if the merge folders paths are changed.

  1. Navigate to Faculty/Staff > Notepad++
  2. Log in to the server
  3. Click File
  4. Click Open
  5. Navigate to This PC > Scanware > Merge
  6. If the folder to hold the templates does not exist, create the folder by right mouse clicking and clicking New Folder and name it the same name specified in custom control maintenance
  7. If the folder to hold the outputted templates does not exist, create the folder by right mouse clicking and clicking New Folder and name it the same name specified in custom control maintenance

Available Merge Fields

  1. Navigate to Admin > Mail Merge Data
  2. The Existing Merge Field drop down contains a list of all mergeable fields reproduced below

Table

Field Name

Web Merge Name

Web Export Name

STBIOS

SALUTATION

Salutation

Salutation

STBIOS

LAST_NAME

Last_Name

Last Name

STBIOS

FIRST_NAME

First_Name

First Name

STBIOS

MIDDLE_NAME

Middle_Name

Middle Name

STBIOS

ID_NUMBER

Id_Number

Id Number

STBIOS

SUFFIX

Suffix

Suffix

STBIOS

SOCIAL_SEC_NUMBER

SSN

SSN

STBIOS

BIRTH_DATE

Birth_Date

Birth Date

STBIOS

NICK_NAME

Nick_Name

Nick Name

STBIOS

SEX

Gender

Gender

STBIOS

CONTINUING_ED_CODE

Continuing_Ed_Code

Continuing Ed Code

Based on address type drop down selection

Address_Line_1

NONE

Based on address type drop down selection

Address_Line_2

NONE

Based on address type drop down selection

Address_Line_3

NONE

Based on address type drop down selection

Address_Line_4

NONE

Based on address type drop down selection

Address_Line_5

NONE

STBIOS

HOME_ADDRESS_ONE

Home_Street1

Home Street1

STBIOS

HOME_ADDRESS_TWO

Home_Street2

Home Street2

STBIOS

HOME_ADDRESS_THREE

Home_Street3

Home Street3

STBIOS

HOME_CITY

Home_City

Home City

STBIOS

HOME_STATE

Home_State

Home State

STBIOS

HOME_ZIP

Home_Zip

Home Zip

STBIOS

HOME_ZIP_EXTENSION

Home_Zip_Ext

Home Zip Ext

STBIOS

HOME_PROVINCE

Home_Province

Home Province

STBIOS

HOME_COUNTRY

Home_Country

Home Country

GENERIC!

Home Phone Number

Home_Phone

Home Phone

STBIOS

HOME_PHONE_AREA

Home_Phone_Area

Home Phone Area

STBIOS

HOME_PHONE_EXCHANGE

Home_Phone_Exchange

Home Phone Exchange

STBIOS

HOME_PHONE_NUMBER

Home_Phone_Num

Home Phone Num

STBIOS

BILLING_ADDRESS_ONE

Bill_Street1

Bill Street1

STBIOS

BILLING_ADDRESS_TWO

Bill_Street2

Bill Street2

STBIOS

BILLING_ADDRESS_THREE

Bill_Street3

Bill Street3

STBIOS

BILLING_CITY

Bill_City

Bill City

STBIOS

BILLING_STATE

Bill_State

Bill State

STBIOS

BILLING_ZIP

Bill_Zip

Bill Zip

STBIOS

BILLING_ZIP_EXTENSION

Bill_Zip_Ext

Bill Zip Ext

STBIOS

BILLING_PROVINCE

Bill_Province

Bill Province

STBIOS

BILLING_COUNTRY

Bill_Country

Bill Country

GENERIC!

Billing Phone Number

Bill_Phone

Bill Phone

STBIOS

BILLING_PHONE_AREA

Bill_Phone_Area

Bill Phone Area

STBIOS

BILLING_PHN_EXCHANGE

Bill_Phone_Exchange

Bill Phone Exchange

STBIOS

BILLING_PHN_NUMBER

Bill_Phone_Num

Bill Phone Num

STBIOS

MAIL_ADDRESS_ONE

Mail_Street1

Mail Street1

STBIOS

MAIL_ADDRESS_TWO

Mail_Street2

Mail Street2

STBIOS

MAIL_ADDRESS_THREE

Mail_Street3

Mail Street3

STBIOS

MAIL_CITY

Mail_City

Mail City

STBIOS

MAIL_STATE

Mail_State

Mail State

STBIOS

MAIL_ZIP

Mail_Zip

Mail Zip

STBIOS

MAIL_ZIP_CODE_EXT

Mail_Zip_Ext

Mail Zip Ext

STBIOS

MAIL_PROVINCE

Mail_Province

Mail Province

STBIOS

MAIL_COUNTRY

Mail_Country

Mail Country

GENERIC!

Mail Phone Number

Mail_Phone

Mail Phone

STBIOS

MAIL_PHONE_AREA

Mail_Phone_Area

Mail Phone Area

STBIOS

MAIL_PHN_EXCHANGE

Mail_Phone_Exchange

Mail Phone Exchange

STBIOS

MAIL_PHONE_NUMBER

Mail_Phone_Num

Mail Phone Num

STBIOS

RETURN_MAIL_CODE

Mail_Code

NONE

STBIOS

RETURN_MAIL_CODE decoded

Mail_Code_Decoded

NONE STPARM SBRMCD

GENERIC!

ALL MAJORS

Inq_Majors

Inq Majors

STBIOS

ADMISSIONS_INQ_MAJOR

Adm_Inq_Major

Adm Inq Major

STBIOS

ADMISSIONS_INQ_MAJOR decoded

Adm_Inq_Major_Decoded

Adm Inq Major Decoded

STBIOS

ADMISSIONS_INQ_MAJ_02

Adm_Inq_Major2

Adm Inq Major 2

STBIOS

ADMISSIONS_INQ_MAJ_02 decoded

Adm_Inq_Major2_Decoded

Adm Inq Major 2 Decoded

STBIOS

ADMISSIONS_INQ_MAJ_03

Adm_Inq_Major3

Adm Inq Major 3

STBIOS

ADMISSIONS_INQ_MAJ_03 decoded

Adm_Inq_Major3_Decoded

Adm Inq Major 3 Decoded

STBIOS

ADMISSIONS_INQ_MINOR

Adm_Inq_Minor

Adm Inq Minor

STBIOS

ADMISSIONS_INQ_MINOR decoded

Adm_Inq_Minor_Decoded

Adm Inq Minor Decoded

STBIOS

ADMISSIONS_ENT_SEM

Adm_Ent_Sem

Adm Ent Sem

STBIOS

ADMISSIONS_ENT_SEM

Adm_Ent_Sem_Decoded

Adm Ent Sem Decoded

STBIOS

ADM_PROGRESS_CODE

Prog_Code

Prog Code

STBIOS

ADMISSIONS_SITE

Admissions_Site

Admissions Site

STBIOS

ADMISSIONS_SITE decoded

Admissions_Site_Decoded

Not in Export STPARM SBPRCC

STBIOS

ADMISSIONS_RES_CODE

Resident_Code

Resident Code

STBIOS

TRANSFER_CODE

Transfer_Code

Transfer Code

STBIOS

HIGH_SCHOOL_CODE

High_School

High School

STBIOS

HIGH_SCHOOL_CODE decoded

High_School_Decoded

High School Decoded

STBIOS

PREV_ATTED_COLLEGE

Last_College

Last College

STBIOS

PREV_ATTED_COLLEGE    decoded

Last_College_Decoded

Last College Decoded

STBIOS

ETHNIC_CODE

Ethnic_Code

Ethnic Code

STBIOS

ETHNIC_CODE decoded

Ethnic_Code_Decoded

Ethnic Code Decoded

STBIOS

FIN_AID_CODE

Financial_Aid_Code

Financial Aid Code

STBIOS

FIN_AID_CODE decoded

Financial_Aid_Code_Decoded

Financial Aid Code Decoded

STBIOS

ADMISSIONS_PT_CODE decoded

Part_Time_Code_Decoded

Part Time Code Decoded

STBIOS

VETERAN_CODE

Veteran_Code

Veteran Code

STBIOS

VETERAN_CODE decoded

Veteran_Code_Decoded

Veteran Code Decoded

STBIOS

PRIVACY_CODE

Privacy_Code

Privacy Code

STBIOS

PRIVACY_CODE decoded

Privacy_Code_Decoded

Privacy Code Decoded

STBIOS

SITE_NUMBER

Site_Code

Site Code

STBIOS

SITE_NUMBER decoded

Site_Code_Decoded

Site Code Decoded

STBIOS

PREFERRED_ADDRESS_CD

Preferred_Address_Code

Preferred Address Code

STBIOS

PREFERRED_ADDRESS_CD decoded

Preferred_Address_Code_Decoded

Preferred Address Code Decoded

STBIOS

INTL_STUDENT_CODE

International_Student_Code

International Student Code

STBIOS

INTL_STUDENT_CODE decoded

International_Student_Code_Decoded

International Student Code Decoded

STBIOS

US_CITIZENSHIP

US_Citizenship

US Citizenship

STBIOS

US_CITIZENSHIP decoded

US_Citizenship_Decoded

US Citizenship Decoded

STBIOS

HIGH_SCHOOL_GRAD_YR

HS_Grad_Year

HS Grad Year

STBIOS

 

Advisor_1

 

STBIOS

 

Advisor_1_Name

 

STBIOS

 

Advisor_2

 

STBIOS

 

Advisor_2_Name

 

STBIOS

EMPLOYER_NAME

Employer_Name

 

STBIOS

CONTACT_FIRST_NAME

+CONTACT_LAST_NAME

Contact_Name

Contact Name 

STBIOS

EMPLOYER_ADDRESS_ONE

Employer_Address1

Employer Address1

STBIOS

EMPLOYER_ADDRESS_TWO

Employer_Address2

Employer Address2

STBIOS

EMPLOYER_CITY

Employer_City

Employer City

STBIOS

EMPLOYER_STATE

Employer_State

Employer State

STBIOS

EMPLOYER_ZIP

Employer_Zip

Employer Zip

STBIOS

EMPLOYER_COUNTRY

Employer_Country

Employer Country

STBIOS

EMPLOYER_SORT_FIELD

Employer_Sort_Field

Employer Sort Field

STPARM

SBCLOR (STPARM CODE)

Counselor_Name

Counselor Name

STPARM

SBCLOX (STPARM CODE)

Counselor_Title

Counselor Title

STPARM

SBCLOY (STPARM CODE)

Counselor_Email

Counselor Email

STPARM

SBCLOZ (STPARM CODE)

Counselor_Phone_Number

Counselor Phone Number

STPARM

SBCLOG (STPARM CODE)

Counselor_Gender

Counselor Gender

ADMAST

APPLIC_DATE

App_Date

App Date

NONE

App date decoded (does not exist?)

None

None

ADMAST

APPLIED_FOR_MAJOR

Applied_For_Major

Applied For Major

ADMAST

APPLIED_FOR_MAJOR decoded

Applied_For_Major_Decoded

Applied For Major Decoded

ADMAST

ACCEPT_MAJOR

Accept_Major

Accept Major

ADMAST

ACCEPT_MAJOR decoded

Accept_Major_Decoded

Accept Major Decoded

ADMAST

DECISION_DATE

Decision_Date

Decision Date

ADMAST

DECISION_DATE decoded

Decision_Date_Decoded

Decision Date Decoded

ADMAST

SCHOOL_DECISION

School_Decision

None

ADMAST 

SCHOOL_DECISION decoded

School_Decision_Decoded

None (STPARM ACDECD)

ADMAST

COMMITMENT_DATE

Commitment_Date

Commitment Date

ADMAST

COMMITMENT_DATE decoded

Commitment_Date_Decoded

Commitment Date Decoded

ADMAST

DEPOSIT_DATE

Deposit_Date

Decision Date

ADMAST

DEPOSIT_DATE decoded

Deposit_Date_Decoded

Deposit Date Decoded

ADMAST

DATE_ACCEPTED

Date_Accepted

Date Accepted

ADMAST

DATE_ACCEPTED decoded

Date_Accepted_Decoded

Date Accepted Decoded

ADMAST

DATE_REPLIED

Date_Replied

Date Replied

ADMAST

DATE_REPLIED decoded

Date_Replied_Decoded

Date Replied Decoded

ADMAST

APP_AMOUNT

App_Amount

App Amount

ADMAST

TUITION_DEP_AMT

Tuition_Dep_Amount

Tuition Dep Amount

ADMAST

HOUSING_DEP_AMT

Housing_Dep_Amount

Housing Dep Amount

ADMAST

APP_COMPLETED

App_Completed

App Completed

ADMAST

SCHOOL_DIV_APP

School_Div_APP

School Div APP

ADMAST

SCHOOL_DIV_ACC

School_Div_ACC

School Div ACC

ADMAST

ACTION_TYPE

Action_Type

Action Type

ADMAST

ACTION_TYPE decoded

Action_Type_Decoded

Action Type Decoded

ADMAST

COND_ACC 

Cond_ACC

Cond ACC

ADMAST

COND_ACC  decoded

Cond_ACC_Decoded

Cond ACC Decoded

SYUSER

USERNAME

Username

Username

SYUSER

PASSWORD

Password

Password

SYUSER

LOGIN_COUNT

Login_Count

Login Count

SYUSER

LAST_LOGIN_DATE

Last_Login_Date

Last Login Date

SYUSER

LAST_LOGIN_DATE decoded

Last_Login_Date_Decoded

Last Login Date Decoded

SYUSER

MAINTENANCE_PWD

Maintenance_Password

None

ADDTRK

PLEDGED_AMOUNT

Amount_Original

Amount_Original

ADDTRK

AMOUNT_DUE

Amount_Due

Amount_Due

ADDTRK

PAID_AMOUNT

Amount_Paid

Amount_Paid

ADDTRK

FIRST_COMMENT

Comment_1

Comment_1

ADDTRK

SECOND_COMMENT

Comment_2

Comment_2

ADDTRK

THIRD_COMMENT

Comment_3

Comment_3

ADDTRK

FOURTH_COMMENT

Comment_4

Comment_4

ADDTRK

FIFTH_COMMENT

Comment_5

Comment_5

ADDTRK

SIXTH_COMMENT

Comment_6

Comment_6

ADDTRK

SEVENTH_COMMENT

Comment_7

Comment_7

ADDTRK

EIGHT_COMMENT

Comment_8

Comment_8

ADDTRK

FIRST_REQ_DATE

Request_Date

 

STSALU

FORMAL_CODE

Formal_Code

Formal_Code

STSALU

FORMAL_SAL

Formal_Sal

Formal_Sal

STSALU

FORMAL_DEAR

Formal_Dear

Formal_Dear

STSALU

INFORMAL_CODE

Informal_Code

Informal_Code

STSALU

INFORMAL_SAL

Informal_Sal

Informal_Sal

STSALU

INFORMAL_DEAR

Informal_Dear

Informal_Dear

STSALU

ALUMNI_CODE

Alumni_Code

Alumni_Code

STSALU

ALUMNI_SAL_1

Alumni_Sal_1

Alumni_Sal_1

STSALU

ALUMNI_SAL_2

Alumni_Sal_2

Alumni_Sal_2

STSALU

ALUMNI_DEAR

Alumni_Dear

Alumni_Dear

STSALU

VERY_INFORMAL_CODE

Very_Informal_Code

Very_Informal_Code

STSALU

VERY_INFORMAL_SAL

Very_Informal_Sal

Very_Informal_Sal

STSALU

VERY_INFORMAL_DEAR

Very_Informal_Dear

Very_Informal_Dear

STSALU

CUSTOM_CODE

Custom_Code

Custom_Code

STSALU

CUSTOM_SAL

Custom_Sal

Custom_Sal

STSALU

CUSTOM_DEAR

Custom_Dear

Custom_Dear

STSALU

CUSTOM_CODE_2

Custom_Code_2

 

STSALU

CUSTOM_SAL_2

Custom_Sal_2

 

STSALU

CUSTOM_DEAR_2

Custom_Dear_2

 

STSALU

RECOGNITION_SAL

Recognition_Sal

 

STSALU

Based on Salutation Selection

Formatted_Sal

NONE

STSALU

Based on Salutation Selection

Formatted_Dear

NONE

ALFUND

FUND_DESCRIPTION_1

Fund_Description

NONE

ALDONA

NON_DEDUCT_AMT

Non_Deductible_Amount

NONE

ALDONA

COMMENT

Gift_Comment_1

NONE

ALDONA

COMMENT_TWO

Gift_Comment_2

NONE

ALDONA

AMOUNT_PAID

Paid_To_Date

NONE

ALDONA

APPEAL

Appeal_Code

 

 

 

Appeal_Code_Decoded

 

ALDONA

CAMPAIGN

Campaign_Code

 

 

 

Campaign_Code_Decoded

 

ALDONA

IN_MEMORIAM_CONS_NUM

In_Memoriam_Cons_Num

 

 

 

In_Memoriam_Name

 

ALDONA

GIFT_PURPOSE

Gift_Purpose

 

ALDONA

MATCH_GIFT_CONS_NUM

MG_Constituent_Number

 

 

 

MG_Constituent_Name

 

ALDONA

PRIMARY_CONS

Primary_Cons_Code

 

 

 

Primary_Cons_Code_Decoded

 

ALDONA

REQUEST_DATE

Request_Date

 

TRK

COURSE_NUMBER / COURSE_SECTION

Course_And_Section

 

TRK

CURRENT_SEMESTER

Course_Semester

 

TRK

REG_SYLLABUS_OID

Syllabus_Decoded

 

RGCRSE

PROF_ID_NUMBER (Name)

Instructor

 

RGCRSE

BEGIN_DATE

Course_Start_Date

 

RGCRSE

END_DATE

Course_End_Date

 

RGCRSE

COURSE_TITLE

Course_Title

 

RGENRL

ENROLLMENT_DATE

Enrollment_Start

NONE

RGENRL

ENROLLMENT_END_DATE

Enrollment_End

NONE

RGENRL

LAST_ATTEND_DATE

Last_Attend_Date

NONE

NONE

Will output a ‘$’ without quotes

Dollar_Sign

NONE

TRK

DUE_DATE

Due_Date

NONE

TRK

SUBMITTED_DATE

Submitted_Date

NONE

TRK

COMPLETED_DATE

Completed_Date

NONE

TRK

DUE_TIME

Due_Time

NONE

TRK

SUBMITTED_TIME

Submitted_Time

NONE

RGDEGR

DEGREE_MAJOR

Degree_Major

NONE

RGDEGR

DEGREE_MAJOR Decoded

Degree_Major_Decoded

NONE

When using the school finder sub-module – use the address_line_x solution when a formatted address is needed

ADSCHL

SCHOOL_NAME

Name

No Export on School Finder

ADSCHL

ALPHA_SORT_FIELD

Sort_Name

No Export on School Finder

ADSCHL

SCHOOL_CODE

CEEB

No Export on School Finder

ADSCHL

SCHOOL_SUFFIX

Suffix

No Export on School Finder

ADSCHL

ADDRESS_ONE

Street1 (use Address_Line_X)

No Export on School Finder

ADSCHL

ADDRESS_TWO

Street2 (use Address_Line_X)

No Export on School Finder

ADSCHL

ADDRESS_THREE

Street3 (use Address_Line_X)

No Export on School Finder

ADSCHL

CITY

City (use Address_Line_X)

No Export on School Finder

ADSCHL

STATE

State (use Address_Line_X)

No Export on School Finder

ADSCHL

ZIP

Zip (use Address_Line_X)

No Export on School Finder

ADSCHL

ZIP_EXTENSION

Zip_Ext (use Address_Line_X)

No Export on School Finder

ADSCHL

COUNTRY

Country (use Address_Line_X)

No Export on School Finder

ADSCHL

PROVINCE

Province (use Address_Line_X)

No Export on School Finder

ADSCHL

AREA_CODE+EXCHANGE+PHONE

Phone

No Export on School Finder

ADSCHL

CONTACT_FIRST_NAME

First_Name*

No Export on School Finder

ADSCHL

CONTACT_MID_NAME

Middle_Name*

No Export on School Finder

ADSCHL

CONTACT_LAST_NAME

Last_Name*

No Export on School Finder

ADSCHL

CONTACT_TITLE

Title*

No Export on School Finder

ADSCHL

EMAIL_ADDRESS

Email*

No Export on School Finder

View AR_BALANCE_WITH_AID

AMOUNT_DUE

Amount_Due_AR_Bal

None

View

AR_BALANCE_WITH_AID

TOTAL_BALANCE

Actual_Cash_Balance

None

*This data is associated with the contact information of the school.  For example, a school counselor or administrator

Address Fields

Address_Line_1:5 are special merge fields that will pull in and format correctly the address lines from the address type selected.  This is the recommended best practice.  Other address fields such as Home_Address, Mail_Address etc. should only be used in special cases where the letter must be hard coded to always use that specific address.  An example of how the letter should look would be like this:

First_Name Last_Name
Address_Line_1
Address_Line_2
Address_Line_3
Adderss_Line_4

This method will properly space the address lines based on whether or not the address line exists.  For example if the country is stored on the prospect or student record, the output would look like this:

Jane Smith
500 Generic Street
Any City, MA 02142
Unites States

 Address_Line_2 in this example would automatically collapse the space because there is not data (Address_Line_4 is typically the country code)

Conditional Output

You may optionally configure the system to output a defined value if a field holds a specific value.  For example, if the first name equals Jim, you may wish to replace "Chris" with "My favorite first name."

  1. Navigate to Admin > Mail Merge Data
  2. In the Conditional Merge Field, enter a description of the conditional rule, for example, Name Change
  3. In the Existing Merge Field drop down, choose the field desired, for example, First_Name
  4. In the operator choose the logical value, for example, =
  5. In the Value box enter the value to replace, for example, Chris
  6.  In the To Display box enter the desired replacement text, for example, My favorite first name
  7. Click Add

Insert Merge Fields into Word Template

Depending on your version of Word and your Word configuration, the directions may vary.

  1. Open Microsoft Word
  2. Click Insert on the top ribbon
  3. Click Quick Parts
  4. Click Field
  5. In the Field names box, click MergeField
  6. In the Field name box enter the Campus Cafe merge field in proper case. For example, for the first name enter First_Name
    See list of fields above
  7. Click OK
  8. Repeat for each merge field. Do not copy and paste merge fields. You must insert them through the menu each time.

Available Merge Fields

Save the Word Template

  1. Click File on the top ribbon
  2. Click Save As
  3. Give the document a name that must be all uppercase. This will be the name of the template in Campus Café
  4. Ensure the File Type is .docx
  5. Click Save

 Upload the Word Template

  1. Navigate to Faculty/Staff > Notepad++
  2. Log in to the server
  3. Click File
  4. Click Open
  5. Navigate to This PC > Scanware > Merge
  6. If the folder to hold the templates does not exist, create the folder
  7. Paste the Word Template in the directory
  8. In the merge folder, also create a folder to hold the output

Create Activity Tracking Template

  1. Navigate to All Users > Activity Tracking Templates
  2. In the Member box, select a member associated with the function of the template
  3. In the Template Name box enter exactly the name of the Word template
  4. Click Add
  5. In the Description box enter a description of the template
  6. Click Save
  7. Under all All Template WorkFlow Listing, under Work Flow Edit click the workflow icon next to the workflow 
  8. In the Type choose Document
  9. In the Category choose Document
  10. Under Options next to Is Word Mergeable check the box
  11. Click Save

Initiate the Merge

Permissions #953 and #954 are required for this function. The user will also need access to the activity tracking member.

  1. Navigate to the Student Finder
  2. Set your criteria to return the population to merge
  3. Click + Add Activity
  4. Select the Template
  5. Next to Proceed to Merge check the box
  6. Click Submit
  7. A new screen will appear
  8. In the Destination Type, choose Send to New Document
  9. In the bottom right click Submit
  10. Your browser will prompt you to open or download the document

Was this article helpful?

What's Next