Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Send an email when task is NOT completed.

Posted on 2014-01-14
Medium Priority
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.
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.


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 2000 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

The canonical version of this article is on my web site here: A companion presentation is available here:
Ever notice how you can't use a new drive in Windows without having Windows assigning a Disk Signature?  Ever have a signature collision problem (especially with Virtual Machines?)  This article is intended to help you understand what's going on and…
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…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

688 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