I tend to organize projects in file folders, where I store all project-related information including e-mails. Assign this macro to a toolbar button in Outlook. Select one or more Inbox messages, run the macro, and select the destination folder. Messages will be named according to subject, with a date/timestamp appended. All non-alphanumeric characters are replaced with underscores. (Note: this is a necessary workaround for file naming restrictions within the Windows filesystem.)

Please note: This code utilizes the Microsoft VBScript Regular Expressions 5.5 library (enable it using the Tools- References dialog in the VBA IDE).

Create a VBA module and name it winapi_folderbrowser. Paste the following code into the new module:

""" BEGIN CODE
Private Const BIF_RETURNONLYFSDIRS As Long = &H1
Private Const BIF_DONTGOBELOWDOMAIN As Long = &H2
Private Const BIF_RETURNFSANCESTORS As Long = &H8
Private Const BIF_BROWSEFORCOMPUTER As Long = &H1000
Private Const BIF_BROWSEFORPRINTER As Long = &H2000
Private Const BIF_BROWSEINCLUDEFILES As Long = &H4000
Private Const MAX_PATH As Long = 260
Type BrowseInfo
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszINSTRUCTIONS As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type
Type SHFILEOPSTRUCT
hwnd As Long
wFunc As Long
pFrom As String
pTo As String
fFlags As Integer
fAnyOperationsAborted As Boolean
hNameMappings As Long
lpszProgressTitle As String
End Type
Declare Function SHGetPathFromIDListA Lib "shell32.dll" ( _
ByVal pidl As Long, _
ByVal pszBuffer As String) As Long
Declare Function SHBrowseForFolderA Lib "shell32.dll" ( _
lpBrowseInfo As BrowseInfo) As Long
Function BrowseFolder(Optional Caption As String = "") As String
Dim BrowseInfo As BrowseInfo
Dim FolderName As String
Dim ID As Long
Dim Res As Long
With BrowseInfo
.hOwner = 0
.pidlRoot = 0
.pszDisplayName = String$(MAX_PATH, vbNullChar)
.lpszINSTRUCTIONS = Caption
.ulFlags = BIF_RETURNONLYFSDIRS
.lpfn = 0
End With
FolderName = String$(MAX_PATH, vbNullChar)

ID = SHBrowseForFolderA(BrowseInfo)

If ID Then
Res = SHGetPathFromIDListA(ID, FolderName)
If Res Then
BrowseFolder = Left$(FolderName, InStr(FolderName, vbNullChar) - 1)
End If
End If
End Function
''' END CODE

Create a new VBA module and name it mymacros. Paste the following code into the new module:

''' BEGIN CODE
Sub SaveMsgToFolderWithSubjectAndTimestamp()
Dim Item As MailItem
Dim strTargetFilename As String
Dim strTargetPath As String
Dim objRegExp As New RegExp
With objRegExp
.Global = True
.IgnoreCase = True
.Pattern = "[^\w+]"
End With
strTargetPath = BrowseFolder
Dim SelectedItems As Selection
Set SelectedItems = Outlook.ActiveExplorer.Selection
For Each Item In SelectedItems
strTargetFilename = objRegExp.Replace(Item.Subject, "_") + "_" + Format(Item.ReceivedTime, "yyyy_mm_dd_hhnnss")
Item.SaveAs strTargetPath + "\" + strTargetFilename + ".msg", olMSG
Next Item
End Sub
''' END CODE

Assign the macro SaveMsgToFolderWithSubjectAndTimestamp to a custom toolbar button and you’re all set.

One step further: Save attachments!

The code has been extended as follows: If message has attachments, a new folder is created, named according to the message subject, with “_ATTACHMENTS” appended, and all attachments are saved in the new folder.

Sub SaveMsgToFolderWithSubjectTimestampAttachments()
 Dim Item As MailItem
 Dim strTargetFilename As String
 Dim strTargetPath As String
 Dim Atmt As Attachment
 Dim strTargetAttachmentsFolderPath As String

 Dim objRegExp As New RegExp
 With objRegExp
 .Global = True
 .IgnoreCase = True
 .Pattern = "[^\w+]"
 End With

 strTargetPath = BrowseFolder

 Dim SelectedItems As Selection
 Set SelectedItems = Outlook.ActiveExplorer.Selection
 For Each Item In SelectedItems
 strTargetFilename = objRegExp.Replace(Item.Subject, "_") + "_" + Format(Now(), "yyyy_mm_dd_hhnnss")
 Item.SaveAs strTargetPath + "\" + strTargetFilename + ".msg", olMSG
 If Item.Attachments.Count > 0 Then
 ' Create new folder
 strTargetAttachmentsFolderPath = strTargetPath + "\" + strTargetFilename + "_ATTACHMENTS"
 MkDir strTargetAttachmentsFolderPath
 ' Save Attachments
 For Each Atmt In Item.Attachments
 Atmt.SaveAsFile strTargetAttachmentsFolderPath + "\" + Atmt.DisplayName
 Next Atmt
 End If
 Next Item
End Sub

Open VBA editor (Alt-F11). In the immediate window, type the following code, replacing ‘x’ with the appropriate Slide and Shape indices:

? Application _
.ActivePresentation _
.Slides(x) _
.Shapes(x) _
.LinkFormat _
.SourceFullName

This will output the full path information to the linked media file. To change the path, such as in the case of removing full path information and leaving just the filename, use the following syntax:

Application _
.ActivePresentation _
.Slides(x) _
.Shapes(x) _
.LinkFormat _
.SourceFullName = "mediafile.mpg"

Yourli.st is a smooth, registration-free online service for sending yourself reminder e-mails. The concept for this plugin came about as I was developing my cooking website, ScreamingSaucepan.com. It occurred to me it might be nice to be able to remind myself about recipes in some way other than tagging them on del.icio.us, perhaps at a time when I might be wandering around the aisles of the grocery store. This plugin has only the most basic functionality, no administration panel, no options. It simply opens the yourli.st page and prepopulates the Event Title field with the title of the blog posting and inserts the permalink in the Event Summary. All you need to do is fill in your e-mail address, target date, and whatever other options you’d like to adjust.

I’d like to thank Alan over at YourLi.st for building this service and his positive feedback on the plugin.

I’d also like to thank Ronald Huereca for his great series How to Write a WordPress Plugin. If you have any intention of developing your own plugins for WordPress, go there immediately.

Download the plugin here.

To avoid losing action items as my e-mail flows in, I make extensive use of the “Flag for Follow Up” feature in Outlook. Having grown tired from clicking around the dialog box with the calendar that pops up next to the “Due By” field, I created the following VBA macro:

Sub FlagForXMinutes(intMinutes As Integer, strFlagRequest As String)
Dim Item As Object
Dim SelectedItems As Selection
Set SelectedItems = Outlook.ActiveExplorer.Selection
For Each Item In SelectedItems
With Item
.FlagStatus = 2
.FlagDueBy = CStr(CDate(Format(CDbl(Now) + intMinutes / 1440)))
.FlagRequest = strFlagRequest
.Save
End With
Next Item
End Sub

I’ve assigned a couple of buttons on my Outlook toolbar with the following macros attached:

Sub FlagForFollowUp15Minutes()
FlagForXMinutes 15, "Follow Up"
End Sub

Sub FlagForFollowUp30Minutes()
FlagForXMinutes 30, "Follow Up"
End Sub

I have not played around with too many variations, but this works with items contained within Public Folders as well as Meeting Requests and Responses, thanks to the “Dim Item As Object” declaration which provides some added flexibility.

Here are a couple of not-so-very-well documented tips for MS Access 2000. I haven’t tested them on any other versions of Access, though I suspect most of them should work on later versions.

These tips require the use of the Microsoft Visual Basic Integrated Development Environment (IDE), which can be accessed by Alt-F11.

Use the Immediate Window

The Immediate Window can be accessed via Ctrl-G (or View – Immediate Window). It is an invaluable asset when debugging or testing. I’m not a programmer. I like to say I know just enough programming and scripting to be extremely dangerous. Despite this, I’ve saved myself days, possibly weeks, of time and frustration using this tool.

Use the Question Mark (?)

I discovered this by accident, but I remembered back in the old days programming Basic on my Atari 400 that the question mark character was shorthand for “Print.” As it turns out, VBA is a distant cousin to the old Basic. You can still use the “print” keyword, but the question mark is your friend. (Note that the question mark will be converted to the “print” keyword automatically in the Code Editor.)

Get Full Path and Connection Information to Linked Table

This can be a huge time saver, especially when working with databases that have a lot of linked tables and those with long path names. Sometimes the Linked Table Manager utility just doesn’t cut it.

? currentdb.TableDefs("tablename").Connect
or
print currentdb.TableDefs("tablename").Connect

Try it. You just might like the results.

Incorporate Custom Functions in Queries

This one might scare some people, as it requires some hand coding of SQL. That means leaving the comfort zone of the visual Query Designer and entering the land of Structured Query Language. It also requires some hand coding of VBA. This can be terrifying. Once you’re over the initial fear, however, you’ll be pleased to know that it’s pretty easy to get started.

Let’s take a sample table. We’ll call it tblPeople. Please note: The prefix “tbl” is a standard naming convention, but I won’t get into that here. I’d highly recommend the VBA Developer’s Handbook – (buy it here) for anyone interested in getting their hands dirty with that sort of thing. We have three fields we’re concerned with: fname, mname, and lname. We have three records:

Suppose we wanted these names to print out in a mail merge-friendly format. One method would be to hard code the logic in SQL. My personal preference is to write custom functions for tasks like this, as you end up with more readable and maintainable SQL. For instance, here’s the function:

Function fnNameForMerge(strFName As Variant, strMName As Variant, strLName As Variant) As String
fnNameForMerge = strFName & IIf(strMName = "" Or IsNull(strMName), " ", " " & strMName & " ") & strLName
End Function

We can now test the function using the Immediate Window:

Once satisfied, we can now use the function in the following SQL statement:

SELECT tblPeople.fname, tblPeople.mname, tblPeople.lname, fnNameForMerge(fname,mname,lname) AS NameForMerge
FROM tblPeople

This can also be done in the Query Designer:

The results look like this:

The more I use Access, the more I tend to dig into the SQL code. I’ve found myself leaning towards custom functions whenever my SQL starts getting messy, particularly when logic starts creeping in, such as in the example. Even if you’re a SQL-phobe, consider that it’s still pretty easy to use custom functions in the Query Designer. You will, however, need to acquaint yourself with Modules.

Don’t let VBA scare you. You don’t have to be a “programmer” to mess around with some code. It just might save you some time or even add quality years to your life. I know it has for me.

My Rant Against Anti-Automation

Unfortunately, there seems to be a tendency within organizations to disable macros due to the proliferation of malicious code. This inevitably means more people doing more manual labor, typing things they’ve typed hundreds of times before, moving the mouse and clicking on things over and over, inching ever closer to Repetitive Stress Injuries. It’s a shame. The last I checked, computers were supposed to automate things. Personally, I’d rather take the risk and encourage people to experiment, to unleash their Inner Automator. Repetition is evil – unless you’re actually LEARNING from the exercise.

If you’ve found any of this useful, please feel free to drop me a line or comment.

Access Links, the Add-In included (but not enabled by default) with Excel, can save time when exporting data to MS Access. However, for frequent GeoAccess reporting, I was craving some more flexibility, so I started scripting some alternative solutions (please note: I have only tested this with Excel 2000). Three of them are packaged in this add-in, GeoAccessAssistant.xla (click here to download). Once installed, it adds a GeoAccess menu bar with the following three buttons:

Export Zip Selection to MS Access

Select a range of zip codes. (Please note: Zips must be in a single column. At a later date, I might enhance this to accommodate other types of ranges, i.e., horizontal, non-contiguous, etc., but for now, this should still be useful in the majority of scenarios.) Click Export Zip Selection to MS Access. This script will trim the contents of each cell to remove surrounding white space, add leading zeroes wherever necessary, shift the selection down one row, add zip as the value in the empty cell at the top of the selection, expand the selection one row at the bottom to catch the stray extra cell, create a new, blank Access database with the same name as the active workbook, then transfer the selected range to the new database into a new table named census. The script goes one more step by running this JetSQL Data Definition Query:

alter table census alter column zip text(5)

It seems that GeoNetworks can return inaccurate results if the zip field is not a text field five characters in length. I found this out the hard way. Three hours and two tech support calls later, I decided to avoid that particular trap going forward.

Export Zip Region to MS Access

This goes through essentially the same steps as the above, except you only need to have the cursor in a single column of zips. When you press Export Zip Region to MS Access, it first selects the current region, meaning it will expand to select contiguous, neighboring cells with contents. Try to make sure those cells happen to be in a single column.

Export Zips and Counts (Populate)

This one does not require anything to be selected initially. Clicking Export Zips and Counts (Populate) pulls up a single-button UserForm prompting the user to Select Zip Range, then Click Here. Next, it prompts to Select Counts, then Click Here. Both selections must contain the same number of cells, i.e., one zip code per count, otherwise a message box will pop up warning Cell counts not matching. Try again? The user can either try again or cancel the operation. (I have not torture-tested this, but it should work with all types of selections including non-contiguous areas, as long as both zips and counts have the same number of cells. However, I can’t imagine encountering a scenario such as that. Then again, one never knows…) If the cell counts match, it will prompt Click to Export Data to Access. It will then create a new, blank Access database with the same name as the active workbook, create a new table named census with a five-character text field named zip, then iterate through each zip in the originally selected range, duplicating records according to each corresponding count, essentially replicating the GeoNetworks Data-Populate feature.

I haven’t provided further instructions, as this assumes some Intermediate knowledge of Excel which should include managing Add-Ins. Details are freely available in Excel’s online help.

If anyone either finds this useful or feels that I must be stopped, please feel free to share opinions, experiences, rants, and raves. Any suggestions are always more than welcome, though I cannot guarantee that I will act on them other than to respond to your comments. I assure you, I’m driven by an uncontrollable desire to automate away repetitive tasks wherever possible, not including, of course, those repetitive tasks which might actually prove beneficial, such as breathing.

I was out taking a training class the past two days. It was a beginner’s class in Flash MX 2004 Professional.

I took it because the graphic designer working with me wanted to take it. I’ve taken this class before, for the prior version of the software, Flash MX. In fact, I’ve taken more than one Flash class before.The first Flash class I took was an advanced class almost five years ago, which was by far my favorite, since it was taught by some young entrepreneur guy who sent out a mass e-mail as an “experiment” (his words) advertising this particular class. It’s still a mystery to me how he managed to get a mailing list with my name on it, since I’ve always been very selective about giving out this particular e-mail address. Regardless, that class was great primarily because whatever the course curriculum was, we more or less abandoned it. It helped that I happened to be obsessed with Flash at the time, and it really seemed to be the hot thing. I had already gotten my feet wet with it, so I opted to go straight for the advanced material. The class consisted of three people: the instructor, myself and the president of a design firm who was taking the class to learn how to better manage his developers. Good call. We ended up discussing all sorts of interesting real-life subjects, most of which were not even remotely related to Flash. I left that class feeling truly inspired.

A couple of years later, some associates of mine registered for an introductory Flash class, mainly so they could get up to speed and collaborate with me. I decided to tag along and see what they were being taught. This class was being offered by a more established organization, rather than one guy masquerading as an established corporate training center. The instructor seemed like a pretty cool guy, definitely knowledgeable, willing and able to answer more advanced questions without veering too far off course. I had a couple of issues with the approach taken at the time, which I brought up. He acknowledged those issues fairly. The fact is, Flash could be used for great things, provided people are exposed to the real power behind it, which, let’s face it, lies with scripting (rather, PROGRAMMING). Sure, the concept could easily scare some people off, unless it’s approached gently. However, avoiding the subject, I feel, is a crime. The result is a bunch of people playing with an expensive new toy to do simple animation.

This last class was also being offered by an established training center. It’s very well known in this region, so much so that it’s virtually synonymous with ‘training.’ Ask anyone around here if they’ve attended or would consider attending outside computer training, and the response is very likely to contain references to this place. This was my first real exposure to it. I won’t mention the name of the organization, as it might very well fill a need. In fact, I’m an avid supporter of ongoing training. Attending any sort of elective training immediately puts you in the very small percentage of people that takes any initiative whatsoever. So, I say, go for it. Lousy training is better than no training… usually. (I’m sure there are times when the opposite is true, but most of the time, I think the very act of opting for training is beneficial.) This instructor was much more by-the-book. Not once did he ask who anyone was, why they were there, nothing. In all fairness, I believe part of the problem lies in the software itself. I mean, it’s called “Flash MX 2004 Professional.” As if life isn’t confusing enough, Flash now comes in all sorts of flavors, each with its own subset of features. I’m pretty sure Flash MX 2004 Professional is the version with all of the features, but I don’t really care enough to look into it at this point. I’m hoping that with Adobe’s acquisition of Macromedia, a little housecleaning will take place, and they’ll do something to straighten out the confusion, because right now, I’m not the least bit inspired to get back into Flash. Something seriously groundbreaking will have to take place to change my mind.

I asked the guy sitting next to me in the class what his deal was. He was busy sketching (on paper, not on his computer) an action figure, which he showed to me. It looked pretty good. It’s some character which is supposed to appear in some cartoon they’re planning on submitting to… whoever. He went on about how it’s really tough to draw a character in this particular position, since he’s supposed to be a superhero, and he doesn’t really have his chest all puffed out, he’s just kind of, you know, leaning back a little, sort of hunched over. I asked the guy where he worked. “I’m freelancing right now.” Images of his bedroom in the attic of his mom’s house wallpapered with Korn posters flew through my head. I asked him why he was taking this class. “I’ve been using Dreamweaver for a while now, so I figured I could build Flash websites.” Great. Just what we need, more websites that only people with high bandwidth connections and the latest Flash-enabled browsers could look at. Guaranteed that a couple of months from now, that website will be up there, complete with our superhero plastered all over it, still trying to fix his posture. Not once did the instructor try to discuss the logic (or lack of it) of getting into Flash with the intention of building full-blown websites with it. Our artist friend is probably hard at work right now, hung over from a rave, frantically trying to figure out how to make hyperlinks or something, getting really frustrated because those designer drugs wore off without releasing the part of his brain he needs to wake up and realize he’s wasting his precious youth holed up in his mom’s house. Another victim of Dreamweaver, yet another tool which has been terribly abused, notorious for creating a ton of extra code, meaning larger files, meaning pages which take longer to load. Sure, it makes it real easy to build basic websites. Too easy. Now there are tons of clowns building nasty, unmanageable websites. Just like Photoshop and Illustrator turned a bunch of spoiled rich computer nerds into so-called graphic designers, we can thank the Dreamweaver team in part for the proliferation of garbage out there on the Internet. Maybe it’s time to issue licenses to people who want to do certain things on their computers. Not software licenses, mind you, but operator’s licenses. You need one to drive a car, you should absolutely need one to have children, and you should need one to prevent you from polluting the Internet.

Most of the class was spent dealing with the drawing tools within Flash. Again, I’ll place part of the blame on the software. Flash has, in my opinion at least, the worst set of drawing tools imaginable. They behave unlike any drawing tools in any other graphics program. Once you learn how they work, you realize that they’re essentially worthless. I’m convinced to this day that whoever was responsible for implementing this set of drawing tools needs to undergo a comprehensive drug test and psych evaluation. Thankfully, I realized this early on, so whenever I needed to draw anything remotely complex, I used Illustrator, or something, ANYTHING, else. Importing graphics into Flash is trivial. The instructor himself acknowledged once or twice that a particular tool “shouldn’t do this, but it does,” or “I don’t know why they decided to do it this way, but that’s the way it is.” Not once did he suggest using any tool other than Flash. Of course not, since that wouldn’t fit neatly into the rigid curriculum. Instead, he spent almost the entire time trying to fit the proverbial square peg into the round hole. There were no complaints. I restrained myself, except for the occasional aggravated whisper to my graphic designer associate whenever I disagreed with the instructor’s approach. I didn’t publically assault the guy. He was just doing his job, I suppose, as soul crushing as that job might be.

We’re registered to take the next level of this class later this month, I think. Supposedly, they’ll get into scripting a little more, but I have my doubts. Maybe I’ll get to meet another batch of slacker artists with nothing better to do but draw action figures while their parents pay their rent. Sounds like a pretty good deal.

This is what it generally looks like in front of me while I’m working.

Very exciting. Two monitors. That tends to freak people out. They get dizzy when I start moving things from one monitor to the other. It can be pretty amusing. It’s also a good way to get rid of people I don’t want hovering over my shoulder. It amazes me how most people can’t comprehend the usefulness of having the dual monitor setup. I think most people are comfortable doing the minimum amount of work (there’s that ‘work’ word again) possible. I think they’re comfortable afflicting themselves with Carpal Tunnel Syndrome as well, considering how few people use an ergonomic keyboard these days. I’d be a cripple by now if I hadn’t been using one all these years. I really want to learn the Dvorak Keyboard Layout. Turns out we’re all using an antiquated keyboard layout, one that was intentionally designed to slow us down back in the days when typewriters had all sorts of mechanical stuff going on which wouldn’t work right if you typed too fast.

On the plus side, it is Friday. I’m sitting here trying to digest the liter and a half of water I just drank too fast. It hurts. I’m hoping the bloat subsides within the next hour so I can move towards the door. I might just have to float myself out of here.

I’ve been dealing with large quantities of Access databases. They tend to get bloated with unused space. So, I built this utility which searches folders and subfolders for databases, populates a listbox (with extended multiselect) with the filenames, then uses the Jet database engine to compact the selected files. It shows before and after filesizes. Oh… and it WORKS. (If it doesn’t, please drop me a line with the details.)

Download the zipped file here.

Feel free to let me know if you find this useful, suggest enhancements, tell me to shut up already, etc.