Click here to Skip to main content
Click here to Skip to main content
Go to top

Send scheduled Reminder/Alerts by email in SharePoint

, 24 Mar 2009
Rate this:
Please Sign up or sign in to vote.
Learn how to create a SharePoint Job that queries lists and sends results via email.

Download Mullivan.SharePoint.zip - 576.79 KB

Introduction

Currently SharePoint only supports alerts when specific event occurs. For instance when an item is created or modified. But what if we want to send a reminder/alert at 3pm every day to notify users that they have had a file check out for over a week. The development of this SharePoint feature is a solution to this well known problem.

Design

So where do we start with our Architecture? Are we going to use a windows service? How are we going to configure our reminders? There are many different ways that I could have designed this application but I decided to take advantage of all the cool features SharePoint has to offer.

Configuration

So SharePoint is pretty flexible and it allows us to add action items to the settings menu in our list views. So that will help users easily navigate to our settings page. Since these are going to be pretty complex settings we are also going to only allow administrators to see this setting.

Menu_Item.gif

Now all we'll have to do is point this menu item to send the user to our configuration page. The next step is to create our settings pages and drop them in the layouts directory. The path to the layouts directory is C:\Program Files\Common Files\microsoft shared\Web Server Extensions\12\TEMPLATE\LAYOUTS. Once they are in that directory then they can be accessed in the browser by going to http://<yourserver>/<yoursite>/_layouts/<yourpage>.aspx This is very important because it is going to allow us to access the page from any subsite by just using the subsites url plus _layouts/<yourpage>.aspx. So we'll get http://<yourserver>/<yoursite>/<yoursubsite>/_layouts/<yourpage>.aspx. So just the clearify, this page only exists once on the file system.

But wait!!?!?!? Were are we going to STORE our configuration? Crap... I thought long and hard about this one and I decided to go away from creating a database schema and coding all the CRUD that needs to happen. So I went ahead and decided to store everything in the parent SPWeb object to the current list that is being configured. We'll do this by using xml serialization to serialize our configuration object and storing it in the Property Bag on the SPWeb object. We'll go over this more later. Just keep in mind there is no extra database tacked on. Yeah!

Services

So are we going to need a windows service? Absolutely not!! SharePoint already has a service that is running called "Windows SharePoint Services Timer". This service runs "Jobs" that are set up on a schedule. All we have to do is Inherit from SPJobDefinition, implement the Execute method, drop our dll in the gac and then activate our job on the same server that a central admin is on. It is in this job that we will crawl every list in the site-collection to see if it has any reminder/alert settings. If it does then we'll see if there are any reminders that need to execute.

Flexibility

So now we need to come up with a way for our alerts and reminders to be flexible. How are we going to allow our users this feature to be flexible enough so that it can be reused on different lists for different purposes? Well, we are going to be quering the lists for list items. That will require the user to configure a CAML query that will be used to pull back the items they want. That query will then give us a collection of SPItem. That's great because the collection itself and the item have properties that represent them in a XML string format. So how do we turn this XML into something that is readable by every day SharePoint users who don't know what XML is? Also, we need it to be flexible so that we can do calculations and such on the results. Ah yes, XSLT transformation. It requires quit a bit of knowledge but it's a powerful way to transform that XML into viewable HTML pages that can be seen in an email. Sweet!!!

Installation

GAC the following dlls (Located in the Build Folder)

Mullivan.Shared.dll
Mullivan.SharePoint.dll
Mullivan.SharePoint.Pages.dll
Mullivan.SharePoint.Reminders.dll
Mullivan.SharePoint.WebParts.dll

Navigate to C:\Program Files\Common Files\microsoft shared\Web Server Extensions\12\TEMPLATE\LAYOUTS

Copy Build\Layouts\ReminderSettings.aspx to this dir
Copy Build\Layouts\EditReminderSettings.aspx to this dir
Copy Build\Layouts\QueryBuilder.aspx to this dir
Copy Build\Layouts\FieldValueDialog.aspx to this dir

Navigate to C:\Program Files\Common Files\microsoft shared\Web Server Extensions\12\TEMPLATE\LAYOUTS\1033

Copy Build\Layouts\1033\QueryBuilder.js to this dir
Copy Build\Layouts\1033\MullivanUtility.js to this dir
Copy Build\Layouts\1033\FieldValueDialog.js to this dir

Navigate to C:\Program Files\Common Files\microsoft shared\Web Server Extensions\12\TEMPLATE\Images

Copy Build\Mullivan.SharePoint.Pages\ReminderSettings.gif to this dir

Navigate to C:\Program Files\Common Files\microsoft shared\Web Server Extensions\12\TEMPLATE\Features

Copy directory Mullivan.SharePoint.Reminders\Features\Reminder to this directory

Now go and open up Command Propmt

type -> cd "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\12\BIN"
type -> stsadm -o installfeature -filename "reminder\feature.xml"
type -> stsadm -o activatefeature -filename "reminder\feature.xml" -url "http://<yourserver>"

Restart

Go to Start -> Administrative Tools -> Services and restart Windows SharePoint Timer Service
Go to Start -> Run and type iisreset and click ok

Sweet!!! You should be good to go now. Navigate to any list and you should see the Reminder Settings item in the Settings menu.

Just to make sure you the Scheduled Reminder Job installed successfully, go to your central admin page. Go to Operations -> Timer Job Status. Make sure "Scheduled Reminder Job" exists and is either initialized or succeeded.


Timer_Job_Status.png

If it's not there then you did not install this feature on your central admin/application server.

Configuration

So the first thing we are going to do is go to Central Admin. Navigate to Application Management -> Web application out-going email settings.

App_Manage_-_Email_Link.png

In order for email to be sent you need to configure you web applications email settings. You'll need to point it to you smtp server so that emails can be sent out to the users.

Email_Settings.png

Now you can go to any list and go to the Reminder Settings.

Menu_Item.gif

Now you see the following page.

Reminder_Settings.png

On this page you'll be able to view, edit, edit, remove alerts/reminders. I don't know why I put the up and down functionality. I realized after I did it that the order is purposeless. I guess I drank too much coffee.

So click add or edit to get the edit reminder settings page.

EditSettings_-_Basic.png

Name - This field is of course our name of our alert. This is just a display name and is not used as a unique key. The unique key is a guid that is used behind the scenes.

Type - This field is either one of two choices: Item or Bulk. Item means that each item pulled back in the query will be in a separate email and your XSLT that you configure at the bottom of the page will only have to transform one item. Bulk means that there will only be one email sent and it is up to you XSLT to transform all the items at once.

Recurrence - This field represents how often your query is going to occur and on what days or time. There are four options: Hourly, Daily, Weekly, and Monthly. For the Hourly option, you will be displayed a Start Minute field that represents the minute (0 - 59) of that our the query will run. The query will then run every hour just after that minute occurs. The Daily option will display a Start Hour field that represents the military hour (0 - 23) that our query will run. The query will then run every day just after that hour occurs. The weekly options will display two fields, one being the day it should run, and the other being the military hour that it should run just after. The Monthly option will display two fields, one being the day number (1 - 31) that it should run on and the other being the hour it should run just after. If you put 31 on a day that only has 28 - 30 days then the query will run on the last day of the month. This is a way to ensure that you'll run the query on the last day.

EditSettings_View_Fields.gif

View Fields - This field is the XML that is used by the query object to know what fields you want back. On the right of the View Field you'll see an option to pull in the XML configuration for any of the views that you have configured for this list.

EditSettings_-_Query.png

Query - This field represents the where and orderby clause in XML. Known as CAML query, this XML is used to by the SPQuery object to generate it's SQL statements for pulling back data for this list. Make sure when you're constructing you're queries that you use the internal name for you're fields. There are a lot of tool out there that will help you build CAML queries. Just run a google search and I'm sure you'll find one quickly. You may also just click the fill button and fill the field with the CAML query for any of your views.

EditSettings_-_Mail_To1.png

Mail To - This field is a semi-colon delimited field that represents all of the email addresses that you want to send this alert/reminder to. There are several place holders that I've built in to allow flexibilty on who the email is sent to. These fields are all User fields in SharePoint. This feature checks for this place holder and checks to see if there is a value in the Item for this field. If so then it queries the SharePoint profile list to get an email address back for that user. If it finds a user then it appends them to the list of recipients. These placeholders will only work if the reminder/alert is set up to be in Item mode. If it is in Bulk mode, they won't work because Items may have different users for one of the fields.

[#assignedto#] - Person that is set to the Site field "Assign To"
[#author#] - Person that created the item
[#editor#] - Person that last modified the item
[#checkoutuser#] - Person that currently has the item checked out
[#<insert user field here(internal name)>#] - Person that currently fills this field

EditSettings_-_Subject.png

Subject - This is the field that is used as the subject text for you alert/reminder in the email.

EditSettings_-_Xslt.png

XSL Transform - This is the field that you use to drop your XSLT transform string into to generate the HTML that is going to be sent into the body of your email. The xml uses special namespaces and also passes in parameters that can be used to help generate your html. That is why the header for you're XSLT should always look like the following:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0"
                xmlns:xsl="<a href="%22%22%22%22%22%22%22http://www.w3.org/1999/XSL/Transform%22%22%22%22%22%22%22">http://www.w3.org/1999/XSL/Transform</a>"
                xmlns:rs="urn:schemas-microsoft-com:rowset"
                xmlns:z="#RowsetSchema">

EditSettings_-_Other_Options.png

Send reminder even if there are no results - Check this option if you are using Bulk mode and would like to send an email even though there are no results returned from your query. This is important because it will let users know that this feature still works, there was just no data returned.

EditSettings_-_Test_Button.png

Test Button - Now that you have all of your configurations set you can click the test button to see what you email is going to look like. This will NOT send any emails out. This is only going to display to you what the email will look like when it is sent. IMPORTANT!!! If you would like to view the raw XML that you're XSLT needs to transform, just leave the XSL Transform field empty and click the test button.

IMPORTANT!!! Keep in mind that all alerts/reminders will run right away for the first time. That is because they have never been run before and alert/reminder will always always pass the validation that says it needs to run.

Code

So I'll first go over the RmdJobDefinition class that Inherits from SPJobDefinition. Again, this is the object that the SharePoint Timer Service uses to execute our scheduled alerts/reminders.

namespace Mullivan.SharePoint.Reminders
{
    public class RmdJobDefinition : SPJobDefinition
    {
        internal const string JOBNAME = "Scheduled Reminder Job";
        public RmdJobDefinition () : base() { }
        public RmdJobDefinition(SPWebApplication webApp)
            : base(JOBNAME, webApp, null, SPJobLockType.Job)
        {
            this.Title = JOBNAME;
        }

Above, you'll see that we are passing in SPJobLockType.Job. That means that the SharePoint Timer service is only going to run one instance of this job at a time in your farm.

        public override void Execute(Guid targetInstanceId)
        {
            int poolCount = 0;
            DateTime dtStart = DateTime.Now;
            RmdProcessCounter counter = new RmdProcessCounter();
            
            try
            {
                if (this.WebApplication.OutboundMailServiceInstance == null)
                    throw new Exception("E-mail is not set up for this Web Application.");
                if (string.IsNullOrEmpty(this.WebApplication.OutboundMailSenderAddress))
                    throw new Exception("Outbound mail sender address is required for this feature.");
                foreach (SPSite spSite in this.WebApplication.Sites)
                {
                    //Increment pool count so we have a reference to how many
                    //threads are running
                    poolCount++;
                    RmdProcessSPSiteContext context = new RmdProcessSPSiteContext();
                    //Pass the counter so that each thread can record
                    //when it is finished processing
                    context.Counter = counter;
                    context.Site = spSite;
                    ThreadPool.QueueUserWorkItem(ProcessSPSite, context);
                }
 
                //Wait until all threads are done processing
                while (counter.Count < poolCount)
                {
                    Thread.Sleep(1000);
                }
            }
            catch (Exception ex)
            {
                Logging.ServiceLog.LogException(ex);
            }
            base.Execute(targetInstanceId);
        }

The Execute method is our intial method that the Timer Service calls. In here we will want to loop through every Site in the web application and start a new thread that will process it. We want to do this so that we can get better performance.

        private void ProcessSPSite(object state)
        {
            RmdProcessSPSiteContext context = (RmdProcessSPSiteContext)state;
            SPSite spSite = null;
            try
            {
                spSite = context.Site;
                foreach (SPWeb spWeb in spSite.AllWebs)
                {
                    ProcessSPWeb(spWeb, context);
                    spWeb.Dispose();
                }
            }
            catch (Exception ex)
            {
                Logging.ServiceLog.LogException(ex);
            }
            finally
            {
                context.Counter.Increment();
                spSite.RootWeb.Dispose();
                spSite.Dispose();
            }
        }

Above, you'll see that we want to go through each SPWeb in the spSite.AllWebs property. AllWebs represents every web in the site collection no matter what sub site it is located in.

        private void ProcessSPWeb(SPWeb spWeb, RmdProcessSPSiteContext context)
        {
            foreach (SPList list in spWeb.Lists)
            {
                try
                {
                    ProcessList(list, context);
                }
                catch (Exception ex)
                {
                    Logging.ServiceLog.LogException(ex);
                }
            }
        }

Now process each list in the web.

        private void ProcessList(SPList list, RmdProcessSPSiteContext context)
        {
            string emailBody = string.Empty;
            string strXml = string.Empty;
            XsltArgumentList argList = null;
            MailAddress[] mailToAddresses = null;
            List<RmdConfiguration> rmdConfigs = null;
            SPListItemCollection items = null;
            rmdConfigs = RmdUtil.GetRmdConfigs(list);
            //There is no configuration so just move on
            if (rmdConfigs == null)
                return;
            //Process each Reminder in the configuration
            foreach (RmdConfiguration rmdConfig in rmdConfigs)
            {
                //Check if it's eligable to run
                if (rmdConfig.Recurrence.CanRun(rmdConfig.LastRun))
                {
                    //Get the items for the list
                    items = RmdUtil.ExecuteQuery(list, rmdConfig.Query, rmdConfig.ViewFields);
                    if (items.Count > 0 || rmdConfig.SendNoResults)
                    {
                        //Get the argument list for the transform
                        argList = RmdUtil.GetXsltArgList(list);
                        if (rmdConfig.Type == RmdType.Bulk)
                        {
                            //Convert the items to xml
                            strXml = RmdUtil.GetXml(items);
                            mailToAddresses = RmdUtil.GetMailToAddresses(rmdConfig.MailTo, null, list);
                            //Transform the items into html
                            emailBody = RmdUtil.RenderHtml(strXml, argList, rmdConfig.XslTransform);
                            SendEmail(mailToAddresses, rmdConfig.Subject, emailBody);
                        }
                        else // Item Mode
                        {
                            //Go through each item and send the email
                            for (int i = 0; i < items.Count; i++)
                            {
                                SPListItem spItem = items[i];
                                strXml = RmdUtil.GetXml(spItem);
                                mailToAddresses = RmdUtil.GetMailToAddresses(rmdConfig.MailTo, spItem, list);
                                //Transform the item into html
                                emailBody = RmdUtil.RenderHtml(strXml, argList, rmdConfig.XslTransform);
                                SendEmail(mailToAddresses, rmdConfig.Subject, emailBody);
                            }
                        }
                    }
                    //Set the LastRun to the current time and then save it back to the 
                    //ParentWeb PropertyBag
                    rmdConfig.LastRun = DateTime.Now;
                    RmdUtil.SetRmdConfigs(list, rmdConfigs);
                }
            }
        }

The above method is a little more complicated because it is where we pull out our configuration from the list and determine if the recurrence is set to run.

        private void SendEmail(MailAddress[] masTo, string subject, string body)
        {
            using (MailMessage mail = new MailMessage())
            {
                mail.From = new MailAddress(this.WebApplication.OutboundMailSenderAddress);
                foreach (MailAddress ma in masTo)
                    mail.To.Add(ma);
                mail.Subject = subject;
                mail.IsBodyHtml = true;
                mail.Body = body;
                SmtpClient smtp = new SmtpClient(this.WebApplication.OutboundMailServiceInstance.Server.Address);
                
                smtp.UseDefaultCredentials = (smtp.Credentials == null);
                smtp.Send(mail);
            }
        }

The final method is our SendEmail method that uses our SharePoint web application settings to send the email. As you can see it will provide us with the address to the SMTP server and the email address that it needs to send from.

OK.. So now lets take a look at a couple methods in our RmdUtil class.

       public static SPListItemCollection ExecuteQuery(SPList list, string query, string viewFields)
        {
            SPQuery spQuery = new SPQuery();
            if (query != null)
                query = query.Trim();
            if(viewFields != null)
                viewFields = viewFields.Trim();
            if (!string.IsNullOrEmpty(query))
                spQuery.Query = query;
            spQuery.IncludeAttachmentUrls = true;
            spQuery.ViewFields = viewFields;
            spQuery.ViewAttributes = "Scope=\"Recursive\"";
            return list.GetItems(spQuery);
        }

Above, you'll see how the query execute's on the list. I just wanted to point out the line that says spQuery.ViewAttributes = "Scope=\"Recursive\"";. This line is important because it tells the query object to display items in all folders.

        public static string RenderHtml(string strXml, XsltArgumentList argList, string strXsl)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("<html>");
            using (TextWriter textWriter = new StringWriter(sb))
            {
                using (HtmlTextWriter writer = new HtmlTextWriter(textWriter))
                {
                    using (StringReader srXsl = new StringReader(strXsl))
                    {
                        using (XmlTextReader xrXsl = new XmlTextReader(srXsl))
                        {
                            using (StringReader srXml = new StringReader(strXml))
                            {
                                using (XmlTextReader xrXml = new XmlTextReader(srXml))
                                {
                                    XslCompiledTransform cmplTrans = new XslCompiledTransform();
                                    cmplTrans.Load(xrXsl);
                                    XmlWriterSettings settings = new XmlWriterSettings();
                                    settings.CheckCharacters = false;
                                    settings.ConformanceLevel = ConformanceLevel.Fragment;
                                    using (XmlWriter xwHtml = XmlWriter.Create(writer, settings))
                                    {
                                        cmplTrans.Transform(xrXml, argList, xwHtml);
                                    }
                                }
                            }
                        }
                    }
                }
                textWriter.Flush();
            }
            sb.Append("</html>");
            return sb.ToString();
        }

If you are curious on how to take an XML string and an XSLT string and convert them to HTML. Well, here is you answer. This method also shows how to pass in an Argument list so that the XSLT can have access to more values that may not be provided in the XML.

        private static MailAddress GetMailAddress(string strAddress, SPListItem item, SPList list)
        {
            try
            {
                string userName = string.Empty;
                int id = -1;
                string fieldName = strAddress.Substring(2, strAddress.Length - 2);
                fieldName = fieldName.Substring(0, fieldName.Length - 2);
                switch (fieldName.ToLower())
                {
                    case "checkoutuser":
                        if (item.File != null && item.File.CheckedOutBy != null)
                            return new MailAddress(item.File.CheckedOutBy.Email);
                        break;
                    case "author":
                        userName = item.Properties["vti_author"].ToString();
                        break;
                    case "editor":
                        userName = item.Properties["vti_modifiedby"].ToString();
                        break;
                    default:
                        //Because the HashTable is Case Sensitive
                        foreach (DictionaryEntry entry in item.Properties)
                        {
                            string key = entry.Key.ToString();
                            if (key.Equals(fieldName, StringComparison.InvariantCultureIgnoreCase))
                            {
                                if(!int.TryParse(entry.Value.ToString(), out id))
                                    return null;;
                                break;
                            }
                        }
                        break;
                }
                using (SPWeb web = list.ParentWeb)
                {
                    SPListItem user = null;
                    if (id < 0)
                        user = GetUserInfo(web, userName);
                    else
                        user = GetUserInfo(web, id);
                    if(user == null)
                        return null;
                    object email = user["EMail"];
                    if (email != null)
                        return new MailAddress(email.ToString());
                    else
                        return null;
                }
            }
            catch (Exception ex)
            {
                Logging.ServiceLog.LogException(ex);
                return null;
            }
        }

        public static SPListItem GetUserInfo(SPWeb web, string loginName)
        {
            try
            {
                SPUser u = web.SiteUsers[loginName];
                
                if (u == null)
                    return null;
                return GetUserInfo(web, u.ID);
            }
            catch (Exception ex)
            {
                Logging.ServiceLog.LogException(ex);
            }
            return null;
        }

        public static SPListItem GetUserInfo(SPWeb web, int id)
        {
            try
            {
                SPList userList = web.SiteUserInfoList;
                SPQuery query = new SPQuery();
                query.ViewFields = 
@"<FieldRef Name=\"ContentTypeId\"/><FieldRef Name=\"ContentType\"/>
<FieldRef Name=\"Title\"/><FieldRef Name=\"Name\"/><FieldRef Name=\"EMail\"/>
<FieldRef Name=\"JobTitle\"/><FieldRef Name=\"WorkCity\"/>
<FieldRef Name=\"WorkState\"/><FieldRef Name=\"WorkCountry\"/>";
                query.Query = string.Format(
@"<Where><Eq><FieldRef Name='ID'/><Value Type='Int'>{0}</Value></Eq></Where>", id); // u.ID is the ID of logged in user
                SPListItemCollection items = userList.GetItems(query);
                if (items.Count > 0)
                    return items[0];
            }
            catch (Exception ex)
            {
                Logging.ServiceLog.LogException(ex);
            }
            return null;
        }

Curious on how to get the SPUser object from an Items Assign To, Created By, or Modified By fields. The above code pulls the users login name out of the results and queries the user profile list to get the users email.

        public static List<RmdConfiguration> GetRmdConfigs(SPList list)
        {
            string configName = GetConfigName(list.ID);
            string configuration = string.Empty;
            //I used the ParentWeb because the SPList object doesn't have a property bag
            using (SPWeb web = list.ParentWeb)
            {
                if (web.Properties.ContainsKey(configName))
                    configuration = list.ParentWeb.Properties[configName];
            }
            if (string.IsNullOrEmpty(configuration))
                return null;
            return RmdConfiguration.ReadConfigurations(configuration);
        }
        public static void SetRmdConfigs(SPList list, List<RmdConfiguration> rmdConfigs)
        {
            string configName = GetConfigName(list.ID);
            string configuration = string.Empty;
            //I used the ParentWeb because the SPList object doesn't have a property bag
            configuration = RmdConfiguration.WriteConfigurations(rmdConfigs);
            using (SPWeb web = list.ParentWeb)
            {
                if (!web.Properties.ContainsKey(configName))
                    web.Properties.Add(configName, configuration);
                else
                    web.Properties[configName] = configuration;
                web.Properties.Update();
                web.Update();
            }
        }

The above code serializes/deserializes our configuration object and stores them into the Lists parent SPWeb Properties collection. This is known as the Property Bag.

Conclusion

Hopefully this feature is going to help you with your SharePoint implementation. Let me know if you have any problems or would like to suggest any new features to be built in. Thanks

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Sike Mullivan
Software Developer (Senior)
United States United States
I am a Sr SharePoint Developer in Saint Louis, MO. I've been developing professionally for about four years now. For three years I worked for a Document Imaging company that developed applications for Scanning, Indexing, Migrating, and Searching SharePoint (MOSS, WSS). I'm now working on a team for a Cable company that customizes their internal and external SharePoint implementations.

Comments and Discussions

 
GeneralMy vote of 5 PinmemberMohamed Sadek Rady20-Jan-12 1:09 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140922.1 | Last Updated 24 Mar 2009
Article Copyright 2009 by Sike Mullivan
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid