Mail Merge

1 About Mail Merge
2 Available Merge Fields
3 Address Fields
4 Insert Merge Fields into Word Template
- 4.1 Available Merge Fields
5 Save the Word Template
6 Upload the Word Template
7 Create Activity Tracking Template
8 Initiate the Merge
9 Conditional Output
10 Set the Merge Folders Paths
11 Verify the Merge Folders Exist

About Mail Merge

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.

Available Merge Fields

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

Table	Field Name	Word Merge Field Name	Export Spreadsheet Field 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

TypeScript

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)

Insert Merge Fields into Word Template

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

Open Microsoft Word.
Click Insert on the top ribbon.
Click Quick Parts.
Click Field.
In the Field names box, click MergeField.
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
Click OK.
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

Click File on the top ribbon.
Click Save As.
Give the document a name that must be all uppercase. This will be the name of the template in Campus Cafe.
Ensure the File Type is .docx.
Click Save.

Upload the Word Template

Navigate to Admin > Appearance & Student Photos.
Scroll down to the Mail Merge Templates section.
Click on Choose Files and select your MS Word document from your machine.
Click the Upload templates button.

Create Activity Tracking Template

Navigate to All Users > Activity Tracking Templates.
In the Member box, select a member associated with the function of the template.
In the Template Name box, enter exactly the name of the Word template. This name needs to match the filename you saved (not including the .docx extension). In the picture above, we uploaded a file named BILLING_MM, therefore that will be the name of our template.
Click the Add button.
In the Description box, enter a description of the template.
Then, click Save.
Scroll down to the Added Workflows section and click the Workflow Edit icon next to the workflow.
In the Type drop-down, select Document.
In the Category drop-down, select Document.
Under Options, check the box next to Is Word Mergeable.
Then, click the Save button.

Initiate the Merge

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

Navigate to Student > Student Finder.
Set your criteria to return the population to merge.
Click the + Add Activity button and select For All if you want to include all students in the results in the mail merge. If you select For Page, you will only include those students on the current page in the mail merge.
Select the Template using the magnifying glass icon.
Next, make sure to check the box for Proceed to Merge.
Then, click the Submit button.
You will be prompted to confirm you want to add tracking for these students. Click Yes.
A new screen will appear. You will see the mail merge template document you uploaded in the gray area.
In the Destination Type, make sure to select Send to New Document.
In the bottom right, then click Submit.
Your browser will prompt you to open or download the document. It will appear in your downloads directory unless you save it elsewhere. Each student will be displayed as a separate page and your merged fields will be populated with the data from Campus Cafe.

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."

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

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.

Navigate to Admin > Custom Control
Locate the ProgramId MAILMERGE
Take note of parameter 1, which specifies the path to the Word Templates, change the specific folder if desired
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.

Navigate to Faculty/Staff > Notepad++
Log in to the server
Click File
Click Open
Navigate to This PC > Scanware > Merge
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
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