Managing Email Attachments

Following on from the previous posting, I’ve been wondering what else to do with Outlook.

One thing that’s I’ve meant to sort out for a long time is managing email attachments (Word documents, PDFs, etc). My outlook.pst file is not far short of half a gigabyte, and presumably this is largely due to lots and lots of  message attachments. Nearly all of these will have been Saved As, but almost never Removed. It’s annoying that there isn’t a Detach operation that does both.

So what I’d like is an Attachments Manager. Yes, there are some out there, but it’s more fun to roll your own, and learn something in the process.

One issue to decide is whether we want the saving/removal to be done automatically, on receipt of the message, or manually initiated as a housekeeping task (or both). I think I’ll start with the latter, as it gives an opportunity to check an incoming message.

We’ll want to scope the messages being processed, for example by:

• Folder (e.g. Inbox, Saved, all folders)
• Date (e.g. older than)
• File types for the attachments (e.g. only MS Office, PDFs)
• Message title (or content)
• Message sender.

We’ll also need to specify target folders for the saved files.

It would be good to maintain a link from the message to the saved file. I’m thinking this can just be a file:/// URL. We get a sort of back-link just by searching for the filename (I use the Lookout search facility).

Given that we want a degree of flexibility in setting up the processing parameters, a front-end user-form would be good. I can see this turning into a bit of a mini-project…

Email Auto-Reply – Solution

In the previous posting, I was wondering how to generate an email auto-reply that contains your next free day, based on Calendar appointments. I had been thinking of using Rules, but a comment by Jon Peltier pointed me in the right direction.

The trigger is to handle the event Application_NewMail (the handler goes in the ThisOutlookSession module):

Private Sub Application_NewMail()
    Dim ib As MAPIFolder
    Dim mi As MailItem

    Set ib = _
       Application.Session.GetDefaultFolder(olFolderInbox)
    Set mi = ib.Items.GetLast

    RespondTo mi
End Sub

The incoming message needs to be inspected for information about the sender, and responded to, if appropriate:

Sub RespondTo(mi_in As MailItem)

    If IsAutoRespond(mi_in) Then
        MakeResponse mi_in
    End If

End Sub

An incoming message has a SenderName property. Oddly, it has a To property, but not a From property (why? am I missing something?). The SenderName is the ‘display’ name, that you typically see in the ‘From’ column of your Inbox, in preference to the actual email address. As far as I can see, this comes from the alias set up for the email account of the sender; it doesn’t correspond to any name field in the corresponding Contact (Full Name, E-mail, etc).

So we need to record the SenderName, as it appears in your Inbox, in a field of the corresponding Contact. We could add a user-defined field, but on the Details tab there’s a Nickname field, which I doubt is ever used, and so can be hi-jacked. Use of this field could be taken as enabling our auto-reply, but to be on the safe side, let’s add a user-defined field AutoRespond, set to True.

The IsAutoRespond function uses the Find function on the Contacts:

Set contact = contactsfolder.Items.Find(filter)

where

filter = _
     "[Nickname] = " + Chr(34) + mi.SenderName + Chr(34) + _
     " And [AutoRespond] = " + Chr(34) + "True" + Chr(34)

When a filter string is given as a literal, the double-quotes are doubled (e.g. “”True”"). I discovered that this isn’t necessary when using chr(34). If the Find fails, then the result variable is Nothing.

MakeResponse replies to the incoming message, using the next free date in the reply message’s body:

Sub MakeResponse(mi_in As MailItem)

    Dim mi_out As MailItem
    Dim nextfree As Date

    Set mi_out = mi_in.Reply

    With mi_out
        .Body = responsetext & NextFreeDay
    End With
    mi_out.Send
End Sub

The NextFreeDay function goes through the Calendar looking for All-Day-Event appointments (these could be fake ones, used just to block out days; I have mine as Free time, so that they don’t clash with ‘real’ appointments). The result starts as tomorrow’s date, and gets pushed into the future, as appointments are found. Weekends and other unavailable dates should be taken into account.

The initial version is pretty basic. It would seem possible to use Calendar information in more sophisticated ways, or indeed to analyse the incoming message content.

Email Auto-Reply with Availability

My wife’s a Business Intelligence (sic) consultant, writing reports on hideously complicated databases. As is the way of things, she does reports for different customers and projects. The difficulty lies in getting across the idea that when working for customer X, she’s not available to fire-fight problems for customer Y. So she responds to emails, saying “I’m sorry, my next availability to look at your problem is on <date>”, where <date> is found by looking in her diary for the next free day.

Assuming that you’re using Outlook as both email client and calendar, is it possible to generate such replies automatically? Presumably incoming emails (with certain properties, say Senders) can be detected using Rules, and a reply sent. But can we intercept the rule-processing, and insert information derived from the Calendar (or indeed elsewhere) into the reply message?

Having not done a lot with Outlook (compared with Excel and Access), I have no idea at this stage, but I’ll investigate, and report back. Obviously, if anyone out there has done this already, let me know!