Send an email when task is NOT completed.

Posted on 2014-01-14
Last Modified: 2014-09-08
Hello all,

Is there a way that I can have an automatic email sent to a distribution list if a person does not complete a task by the end of the day?  This person is a single-point of failure for updates so if something happens and he does not complete the task we want to be aware of it.  My thought was to have something in place where he would go to mark the task as complete.  If that does not happen then an email is sent to others so we know that the data has not been updated.

This would occur on a workday basis. I looked at VBA, Outlook and Exchange, but wasn't sure how to proceed.  In Outlook, I could do the opposite and have it send an email every time it is completed, but that would flood the inbox of the users on the dist list.  

I am not cemented to doing this from Outlook.  It could be Access, SharePoint, etc.  If there is another route, I am willing to give it a try.

Thanks for any pointers.
~ M ~
Question by:MHuss
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
LVL 47

Expert Comment

ID: 39781359
Tricky, but the process would certainly have to involve using a database using the task name, and last_completed_timestamp field.

At 00:00 hours, you search through that list and see if any last_completed_timestamp values are > 24 hours.  If not, skip over it.  If yes, you construct an email message with the task name & last completed time.

Now the tricky part is forcing a human to click a button or run an app that marks he last_completed_timestamp field for every task name as it is accomplished.   A task being completed is subjective and there are way too many corner cases to think of automating such things.  So you're going to have to create some checklist code.

Outlook does have a todo list, and one can write code to query and modify the list, and even script creating emails, but this would probably require a few hours worth of contract programming for somebody to crank it out for you.
LVL 25

Expert Comment

by:Lionel MM
ID: 39782383
What type of environment are you in--do you use Exchange, or Office 365 or Google for inter office communications? Knowing this will help with what type of suggestions we can give you. You could also make a very simple batch file that the user has to run when done but as stated above that requires the user to run the batch file. What specifically is the task you want to monitor--is it something that we can look for, to do a query against to see if it is done?
LVL 47

Expert Comment

ID: 39782408
Thinking about it further, since the ONLY way you know if a task is done is if the user effectively tells the computer it is done, (because the computer will never be able to tell if tasks have really been completed, like follow-up is required or it is 97% done but the 3% isn't relevant in a particular situation but still it was completed) ...

Then your app is only good if the user tells the app the job is done.  

So that being the case, do something very simple.  Have the user email you when the job is complete for the day.  No email = task was not done.

Sometimes it is just easier to think outside the box.
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.


Author Comment

ID: 39791273
Thank you for the replies, each of you.

I was toying with a solution along the line of what dlethe mentioned regarding using Outlook and VBA.  I am proficient in VBA, but have never used it in conjunction with Outlook.  My thought had been to create a task for every work day and if that task did not show as 100% completed by the end of the day (midnight), fire off an email to a distribution list.  

My road block came in trying to think through how to apply that trigger to something that doesn't change versus something that does change.  I also was unsure how it would trigger if it was link to the client side of Outlook, when that application would not be open to run the process.  Is it possible to set this on a mailbox and have it execute from the Exchange server?

What I have done temporarily is create a SQLtable.  And using the workday function with a holiday table, I have populated the table with all of the possible dates between now and x number of months out.  I added two fields, one noting task complete and another stating notification-sent.  I gave the single-point-of-failure person an interface that takes him directly to the current date and allows him to put in a check mark when he has completed the task.  

In another interface that is opened every morning by numerous people, I placed some code that looks at the SQL table to see if both the completion and notification-sent fields of the previous workday are false and if so, it sends an email to the distribution list and changes the notification-sent field to true so that no one else fires the trigger when they open the application.  

This will work, but I would like to eliminate the need for the trigger to be reliant on someone else doing something and it also uses that client's Outlook account to send the email, so that strange too.  The single-point-of-failure person will always have to do something to show completion and I need to be able to track those times when that does not happen.  

So I could go either way with this.  Via Outlook VBA, I think I would have even more questions before I could successfully complete the task and I would be a bit more concerned with some of Outlook's task oddiites.  

Unless you see something I am not, I am thinking the best thing to do now is set a trigger in the SQL server rather than in a client-side app.  I would appreciate any direction you can give me on best practices in doing that.  I don't believe dbmail is setup on the SQL server as of yet, but I would like to remedy that very soon.

Once again, thanks,
~ M ~
LVL 47

Expert Comment

ID: 39791283
Look at the LAST_MODIFIED timestamp of the file they edit.  If it is > 24 hours they never changed it.   There is also a LAST_ACCESSED timestamp to see if they even opened it up to read, but that isn't going to be very reliable since this doesn't necessarily mean they read it.

I can't remember off top of my head how to get this from VBS, but these timestamps are obtained easily enough from a C program.  Even windows explorer has a search to see when a file was last changed, (which is the same thing), so there are certainly ways to get it.

So trigger is to enumerate the list of files, look at the LAST_MODIFIED, convert to a # of seconds, and compare against the system clock at the time you begin.

Certainly SQL has ways to get the timestamps and do the comparison, but that is probably overkill and would make this run a lot slower.
LVL 47

Accepted Solution

David earned 500 total points
ID: 39791291
Here is a link for a very simple powershell function to generate an email, so you can just modify it to pass it the body of the email with the timestamp-generated list.   No need to launch outlook.

Here is another that sends username/password if you have to use authentication to send email

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The password reset disk is often mentioned as the best solution to deal with the lost Windows password problem. In Windows 2008, 7, Vista and XP, a password reset disk can be easily created. But besides Windows 7/Vista/XP, Windows Server 2008 and ot…
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Suggested Courses

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question