none
Alert users from 30 days to "Due Date"?

    Question

  • HI,

    I have a "Due Date" column as a date field in a sharepoint list.

    Is there a way to alert users before 30 days of "Due Date"? so that the users should receive emails every day from 30 days to "Due Date".The emails which users recive should have a link to the item of the list.

    I assume this can be achieved by creating a timer job.Please help me with code.

    If you get a chance please help me.

    Thanks!

    Saturday, April 18, 2009 6:27 PM

Answers

  • I have not tested this code. This is what you will have to write in the Execute method of a custom Timer Job.
    public override void Execute (Guid contentDbId) 
    {
      //Get a reference to the current site collection's content database
      SPWebApplication webApplication = this.Parent as SPWebApplication;
      SPContentDatabase contentDb = webApplication.ContentDatabases[contentDbId];
      //Get a reference to the "Tasks" list in the RootWeb of the first site collection in the content database
      using (SPSite spSiteCurrent = contentDb.Sites["sites/abcd"])
      {
        using(SPWeb spWebRoot = spSiteCurrent.RootWeb)
    	{
    	    TimeSpan tsDuration = new TimeSpan(30,0,0,0);
    	    DateTime dtDueDate = DateTime.Now.Add(tsDuration);
    		string strISODueDate = SPUtility.CreateISO8601DateTimeFromSystemDateTime(dtDueDate);
    		
    		SPList taskList = spWebRoot.Lists["Tasks"];
    		//Get all the task items due for completion 30 days from Now
    		SPQuery spQryDue = new SPQuery();
    		spQryDue.Query = "<Where><Eq><FieldRef Name='Due_x0020_Date'/><Value Type='DateTime'>" + strISODueDate + "</Value></Eq></Where>";
    		SPListItemCollection spTaskItemColl = taskList.GetItems(spQry);
    		       
    		StringBuilder sbMessage = new StringBuilder();
    		StringDictionary sdMailHeader = new StringDictionary();
    		sdMailHeader.Add("To",string.Empty);
    		sdMailHeader.Add("Subject","Task Reminder");
    				
    		foreach(SPListItem spTaskItem in spTaskItemColl)
    		{
    		  string fieldValue = Convert.ToString(task["Assigned To"]);
    		  SPFieldUserValue assignedTo = (SPFieldUserValue) spTaskItem.Fields["Assigned To"].GetFieldValue(fieldValue);
    		  
    		  sbMessage.Append("Hi ");
    		  sbMessage.Append(assignedTo.User.Name);
    		  //Build the Email Message
    		  ........................................
    		  sdMailHeader["To"] = assignedTo.User.Email;
    		  SPUtility.SendEmail(spWebRoot,sdMailHeader,sbMessage.ToString(),true);		  
    		}
    	}
      }
     }
    Regards
    Prasad
    • Marked as answer by sirii Monday, April 20, 2009 2:06 PM
    Sunday, April 19, 2009 9:00 AM

All replies

  • Howdy,

    Please refer the following link to create and deploy the Timer Jobs in shareepoint :
    http://www.andrewconnell.com/blog/articles/CreatingCustomSharePointTimerJobs.aspx

    In this link you can find Execute method in TaskLoggerJob class. Write your code to send email alert to the users in this method.

    I have used this sample in many projects. Thanks a lot to Andrew Connell.

    Hope this will help.

    Thanks,


    Gopinath D - http://sharepointsnippets.com
    Saturday, April 18, 2009 6:52 PM
  • There is an extensive guide on the MSDN web site already:

    http://msdn.microsoft.com/en-us/library/cc406686.aspx

    Look at how to interact with SharePoint lists in the guide (they use it for TimerJobData, but the concept is the same for reading from any other list). To send your e-mail alert, just call the SPUtility.SendEmail() method.


    Anthony Mallgren
    Disruptive Technology LLC
    Saturday, April 18, 2009 6:53 PM
  • Thanks gopi!, I am not a good programer.Can you please help me with code which I need

    Saturday, April 18, 2009 6:55 PM
  • I have not tested this code. This is what you will have to write in the Execute method of a custom Timer Job.
    public override void Execute (Guid contentDbId) 
    {
      //Get a reference to the current site collection's content database
      SPWebApplication webApplication = this.Parent as SPWebApplication;
      SPContentDatabase contentDb = webApplication.ContentDatabases[contentDbId];
      //Get a reference to the "Tasks" list in the RootWeb of the first site collection in the content database
      using (SPSite spSiteCurrent = contentDb.Sites["sites/abcd"])
      {
        using(SPWeb spWebRoot = spSiteCurrent.RootWeb)
    	{
    	    TimeSpan tsDuration = new TimeSpan(30,0,0,0);
    	    DateTime dtDueDate = DateTime.Now.Add(tsDuration);
    		string strISODueDate = SPUtility.CreateISO8601DateTimeFromSystemDateTime(dtDueDate);
    		
    		SPList taskList = spWebRoot.Lists["Tasks"];
    		//Get all the task items due for completion 30 days from Now
    		SPQuery spQryDue = new SPQuery();
    		spQryDue.Query = "<Where><Eq><FieldRef Name='Due_x0020_Date'/><Value Type='DateTime'>" + strISODueDate + "</Value></Eq></Where>";
    		SPListItemCollection spTaskItemColl = taskList.GetItems(spQry);
    		       
    		StringBuilder sbMessage = new StringBuilder();
    		StringDictionary sdMailHeader = new StringDictionary();
    		sdMailHeader.Add("To",string.Empty);
    		sdMailHeader.Add("Subject","Task Reminder");
    				
    		foreach(SPListItem spTaskItem in spTaskItemColl)
    		{
    		  string fieldValue = Convert.ToString(task["Assigned To"]);
    		  SPFieldUserValue assignedTo = (SPFieldUserValue) spTaskItem.Fields["Assigned To"].GetFieldValue(fieldValue);
    		  
    		  sbMessage.Append("Hi ");
    		  sbMessage.Append(assignedTo.User.Name);
    		  //Build the Email Message
    		  ........................................
    		  sdMailHeader["To"] = assignedTo.User.Email;
    		  SPUtility.SendEmail(spWebRoot,sdMailHeader,sbMessage.ToString(),true);		  
    		}
    	}
      }
     }
    Regards
    Prasad
    • Marked as answer by sirii Monday, April 20, 2009 2:06 PM
    Sunday, April 19, 2009 9:00 AM
  • Thanks Prasad
    Monday, April 20, 2009 2:05 PM
  •  

    using System;

    using System.Collections.Generic;

    using System.Text;

    using Microsoft.SharePoint;

    using Microsoft.SharePoint.Administration;

    using Microsoft.SharePoint.Utilities;

    using System.Net.Mail;

    using System.Net;

     

    namespace DueDateNotificationJob

    {

        class DueDateNotification : SPJobDefinition

        {

            /// <summary>

            /// Initializes a new instance of the DueDateNotification class.

            /// </summary>

            public DueDateNotification()

                : base()

            {

     

            }

     

            /// <summary>

            /// Initializes a new instance of the DueDateNotification class.

            /// </summary>

            /// <param name="jobName">Name of the job.</param>

            /// <param name="service">The service.</param>

            /// <param name="server">The server.</param>

            /// <param name="targetType">Type of the target.</param>

            public DueDateNotification(string jobName, SPService service, SPServer server, SPJobLockType targetType)

                : base(jobName, service, server, targetType)

            {

            }

     

            /// <summary>

            /// Initializes a new instance of the DueDateNotification class.

            /// </summary>

            /// <param name="jobName">Name of the job.</param>

            /// <param name="webApplication">The web application.</param>

            public DueDateNotification(string jobName, SPWebApplication webApplication)

                : base(jobName, webApplication, null, SPJobLockType.ContentDatabase)

            {

                this.Title = "DueDateNotification";

            }

     

            /// <summary>

            /// Executes the specified content db id.

            /// </summary>

            /// <param name="contentDbId">The content db id.</param>

            public override void Execute(Guid contentDbId)

            {

                // get a reference to the current site collection's content database

                SPWebApplication webApplication = this.Parent as SPWebApplication;

                SPContentDatabase contentDb = webApplication.ContentDatabases[contentDbId];

     

                // get a reference to the "Test" list in the in the content database

                //SPList testList = contentDb.Sites[0].OpenWeb("URL").Lists["TestList"];

                SPList testList = contentDb.Sites[0].OpenWeb("URL").Lists["TestList"];

     

                /*SPQuery query = new SPQuery();

                query.Query = "<Where><Leq><FieldRef Name='DueDate' /><Value Type='DateTime'>[Today+30Day(s)]</Value></Leq></Where>";

                SPListItemCollection myItems = testList.GetItems(query);   */

                foreach (SPListItem item in testList.Items)

                {

     

                    DateTime Today = DateTime.Today;

                    DateTime DueDt = (DateTime)Convert.ToDateTime(item["DueDate"]);

                    TimeSpan Span = DueDt - Today;

                    int days = (int)Span.TotalDays;

                    if (days <= 180)

                    {

                        if (item["status"] != null)

                        {

                            if (item["Sourcing Manager"] != null)

                            {

                                if (item["Sourcing Director"] != null)

                                {

     

                                    string encAbsUrl = item["Encoded Absolute URL"].ToString();

     

                                    string url = encAbsUrl.Substring(0, encAbsUrl.LastIndexOf("/")) + "/Forms/DispForm.aspx?ID=" + item["ID"];

                                    string messg = "Due date for " + item["Vendor"] + " is approaching. Click here to go to the link. " + url;

                                    string SrcMgr = item["Sourcing Manager"].ToString();

                                    string SrcDir = item["Sourcing Director"].ToString();

     

                                    if (item["status"].ToString() == "Active")

                                    {

                                        {

                                            SPUtility.SendEmail(contentDb.Sites[0].OpenWeb("URL"), false, false,

                                                SrcMgr.ToString(), "Due Date Notification",

                                                messg.ToString());

     

                                            item["NotificationStatus Manager"] = "Sent Email To This User   " + DateTime.Now.ToString();

     

                                            item.Update();

     

                                            if (days <= 30)

                                            {

     

                                                SPUtility.SendEmail(contentDb.Sites[0].OpenWeb("URL"), false, false,

                                                    SrcDir.ToString(), "Due Date Notification",

                                                    messg.ToString());

                                                item["NotificationStatus Director"] = "Sent Email To This User   " + DateTime.Now.ToString();

                                                item.Update();

                                            }

                                        }

                                        if (days < 0)

                                        {

                                            if (item["Risk"] == null)

                                            {

                                                item["Risk"] = "Risk";

                                                item.Update();

     

                                            }

                                        }

                                        else if (days >= 0)

                                        {

                                            item["Risk"] = null;

                                            item.Update();

     

                                        }

                                    }

                                    else if (item["status"].ToString() == "Research")

                                    {

                                        {

     

                                            if (days <= 7)

                                            {

                                                SPUtility.SendEmail(contentDb.Sites[0].OpenWeb("URL"), false, false,

                                                    SrcMgr.ToString(), "Due Date Notification",

                                                    messg.ToString());

                                                SPUtility.SendEmail(contentDb.Sites[0].OpenWeb("URL"), false, false,

                                                    SrcDir.ToString(), "Due Date Notification",

                                                    messg.ToString());

                                                item["NotificationStatus Manager"] = "Sent Email To This User   " + DateTime.Now.ToString();

                                                item.Update();

                                                item["NotificationStatus Director"] = "Sent Email To This User   " + DateTime.Now.ToString();

                                                item.Update();

                                            }

     

                                            else if (days < 30)

                                            {

                                                if (days > 8)

                                                {

                                                    if ((days % 7) == 0)

                                                    {

                                                        SPUtility.SendEmail(contentDb.Sites[0].OpenWeb("URL"), false, false,

                                                          SrcMgr.ToString(), "Due Date Notification",

                                                        messg.ToString());

                                                        SPUtility.SendEmail(contentDb.Sites[0].OpenWeb("URL"), false, false,

                                                        SrcDir.ToString(), "Due Date Notification",

                                                        messg.ToString());

                                                        item.Update();

                                                        item["NotificationStatus Manager"] = "Sent Email To This User   " + DateTime.Now.ToString();

                                                        item.Update();

                                                        item["NotificationStatus Director"] = "Sent Email To This User   " + DateTime.Now.ToString();

                                                        item.Update();

                                                    }

                                                }

                                            }

     

                                            else if (days <= 180)

                                            {

                                                if (days >= 30)

                                                {

                                                    if ((days % 30) == 0)

                                                    {

                                                        SPUtility.SendEmail(contentDb.Sites[0].OpenWeb("URL"), false, false,

                                                                    SrcMgr.ToString(), "Due Date Notification",

                                                                 messg.ToString());

     

                                                        item["NotificationStatus Manager"] = "Sent Email To This User   " + DateTime.Now.ToString();

                                                        item.Update();

                                                    }

                                                }

                                            }

                                        }

                                        if (days < 0)

                                        {

                                            if (item["Risk"] == null)

                                            {

                                                item["Risk"] = "Risk";

                                                item.Update();

     

                                            }

                                        }

                                        else if (days >= 0)

                                        {

                                            item["Risk"] = null;

                                            item.Update();

     

                                        }

     

                                    }

                                    else

                                    {

                                        item["NotificationStatus Manager"] = " ";

                                        item.Update();

                                        item["NotificationStatus Director"] = "";

                                        item.Update();

                                        item["Risk"] = null;

                                        item.Update();

                                    }

                                }

                            }

                        }

                    }

                }

            }

        }

    }

     

    Tuesday, May 26, 2009 9:25 PM