I’ve recently started using del.icio.us to manage my bookmarks. I don’t have any interest in social bookmarking, but I like that I can get to my bookmarks from any computer and I love the tagging system. I set my home page to my frequent category and I can easily access other categories from there. There’s a shortcut key to add a site and assign a tag. If the tag doesn’t exist, it’s created for you. If you delete all the bookmarks in a tag, it’s removed.
I currently have 434 items in my inbox, down from over 1,200 a couple of weeks ago. I’ve been whittling away at them, getting them into folders. The end-game is keep my inbox cleaner – consisting of only those email that need my attention. The process to move items to folders is painful: Right click, Move to folder, click on folder, OK. And that’s only if the folder exists.
I need a delicious-like way to move email so I wrote the below code.
The userform takes the currently selected email item (it doesn’t work with other items – yet). It shows the subject so I can make sure I had the correct email selected. If the email is unread, it defaults to flagging the email as red. I’ll be using flagged email to determine what needs my attention rather than unread mail. The cursor starts in the Tag textbox so that a tag can be entered quickly.
If I type in an existing folder, the email is moved there. If I type in a new folder, the folder is created under Inbox and the email is moved there.
Limitations:
- I can’t search for a specific subfolder. That is, if I have two subfolders (Products: Datasheets and Marketing: Datasheets), by typing in ‘Datasheets’, it will use the first one it finds.
- All created folders end up under Inbox and I have to manually move them if I want.
- Misspellings create new folders.
- Empty folders are not automatically deleted.
Most of these limitations are by design – I wanted to keep it simple and flexible.
This is definitely a first draft, so there’s probably tons of bugs in it. Let me know what you think of the concept, the implementation, and particularly the recursive function. It’s been a while since I’ve done recursion and I’d like to know if there’s a better way. And if you’re itching to comment ‘You can already do that in Outlook’, then please be specific, because I prefer built-in solutions over my crappy VBA any day. If you haven’t figured it out yet, this post has nothing to do with Excel – all the code goes into Outlook.
You can download OutlookTags.zip and view the code below:
Option Explicit
Private msSubject As String
Private mbUnread As Boolean
Private mbFlag As Boolean
Private mlFlagColor As Long
Private mbUserCancel As Boolean
Private msXTag As String
Public Property Get Subject() As String
Subject = msSubject
End Property
Public Property Let Subject(sSubject As String)
msSubject = sSubject
End Property
Public Property Get Unread() As Boolean
Unread = mbUnread
End Property
Public Property Let Unread(ByVal bUnread As Boolean)
mbUnread = bUnread
End Property
Public Property Get Flag() As Boolean
Flag = mbFlag
End Property
Public Property Let Flag(ByVal bFlag As Boolean)
mbFlag = bFlag
End Property
Public Property Get FlagColor() As Long
FlagColor = mlFlagColor
End Property
Private Sub cmdCancel_Click()
mbUserCancel = True
Me.Hide
End Sub
Private Sub cmdMove_Click()
mbUserCancel = False
mbFlag = Me.chkFlag.Value
mlFlagColor = Me.cbxFlag.ListIndex
msXTag = Me.tbxTag.Text
Me.Hide
End Sub
Private Sub UserForm_Activate()
Dim vaColors As Variant
vaColors = Array(“None”, “Purple”, “Orange”, “Green”, “Yellow”, “Blue”, “Red”)
Me.cbxFlag.List = vaColors
Me.tbxSubject.Text = msSubject
If mbUnread Then
Me.chkFlag.Value = True
Me.cbxFlag.Value = “Red”
End If
End Sub
Public Property Get UserCancel() As Boolean
UserCancel = mbUserCancel
End Property
Public Property Get xTag() As String
xTag = msXTag
End Property
‘Module: MEntryPoints
Option Explicit
Sub ShowTagForm()
Dim ufTag As UTags
Dim mi As MailItem
Dim sTag As String
Dim lFlagColor As Long
Dim fldTag As MAPIFolder
On Error Resume Next
Set mi = Application.ActiveExplorer.Selection.Item(1)
On Error GoTo 0
If Not mi Is Nothing Then
Set ufTag = New UTags
‘pass info to userform
ufTag.Subject = mi.Subject
ufTag.Unread = mi.Unread
‘show userform
ufTag.Show
‘get info back from userform
If Not ufTag.UserCancel Then
sTag = ufTag.xTag
If ufTag.Flag Then lFlagColor = ufTag.FlagColor
mi.FlagStatus = olFlagMarked
mi.FlagIcon = lFlagColor
‘move item to folder
With Application.GetNamespace(“MAPI”)
Set fldTag = GetOrCreateFldr(.GetDefaultFolder(olFolderInbox), sTag)
mi.Move fldTag
End With
End If
Else
MsgBox “No Email Selected”
End If
Set ufTag = Nothing
End Sub
Private Function GetOrCreateFldr(fMain As MAPIFolder, sTag As String) As MAPIFolder
Dim fSub As MAPIFolder
Dim fEnd As MAPIFolder
Set fEnd = CheckSubs(fMain, sTag)
If Not fEnd Is Nothing Then
Set GetOrCreateFldr = fEnd
Else
Set GetOrCreateFldr = fMain.Folders.Add(StrConv(sTag, vbProperCase))
End If
End Function
Private Function CheckSubs(fSub As MAPIFolder, sTag As String) As MAPIFolder
Dim fldr As MAPIFolder
Dim fEnd As MAPIFolder
For Each fldr In fSub.Folders
If UCase(fldr.Name) = UCase(sTag) Then
Set CheckSubs = fldr
Exit Function
Else
If fldr.Folders.Count > 0 Then
Set fEnd = CheckSubs(fldr, sTag)
If Not fEnd Is Nothing Then
Set CheckSubs = fEnd
Exit Function
End If
End If
End If
Next fldr
End Function
“Outlook already does this”, to some extent. Outlook has a feature called “Categories”, which when used properly are incredibly powerful. The gist is you can “tag” messages with categories (one or many), and then do what you will from there. What *I* do, is have a folder called “Archive” as the only subfolder to my Inbox. In conjunction with a short and sweet macro (below), you can hit the “Archive This” button, get a subject-line confirmation prompt, and then choose what Categories to tag it. After you hit OK, it flags the message as complete (checkbox icon) and moves it to the Archive folder. Now here’s the great part- just setup Search Folders using the Categories to emulate your old “mess of folders” method (I say that tongue in cheek- I used to have that too). Anyways take it for what it’s worth. At least make use of Categories though :)
‘ AEMT v1.0
‘ Coded by Nick Pappas (nick@nosp4m.radicand-org)
‘ With snippets from http://www.webfoot.com/oeo/outlook/vb/OEOmacros.txt
‘ Purpose: To provide a highly efficient email workflow system so that
‘ a user can spend more time on “doing things” instead of “managing” them.
‘ Move the selected message(s) to the “done” folder. ************************
Sub MoveToDone()
‘ Change “Archived” below to whatever the name of the subfolder in your Inbox
‘ where you want to keep archived messages is.
MoveToFolder (“Archived”)
End Sub
‘ Move the selected message(s) to the “to-do” folder. ***********************
Sub MoveToToDo()
‘ Change “To-Do” below to whatever the name of the subfolder in your Inbox
‘ where you want to keep archived messages is.
MoveToFolder (“To-Do”)
End Sub
‘ Returns TRUE if a folder named folderName is a child of the folder
‘ named parentFolder, FALSE otherwise. Note that if folderName is in
‘ a SUBfolder, this will return FALSE.
Function FolderExists(parentFolder As MAPIFolder, folderName As String)
Dim tmpInbox As MAPIFolder
On Error GoTo handleError
‘ If the folder doesn’t exist, there will be an error in the next
‘ line. That error will cause the error handler to go to :handleError
‘ and skip the True return value
Set tmpInbox = parentFolder.Folders(folderName)
FolderExists = True
Exit Function
handleError:
FolderExists = False
End Function
‘ Move the selected message(s) to the given folder **************************
Function MoveToFolder(folderName As String)
Dim myOLApp As Application
Dim myNameSpace As NameSpace
Dim myInbox As MAPIFolder
Dim currentMessage As MailItem
Dim errorReport As String
‘ Housekeeping: set up the macro environment
Set myOLApp = CreateObject(“Outlook.Application”)
Set myNameSpace = myOLApp.GetNamespace(“MAPI”)
Set myInbox = myNameSpace.GetDefaultFolder(olFolderInbox)
‘ See if the folder exists. If it doesn’t, print an informational
‘ error.
If Not FolderExists(myInbox, folderName) Then
MsgBox “Folder “ & folderName & ” does not exist.” & _
vbNewLine & vbNewLine & _
“Please either: “ & vbNewLine & vbNewLine & vbTab & _
“create the folder “ & folderName & ” under Inbox” & vbNewLine & _
“or” & vbNewLine & vbTab & _
“change the name of the folder in the Visual Basic code “ & _
“that you downloaded. (The name of the folder is well marked, “ & _
“near the beginning of the code.)”
Exit Function
End If
‘ Figure out if the active window is a list of messages or one message
‘ in its own window
‘On Error GoTo QuitIfError ‘ But if there’s a problem, skip it
Select Case myOLApp.ActiveWindow.Class
‘ The active window is a list of messages (folder); this means there
‘ might be several selected messages
Case olExplorer
‘ Move the selected messages to the “done” folder
For Each currentMessage In myOLApp.ActiveExplorer.Selection
If currentMessage.Categories = “” Then
MsgBox currentMessage.Subject
currentMessage.ShowCategoriesDialog
End If
currentMessage.FlagIcon = olNoFlagIcon
currentMessage.FlagStatus = olFlagComplete
currentMessage.UnRead = False
currentMessage.Save
currentMessage.Move (myInbox.Folders(folderName))
Next
‘ The active window is a message window, meaning there will only
‘ be one selected message (the one in this window)
Case olInspector
‘ Move the selected message to the “done” folder
Set currentMessage = myOLApp.ActiveInspector.CurrentItem
If currentMessage.Categories = “” Then
currentMessage.ShowCategoriesDialog
End If
currentMessage.FlagIcon = olNoFlagIcon
currentMessage.FlagStatus = olFlagComplete
currentMessage.UnRead = False
currentMessage.Close olSave
currentMessage.Move (myInbox.Folders(folderName))
‘ can’t handle any other kind of window; anything else will be ignored
End Select
QuitIfError: ‘ Come here if there was some kind of problem
Set myOLApp = Nothing
Set myNameSpace = Nothing
Set myInbox = Nothing
Set currentMessage = Nothing
End Function
Hey Dick,
Interesting concept. I’m a big fan of gmail’s “label” system which allows me to flag under multiple categories. It’s a shame that Outlook doesn’t have that capability. ;)
Re the creation of new folders limitation… why not use a combobox to enumerate the folders? You’ve probably already thought of that, but at least it should help out. :)
For the flag color, instead of using a combo which shows only one row at a time, why not a listbox that shows at least a few rows, or even a whole set of option buttons?
One of the weaknesses of the new dialogs in Office 2007 is that list boxes and option buttons have been reduced to combos, so you have to keep reloading the choices into your short term memory instead of simply seeing them in the dialog. (Ironically, the O-07 dialogs violate a whitepaper MS released to give guidance to Vista developers.)
Nick: If I have to set up a search folder for every category anyway, why not just use a real folder? There’s no way to create categories on the fly, just like with folders. I looked at cats, but didn’t see any advantage over using just folders.
Ken: That would be on advantage of using Outlooks categories like Nick suggests – assign more than one cat to an email. I’ll have to consider using cats just because of that. Of course then I’ll have to learn how to create search folders in VBA. I didn’t go with a combobox because I didn’t want to have to list all the folders in there, but I’m not sure if I picked the right side of that trade-off.
Jon: I did it that way because I only use Red. But I knew if I didn’t allow the option, someone would complain about it. Switching to a listbox is a good idea.
I am sure folks have their well-reasoned reasons for doing what they do but here’s what I do…no code required.
My mail folders are organized along functional lines (this should be a familiar theme for those who know how I organize add-ins and files). So, for example, there’s a folder (at the same hierarchical level as the Inbox) named Academia. Another is Friends. Yet another is Work. Yet another is Travel.
Each folder contains a sub-folder for those entities that have enough activity to warrant one. So, under Academia I have Research Papers, Rochester, Upcoming Conferences, Yale, etc.
Over time the hierarchy can evolve with the top-level folders seeing the slowest movement. For example, while I was still a doctoral student Rochester was a top-level folder with several sub-folders. Now, of course, it doesn’t warrant that prominence.
In the left navigation pane of Outlook, I show all these Mail folders in the default Tree view that Outlook uses. Depending on the current activity level, I might use the +/- button to show/hide certain sub-folders.
Given the above, for those emails that I want to file somewhere I click-and-drag that item from the Inbox to the appropriate folder in the left pane. (*)
As far as tagging emails goes, I only use the ‘follow up’ flag.
The easiest way to do that is to show the Flag column in the Inbox (customize the display if necessary) and then click the cell at the intersection of the column and the row containing the email.
To see all items flagged for follow-up, scroll down in the left pane to the bottom. In there select the ‘For Follow Up’ sub-folder of the Search folder.
(*) What would be really nice would be to customize the right-click menu so that it offers a “Move To” item that leads not to a default dialog box but to a tree display of all the folders and sub-folders (very much like the Desktop Toolbar of the Windows Taskbar). I guess I could write one but it’s the Outlook OM {shudder, shudder} {vbg}
Dick “Right click, Move to folder, click on folder, OK”
Tushar has already alluded to this, Dick, but why not just click-and-drag? (Since you’re already using the mouse, which I know is painful).
Like Tushar, I have subfolders, so the categories wouldn’t work. I do use them for Tasks though.
Doug: I use the ‘right-click key’ that’s between Alt and Ctrl on the lower right portion of my keyboard. :) If I had to use the mouse, I would have come up with something a long time ago.
Tushar: I have too many top level folders so that I can’t see them all and I would have to scroll before I move (or during, which is a pain) if I were using the mouse. Do all your top level folders show? Maybe I have too many – here they are including subfolder count:
ACT (0)
Backup (0)
Payroll (5)
Facilities (0)
Personal (0)
FTP (0)
Inventory (12)
Banking (0)
CPA (0)
VPN (0)
IT (0)
Office Equip (0)
Marketing (0)
CondoAssoc (0)
Financials (0)
Quality (0)
Policies and Procedures (0)
Subversion (0)
Accts Rec (0)
Insurance (0)
LunchandLearn (0)
Sales (0)
Vendors (0)
Intel Property (0)
Equity (0)
Quickbooks (0)
Scans (0)
Xmas (0)
Teradyne (0)
Purchasing (0)
Websites (0)
ImportExport (0)
NewOffice (0)
Travel (0)
FromMe (0)
Budgets (0)
ManReps (0)
Gmbh (0)
Legal (0)
Ah, but of course!
Gee, Dick, that’s an awful lot of top level folders, and such a small number of subfolders. You could probably group them into half a dozen or so top level folders, put about half a dozen subfolders into each, half a dozen into each of those, etc. It’s called nesting, and it was introduced to file systems about the time the mouse was introduced as computer hardware; they’re both nifty inventions.
Keep just the top level folders visible. When you hold a message over a folder, the folder unfolds so you can see the subfolders. If a particular folder gets opened a lot, just leave it open; close the ones you don’t go into frequently.
Since I never throw anything away, I have taken this one level further up the chain, and I have five pst files. One for current work, two for archived work (A-L, M-Z), one for personal correspondence, and one for login and signup confirmation, mailing lists, and the like. This makes backups more flexible, and I don’t need to keep the archives open if I don’t want.
Jon, “When you hold a message over a folder”
What are the keystrokes for that? ;)
Re: Dick:
The reasoning behind this is it’s so much easier to find mail if it’s in a single folder. I used to run into the problem of trying to remember if I filed a piece of mail as “Reports” or the type of report it was. Using cats, you can tag a message as both and finding it is a matter of keying in a few words or just grouping your archive folder by categories (in which case the same message will appear under both categories).
Doug –
Keystrokes are simple: Hold left mouse button down, move mouse, release button. So it’s a click and an unclick.
As I said, the mouse was developed in the same era as nested folders. If you use one, you may as well use the other. Get your hands off the keyboard once in a while to prevent repetitive stress injury.
I’ve never been a raving keystrokes guy. I’ve been using a mouse extensively for well over 20 years.
Jon,
I’ve never been much of a keystroke person either – beyond, Ctl-c, v, f, h and some VBE combos (all of which use my free hand) I just find the mouse easier – certainly not as much to remember. At home on my laptop I use the touchpad. That’s definitely my favorite way to navigate. It’s fast, eliminates all the hand movement between mouse and keyboard, and it just feels right.
I always set my mouse or touchpad to “single-click” in Windows. I found that setting about 5 years ago and am hooked on it. I don’t know that I’ve ever seen anybody else use it.
Oh sure, make fun of the keyboard guy. I have so many top level folders because I hate navigating sub folders – it takes 1 second to hover over a top level folder and make it expand – too long. But with my super-slick tag system, I could nest them more and I not have to worry about it.
Nick: Multiple categories is a great reason to use cats and search folders. I looked into making search folders programmatically, but it wasn’t as easy as normal folders. The main obstacle is that I can’t tell which search folders are already created. I’m working a hypothesis on how to get around this and I’ll post it if I come up with something useful.
I like the multiple pst idea. I could backup my personal stuff like…never, which would save backup space and time.
Dick – I would like to see any info you do find on creating search folders…
I have to agree with Nick, using search folders does solve issues if an item falls into 2 categories.
As far as sub folders go, I now don’t have any – just an ibox which keeps everything in it from the past 2 months. I have a rule setup to automatically move anything older to a PST. Like Jon, I never delete anything, however unlike Jon, I just have 2003, 2004, 2005, 2006 and 2007 which brings another question – how long do you need to keep emails for? If you want any file attachements from them these would be better off stored elsewhere, do you keep the actual email?
Simon, A couple of years ago I wrote a routine that deletes the attachments of selected emails, and adds “Attachment Deleted” to the title. In the body, it says the same thing along with the name of the attachment(s) that was deleted. I’m in the habit of saving attachments as I get them – then I run this routine a couple of times a year. This keeps my pst size down, but retains the ability for me to go back and see what files I sent or received, and when.
Doug,
That sounds like a very useful routine.
Does it work with OE also?
Any chance of sharing the code?
Doug –
I used to be a fanatic about moving attachments to the hard drive, but I’ve gotten lazy in my old age. They’re as easy for me to find in my pst files, so why bother?
Jon, my situation is different, otherwise I might do as you. A good percentage of the files need to be accessible to other people at some point, so they get saved to the network drive. And our email is synchronized to an Exchange server and if I bring it up at another workstation for the first time, it can take a long while for all those files to load into Outlook.
John, I’m definitely a cut-and-paste Outlook coder (in fact, it may have been an overstatement to say that I “wrote” this) so this could be improved. Select some emails and run this. I’m sure it doesn’t work in OE though, sorry.
Dim oSelection As Outlook.Selection
Dim deleted_notification As String
Dim outItem As MailItem
Dim InitialCount As Long
Dim i As Long
Dim j As Long
Dim SelectedCount As Long
Set oSelection = ActiveWindow.Selection
SelectedCount = oSelection.Count
For j = 1 To SelectedCount
On Error Resume Next
Set outItem = oSelection.Item(j)
If Err 0 Then
MsgBox “Can’t delete the attachment”
Exit Sub
End If
On Error GoTo 0
InitialCount = outItem.Attachments.Count
If InitialCount > 0 Then
‘note deletion in subject line
outItem.Subject = outItem.Subject & ” (Attachments Deleted)”
‘list of deleted items
deleted_notification = ” Attachments Deleted “ & Now() & Chr(10)
For i = InitialCount To 1 Step -1
deleted_notification = deleted_notification & Chr(10) & outItem.Attachments(1)
outItem.Attachments.Remove (1)
Next
deleted_notification = “—————————————————-“ & _
Chr(10) & deleted_notification & Chr(10) & _
“—————————————————-“ & Chr(10)
outItem.Body = deleted_notification & Chr(10) & outItem.Body ‘add list of deleted items
outItem.Save
End If
Next
End Sub
Nice macro Doug. I had a similar one but have replaced it with yours (it does more!)
Because of the html encoding several things are garbled including all ampersands and greater thans in the code. But also the line reading
If Err 0 Then
should read
If Err lessthan greaterthen 0 Then
or if err not equal to 0
Terry
Thanks Terry.
Thanks Doug,
There are a number of people in my office that are always having problems with that 2 gB limit on OE and Outlook. I’ll try the above on my Outlook users.
Of course, if I could get my boss to change the way he does things it wouldn’t be a problem in the first place. Typically he’ll get a multi-mb attachment and forward it someone else in the office to save it to the server whilee cc’ing a few others (with the attachment) to make them aware of it. And everyone saves the original e-mails (until they eventually crash and then I just wipe the slate clean).
Thanks again.
We offer a free version of a tagging client for Outlook. Disclaimer: I work for them, but thought people wouldn’t object to at least knowing about it!
http://www.taglocity.com
You might want to look into ClearContext as well.
Nick,
Thanks for bringing Categories to light! I never realized they could be used as tags – love it! An interesting add-on with free version is Taglocity (which appears to use categories as tags!)