Link to home
Create AccountLog in
Avatar of MHuss

asked on

Send an email when task is NOT completed.

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 ~
Avatar of David
Flag of United States of America image

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.
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?
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.
Avatar of MHuss


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 ~
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.
Avatar of David
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account