Send Reminder Emails through MS Outlook to appointment items in MS Outlook

In office settings, efficiency is always a high priority. Within the Academic Advisement Center for the College of Family, Home, and Social Sciences at Brigham Young University, efficiency is in short supply. When I began working in the Advisement Center nearly a year ago, I immediately recognized that there were several processes that were extremely tedious and repetitive. Our office is utilized mostly by students who need assistance in scheduling classes for future semesters and who are looking for guidance in their career decisions. We have seven different academic advisors for students to meet with, depending upon their declared major or minor.

      One of the most inefficient processes was that of sending emails to remind the students of their upcoming appointment in our office. The process to send just one email was long and tedious. A student staff member in the office would click on an appointment in Microsoft Outlook and copy the student’s BYU ID number. With that ID number, that staff member would open a Web browser and sign in to their account on BYU’s website. After navigating through a few pages, the copied ID number could be inserted into a text box on the page and, after submitting that page, the student’s email address could be obtained. And finally, a new email from MS Outlook could be created and sent to the student reminding them of the appointment that they have made in our office. The email that was sent was identical for each student, just saved as an email signature to help the process move just a little quicker.

      With this project, I have been able to automate that entire process, using MS Excel to do all of the grunt work. With the click of a button now, the calendar appointments for a specified day will be imported to Excel and the student’s BYU ID number will be extracted automatically. An Internet browser will pop up and prompt the user to sign in to BYU’s website and then will retrieve the email address of each student who has an appointment on the specified day. After collecting the email addresses, MS Excel will call on MS Outlook to send a personalized reminder email to each student, addressing the student by name and including the appointment date and time. What used to take a staff member nearly two hours per day to complete can now be done in less than two minutes.

Write-Up Report and Excel File: 

• http://files.gove.net/shares/files/15w/mlvaughn/Final_VBA_Report.pdf
• http://files.gove.net/shares/files/15w/mlvaughn/AutomateOutlookEmail.xlsm

*Unfortunately, this code will only work for BYU employees with special access to student information. That being said, all parts of the code will work with your Outlook account except the "getEmails" sub procedure.