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

Comment Utility
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

Comment Utility
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

Comment Utility
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.
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.


Author Comment

Comment Utility
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

Comment Utility
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
Comment Utility
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS PAint how to type 14 122
cron.exe SCOM alert 3 45
Excel Hangs / Not enough Memory 5 64
Windows Server 2012 Data Center Edition 11 56
We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now