Automated Reminder Email

 

A gent named Joni Räsänen contacted me on Twitter with a question/request. Here’s Joni’s tweet.

@TechnicLee Would be possibe to build script to automatically send meeting memo template by email before appointment starts?

— Joni Räsänen (@rasanenj) December 15, 2013

The solution to Joni’s request is to use Outlook’s built-in appointment reminders to trigger sending the email. To do this, we’ll trap the event that fires each time a reminder goes off. The code will check to see if the item that triggered the reminder is an appointment and if it’s a member of a certain category. Checking the category will allow Joni to set reminders on any appointment, including those he doesn’t want an email sent for. If the appointment is a member of the designated category, then the code will create and send a predefined message. If the appointment is not a member of the designated category, then the code will exit without doing anything.

Advertisement

Vacation Reminders

 

A reader named Jasmine contacted me last week with an interesting request. Jasmine’s office maintains a shared Outlook calendar used to keep track of vacations. They’d like the ability to send an employee an email a few days before they go on vacation reminding them to change their voice-mail message. Each vacation entry consists of an appointment on the shared calendar. The subject of each appointment uses the form “XXX vacation” where “XXX” is the employee’s initials. Jasmine asked if I could build something that would do this automatically. Here is the solution I put together for her.

I wrote the solution in VBScript so it can run from a scheduled task (i.e. a task in Windows Task Scheduler). When the task executes it will run the script. The script will open Outlook, connect to the shared calendar, retrieve all the appointments for the day X days from now (Jasmine will set the value of X), look through those appointments to find the vacation entries (it identifies them by the word “vacation” appearing in the subject), then create and send a reminder to each vacation entry it found. Because Jasmine’s office uses initials to identify each employee, the script uses a file Jasmine will build to match an employee’s initials to their email address.

Exporting Appointments from Outlook to Excel

I put this solution together for Allen, who had seen my post demonstrating how to export Outlook messages to Excel and asked if I could create a similar macro to export appointments. Allen went on to explain that his organization uses a shared calendar and that periodically he would like to export its contents to Excel. For each appointment, Allen wants to export the organizer (i.e. the person that added the appointment to the calendar), when the appointment was added, the appointment’s subject, when it starts, and who was invited. To make it a little easier for Allen, I broke “who was invited” down into two groups: required and optional attendees. The solution itself is nearly identical to the one I used to export messages.