SharePoint Experts Blog

How to send periodic expiration email notifications in Office 365 and SharePoint 2013 Workflows

Introduction

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

Personal Item/Task/Document expiration reminders

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

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

Below I showed the configured workflow:

I used the following CAML query to get all list items that expire in two days in 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>

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 result:

Daily/Weekly/Mounthly aggregated expiration reports by email

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

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

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

The first step is just 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>

After it, I prepared 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 Regular Expression Replace workflow action to replace values in the User’s field from value like 24#;John Martin to 24 – John Martin.

And at the final step I sent complete email report to an administrator

Conclusion

In this article I wanted to describe some common cases which 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 the article.

Posted in: Products, Workflow Actions Pack

  • How are you starting these workflows?
  • Alfred
    Can you explain more on the Build dictionary action in the Create Admin Report stage of the second example. Seems it is used to store the queried data. Need to know how. Thanks.
    • Alfred
      I think I figured it out! Add the queried result dictionary to the new dictionary.
  • gowtham
    I liked your workflow steps and really amazing things you have done.i need your help in this workflow.I have requirement to send a weekly email to user(status =Pending with user),i have to retrive the pending user name and send a email to the users.is it possible to do.The mail will goes to automattically every week friday
    • Roman Rylov
      Hi Gowtham,Yes it is possible, I will prepare detailed answer during a few days. I will notify you here, when it will be ready.
    • Roman Rylov
      Gowtham, Please see example on our forum https://plumsail.com/forum/viewtopic.php?f=22&t=255
      • Gowtham
        Hi Roman, Thanks for your reply. I got your point to retrieve the item,but what i’m expecting ,how to execute the workflow weekly once automatically.let me explain clearly ,every Wednesday we need to automatically execute the workflow to notify the user using workflow.
  • Jack Newson
    Hi I think I am struggling with the build dictionary functionI have got the email to send but there is no information apart from the headersI have used the caml query externally and it is returning all of the information providedthis is what im using in the render templatePlease review the list of documents which are due to expire within the next 6 weeks:TitleReview Date{{#each Documents}} {{FieldValues.Title}} {{FieldValues.Review_x0020_Date}} {{/each}}Any help would be greatfully recieved
    • Roman Rylov
      Hello Jack,Please add send email workflow action and send Overdue Items to your email. Thus, you can see JSON result. Please send us this data to support@plumsail.com.