Appointment Reminder Emails Using Gmail and Google Sheets

The Google version of emailing appointment reminders

Preamble:

There have been other options already described for generating email appointment reminders for patients. While these are not fully automated solutions in OSCAR, a simple process that can be easily performed by an MOA on a daily or weekly basis will help to keep down the "no-shows". Keep in mind that there are certain legal and privacy issues that have to be considered prior to making a policy decision to contact patients by email. Please review the CMPA position on the subject and consider using the CMPA's email consent form for patients prior to enrolling them in this solution.

Setup:

Import this database report template into your OSCAR system. This report template will search your appointment database (for example, all of the appointments scheduled for tomorrow) for patients who have email addresses registered in their demographic master files. You can specify the date range of the report (e.g. you can generate a list of emails for tomorrows appointments, or for the whole of next week or next month - be aware though that Gmail limits you to 50 emails per day).  To add the report to your OSCAR system, you first have to save the report text file to your computer. Then pick it up with your OSCAR. Go to Admin > Report by Template > Add New Template (left bottom) > browse to the file and upload. For a basic tutorial on how to run 'Report by Template' database searches in OSCAR, click here.

You will also need:

1) A Gmail account - you can decide if you wish to have a separate one for these emails - it may help to be able to mail out more reminders

2) The Chrome web browser installed on your computer (need to use a chrome app)

3) (optional) A desktop link directly to Google Sheets running in Chrome. Open up Chrome. Log in to your Gmail account (or Google). Search for google sheets and open in up. Then go to the settings menu in Chrome (the three dots at the top right of the browser),


 More Tools, and Add to desktop

4) The following Chrome App - Mail Merge with Attachments - found here:

https://chrome.google.com/webstore/detail/mail-merge-with-attachmen/nifmcbjailaccmombpjjpijjbfoicppp

must be opened with Chrome and installed


5)  A draft in your gmail account that could look something like this (Double curly brackets allow for individualization of the email with values from your table):

Subject: Doctor's appointment for {{Firstname}} {{Lastname}}

Body:

This is a friendly reminder of your upcoming appointment with Dr. {{Doctor}} On {{Date}}(dd/mm/yyyy) at {{Time}}. We hope to see you as scheduled. If however you can't make it, please call us today at (555) 555 5555 to cancel or to re-book, or simply reply by email to MedicalClinic@ouremail.com. Please note that we have a policy not to discuss any specific medical information over open email due to privacy concerns, and that our phone is answered during office hours. Please also note that we do charge a modest fee for missed appointments, in order to recover our costs. If you have any further questions please call the clinic.

Thank you, MOA, Medical Clinic

PS: If you have received this message in error, please notify us immediately by email or by telephone

You can edit this inside Gmail to include high lights or changes in the color of the text - click on the underlined A at the bottom beside "send" to change the formatting.  Remember - this stays saved in your draft emails.  Check how your doctors are listed in your table - if they have their billing number in with their name, you may not wish to use that tag, you could just say "your doctor" instead.

Then run the report - see below:

Operation:

• Step 1: Run the report (pick the date range and click) to generate the list of email addresses for patients with upcoming appointments.  The screenshot below shows a completed report. Two patients on the specified date have email addresses registered in their demographic master files. Export the output as csv (comma separated values) to your workstation computer. (click "Export to CSV", and save to your hard drive).

• Step 2 Double click on the google sheets icon on the desktop - green square with white lines - sign in to your account.
• Step 3 Below the black bar on the line that says "Recent Spreadsheets" and "Owned by Anyone" click on the folder icon (Open file picker) - Click on Upload - Click on select a file from your computer - Navigate to your file and select it (usually named oscarReport.cvs)
• 
• Step 4 At the bottom where the tab is named oscarReport click the down arrow and choose Rename ...  -  Rename the tab to "Mail Merge" (without the quotations and case sensitive)
  
• Step 5 Click on Add-ons - Mail Merge with Attachments - Create Mail Merge Template - Allow it to run
• Step 6 Click on Add-ons - Mail Merge with Attachments (again) - only this time click on - Configure Mail Merge
• Step 7 Set up the various fields as you see fit - which email address to respond to, how you want the sender name to appear etc.
  
• Step 7 Set up the various fields as you see fit - which email address to respond to, how you want the sender name to appear etc.
  
• Step 8 Click on Continue and select the draft email that you created earlier (look for the curly brackets). It should highlight blue, then click on "Run Mail Merge"
  
• Step 9 Remember that you can only send 50 messages per day. Fortunately if you are over, you can simply click "Run Mail Merge" again the next day and send the rest of them.  You can also delay the send, so if you wish to send more on Saturday or Sunday to catch up, you can.  The video that comes with the app is worth a watch.
  
• 
   
  

Repeat daily as necessary.

Notes:

in order for your patients to show up in the list, you of course have to first register their email addresses in relevant field in their demographic master file. Patients without a registered email address don't show up in the report. 

Earl mentioned that, depending on how many appointment reminders you are sending out, your bulk emails might be blocked by your Internet service provider's spam filter. Check with your ISP if you come across this and have them adjust the limit on the number of outgoing emails per hour in your account.

As people vary in the frequency that they check their emails it is suggested that you run the report weekly for the following weeks appointments.

This method has been tested using OSCAR 12.1, and Chrome 55.  This presentation shamelessly cribbed from Günther Klein

Compiled by John Robertson 31-Dec-2016. If you get stuck, please let me know on the OSCAR email discussion list.