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