Grant Lewis

Grant Lewis is an Software Engineer with ThreeWill. Grant’s consulting experience consists of using Microsoft technologies to enhance business operations in a manufacturing environment.

Sending reminder emails

A client of mine recently had a need to send Reminder emails at certain intervals. For many people, the best way to achieve such a behavior would be with a timer job. However, there are no timer jobs in O365, so with an eye to the cloud, I set out to find another solution.

The basics of creating looping workflows with SharePoint Designer

In the ideal case, we would create a SharePoint workflow that would wait until a designated time, send a reminder email, schedule the next reminder time, and wait again. However, SharePoint Designer workflows do not give you the ability to loop in this manner. Also, starting in SharePoint 2010, a workflow cannot trigger itself to prevent infinite loops, but that is exactly what we want in this instance. A well-known workaround for this is to create two workflows that call each other to simulate this looping behavior, which is the method I will use in the following tutorial.

Using Calculated Columns to modify dates

SharePoint Designer workflows are limited when it comes to working with dates. The only two operations that are available to modify a Date/Time field are “Set Time Portion of Date/Time Field” and “Add Time to Date”. The “Add Time to Date” action is potentially useful if you wanted to do things on a daily, weekly, monthly, or annual basis; however, my requirement was to send reminders on the last day of the month. Because the number of the days in a month changes, this meant that I needed to somehow calculate the last day of the current month. The best option that we have for manipulating dates in SharePoint without custom code is using calculated columns. In a calculated column, you can use the DATE function just like you would in Excel.

Let’s say I have a column called “DateColumn” that has a date. Here are some examples of date formulas that can be used with calculated columns:

• The next 15^th day of the month (could be either later this month if we are currently earlier than the 15^th in the month, or it may be next month if we are past the 15^th in the month):
  DATE(YEAR([DateColumn]),IF(DAY([DateColumn])>14,MONTH([DateColumn])+1,MONTH([DateColumn])),15)
  
• The last day of the current month:
  DATE(YEAR([DateColumn]),MONTH([DateColumn])+1,1)-1

Creating the SharePoint lists

The first step is to create a list (“List A”) that will be used to drive the reminder email workflow. For my example, this list had three fields:

1. Title (Single line of text) – the default title column, also used to link to the other list
2. LastReminderTime (Date/Time) – the last time the reminder was sent, used to calculate the next reminder time
3. NextReminderTime (Calculated Column) – calculated from the current time to find the next reminder time. Set the return type to “Date and Time”

The other list (“List B”) only has two fields:

1. Title (Single line of text) – this should match the title of the other list for the lookup
2. LastReminderTime (Date/Time) – this field will be copied from the first list

Creating the SharePoint Designer Workflows

First, create a list workflow for List A to send the reminder emails. This workflow needs three steps:

1. Pause until Date – set the date to the NextReminderTime field of the current item.
2. Send an Email – send the email as required.
3. Update List Item – set the LastReminderTime field of List B to the value of NextReminderTime in the current item, looking up the correct list item on ListA:Title = ListB:Title.

Set the start condition for this workflow to “Start workflow automatically when an item is changed”. Publish the workflow.

Next, create a list workflow for List B to update List A to create a loop. This workflow just has one step.

1. Update List Item – set the LastReminderTime field of List A to the LastReminderTime of the current item.

Also set the start condition for this workflow to “Start workflow automatically when an item is changed”. Publish the workflow.

Initial Setup

First, create an item in List A. Give the item a Title, but nothing else. Next, create an item in List B with the same title as the item in List A. Go back to the item in List A and add a value for the LastReminderTime such that the calculated NextReminderTime will be a future date and time.

And that is it. Your reminder email process is in progress. You should receive an email reminder at the time specified by the NextReminderTime field in List A. The Reminders should be continued to be sent on a schedule defined by the calculated column field that you set up.