by on February 5, 2009 9 Comments

Use VBA To Reset Task Due Dates in Outlook

There was an earlier version of this post. It had more words and as it went on and on about how I track damn near everything in Outlook. It’s true. Everything. If I don’t track something I want to do or have to do as an Outlook task, it won’t get done.

I deleted that post by accident as I thought I was deleting something else. As it turns out, the wrong Window had the focus when I hit the Delete button and I deleted this post. Dammit!

Anyways, I always fall way behind on the deadlines I set for my tasks. So each Monday morning, I set the deadlines to a week from the current day.

Here is the VBA code to achieve this.

Sub ResetTaskDeadlines()
    Dim ns As NameSpace
    Dim fld As Folder
    Dim task As TaskItem
    Dim items As Outlook.items
    Dim rest As Outlook.items
    Dim filterSQL As String

    Set ns = Application.GetNamespace("MAPI")
    Set fld = ns.GetDefaultFolder(olFolderTasks)
    Set items = fld.items
    filterSQL = "@SQL=" & """http://schemas.microsoft.com/mapi/id/"
    filterSQL = filterSQL & "{00062003-0000-0000-C000-000000000046}/"
    filterSQL = filterSQL & "81010003""" & " <> 2"
    Set rest = items.Restrict("[Status] <> 'Completed'")

    For Each task In rest
    ''UNCOMMENT THE OPTION YOU WANT TO USE
        ''task.DueDate = "1/1/4501" 'Set to NO Due Date
        ''task.DueDate = task.DueDate + 7 ' Set to 7 days from current due date
        task.DueDate = today() + 7 'Set to a week from today
        ''task.DueDate = FirstDayOfNextMonth ' Set to first day of next month
        'task.Save
    Next

End Sub

Function FirstDayOfNextMonth() As Date
   Dim d As Date
   d = Date
   FirstDayOfNextMonth = DateSerial(Year(d), Month(d) + 1, 1)
End Function

I pretty much use VSTO to build Outlook add-ins professionally. But for quick personally useful scripts like this, VBA still reins.

Filed Under: Featured, Office Development, Outlook, Samples Tagged With: , , , , , , , , , ,

Comments

  1. Korayem says:
    February 11, 2009 at 4:39 am

    Awesome. Just showed this to my boss and he loves it. I even assigned a shortcut for this macro…

  2. Mark says:
    April 23, 2009 at 7:26 pm

    What was the purpose of creating and populating the filterSQL string? It doesn’t appear to get used – Am I missing something?

  3. Ty says:
    May 2, 2009 at 9:54 pm

    it’s another filter that isn’t used in this code sample. It can be used in Items.Restrict(…) . I believe it will restrict the items collection to the same filter (<> ‘Completed’). The difference is the unused filter string references the MAPI folder id and is a bit of a pain to figure out.

  4. Danny Puype says:
    June 21, 2009 at 2:06 am

    This is nice -
    however I tried it out, but can’t get it to work in my Outlook 2007 – …
    tried a bit of everything, but don’t see it – dommage :-)

    It seems the macro rolls through, but there’s no records in the recordset…
    wonder whether it could be linked to the default folder?
    I don’t get an error message, just goes through it doing nothing.
    Tried to fiddle around with different settings for rest var with different fields too, but no luck -

    Also I wondered why you use the today() function, shouldn’t that be date()?

    Any suggestions much welcomed of course
    rgds

  5. Ty says:
    June 22, 2009 at 8:14 pm

    It is mapped to the default folder. If you want to check a different folder you need to grab a reference to it using the Namespace object (ns in the code).

    Maybe call the PickFolder method and ask the user which folder to work against. Something like:

    Set fld = ns.PickFolder()

    I use the today() function because I am an old VBA developer and I like the today() function. It works.

  6. Danny says:
    June 27, 2009 at 3:42 pm

    Thanks for responding -
    it’s the default folder actually I’m using.
    I tried the ns.PickFolder() method, but same result – code runs but nothing happens…

    Don’t see it – so I’m stuck here basically.
    Would be lovely if this works

  7. Danny says:
    June 27, 2009 at 3:46 pm

    hey got it ! :-)

    hadn’t realised this, but task.save was out-commented !
    I would have imagined that setting task.Duedate was enough, but this is VBA isn’t :-)

    thanks

  8. Bonnie says:
    October 9, 2009 at 4:43 pm

    I want to use a vba script to change due date for selected tasks to a certain date, can someone tell me how to write that VBA? Thanks.

  9. Marcus says:
    February 18, 2010 at 7:33 am

    I am trying to get the Status property of an Outlook task using VBA but whenever I try to reference it I get the error object doesn’t support this property or method.

    Set taskItems = itTasks.Items

    For Each task In taskItems
    count = count + 1

    statusText = ""
    With task
    creationTime = .creationTime . Works
    subject = .subject ' Works
    statusText = .Status ' Does not work
    End With

    I can get the subject and the creationTime but how do I get the status?
    I can't seem to get it in the same manner.
    I am using Microsoft Outlook 2003, (the mail box is on an Exchange Server 2007 server , same DOMAIN same LAN)

Speak Your Mind

*