Send Periodic Expiration Email Notifications in Office 365 and SharePoint 2013 Workflows

Introduction

In this article, I want to cover the very common scenario in any business system, alert notifications or a reporting subsystem. Usually that function has the responsibility of notifying end users about new events, overdue tasks and sending summary reports via e-mail. I want to review a couple of simple cases that I hope you can manually customize and implement in your environment. All the cases I created use SharePoint Online with an installed Plumsail Workflow Actions Pack, but you can implement it in an OnPremise environment as well.

Personal Item/Task/Document expiration reminders

The first case that I want to describe has a very simple simple goal, that is for a user to be able to track the status of sent items, tasks or documents. It could be an application form for a business trip, day-off request, assigned task or something else. It depends on your business process, but the core idea is the same. You have an item with a specific due date and a person responsible for this item. You need to send a notification message to the user when the due date arrives and of course you can customize the message template. Using SharePoint Designer you can create a site-level workflow that will select all list items by specific query and process each item. As an example, it could generate an email by template and send it.

Out-of-the-box workflow actions don't allow the querying of list items by CAML or generation of email messages by templates. That is why I used some workflow actions from the Workflow Actions Pack to extend the out-of-the-box functionality.

The following shows the configured workflow:



Figure 1: Reminder Workflow

I used the following CAML query to get all the list items that expire in two days in the Get Items by query workflow action:

<View>
    <ViewFields>
        <FieldRef Name='ID' />
        <FieldRef Name='Title' />
        <FieldRef Name='DueDate' />
        <FieldRef Name='AssignedTo' />
    </ViewFields>
    <Where>
        <And>
            <Neq>
                <FieldRef Name='Status' />
                <Value Type='Choice'>Completed</Value>
            </Neq>
            <Lt>
                <FieldRef Name='DueDate' />
                <Value Type='DateTime'>
                    <Today OffsetDays='2' />
                </Value>
            </Lt>
        </And>
    </Where>
</View>

The following is the send email workflow action configuration (to simplify the process I designed the email template inside):

Hello [%Variable: userField%] <br /><br /> Have you had time to look into this? <br /><br />

<a href=”http://demo.plumsail.com/sites/NotificationCase/Lists/Workflow Tasks/DispForm.aspx?ID=[%Variable:ListItemID%]”>      [%Variable:ListItemTitle%]</a><br />
The Due Date is [%Variable: DueDate%]

<br /><br />
Kind Regards, <br />

SharePoint Notification System

You can see the sceenshot below as the result:



Figure 2: Result Email
 
Daily/Weekly/Mounthly aggregated expiration reports by email

The second example that I want to show is based on the same algorithm, but you need to get an aggregated summary report instead of sending notifications to each user. To do this you can get all the data and use a Render Template workflow action to build the complete email by predefined template.

In the following I implemented the workflow for a typical case of when an administrator wants to get a summary report with a list of all checked-out documents ordered by a user.

In the figure below you can see the configured workflow, I split it into two stages to simplify the configuration and copying to other document libraries.



Figure 3: Workflow

The first step is just the configuration section.

Next, I got all list items where the field CheckoutUser is not null, I used the following CAML query for this:

<View Scope="RecursiveAll">
    <ViewFields>
        <FieldRef Name="CheckoutUser"/>
        <FieldRef Name="FileRef"/>
        <FieldRef Name="FileLeafRef"/>
    </ViewFields>
    <Query>
        <Where>
            <IsNotNull>
                <FieldRef Name="CheckoutUser"/>
            </IsNotNull>
        </Where>
        <OrderBy>
            <FieldRef Name="CheckoutUser"/>
            <FieldRef Name="FileLeafRef"/>
        </OrderBy>
    </Query>
</View>

Then I prepared the data for the Render Text Template workflow action. I used the following template:

Please review the list of documents which was checked out:
<br />
<br />
<table>
    <thead>
        <td>
            <strong>User Name</strong>
        </td>
        <td>
            <strong>Document</strong>
        </td>
    </thead>
    <tbody>
{{#each Documents}}

        <tr>
            <td>{{FieldValues.CheckoutUser}}</td>
            <td>
                <a href="{{SiteUrl}}{{FieldValues.FileRef}}">{{FieldValues.FileLeafRef}}</a>
            </td>
        </tr>
{{/each}}

    </tbody>
</table>
<br />
<br />

Thank you,
<br />
SharePoint Notification System.
<br />

Before sending the message I used a Regular Expression Replace workflow action to replace values in the User's field from the value like 24#;John Martin to 24 - John Martin.

And at the final step I sent a complete email report to an administrator as in the following:



Figure 4: Result Email

Conclusion

In this article I wanted to describe some common cases that can be used for alerting or reminding purposes. I hope it was clear enough and it will not be difficult to repeat it or customize to your own requirements. Please feel free to comment on the article.

Origianl posted at How to send periodic expiration email notifications in Office 365 and SharePoint 2013 Workflows.