Showing results for 
Search instead for 
Did you mean: 

Send a Summary Email of Outstanding Items. A simple example.

Workflow Hero
2 2 977

Hello! I wanted a solution to remind my users that they had outstanding and overdue items. My current solution is to query a list for users with outstanding items and send them an email with a link to a page showing the items to do.

While this works to a point I wanted to spoon feed them a little more. I wanted the email to include information about each item and to have a link to each item. Something like...

The list for this example is a Diary list. A simple list that sends reminders on a due date for the Event Owner to do a job and then Close or Rollover the item. Of course many don't bother closing the item so we have lot's of events that look overdue. Hence the reason for this reminder.

We need to use a site workflow for this to work.

First couple of Actions. Query List to capture the EventOwners and then a Collection Operation to Remove Duplicates

Query List. Filter to just overdue Items in progress and gather the EventOwner field into a collection.

Collection Operation. Remove duplicates from the collection.

Now for each User in the collection we need to do the following....

For Each User in the collection, store in a Person Variable.

We need this as a person variable to get the ID and Name.

Get the users SharePoint ID. I need this because we are querying the list next and I need to filter outstanding items down to the user.

Get the Users name. Used in the email.

Lookup Outstanding items for user and get the fields we want to include in the summary email. You can see the filter is the same as the original query list with the addition of filtering down to the users ID.

And the fields I want to include in the summary email at the end . ID is a must and I'm keeping it simple by just bringing in the Title and Due Date. Each extra field you want needs a Collection variable and a Single Text Variable.

Now we want to summarize the items outstanding for the user. To do that for each item ID, we need to collect our required fields ( Title and Due Date ).

For Each ID in the collection. This also requires an index (number variable). This keeps the Title and Due Date fields together from the relevant item.

Run parallel actions. Lookup the fields we need from the item.

Last part of the Summary section.

Build String. Here I'm putting the Fields onto a single line.

Title - Due Date - Link.

{WorkflowVariable:V_Items_Title} - Due : fn-FormatDate({WorkflowVariable:V_Items_Due_Date},"dd/MM/yyy") : <a href="/Applications/Diary/Lists/DiaryItems/EditForm.aspx?ID={WorkflowVariable:V_Items_ID}">Update</a>.</br>

Add the Built string into a collection variable. 

Update the Index to get the Next item Info

Last bit! Now we tidy the string collection variable, send the email and clear the Task String collection for the next loop.

Regular Expression. Get rid of the ;

Send the Email! Send an email to the Assigned Person.

Clear Collection string for next user.

The End.

Full Workflow layout. Example attached.

There's probably a more efficient way to to this, as we could get all the information in the initial lookup but my tiny brain can't make that leap

Also of course you can schedule this site workflow to run when you want.


Tags (1)
Workflow Hero

Great solution - I did similar approaches in the past.

Just a minor observation: you don't need the math-action to increment the v_index as this is done in the for-each action already. The for-each action is writing the current index of the iterator into the v_index.

Workflow Hero

Noted, thank you