reminder-emails.jpg

Sending Reminder Emails Using SharePoint Designer Workflows

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 15th day of the month (could be either later this month if we are currently earlier than the 15th in the month, or it may be next month if we are past the 15th 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.


Related Posts

Grant LewisSending Reminder Emails Using SharePoint Designer Workflows
  • Kirk Liemohn

    Great post, Grant! It stinks that SPD WFs have this limitation, but it’s great that you can give us a solution. We’re no-code Visual Studio WFs out of the question? I suspect that they could do it in a single list and workflow, but I’m not sure. I think you have to go to great lengths to get them to run outside of an app web, so that may be enough of a reason right there.

    • Good question, Kirk. I did not consider a no-code Visual Studio for this client’s project for a couple of reasons. The first reason is simply that the client wanted to enable the process owner’s to maintain the workflows if they so chose, and SharePoint Designer is a more accessible tool for power users than Visual Studio, not to mention licensing and other considerations. This particular client is also has an eye on a future in the cloud. I believe that the no-code Visual Studio project types can only be deployed as Farm Solutions and therefore would not be able to move to SharePoint Online.

    • Kirk Liemohn

      Hi Grant, I agree that SPD is much simpler for power users. Note that you can have no-code VS workflows in SharePoint Online. The last time I did one (18 months ago) the only way to deploy it (without a lot of custom effort) was to deploy it to the app web which means you have to have a SharePoint app to begin with. I thought that PnP had come up with a way to deploy them to regular SharePoint sites (host web, if you will), but in looking just now I couldn’t find anything for SPO: https://github.com/OfficeDev/PnP/tree/master/Samples.

    • Lane Goolsby

      There is a section in the PnP Provisioning Engine Template for deploying workflows. I haven’t actually used the engine for workflows but it should work the same for on prem vs. SPO (in theory, at least). https://github.com/OfficeDev/PnP-Provisioning-Schema/blob/master/ProvisioningSchema-2015-12.md#workflows

  • viplove

    I am working with a sharepoint list
    and want to set a reminder to user who submitted request when activity end time reaches
    can you please help me setting this with my SP library

  • RameshKumar

    how to enable email notification in O365 using sharepoint designer

  • Roman Rylov

    OOB workflow actions cannot send an email to external users, you need to use 3rd party products like Workflow Actions Pack (https://plumsail.com/docs/workflow-actions-pack/Actions/E-mail%20processing.html#send-e-mail-with-attachments)

  • Darion D’Anjou

    Hi Grant, thanks for the article.

    How might I create a single custom page for my workflow approvals? I’m trying to stay client side as I’m in SharePoint Online. Basically when original user creates an item in a custom list, the workflow kicks off and sends the manager an email. What i want is that when Manager clicks the link in the email, they land on one page, where they can see the details of the request AND click Approve or Reject, right there on the page?

    Ideas how to achieve this? To me (and to the end users who want me to make this for them) it seems like the most common sense user experience expected with a workflow, but for some reason it seems difficult to find reference where someone has done this in SharePoint, especially SharePoint Online

  • Jeb Castelo

    https://uploads.disquscdn.com/images/0325f081210c3f3622068edad678a815bf5a7e2ffc5a6bfe447051db8e24c669.jpg
    This workflow should do the trick (see the screen shot attached), note in this workflow I used different status to set isreminder email to no. this stops the workflow from firing the reminder if it is not needed

  • Tom Braman

    Thanks for your post; I’m looking forward to seeing it work! I lost you when you wrote, “looking up the correct list item on ListA:Title = ListB:Title” (bullet 3, under “Creating the SharePoint Designer Workflows”). Perhaps you could add a screenshot indicating what you’re referring to? I was able to set the LastReminderTime field of List B to the value of NextReminderTime in the current item, but never saw an option to do a lookup. In SP Designer, my update item step reads just “then Update item in Current Item.” Am I’m doing something wrong?

  • Juan Mora

    I created the following workflow but it doesn’t work and I really don’t know why, can some one assist me?

  • Juan Mora

    I created the following workflow but it doesn’t work and I really don’t know why, can some one assist me?

    https://uploads.disquscdn.com/images/649727cabfa4d40b159dc3d3b1401e28aebcd95eacfc22f3f697a9e81c07a21e.png

    • One option for debugging issues with SharePoint Designer workflows is using the Log to History List action to write debug information to the workflow history list. That might provide some assistance as you work to track down the issue.

  • ze pimousse

    Hi all, someone knows how approve an overdue task in SPDesigner ? Regards, ZP

  • Victoria

    Nice suggestions! Maybe this post could also be usefull to someone. http://blog.virtosoftware.com/2017/02/sharepoint-reminder-email.html