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
  • 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 24

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.
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your 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

dlethe 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Converting Excel to OFX 2 104
SCCM 2012 SP1 to SCCM SP2 upgrade 6 108
Outlook 365 8 67
How to make a exe file from access 2013 database 5 216
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…
Citrix XenApp, Internet Explorer 11 set to Enterprise Mode and using central hosted sites.xml file.
Windows 8 comes with a dramatically different user interface known as Metro. Notably missing from the new interface is a Start button and Start Menu. Many users do not like it, much preferring the interface of earlier versions — Windows 7, Windows X…
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 …

785 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