Business Problem

One of my clients wanted to send email reminders to users one week prior to the project assigned tasks Due Date.

Solution

As you may or may not know, MOSS does not have that built-in functionality, but it is easily accomplished via a workflow created using the SharePoint Designer 2007.

Architecture

Calculated Column
I feel that working with complex date calculations (view my other post for a more date formulas  SharePoint List Calculated Column - Calculate Business Days) are best done on the SharePoint List side.  Therefore, I decided to have a calculated column that would already have the DateTime for when I need to send my Email Alert.

In this scenario it was fairly straight forward and so my formula simply looks like this:


This is what the column looks like when viewing the list items.  If you look closely, you will see that the column Days Prior To Due Date shows 3 days, but my email reminder column says the date to send my email is 6/21/2008.  The reason is that the formula on that column excludes weekend days!   

Workflow

The first Step on the workflow – Set Reminder Time

• I use the Add Time To Date Action, but don’t actually add time, I simply capture the value of the column TaskEmailReminderDate and store it on a variable called ReminderDate for use on my Step 2.
• Store the Task Title on a variable called SubjectTaskTitle, used when sending my email
• So that I can debug, I simply use the Log To History List Action to view the value of the ReminderDate variable

The Second Step on workflow – Send Task Reminder Email

There are no conditions, this step simply uses the Pause Until Date Action and uses the variable on Step 1 called ReminderDate to send out my email.



NOTE:
When I was testing my workflow, I was of course logged in as the “Administrator”, and so the workflow always failed to start automatically!  View this KB article for details.  My resolution was to simply create another account in AD and grant that account admin rights to my portal.  This prevented me from updating the Application Pool Identity for my Web Application.

Well I hope this helps you and let me know if it did!

Cheers,
Oscar