Generate appointment reminder emails!

Email your patients to remind them of upcoming appointments!

Page Index:

• Basic setup for email messages
  
• Mail Merge with LibreOffice
• SMS text messages
• Automated solution for SMS text messages
  

Preamble:

Earl Wertheimer shared his solution for generating email appointment reminders for patients. While this is not a fully automated solution in OSCAR, it is a simple two step operation that can be easily performed by an MOA on a daily or weekly basis. 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).  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.

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 (fancy): Generate a mass email reminder. There are two ways to do this. The fancy way is to set up a proper mail-merge operation. How exactly that works depends on the office software you use, e.g. LibreOffice / Open Office, MS Office , Apple iWork, even Google Docs, etc. Peter HC compiled a detailed step by step walk-through for mail-merge using LibreOffice at the end of this post. Doing the fancy mail-merge will allow you to address each patient by name and insert the date and time of the relevant appointment as well as the name of the doctor into the body of the message. You have to only set this up initially, and then subsequent email reminder batches can be run through very quickly without further setup.
  
• Step 2 (easy): For people like me with short attention spans, the quick & dirty way is to simply create a generic reminder email message in your favourite email software (you can save one as a draft and then keep making new copies of it). Then, to add the email addresses, simply open that csv file you saved from OSCAR with any spreadsheet program (Excel, LibreOffice Calc, Numbers etc.), and then copy and paste the column containing the email addresses from the spreadsheet into the 'Bcc' field of your email reminder message (TAKE CARE not to paste that into the 'To' or the 'Cc' field, if you don't want your patients to all see each others email addresses - privacy issues!). Click send
   
  

Repeat daily as necessary. Suggested email message:

Dear Valued Patient

This is a friendly reminder of your upcoming appointment at the medical clinic tomorrow. 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

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, Firefox 17 ESR and Firefox 20, Thunderbird 17 and for the mail merge with both Open Office 3.2.0 on Ubuntu 10.04LTS as well as LibreOffice 4.0.2 running on an Ubuntu 12.04 desktop connecting to postfix and to a local ISP.  Thanks Earl for building this for us, and Peter HC for providing the tweaks and the nice mail-merge tutorial below. Gunther

How-to setup mail merge to email with LibreOffice

This is for users of LibreOffice, a free, open-source office suite available for Windows, Mac and Linux users. Open Office is very similar. If you want to set up a mail merge to email using MS Office instead, then refer to Microsoft's help documentation here.

• Step 0:  Download and install the latest complete version of the LibreOffice suite from the official Document Foundation website here.
  
• Ubuntu users please note:  While Ubuntu includes the LibreOffice suite by default, the distro install has been pared down to save space and is missing a key component of the full suite, namely the database module, LibreOffice Base, and so will not let you mail merge by default. So you need to install the full version of LibreOffice by opening Ubuntu Software Center, search for LibreOffice, then click install. Or download the .deb from the above site, or via command line: sudo apt-get install libreoffice

• Step 1: Run the report in OSCAR (pick the date range and click) to generate the list of email addresses for patients with upcoming appointments. Export the output as csv (comma separated values) to your workstation computer. (click "Export to CSV", and save to your hard drive)

• Step 2: Set up your LibreOffice Writer to mail-merge to your email address: Open LibreOffice and navigate to Tools > Options > expand the LibreOffice Writer options > click Mail Merge E-mail

Click the "Server Authentication" button and enter those settings. You might have to take a peek at your account setup in your email software and copy the settings from there, or call your Internet service provider if you're not sure.  Use the Test Settings button to ensure they are correct.

• Step 3: Now go ahead and compose your patient reminder letter in LibreOffice Writer. Then click Edit then Exchange Database… Then click Browse.  Select the file you just made and it will show up as an Available Database
  

• Step 4: Click View > Data sources (or press F4)

• Step 5: On the left is a list of data sources. These should include the file you just made. Select it, then Tables then oscarReport so that the data appears on the right

• Step 6: You will see the data from the spreadsheet. Click and drag the column you want into your document. For example if I wanted First Name, I would click and drag the First Name column title and not an individual cell like STUART. You will notice it appears gray in your document. You are free to cut and paste it or change the formatting. These words will be replaced with the data in your spreadsheet.  Below we have just dragged <<Time>> into the Office template
  

• Step 7: Click Tools, Mail Merge Wizard or click the envelope icon in data sources.

1. Select starting Document: Select current document.
2. Select document type: In this example we intend to email the letters so select E-mail message.
3. Click past Insert address block:
   
4. Click past Create salutation:
5. Click past Adjust Layout, its not relevant for E-Mail
6. Edit document: By clicking the left and right arrow we can preview each page.
7. Click through Personalize document: This screen shows us the finished document which is editable.
8. Save, print or send: What do you want to do this the finished document? Save starting document will save your template for later use (which you want).  Send merged document as E-mail leaving the Email field as the one to draw the emails from
   

The To will default to the first field of the spreadsheet, good idea for it to be Email

The subject you need to add "Appointment Reminder" as shown above will probably do

Then you click Send documents

Subsequent Emails batches

Are much easier.  All you have to do is

1. Overwrite the CSV report file with a new one from OSCAR report by template. 
   
2. Open the writer file template that you saved in Open Office / Libre Office. 
   
3. Open the mail merge wizard and click to 8 and send the emails.

Sending Appointment reminders by SMS text message

(by Earl Wertheimer)

I have written about a simple way to send free appointment reminders by email, using an Oscar Report Template and Open Office (or Word) Mail Merge to Email. This is a variation on the same technique, but will allow you to send a text message instead of email.

Setup:
Enter the patient cell phone number in the appropriate place in the Demographic Record. Ask them what network they are on. Add one of the following codes to the Cell Phone number:

• Bell Mobility: [10-digit phone number]@txt.bell.ca

• Fido: [10-digit phone number]@fido.ca
• Kudo Mobile: [10-digit phone number]@msg.koodomobile.com
• MTS: [10-digit phone number]@text.mtsmobility.com
• Northern Tel: [10-digit phone number]@txt.northerntelmobility.ca
• President’s Choice: [10-digit phone number]@txt.bell.ca
• Rogers Wireless: [10-digit phone number]@pcs.rogers.com
• Sasktel: [10-digit phone number]@sms.sasktel.com
• Solo: [10-digit phone number]@txt.bell.ca
• Telus: [10-digit phone number]@msg.telus.com
• Virgin: [10-digit phone number]@vmobile.ca

So, if I am on Rogers and my cell is 514-555-1212 then you would enter 5145551212@pcs.rogers.com into the Cell Phone field of the Patient Master record. Note that you can add spaces and dashes for readability, but you would have to remove them in the Report Template (this is very easy).  This way, the field is still readable if you need to call the patient. Let's say I entered  (514) 555-1212@pcs.rogers.com

Next, you need to add the SMS Report Template to make this work to your OSCAR system. It is available from here. The operation of running the report and doing the mail merge is the same as described above.

For patients who prefer to receive their notifications by SMS, you could also consider simply registering the above cell number format in the email field of the patient instead of their usual email address. In that case you would simply use the old email report template instead. The advantage would be that you could run just one report and catch both the email and the SMS recipients at once. Keep in mind however that SMS text messages have a limit of 140 characters, so the reminder template has to be short to handle SMS (the acronym SMS stands for Short Message Service after all).

Note:

I tested the email to SMS with my Rogers phone, but I'm not sure about the other Cellular networks. A further improvement would be to omit patients who have valid email
addresses, since you don't want to send them both an email and a text message reminder.  If anyone could test some of the other mobile networks, that would be appreciated.

Earl Wertheimer

 Completely automated solution for appointment reminder SMS

Compiled by Peter HC, Earl Wertheimer and Gunther 02-Jun-2013. If you get stuck, please let us know on the OSCAR email discussion list.