Office Space: Tips and Tricks for Scripting Microsoft Office Applications

Welcome to Office Space, the column that offers tips and tricks for scripting Microsoft® Office applications. We’ll post new tips every Tuesday and Thursday; to see an archive of previous tips, visit the Office Space Archive. And if you have particular questions about Microsoft Office scripting, feel free to send them to scripter@microsoft.com (in English, if possible). We can’t promise to answer all the questions we receive, but we’ll do our best.

Working with Outlook Tasks

Any time an Exchange server goes down here at Microsoft business screeches to a halt. This is because – hold on a second….

Our manager suggests we change that first sentence to this: If an Exchange server ever went down at Microsoft business would screech to a halt. We – wait a minute….

Fine: In the highly-unlikely and downright impossible event that an Exchange server – well, you get the idea.

The point is, Exchange is an important part of day-to-day life here at Microsoft. For one thing, a huge percentage of people refer to their co-workers not by name but by email address. Bill Gates? Who – oh, you mean billg. Well then why didn’t you say so?

For another, many people have their meetings and appointments recorded solely in Microsoft Outlook. If a tree falls in the forest and no one is around, we have no idea whether or not it makes a sound. But we do know this: if an appointment isn’t in Outlook, then that appointment doesn’t exist. And no one can make you go to it.

In other words, the calendar and scheduling portion of Outlook has become extremely popular; people simply can’t live without their Outlook Calendar. Oddly enough, though, these same people are always making to-do lists, but they aren’t saving them in electronic format; instead, they write tasks down on the backs of envelopes, they attach sticky notes to their monitors, they scribble items in the margins of legal pads. If only Outlook had a built-in mechanism for keeping tracks of all the things you have to do!

Well, needless to say, Outlook does have a built-in mechanism for keeping track of all the things you have to do; for some reason, however, Outlook Tasks have never caught on the way the Outlook Calendar has. (For example, many people schedule appointments with themselves rather than creating a task with a specific due date.) Why don’t people take advantage of Outlook Tasks? Well, there’s only one possible answer to that question: they have no idea how easy it is to script Outlook Tasks.

Until now. Need to create a new task? Well, here’s a script that does that very thing:

Const olTaskItem = 3

Set objOutlook = CreateObject("Outlook.Application")
Set objTask = objOutlook.CreateItem(olTaskItem)

objTask.Subject = "Script Center Master Plan"
objTask.Body = "Final report for Script Center master plan."
objTask.ReminderSet = True
objTask.ReminderTime = #10/10/2005 12:00 PM#
objTask.DueDate = #10/11/2005 12:00 PM# 
objTask.ReminderPlaySound = True
objTask.ReminderSoundFile = "C:\Windows\Media\Ding.wav"

objTask.Save

We told you it was easy. The script begins by defining a constant named olTaskItem and setting the value to 3; we’ll use this later on to actually create our task. We create an instance of the Outlook.Application object, and then use this line of code to create an instance of the TaskItem object:

Set objTask = objOutlook.CreateItem(olTaskItem)

Nothing too fancy there: we simply call the CreateItem method, passing the constant olTaskItem as a way of ensuring that Outlook creates a new task.

We then have several lines of code that configure property values for our new task:

objTask.Subject = "Script Center Master Plan"
objTask.Body = "Final report for Script Center master plan."
objTask.DueDate = #10/11/2005 12:00 PM# 
objTask.ReminderSet = True
objTask.ReminderTime = #10/10/2005 12:00 PM#
objTask.ReminderPlaySound = True
objTask.ReminderSoundFile = "C:\Windows\Media\Ding.wav"

You shouldn’t have much trouble figuring out what these properties are for; basically we’re creating a task named Script Center Master Plan that needs to be done by noon on October 11, 2005. We’ve also set a reminder for this task; exactly 24 hours in advance (noon on October 10, 2005) we’ll get a reminder, accompanied by the stirring sounds of Ding.wav. All in all, pretty simple.

That gives us a new task in memory. To actually save this task and get it added to Outlook Tasks, we need to call the Save method:

objTask.Save

Very easy.

Some of you look a little skeptical, though; after all, how do we know that the task was actually added to Outlook Tasks? Well, we could always run a script like this one, a script that returns a list of all the Outlook tasks:

Const olFolderTasks = 13

Set objOutlook = CreateObject("Outlook.Application")
Set objNamespace = objOutlook.GetNamespace("MAPI")
Set objFolder = objNamespace.GetDefaultFolder(olFolderTasks)

Set colTasks = objFolder.Items

For Each objTask In colTasks
    Wscript.Echo "Subject: " & objTask.Subject
    Wscript.Echo "Date due: " & objTask.DueDate 
    Wscript.Echo "Percent complete: " & objTask.PercentComplete 

    Select Case objTask.Status
        Case 0
            Wscript.Echo "Status: Not started."
        Case 1
            Wscript.Echo "Status: In progress."
        Case 2
            Wscript.Echo "Status: Complete."
        Case 3
            Wscript.Echo "Status: Waiting."
        Case 4
            Wscript.Echo "Status: Deferred."
    End Select

    Select Case objTask.Importance
        Case 0
            Wscript.Echo "Importance: Low"
        Case 1
            Wscript.Echo "Importance: Normal"
        Case 2
            Wscript.Echo "Importance: High"
        End Select

    Wscript.Echo "Last modified: " & objTask.LastModificationTime 
Next

Another script so simple that it requires little explanation. This script starts out by defining a constant – olFolderTasks – and setting the value to 13; we’ll use this constant to tell Outlook which folder to retrieve information from. We create an instance of the Outlook.Application object, then use the GetNamespace method to connect to the MAPI namespace. (As we’ve noted in previous columns, this is the only namespace you can connect to. However, this line of code must still be included in your script.) To connect to the Tasks folder we then call the GetDefaultFolder method, passing along the constant that tells Outlook which folder to access:

Set objFolder = objNamespace.GetDefaultFolder(olFolderTasks)

After making our connection it takes just a single line of code to retrieve a collection of all the items found in the Tasks folder:

Set colTasks = objFolder.Items

From there we set up a For Each loop and simply walk through the collection, echoing back the values for properties such as Subject, DateDue, and PercentComplete. You might notice that one property value we don’t retrieve is Body. That’s because retrieving the Body property would trigger an Outlook security alert, and force us to respond to a message box giving the script the right to access the tasks. Bypassing the Body property allows us to bypass the security alert, and thus run the script in unattended fashion.

You might also have noticed that we used Select Case statements when dealing with Status and Importance. That’s because both of these properties store values as integers; in the case of Status, for example, a 0 means that the task has not been started, a 1 means that the task is in progress, etc. Rather than echo these seemingly-meaningless integer values, we use Select Case to analyze the value and report back an appropriate message. For example, if the Status is equal to 2 we echo back the fact that the task has been completed:

Case 2
    Wscript.Echo "Status: Complete."

Cool, huh?

You can also employ filters to limit the type of tasks returned; for example, you might be interested only in returning active tasks (that is, tasks not yet complete). Here’s a sample script that does that very thing:

Const olFolderTasks = 13

Set objOutlook = CreateObject("Outlook.Application")
Set objNamespace = objOutlook.GetNamespace("MAPI")
Set objFolder = objNamespace.GetDefaultFolder(olFolderTasks)

Set colItems = objFolder.Items
strFilter = "[Complete] = FALSE"
Set colTasks = colItems.Restrict(strFilter)

For Each objTask In colTasks
    Wscript.Echo "Subject: " & objTask.Subject
    Wscript.Echo "Date due: " & objTask.DueDate 
    Wscript.Echo "Percent complete: " & objTask.PercentComplete 

    Select Case objTask.Status
        Case 0
            Wscript.Echo "Status: Not started."
        Case 1
            Wscript.Echo "Status: In progress."
        Case 2
            Wscript.Echo "Status: Complete."
        Case 3
            Wscript.Echo "Status: Waiting."
        Case 4
            Wscript.Echo "Status: Deferred."
    End Select

    Select Case objTask.Importance
        Case 0
            Wscript.Echo "Importance: Low"
        Case 1
            Wscript.Echo "Importance: Normal"
        Case 2
            Wscript.Echo "Importance: High"
        End Select

    Wscript.Echo "Last modified: " & objTask.LastModificationTime 
Next

We won’t discuss filters in any detail today; for more information see one of our previous columns dealing with Microsoft Outlook. We will note, however, that setting a filter requires just two lines of code, one to specify the filter (in this case, we’re interested only in tasks where the Complete property is equal to False) and one to retrieve a new collection of items based on the specified criteria:

strFilter = "[Complete] = FALSE"
Set colTasks = colItems.Restrict(strFilter)

In our For Each loop we then walk through this new, filtered collection rather than the original collection, the one that included all the tasks.

Of course, we’ve saved the best for last. Have something on your to-do list that you don’t really want to do? Then just delete the task:

Const olFolderTasks = 13

Set objOutlook = CreateObject("Outlook.Application")
Set objNamespace = objOutlook.GetNamespace("MAPI")
Set objFolder = objNamespace.GetDefaultFolder(olFolderTasks)

Set colItems = objFolder.Items

strFilter = "[Subject] = 'Script Center Master Plan'"
Set colTasks = colItems.Restrict(strFilter)

For Each objTask In colTasks
    objTask.Delete 
Next

As you can see, this script is similar to the previous script, the one that limited returned data to completed tasks. In this case, we’ve set a filter that returns only tasks that have the subject Script Center Master Plan:

strFilter = "[Subject] = 'Script Center Master Plan'"
Set colTasks = colItems.Restrict(strFilter)

For each task in the returned collection (we’re assuming that task subjects are unique, although they don’t have to be) we then call the Delete method to remove the item from our to-do list:

objTask.Delete

And now you don’t have to worry about the Script Center Master Plan. You know what they say: if it’s not in Outlook, it’s not real.