Recently, I was tasked with a project that consisted of a form to enter in projects and add tasks to the project. One of the requirements was to send out emails once a week to each user that has tasks assigned to them in a summary format. The first thing that came to mind was to use a SharePoint 2013 workflow which can achieve this using looping. Because my client wanted to remain OOTB for maintenance purposes, it had to be code free. Also, the client did not have any third party tools to iterate through SharePoint 2013 workflows (Harepoint, virto, etc). They did, however, have these tools to iterate through 2010 workflows. The first thought was to iterate through the 2010 workflows and I could kick off the 2013 workflows when something is updated or created. Well, I discovered that you cannot start a 2013 workflow in this way. I had to get creative and found nothing out there relevant to my situation. After thinking about this and testing different scenarios, I came up with a fairly straightforward solution to this problem. Let’s step through this process.

Requirements: SharePoint 2010/2013/O365, SharePoint Designer 2010/2013, & Some knowledge of HTML & CSS if you want to style the emails but not necessary

We will need two lists and four Workflows to get the results needed.

Step 1 – Create Lists. Leave as default unless otherwise specified.
If you prefer, you can download the list templates by clicking here (created in SharePoint 2013).

List Name: Custom Tasks (you can use the OOTB tasks list if you prefer but in my case I needed a custom list which is much more detailed than this basic one). This is the main Task list that contains all tasks and to whom they are assigned.
Column: Assignee – Person or Group
Column: Due Date – Date and Time
Column: Status – Choice (menu to choose from), Choices – New, In Process, Completed, Cancelled
Column: Task/Deliverable

List Name: Loop Through Tasks. This list is used as a controller to loop through all the Tasks contained in the “Custom Tasks” list. It will also contain how many tasks the assignee has as well as other information to create the body of our email.

Allow management of content types in Advanced Settings:
Add Task AdvancedAdd Task Advanced SettingAdd Task Advanced OK

We don’t want the title field required:
Add Task ItemAdd Task Item TitleAdd Task Item Title Optional

Column: Active Tasks – Number, Number of decimal place = 0, Default value = 0
Column: Assignee – Person or Group
Column: Email Body – Multiple lines of text, Plain text
Column: HTML Created – Number, Number of decimal places 0, Default value = 0
Column: Weekly Summary Run Date – Single line of text

Step 2 – Create Workflows.
Create a Workflow on the Custom Tasks list called Add Task. This Workflow will add the assignees to the “Loop Through Tasks” list so we can use it for our looping. It will only add the assignee if they do not yet exist in the “Loop Through Tasks” list:
Add Task CreateChange the Workflow settings

Add a condition (If any value equals value):
Add condition

Set the first value and set the comparison to “not equals”:
Add Tasks C1 Add Task Compare

Set the last value of the condition:
Add Task C2Add Tasks C2 Detail

Add an action to create a list item:
Add Task Create List Item

Click on “this list” to set what list to create the item in and the field we will set:
Add Task Creating List Item

Publish the Workflow.

Create a Workflow on the Custom Tasks list called Count Tasks and change the settings as follows. This workflow will count how many tasks each assignee has. We can match this against how many times we add something to the email body to prevent failures as you will see:
Count Tasks Create Count Tasks Settings

Add a condition (If any value equals value):
Count Tasks Add C1

Set the first value and set the comparison to “not equals”:
Count Tasks C1 ValueCount Tasks Camparison

Set the last value of the condition to “Completed”:
Count tasks C1 second value

Directly under the first condition add another condition (If any value equals value) where the status not equals “Cancelled” this time:
ConditionCount Tasks C2 AddedCount Tasks C2 conditions

Directly under the second condition add another condition (If any value equals value) where the “Due Date” is greater than or equal to “Today”:
Count Tasks C3 value 1Count Tasks Comparison 3Count Tasks Last valueCount Tasks First condition section

Create a Workflow variable as follows called “UserTasks”:
Count Tasks Create VarCount Tasks Var settings

Set the value of the “UserTasks” variable we created:
Set VariableCount Tasks Select varCount Tasks Var detailsCount Tasks Set Var final

Insert a “Do Calculation” action and leave the default variable as “calc”:
Do CalculationCount Tasks Calculation

Set the calculation as follows:
Count Tasks Set V1Count Tasks Operand

Directly under the “Do Calculation” action, insert an “Update List Item” action:
Update List ItemCount Tasks Update List Item

Set the list and values we need to update in the “Loop Through Tasks”:
Count Tasks Update List C1Count Tasks Update Item Detail 2

Publish the Workflow.

Create a Workflow on the Custom Tasks list called Create Email and change the settings as follows. This workflow will create the email body needed for our email:
Create Email CreateCreate Email Settings

Add a condition (If any value equals value). Please note that the screen shots below reflect the step name in the last Workflow we created (Count Tasks). Since these steps are identical, I am using the same screen shots:
Count Tasks Add C1

Set the first value and set the comparison to “not equals”:
Count Tasks C1 ValueCount Tasks Camparison

Set the last value of the condition to “Completed:”
Count tasks C1 second value

Directly under the first condition add another condition (If any value equals value) where the status not equals “Cancelled” this time:
ConditionCount Tasks C2 AddedCount Tasks C2 conditions

Directly under the second condition add another condition (If any value equals value) where the “Due Date” is greater than or equal to “Today”:
Count Tasks C3 value 1Count Tasks Comparison 3Count Tasks Last valueCount Tasks First condition section

Create two Workflow variables:
Name: EmailBody – String
Name: HTMLIterationsCreated – Number

Set the EmailBody Workflow variable:
Create Email V1Create Email V1 ACreate Email V1 B

Set the HTMLIterations variable:
Create Email V2Create Email V2 ACreate Email V2 B

Add a condition to check if the EmailBody variable is blank:
Create Email C4Create Email C4 ACreate Email C4 C

Set the EmailBody variable if it is empty:
Create Email C4 B

Add more to the EmailBody variable:
CReate Email Set V1 Again ACreate Email V1 Detail 2Create Email V1 Detail 4Create Email V1 FinalCreate Email V1 Again Final

Directly after setting the variable, add a “Do Calculation” action:
Do Calculation Create Email Calc

Add 1 to the HTMLIterationsCreated variable:
Create Email Calc A

Insert an “Update List Item” action and Update the following list and fields:
Create Email Update List ACreate Email Update ACreate Emau Update DCreate Email Update ECreate Email Final A

Publish the Workflow.

Create a Workflow on the Loop Through Tasks list called Send Email. This workflow will send the email:
Create Send Email 1Create Send Email 2

Create two Workflow variables:
Name: TodaysDate – Date/Time
Name: EmptyString – String

Set the TodaysDate variable:
Send Email V1Send Email V1 Final

Add a condition (If any value equals value):
Send Email C1

Set the first value to Current Item:Active Tasks, set the comparison to “is greater than,” and the value to 0 (zero):
Send Email C1 ASend Email C1 BSend Email C1 C

Directly under the first condition add another condition (If any value equals value) where Current Item:HTML Created equals Current Item: Active Tasks:
Send Email C2 ASend Email C2 BSend Email C2 C

Add a “Send an Email” action:
Send Email Email A

Set the “To” field in our email, a subject, then set the body text using the Email Body field:
Send Email Email BSend Email Email CSend Email Email DSend Email Email E

Add a “Set Field in Current Item” action and set the “Weekly Summary Run Date” field to when the workflow was started:
Send Email Set Field 1Send Email Set Field 1ASend Email Set Field 1 Final

Add a “Set Field in Current Item” action and set the “Email Body” field to our EmptyString:
Send Email Set Field 2BSend Email Set Field 2A Send Email Set Field 2 Final

Add a “Set Field in Current Item” action and set the “Active Tasks” field to 0 (zero):
Send Email Set Field 3ASend Email Set Field 3B

Add a “Set Field in Current Item” action and set the “HTML Created” field to 0 (zero):
Send Email Set Field 4ASend Email Set Field 4B

Publish the Workflow.

This completes all of our Lists and Workflows.

Step 3 – Testing

To test the process, you can first run the “Count Tasks” Workflow manually. When we are done testing, I will show you how to schedule all of these processes using a 3rd party tool. Select one of the names from the “Custom Tasks” list and run the Workflow on every instance of that name. In this example, I will only be running the Workflow on all items with the name MOD Administrator:
Run CountTasks 1

For each one, you will need to run the Workflow (make sure to repeat this for each of the four names in the above example):
Run Count Tasks 2Run Count Tasks 3Run Count Tasks 4

Once you have completed running the Workflows, you can see how many tasks there are in our “Loop Through Tasks” list for this name (please note that although there were four items with the name Mod Administrator, it only counted three tasks. This is because our condition only wants tasks that are not Completed, not Cancelled, and the Due Date has to be greater than or equal to today. One of the items does not meet all of the criteria):
Run Count Tasks 5

After running the “Count Tasks” Workflow, you will now run the “Create Email” Workflow on the same items. Again, I will show you how we can automate this later:
Run Create Email 1Run Create Email 2

You will now see in our “Loop Through Tasks” list that we have some additional information:
Run Create Email 3

Next, we will run the “Send Email” Workflow within the “Loop Through Tasks” list. We only need to run this one time since names will be unique in this list:
Run Send Email 1Run Send Email 2Run Send Email 3

Once this has completed, You can see that the item in the “Loop Through Tasks” List has been cleared and a timestamp has been placed in:
Run Send Email 4

Here is the email generated:
Run Send Email Final Plain

Nifty….But rather plain and boring looking….Read on…..

If you have some knowledge of HTML & CSS you can create some nicely formatted emails as opposed to the plain text emails. For example, in our “Create Email” Workflow above, there is a condition that checks if the EmailBody variable is blank and if so we set it to be fairly generic:
Create Email C4 B

Instead, we can add some HTML and CSS to this section. Lets create a table by replacing what is in the string builder:
Blank HTML

You can copy here:
<div style=”font-weight:bold; font-size: 9pt; font-family: ‘Segoe UI’;”>Here are your Tasks that are currently active:</div><br /><table><tr><th valign=”top” style=”font-size: 9pt; font-family: ‘Segoe UI’; border: 1px solid #ddd; background-color: #eee; padding: 3px 15px;”>Task</th><th valign=”top” style=”font-size: 9pt; font-family: ‘Segoe UI’; border: 1px solid #ddd; background-color: #eee; padding: 3px 15px;”>Due Date</th></tr>

This mark up will create the header of the email as well as create a table and the table header. Next, we will format the cells of the table.

In the body of our email we currently have this in our string builder:
Create Email Body Plain
Let’s enhance the look of this as well. This is what our string builder will look like when done:
Create Email Body Mark Up A

First, clear out everything in the string builder window and insert the EmailBody variable:
Create Email Mark Up B

Next, copy the url of the “Custom Task” list. Mine looks like this:
https://johnhipolito.sharepoint.com/sites/john/_layouts/15/start.aspx#/Lists/Custom%20Tasks/AllItems.aspx

You will need to remove the /AllItems.aspx at the end and add on EditForm.aspx?ID= to make it look similar to this:
https://johnhipolito.sharepoint.com/sites/john/_layouts/15/start.aspx#/Lists/Custom%20Tasks/EditForm.aspx?ID=

Copy the following mark up and paste it right after the EmailBody variable. Do not do any line breaks or hit return and remember, your url will be different:
<tr><td valign=”top” style=”font-weight: normal; font-size: 9pt; font-family: ‘Segoe UI’; border: 1px solid #ccc; padding: 3px 15px;”><a href=”https://johnhipolito.sharepoint.com/sites/john/_layouts/15/start.aspx#/Lists/Custom%20Tasks/EditForm.aspx?ID=
Create Email Mark Up C

Place the cursor directly after ID= and add the Current Item ID:
Create Email Mark Up D

Directly after where we inserted the Current Item ID, paste in the following:
“>
Create Email Mark Up E

After placing in those characters, insert the Current Item Task/Deliverable:
Create Email Mark Up F

After inserting the Current Item Task/Deliverable, copy the following mark up and paste it in. Remember, no line breaks:
</a></td><td valign=”top” style=”font-weight: normal; font-size: 9pt; font-family: ‘Segoe UI’; border: 1px solid #ccc; padding: 3px 15px;”>
Create Email Mark Up G

We now need to insert the Current Item Due Date directly after the last markup:
Create Email Mark Up H

Insert the following mark up directly after the Current Item Due Date:
</td></tr>
Create Email Mark Up ICreate Email Mark Up J

We will now have to close our table tag. We need to figure out how many items there are so we know when to close the table.

Insert a condition action (If any value equals value) between the calculation of the HTMLIterationsCreated variable and the “Loop Through Tasks” list update:
Create Email Mark Up KCreate Email Mark Up L

Set the values and condition:
Create Email Mark Up MCreate Email Mark Up NCreate Email Mark Up O

Within the condition, insert a “Set Workflow Variable” and set the EmailBody variable as follows using the string builder:
Create Email Mark Up PCreate Email Mark Up Q

Copy and paste the following mark up directly after the EmailBody variable:
</table>

Create Email Mark Up R

Test the Workflows again, but before you run the final “Send Email” Workflow, you will see our mark up in the Email Body field within the “Loop Through Tasks” list:
Create Email Mark Up S

Run the final “Send Email” Workflow and you will get the following results or similar:
Final Formatted

To make this automated you can install a free third-party add-on called HarePoint http://www.harepoint.com/Products/HarePointWorkflowScheduler/Default.aspx. There are other tools out there you can use as well. Simply set up a schedule to run the three Workflows in the following order:
1) Count Tasks
2) Create Email
3) Send Email

In my environment, I have the “Count Tasks” Workflow run Saturday nights, the “Create Email” Workflow run Sunday mornings, and I have the “Send Email” Workflow run early Monday Mornings.

As you can see, what most think is not doable utilizing SharePoint 2010 Workflows is in fact possible with a bit of creativity and patience. It is true that it is more work than using a 2013 Workflow, but there are cases where we cannot use 2013 Workflows. At least we have the ability to use both in SharePoint 2013!

Happy SharePointing…

Author

John Hipolito

John is a SharePoint Front End Developer for RBA.

Leave a Reply