Outlook VBA code to auto-increment Actual Work time of a task when it is opened

pixelchef
pixelchef used Ask the Experts™
on
I'd like to automatically increment the "actual work" field of every Outlook Task that is opened (double clicked from the task list). Can someone show me how to do this through VBA? Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
Hi, pixelchef.

Something like this should do it.  This code must go in the ThisOutlookSession module.
Private Sub Application_ItemLoad(ByVal Item As Object)
    If Item.Class = olTask Then
        'Change 5 to whatever time value you want to add to ActualWork'
        Item.ActualWork = Item.ActualWork + 5
        Item.Save
    End If
End Sub

Open in new window

Author

Commented:
That almost works except that I get an error.

2010-10-20-160544.jpg
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
I was expecting the following to work ... but it crashes my outlook and I don't know why ... it starts up again ok so maybe I have a bug because it ought to work!  The code resides in this outlooksession.

th e1 adds 1 minute so scale it up as required.

Chris
Private WithEvents olkTaskItem As Outlook.TaskItem

Private Sub Application_ItemLoad(ByVal Item As Object)

    If Item.Class = olTask Then
        Set olkTaskItem = Item
    End If
    
End Sub

Private Sub olkTAskItem_Open(Cancel As Boolean)
    olkTaskItem.ActualWork = olkTaskItem.ActualWork + 1
    olkTaskItem.Save
End Sub

Open in new window

Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Author

Commented:
Well it works for me on Outlook 2010. However, I think I did not explain my original question clearly. I'd like to increment like a timer. Every minute that the task is open, it would increase actual work by one minute.

If this is not a trivial change, let me know and I'll repost. Thank you.
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
I suspect something could be done with a task timer but from my perspective it'll be a challenge ... is all i'll say.

Chris
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
Have made a quick look and I am still having problems with my install and the earlier code so it's beyond me at the moment.

Chris
Top Expert 2010

Commented:
@pixelchef.

"Every minute that the task is open, it would increase actual work by one minute."
That's very difficult to achieve.  Outlook does not have a built-in timer.  That means using Windows API calls to do the timing.  The problem is that the code has to be able to handle multiple open tasks even if you don't think you'll ever have multiple open tasks.  If the solution is written to handle just one open task and you ever open a second one, then Outlook will crash and you run the risk of losing work.  To handle multiple items at once means writing the code to create and handle multiple timers.  That adds a good bit of complexity.  The solution must also be able to handle multiple tasks to include knowing when each open task is closed.  That adds a lot of complexity.  

The better solution is to not use a timer and not increment/update the task each minute.  Instead, save the time that the task was opened when it opens, then calculate the elapsed time when the item is closed and add that to ActualWork.  This achieves the same outcome (i.e. adding the amount of time the item was open) it just does it when the task is closed rather than updating it every minute.

If you absolutley must have the former (i.e. updating every minute), then the best solution is to create a custom task form and add a timer control to it.  

Author

Commented:
@BlueDevilFan

Your second solution would be just fine. Can you post the code? Obviously, I'm not very familiar with VBA programming in Outlook.
Software Quality Lead Engineer
Top Expert 2011
Commented:
replace the olkTAskItem_Open from my earlier post and add the new version along with close from below.

Chris
Private Sub olkTaskItem_Close(Cancel As Boolean)
    On Error Resume Next
    olkTaskItem.ActualWork = olkTaskItem.ActualWork + DateDiff("n", olkTaskItem.UserProperties("OpenTime"), Now())
    olkTaskItem.UserProperties("OpenTime").Delete
End Sub

Private Sub olkTaskItem_Open(Cancel As Boolean)
    On Error Resume Next
    olkTaskItem.UserProperties.Add "OpenTime", olText
    On Error GoTo 0
    olkTaskItem.UserProperties("OpenTime") = Now
End Sub

Open in new window

Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
Basically when the item is opened I add a variable to hold the required time and then on close I read it to add the relevant number of minutes.  If it is missing then I create it on open, and if missing then I ignore the time addition on close.

Either way the code should run without issue.

Chris

Author

Commented:
Works perfectly! thank you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial