These are projects posted by the students of Dr. Gove Allen at Brigham Young University. These students have taken one semester-long course on VBA and generally have had no prior programming experience

Thursday, December 11, 2014

Mail & Label Merge Program

Executive Summary

Business Description: I work as an academic advisor for the Honors Program at BYU. The Honors Program is an academic program (similar to a minor) which has a set of core requirements students must complete in order to graduate with the honorary distinction of “with University Honors” on their diploma. These requirements include curriculum (courses), experiential learning (service/study abroad), academic research (Honors Thesis), etc. We academic advisors are responsible for advising students in our program as well as maintaining student program records, helping with program events and logistical office tasks, etc.

Some of these tasks involve printing labels for mailing out targeted program mailings, printing name tags for event guests, etc. Several of us in the office (including my boss and other full-time administrative staff persons) have tried and failed to use Microsoft Word’s mail merge program to print labels from Excel/CVS data. In turn, a down side to using Microsoft Word’s version is that we also cannot see or edit the filled labels before they print since Word’s mail merge sends it directly to the printer after you have connected data fields to the template variables. As a result, many people in our office have ended up burning time manually typing the labels by hand because it was taking equally as long or longer to figure out how to use the mail merge.

To address this issue, I created my own mail merge program using VBA. My mail merge system creates templates for mailing address labels, return address labels, and name tag labels and fills these templates with data provided by the user. Once filled with data, users can also view and edit the labels before printing, the handy features Word’s mail merge did not have.

System Overview: The system is divided into four tasks facilitated by the four buttons on the “Mail Merge” tab of the ribbon. Users are guided by detailed instructions and cautions provided in the home Instructions sheet and in forms/sheets throughout the process. The four main tasks are detailed as follows:

(1)    CREATE TEMPLATE: First, users click the CREATE TEMPLATE button to create a blank template and a blank data sheet for the type of labels they want to make. The ribbon button initiates a form which asks the user what type of labels they want to make. All the user has to do is select the type of labels (mailing, return, or name tag) and press the create button and VBA creates a label template and data sheet with instructions for preparing data for that particular template.

(2)    PREPARE DATA: Next, users will populate their blank data sheet with data. There are a few ways they can do this, depending on their source data. If their data is in the form of a CSV file, users click the IMPORT CSV FILE button on the ribbon and a subprogram has them manually select their CSV file using the dialog box and then imports the CSV file data into a newly created “CSV File Data” spreadsheet next to the template and data sheet in the workbook. Users can then manually clean/organize the CSV data to the label data sheet specifications and manually copy/paste the scrubbed data over. There are obviously several other ways users may obtain/prepare data for label filling (e.g. manually entering if the list is short, opening another non-CSV file in a separate workbook and copy/paste data, etc.), but importing a CSV file seemed the most common for large batches of labels.

(3)    FILL TEMPLATE: Once the data is ready, users click the FILL TEMPLATE button, which prompts form with which users select the labels they would like to fill and press the button to continue. Assuming the data was prepared correctly, VBA then fills the blank template with the data sheet info. From here, users can view all sheets of labels, make edits (change fonts, alignment, etc.).

(4)    PRINT:  When the labels are ready to be printed, users click the PRINT LABELS button on the ribbon. Contrary to its name, this button does NOT send the labels to the printer. I’ve done this intentionally so that users (thinking the button prints labels) actually read the PRINTING INSTRUCTIONS before printing. Clicking on this button prompts a form which explains that the label templates are generic and that due to slight differences in printers and versions of excel, minor adjustments to column widths or row heights may be necessary to correctly center the text on the labels when printed. Users then hit a button on the form which prompts VBA to create a PRINTING INSTRUCTIONS spreadsheet with detailed notes on preparing the template for printing and printing the labels themselves.

Not only is this program useful for office use, but also for personal use (e.g. wedding invitations, Christmas cards, etc.).

Here are the file links below for the project deliverables, including a sample CSV file of fake addresses for ease of testing the processes when grading the project (TAs) or figuring out how it works (Users):

No comments:

Post a Comment

Blog Archive