Friday, April 23, 2010

Outlook Email to Task Macro

For several months I have been using ClearContext to categorize and sort my Outlook inbox. One of the features that I liked most about CC is it's abilities to easily create appointments or tasks from an email. However, once the 90 day free trial of ClearContext Pro expires, these utilities are disabled.

Many people probably use the Follow Up flag on the email and call it a day. In fact, I was completely happy with this until just recently. The shortfall with using "follow up" is that it does not create a "task". Both "follow ups" and "tasks" appear in your to-do list in Outlook, and both "follow ups" and "tasks" appear in your tasks pane in Outlook. So, if you don't ever want to do anything but view your tasks in Outlook, you're fine using the follow up flag. However, if you (like me) want to be able to export your outlook tasks in an effort to do something awesome with them, your "follow ups" will be left behind when exporting tasks. (Yes, hopefully that link is a tease to a future post).

I set out to make a macro that:

  1. Made a task out of the currently selected email; and
  2. Used the categories I'd assigned to that email using rules to fill in data for the new task for sorting purposes.
Here is my Macro
Public Sub CreateTaskFromItem()

'This version by Bill Blewett (
'Modified from KC Lemson's script (
'Which was modified from Jim Durant's script (

Dim olTask As Outlook.TaskItem
'Using object rather than MailItem, so that it
'can handle posts, meeting requests, etc as well
Dim olItem As Object
Dim olExp As Outlook.Explorer
Dim fldCurrent As Outlook.MAPIFolder
Dim olApp As Outlook.Application

Set olApp = Outlook.CreateObject("Outlook.Application")
Set olTask = olApp.CreateItem(olTaskItem)
Set olExp = olApp.ActiveExplorer
Set fldCurrent = olExp.CurrentFolder

Dim cntSelection As Integer
cntSelection = olExp.Selection.Count

For I = 1 To cntSelection
Set olItem = olExp.Selection.Item(I)

'add email as an attachment to the new task
olTask.Attachments.Add olItem

'set subject of the new task
' olTask.Subject = "Follow up on " & olItem.Subject
' I moved this down lower to make the new name ONLY the category that's left over not both original categories

'copy body of email into body of the new task // added by me. I prefer the body text plus the attachment.
olTask.Body = olItem.Body


'Copy ALL the categories from the Item (e-mail) to the new Task... Simple.

olTask.Categories = olItem.Categories

'Give the new task the proper overall value using BillingInformation field

If InStr(1, olItem.Categories, "Mark", vbTextCompare) <> 0 Then
olTask.BillingInformation = "Mark"
End If
If InStr(1, olItem.Categories, "Clint", vbTextCompare) <> 0 Then
olTask.BillingInformation = "Clint"
End If
If InStr(1, olItem.Categories, "[2.Clint]", vbTextCompare) <> 0 Then
olTask.BillingInformation = "Clint"
End If
If InStr(1, olItem.Categories, "Me", vbTextCompare) <> 0 Then
olTask.BillingInformation = "Me"
End If

'Strip the task of ONLY the specific categories which were used above to create BillingInformation values.

olTask.Categories = Replace(olTask.Categories, "Mark", "", , vbTextCompare)
olTask.Categories = Replace(olTask.Categories, "Clint", "", , vbTextCompare)
olTask.Categories = Replace(olTask.Categories, "Me", "", , vbTextCompare)
olTask.Categories = Replace(olTask.Categories, "[2 ]", "", , vbTextCompare)
olTask.Categories = Replace(olTask.Categories, "[2. ]", "", , vbTextCompare)
olTask.Categories = Replace(olTask.Categories, "[2]", "", , vbTextCompare)


olTask.Subject = olTask.Categories & " - Follow Up, " & olItem.Subject 'moved from above to include the proper category

' Misc Cleanups for task names and categories

'to clean up the new task's subject use:
'olTask.Subject = Replace(olTask.Subject, "Text to be replaced", "Text to replace it with", , vbTextCompare)
'to clean up the new task's categories use:
'olTask.Categories = Replace(olTask.Categories, "Text to be replaced", "Text to replace it with", , vbTextCompare)

olTask.Subject = Replace(olTask.Subject, "[1.", "", , vbTextCompare)
olTask.Subject = Replace(olTask.Subject, "]", "", , vbTextCompare)
olTask.Subject = Replace(olTask.Subject, "[6 ", "", , vbTextCompare)


'Make the new task display - delete or comment it out (') to not show

'Set the due date for today
olTask.DueDate = Date

'No Reminder, but if it was True it would remind in 3 hours
'the original author of this script had the reminder as True
'and used the 3 hour mark to make sure that she had all of the
'settings correct... it's super annoying to me.
olTask.ReminderSet = False
olTask.ReminderTime = DateAdd("h", 3, Now)

'Saving the new task automatically

End Sub
Now let's take a look at what this is doing.

The first third of the macro is creating a new task, pasting the text of the email into the task and also attaching the email to the task.
The second third of the macro is my custom categorizing. If an email has a category of "Me" "Mark" or "Clint" then that value is given to the "BillingInformation" field of the task. The purpose of this is so that when viewing my tasks I can have them sorted by categories for each client, but also have a higher value in the hierarchy for whose client they are. I then wanted to strip the task of the "BillingInformation" category since I am already using this information somewhere else, and having multiple categories in a task would lead to duplicate entries in my custom task view. However, I wanted to keep the client specific category in tact. This was trickier than I expected.
The final third of the macro cleans up some left over oddities from category names, sets the due date and reminder value of the new task, then opens and saves the new task.

This macro works perfect for what I was trying to do. You will need to customize to suit your specific needs, but I've left comments in the code that should help with that.

If you need help installing the macro see this article.

Monday, April 12, 2010

Automatically Format Names of Word Document

Good organization of your electronic files requires two key elements. First you need a logical and consistent folder hierarchy. Each client or project folder should have identical, or at least similar, folders corresponding to that particular client. Second, you need a consistent file naming procedure to organize files within each folder.

Many people include dates in file names in an attempt to organize their folders and find what they're looking for with a glance. However, too often people fall into the mm/dd/yyyy trap of dating their files. I get it, we're Americans, we were raised writing the date that way. If we were European we would likely name our files dd/mm/yyyy. However, computers are neither Americans nor European. If we want our files to be organized as best they can we need to learn to speak a bit of computer.

Shortfalls of conventional date formats
Your computer wants to organize things alphabetically and in descending order. What this means is that a letter titled 04-12-2010 - Letter to Joe.doc will be placed after 04-11-2010 - Letter to Jane.doc and before 04-15-2010 - Letter to Jim.doc. That's great so far, but we run into trouble when we change years. For example 04-12-2011 - Letter to Joe.doc when placed in the same folder will result in sorting like this:

04-11-2010 - Letter to Jane.doc
04-12-2010 - Letter to Joe.doc
04-12-2011 - Letter to Joe.doc
04-15-2010 - Letter to Jim.doc
The dates become jumbled. Our British friends' folders, would be even more chaotic. And people who put their dates at the end of the file name run into even more of a mess by sorting first by recipient then by date. Fortunately, the solution is simple. We change our date format to a YYYYMMDD format.

A better date format method
Some people like YYYY-MM-DD, some like YYYY.MM.DD. I prefer YYYYMMDD because as applied to my preferred naming convention this method results in the least visually cluttered names.

In my folder the above examples would be formatted as:

20100411 - Letter to Jane.doc
20100412 - Letter to Joe.doc
20100415 - Letter to Jim.doc
20110415 - Letter to Joe.doc
Much better right? But I'll be the first to admit that it's kind of a pain to write out those numbers and place them in front of each file name. Again, there is a simple solution, though it is likely a bit intimidating to the average Word user.

Scripting a solution
Mike has previously written on some of the basics of scripting here. By default Word's suggested Save As filename is the title of the document. Most users probably know that when they first "Save As" a document word picks up the first few words as a suggested file name. Behind the scenes word is actually substituting those words for the document's title field if no title has been defined.

The following script will define the title of the document as the current date in yyyymmdd format whenever a new file is opened:

Private Sub Document_New()

With Dialogs(wdDialogFileSummaryInfo)
.Title = CStr(Format(Now(), "yyyyMMdd "))
End With

End Sub
You can fine tune this script to reflect your personal preferences in naming your files. For example, changing "yyyyMMdd " to "yyyy-MM-dd " or "yyyy.MM.dd ". However, avoid front and back slashes as they are not valid characters for file names.
To apply this script we need to add it to whatever template that you base your documents on. If you want all of your documents you need to add it to your
normal.dotm file.
  1. Open your normal.dotm template (or whatever template file you wish to apply this behavior to).
  2. Press alt+F11 to bring up the visual basic editor. If you have several files open you may see multiple projects on the left of the screen. (If you don't see the Project Viewer screen press Ctrl+R).
  3. Double click "ThisDocument" in the "Microsoft Word Objects" folder of the "Normal" project.
  4. Paste the above code in the code window.
  5. Save your work and close the Visual Basic editor.
Now close out of Word and open a new file. Save the file for the first time to check our work.