Sit back and relax, let Sharepoint remind and chase your team

Introduction

Sharepoint Task List is a great place to record tasks for your team members. However, once you have recorded and assigned the tasks, then the fun begins. You have to remind your team members repeatedly about what they need to do today, what tasks are overdue, what's coming this week and so on. Here's an app that takes that chore away, and you can sit back and relax, while it will reminds your team members as many times as you want. Your team members will get an email like this, reminding them of their assigned tasks:

The email templates are fully customizable. You can define exactly how the email should look like. You can also include custom fields from sharepoint taks items to appear on the email.

• Source code: https://github.com/oazabir/SharepointTaskReminder
• Binary: https://github.com/oazabir/SharepointTaskReminder/blob/master/Binary/SharepointTaskReminder.zip?raw=true 
• Pre-requisite: .NET 4.5

How to use it

First you need to create a nice Sharepoint Task List. Here's how to do it:

Step 1: Create a new Task List

Step 2 Populate tasks and assign to your team members

Step 3 Get the binaries and configure the parameters

Run the DotBits.Configuration and configure the parameters as shown below:

• SiteURL - this is the base URL for your sharepoint site, not the URL to the sharepoint list.
• Domain - Your activate directory domain.
• Username - A user that has access to the sharepoint list. Any user would do. The code just needs to authenticate to sharepoint using some user that has access to the sharepoint list.
• Password - Password for the above sharepoint user. 
• FromEmail - the From address on the emails sent. It is most likely you, the lucky project manager. 
• CcEmail - each sent email will be CCed to this address. It is most likely you, the lucky project manager. 
• ListTitle - This is the name of the list you see on sharepoint. 
• MaxItems - Number of items to fetch in one shot. 
• DueDateFieldName - This is the column name that contains the Due Date. 
• AssignedToFieldName - This is the Assigned To column on the list.
• FilterFieldName - This is the field that is used to check which items we do not need to process. Eg Completed tasks.
• FilterFieldValue - The value to exclude.
• EmaliSubject - Subject of the daily reminder email. {0} gets replaced with current date. 
• ErrorEmailSubject - Subject of the email which contains errors produced by the reminder app.
• ErrorTo - Who gets the error emails. 
• SMTPServer - Put the SMTP server name here. For ex, smtp.yourcompany.com. Gmail SMTP server won't work as it uses TLS. 
• SMTPPort - Put the smtp port here. Default is 25. 

Step 4 Install .NET 4

You need to run the .NET 4 setup from here: http://www.microsoft.com/en-gb/download/details.aspx?id=17851 

Step 5 Configure the app to run periodically

You can run the SharepointListClient.exe manually. Or you can schedule this to run via Windows Scheduled Task.

First Launch Schedule Task and create a new task:

Here, click "Change User or Group" and use the NETWORK SERVICE account. Just type NETWORK SERVICE, then click "Check Names" and click OK.

On The Triggers tab, specify when you want to run this. For ex, here you see it is scheduled to run twice daily. 

On the Actions tab, add an action to run the program. First you need to select the SharepointListClient.exe and then you need to copy what is before it, the folder where the file is, and put it on the "Start In" box. 

That's it!

Using the code

Now, time for a quick code walkthrough. First the code connects to the Sharepoint List:

// Starting with ClientContext, the constructor requires a URL to the 
// server running SharePoint. 
using (ClientContext context = new ClientContext(ConfigurationManager.AppSettings["SiteUrl"]))
{
    CredentialCache cc = new CredentialCache();
    NetworkCredential nc = new NetworkCredential(ConfigurationManager.AppSettings["Username"], ConfigurationManager.AppSettings["Password"], ConfigurationManager.AppSettings["Domain"]);

    cc.Add(new Uri(ConfigurationManager.AppSettings["SiteUrl"]), "Negotiate", nc);
    context.Credentials = cc; 

    Console.WriteLine("Connecting to Sharepoint: {0} ...", ConfigurationManager.AppSettings["SiteUrl"]);                    
    // The SharePoint web at the URL.
    Web web = context.Web;

    // We want to retrieve the web's properties.
    context.Load(web);
    // Execute the query to the server.
    context.ExecuteQuery();
    Console.WriteLine("Connected to Sharepoint...");

Once it has successfully connected to the Sharepoint site, it will connect to the List and get the items from the List:

// Get the list properties. We need the EditURL
Console.WriteLine("Getting list: {0}...", ConfigurationManager.AppSettings["ListTitle"]);
var list = web.Lists.GetByTitle(ConfigurationManager.AppSettings["ListTitle"]);
context.Load(list);
context.ExecuteQuery();
var editFormUrl = list.DefaultEditFormUrl;

// Get items from the list that we will loop through and produce the email
Console.WriteLine("Getting items from list: {0}...", ConfigurationManager.AppSettings["ListTitle"]);
// This creates a CamlQuery that has a RowLimit of 100, and also specifies Scope="RecursiveAll" 
// so that it grabs all list items, regardless of the folder they are in. 
CamlQuery query = CamlQuery.CreateAllItemsQuery(Convert.ToInt32(ConfigurationManager.AppSettings["MaxItems"]));
var items = list.GetItems(query);

// Retrieve all items in the ListItemCollection from List.GetItems(Query). 
context.Load(items);
context.ExecuteQuery();

// This is the Edit URL for each item in the list
var editFormUri = new Uri(new Uri(ConfigurationManager.AppSettings["SiteUrl"]), new Uri(editFormUrl, UriKind.Relative));

Now is the time to process each item and decide whether to mark the task as Overdue, or Due Today, or Due Tomorrow or This week. First we need to get each item and find the assigned person and his/her email address. 

private static void SendEmailReminder(ClientContext context, ListItemCollection items, string editUrl)
{
    var emailsToSend = new Dictionary<string, Dictionary<int,List<ListItem>>>();

    foreach (var listItem in items)
    {
        // let's use the filter criteria. Eg completed tasks are ignored. 
        var filterValue = (listItem[ConfigurationManager.AppSettings["FilterFieldName"]] ?? string.Empty).ToString();
        if (filterValue != ConfigurationManager.AppSettings["FilterFieldValue"])
        {
            var dueDateString = (listItem[ConfigurationManager.AppSettings["DueDateFieldName"]] ?? string.Empty).ToString();
            var assignedTo = listItem[ConfigurationManager.AppSettings["AssignedToFieldName"]] as FieldLookupValue;
            var email = "";

            // Find the email address of the user from username
            if (assignedTo != null)
            {
                ListItem it = context.Web.SiteUserInfoList.GetItemById(assignedTo.LookupId);

                context.Load(it);
                context.ExecuteQuery();
                email = Convert.ToString(it["EMail"]);
            }

Now that we know who that person is, we need to decide whether the task is overdue, due, in future etc. 

// From the due date, see whether the task is today, tomorrow, overdue etc
DateTime dueDate;
if (DateTime.TryParse(dueDateString, out dueDate) && !string.IsNullOrEmpty(email))
{
    dueDate = dueDate.ToLocalTime();
    if (!emailsToSend.ContainsKey(email))
    {
        emailsToSend.Add(email, new Dictionary<int, List<ListItem>>());
        emailsToSend[email].Add(OVERDUE, new List<ListItem>());
        emailsToSend[email].Add(TODAY, new List<ListItem>());
        emailsToSend[email].Add(TOMORROW, new List<ListItem>());
        emailsToSend[email].Add(THIS_WEEK, new List<ListItem>());
    }

    // Today
    if (dueDate.Date == DateTime.Today.Date)
    {
        emailsToSend[email][TODAY].Add(listItem);
    }
    // Overdue
    else if (dueDate < DateTime.Today.Date)
    {
        emailsToSend[email][OVERDUE].Add(listItem);
    }
    // Tomorrow
    else if (dueDate.Date == DateTime.Today.AddDays(1).Date)
    {
        emailsToSend[email][TOMORROW].Add(listItem);
    }
    // This week
    else if (dueDate.Date <= DateTime.Today.AddDays(7 - (int)DateTime.Today.DayOfWeek).Date)
    {
        emailsToSend[email][THIS_WEEK].Add(listItem);
    }
}

Now we know what tasks to remind the person about. Let's prepare an email and send it:

// Use the email templates and inject the task reminders inside them
var emailTemplate = System.IO.File.ReadAllText(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "email_template.html"));
var taskTemplate = System.IO.File.ReadAllText(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "task_template.html"));
            
var tokens = new Dictionary<string,string>();
tokens.Add("@{TODAY}", DateTime.Today.ToLongDateString());
tokens.Add("@{OVERDUE_TASKS}", ConvertTasksToHtml(taskTemplate, items[OVERDUE], editUrl));
tokens.Add("@{TODAY_TASKS}", ConvertTasksToHtml(taskTemplate, items[TODAY], editUrl));
tokens.Add("@{TOMORROW_TASKS}", ConvertTasksToHtml(taskTemplate, items[TOMORROW], editUrl));
tokens.Add("@{THISWEEK_TASKS}", ConvertTasksToHtml(taskTemplate, items[THIS_WEEK], editUrl));

var subject = string.Format(ConfigurationManager.AppSettings["EmailSubject"], DateTime.Today.ToLongDateString());
var body = ReplaceTokens(emailTemplate, tokens);

var filename = email.Replace('@', '_').Replace('.', '_') + ".html";
System.IO.File.WriteAllText(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, filename), body);

SendEmail(ConfigurationManager.AppSettings["FromEmail"], 
    email,
    ConfigurationManager.AppSettings["CcEmail"],
    subject,
    body);

You can customize the email template yourself. The are two html files that contains the email templates:

The first one is email_template.html, that contains the boilerplate:

<!DOCTYPE html>

<html lang="en" xmlns="http://www.w3.org/1999/xhtml">
<head>
    <meta charset="utf-8" />
    <title>Your daily task reminder</title>
    <style>
        * { font-family: arial, Verdana; }
        h1, h2, h3 { color: dimgrey; margin: 5px; margin-top: 30px; }
        .warning { color: brown; font-size:80% }
        div.task { border-bottom: 1px solid lightgrey; padding: 5px; }
        div.task a { font-weight: bold; line-height: 1.5em }
        div.body { border-left: lightgrey 4px solid; padding-left: 5px; color: gray}
        small {color: gray}
    </style>
</head>
<body>
    <p><i><small>This is an automated alert to remind you of some tasks that you are assigned to. </small></i></p>
        
    <h2>Overdue Tasks</h2>
    <div class="warning">These are overdue. Very bad. Click on the task to provide new ETA.</div>
    @{OVERDUE_TASKS}

    <h2>Today's Tasks</h2>
    <div class="warning">You gotta get these done today. If not, then firstly, shame on you, secondly, provide new ETA.</div>
    @{TODAY_TASKS}

    <h2>Tomorrow's Tasks</h2>    
    @{TOMORROW_TASKS}
    
    <h2>This week's Tasks</h2>
    @{THISWEEK_TASKS}

</body>
</html>

Each task comes from task_template.html:

<div class="task">
    <a href="@{EditUrl}">@{Title}</a> <br />
    <div class="body">
        @{ShortBody}
    </div>
    <small>Due on: @{DueDate}</small>    
</div>

In this template, you specify which columns from Sharepoint List needs to appear on each task. You can create your own columns and put that Column's data on the email. 

There are two special columns @{EditUrl} and @{ShortBody} that is produced by the code. ShortBody gives the first 1000 characters of the full Body/Description field. 

Get the code

Firstly, you need .NET 4.5 to be installed. 

http://www.microsoft.com/en-gb/download/details.aspx?id=30653

The source is available at the GitHub:

https://github.com/oazabir/SharepointTaskReminder

You can get the binary of the utility from here, which you just need to extract, run the DotBits.Configuration and you are ready to roll:

https://github.com/oazabir/SharepointTaskReminder/blob/master/Binary/SharepointTaskReminder.zip?raw=true

Enjoy!

Thanks DotBits for your nifty tool.