Executive Summary

Sending emails. Yes, we send lots of emails on a consistent basis at the Alumni Records Office. The question that kept coming to mind is how can I improve something at the office? After we learned in class how to automate emails using Gmail, I thought that I could apply that knowledge to making a better system for sending lots of automated emails where I work. That way, we would spend less time typing or copying and pasting from a template and more time actually updating the records we keep track of.

Company Background

What does the Alumni Records Office do you ask? Simply stated, we keep track of all the alumni that attend and graduate BYU as well as all the students, donors, etc. Four times a year we send out the BYU Alumni Magazine to all of the graduates from BYU. Our office is where all the contact information is stored that the magazine uses to send. After we send out each magazine, we tend to get hundreds if not thousands of magazines returned to us by the post office. These magazines are put into categories based on whether they come with a new forwarding address stamped on it, if we need to do research because it says it was undeliverable, or if the magazine should have been returned to us in the first place. Sometimes the magazine was sent to the right place, but the post office still sends it back to us. Historically, we have manually sorted these magazines into different bins and then other employees in the office can come and grab a stack to work through during their shift.

Problem

One of the main problems that I have seen while working here is the fact that a lot of times these bins get extremely full. It takes a while to get through all the magazines and often times we don’t work through all of them before we start getting the next issue of the magazine returned to us. For this past magazine we started writing down all the information on the magazine into a spreadsheet. This includes a unique id that allows us to look up that person on our internal database. It also comes with the first name, middle initial, last name, and address of the person we sent the magazine to. If it’s one of the magazines with a forwarding address, it would also have a new address on the magazine. However, most of the returns we get are magazines that are undeliverable because that person has moved.

We spend a lot of time trying to get in touch with these graduates to make sure we can update their address so that the next issue of the magazine will go to the right place. We’ve found that the most effective way to reach out to the alumni is through email, but it takes time. Each employee has access to a template of what they should write stored on their computer. That way, they can copy and paste it over to Outlook and just change the name of the person and include more specific and personal information for that person. This still takes a lot of time and as mentioned before, we might not have enough time to email everyone and get responses back in time before the next issue is released.

Solution

For my final VBA project, I chose to create a system that would add data onto the Excel spreadsheet that we started for this past issue of the magazine. I developed a user form to make the data easier to read. This user form can also be used to insert new records or to change an existing record on the system. The next part of my project was figuring out how to send a mass email through Microsoft Outlook because that’s the mailing system we use at our office. I was able to figure out how to automate Outlook and send emails from a list. At first, I tried to figure out a way to use Microsoft Word’s Mail Merge feature, but found that it’s possible to automate a similar process using VBA in Excel. Talking to Professor Allen, I realized that my project was still a little bit light in scope. He suggested if there was a way to access the data we were manually writing into the spreadsheet that I should automate retrieving that data and importing it into our spreadsheet database. I was given access to the MySQL server and was told all the data I would need to query was stored there. However, I soon learned that the data is actually stored somewhere else. To further complicate this problem, I was told that I wouldn’t be given access to where the information is actually stored because of tightened security and policies.

The way I was able to solve the problem was to have my manager export a list from our database using the unique id called the Scrambled ID. The Alumni Records Office employees can use my program to choose the file they want to import into our Excel spreadsheet and then they won’t have to manually type all of that information into the spreadsheet. My VBA code will do the magic necessary to give the Records employees more time to focus on the records without emails.