Posts Tagged 'Outlook'

Moving Data Out of Outlook – part 2

Happy and code-full 2011!

In an earlier post, I was considering exporting my Outlook contacts as XHTML pages, and accessing these as a repository of free-format, but queryable, documents. After some time out for Christmas and New Year, not to mention my family’s seasonal cough-fest, I can report back.

I haven’t attempted a comprehensive export of all contact attributes, just the standard ones that I typically use. This still leaves information in the Contact’s body section, but I’ll migrate that manually, as and when.

Here’s an example contact page:

I just grabbed a standard style sheet, so the formatting is not up to much (for example, the cell text middle-aligns vertically, as you can see with “Address”).

In terms of the VBA code, all we need is an adapter class, ContactPage. Instances of this are created for each ContactItem that we export. A ContactPage is responsible for building the XHTML string corresponding to the ContactItem, and writing it out to a text file. Here’s the root procedure:

Const outdir As String = "C:\Temp\Contacts"

Sub ExportContacts()
  Dim allcontacts As Items
  Set allcontacts = Session.Folders("Personal Folders"). _

  MsgBox "Exporting " & allcontacts.Count & " Contacts"

  Dim cn As ContactItem  'Outlook class
  Dim cp As ContactPage  'Custom class
  For Each cn In allcontacts
    Set cp = New ContactPage
    Set cp.Contact = cn    'Property Set
    cp.OutputToFile outdir
End Sub

ContactPage has a function for PageTitle, which is taken from the ContactItem‘s Company or Full Name (in that order of preference). Functions PageId and PageFile are based on this (with a bit of cleaning up to remove characters such as ‘/’).

The sub OutputToFile uses the Microsoft Scripting Runtime library, specifically FileSystemObject and TextStream. You need to load the Scripting library manually, via >Tools >References.

The rest of ContactPage’s features are just functions that generate elements in the XHTML hierarchy. For example:

Public Function XhtmlPage() As String
'The XHTML representation of cp_contact
    XhtmlPage = Header_ & vbCrLf & _
End Function


Public Function Html_() As String
    Html_ = "<html id=" & Chr(34) & PageId & Chr(34) '& ...
    Html_ = Html_ & vbCrLf & _
            Head_ & vbCrLf & _
            Body_ & vbCrLf & _
End Function

Public Function Body_() As String
    Body_ = "<body>" & vbCrLf & _
            "<h1>" & PageTitle & "</h1>" & vbCrLf & _
            Company_table_ & vbCrLf & _
            Person_table_(1) & vbCrLf & _
            Person_table_(2) & vbCrLf & _
            Person_table_(3) & vbCrLf & _
            Categories_ul_ & vbCrLf & _
End Function


Public Function LastName_td_() As String
    LastName_td_ = "<td class=" & Chr(34) & "LastName" & Chr(34) & ">" & _
     cp_contact.LastName & "</td>"
End Function

The lowest level functions, typically for <td> elements, access the ContactItem‘s properties. As you can see, there are a lot of quotation characters (Chr(34)) in XHTML. The trailing-underscore convention for the function names was so that I could call the functions after the XHTML tags they generate – without the underscore I got a name clash with something existing.

Minor complications where the Contacts folder contains Distribution Lists (class DistListItem), which is a different class from ContactItem – that is, a MAPIFolder.Items is a heterogeneous Collection. This makes the For Each loop throw a type mismatch error, although you can resume execution (>Debug, >Run).

Also a slight issue with duplicate filenames, since the file output overwrites by default. I used Round(Rnd * 1000, 0) to generate a temporary disambiguating suffiix for the filename.

Here’s the Class Module for ContactPage.



Moving Data Out of Outlook

I’ve felt uneasy for a while that important data – for example, my Contacts – is sitting in Outlook, in an enormous and opaque database file (.pst). Okay, it’s backed up, but if it became ‘corrupted’ and wouldn’t open, then I have no way of recovering the data, that I know of. Why should I have to open Outlook, just to find someone’s website, or phone number? The whole idea of organizing your life within a single application seems terribly outmoded. I just want a mail client that handles message send and receipt; all the information I want to be in the ‘outside world’.

Retrieving data can be pretty painful too. I’ve just clicked on the ‘Activities’ tab of a contact, and it’s taken over 2 minutes to come back with about 20 items. While there is free-text search, there is no proper query access.

Outlook’s data model is baroquely large – a futile attempt to cover all possibilities. I’m looking at the class ContactItem, and it has nearly 150 attributes. I can have NickName, TelexNumber (telex?) and YomiFirstName (something Japanese); but I can only have three email addresses and one webiste URL.

So what’s the alternative? What I’d like is for each item (contact, whatever) to be a web page in a local (private) website. New pages could be created, edited and added as necessary. The information is all fully accessible using basic or more sophisticated editors. Website and mailto links work normally – the latter opening a mail client, as usual.

As it happens I’m using a document repository for XML documents, including those in XHTML. This runs on a Jetty server, at a localhost port. The repository can be browsed as a directory hierarchy, free-text searched, and queried (using XQuery). Because the repository is fully indexed, the search and query are very fast. I can create/edit documents in the repository directly, without the need to export/import, using a WebDAV-enabled editor.

So, to populate the repository initially, I would need to export my existing contacts from Outlook in some way. Exporting items as XML or HTML directly is beyond Outlook (up to and including v2007). So I’m thinking that I could write a custom export to XHTML – nothing complicated, just a simple page with tables. Elements, such as the table cells, would be given standard id attributes to indicate the content type – such as ‘company’, ‘address’; these can then be used in queries, analogously to column names in SQL.

In terms of the design, I’m wondering about a custom class, say ContactAdapter, which would wrap a ContactItem, and build the complete XHTML string, and write it to a file (using the Scripting library for the file access). I’ve not done anything like this before, so we’ll see. I suppose that I could export the data to Excel, and work on it there, but dealing with rows and columns is less direct than working with the actual ContactItem objects in Outlook.

More anon…

Managing Email Attachments – Design

In the previous posting, I was thinking about how to go through email messages, saving attchments and then removing them, and possibly doing other things to the messages, like moving them. While you can do a certain amount using Outlook rules, and yes there are third-party utilities out there, it’s interesting to think about how to do this in VBA.

As is often the way, you start with some specific requirements, think about it a bit, and come up with a rather more generic mechanism – something with a bit of Design Pattern about it. It seemed to me that I might want to apply multiple rules to each message, depending on properties of the message or attachment.

So I would give a message to a chain of rules, each of which would be applied to the message in turn. When a rule is applied, it evaluates some test condition, and if true performs some actions on the message (like saving and removing its attachment). So here’s the outline design:

Rule design

The Dispatcher object (these are Class modules) creates and holds on to a RuleChain. When Run is called on the Dispatcher, it goes through the current folder (and potentially its subfolders), getting MailItems, and giving them to the RuleChain:

Sub Run()
    Dim folder As MAPIFolder
    Set folder = Application.ActiveExplorer.CurrentFolder

    Dim item As Object
    For Each item In folder.Items
        Select Case item.Class
            Case olMail
                di_rulechain.Apply item
            Case olFolder
                'recurse ...
        End Select
End Sub

The RuleChain just iterates over its individual Rules:

Sub Apply(mi As MailItem)
    Dim i As Integer
    For i = 1 To UBound(rc_rules)
        rc_rules(i).Apply mi
End Sub

A Rule applies itself to a MailItem:

Sub Apply(mi As MailItem)
    If RuleTest(mi) Then RuleAction mi
End Sub

Simples! Now, we want different rules, but they have to be objects of the same class. So the class has to cover all the tests and actions that we might want, with switches/settings to make them specific. For example:

Dim ru_saveattachment As Boolean       'save attachment?
Dim ru_saveattachmentfolder As String  'where?
Dim ru_removeattachment As Boolean     'remove attachment?

So where do the settings for a particular Rule come from? It seems like a good idea if a Rule is represented by something that is (a) persistent between sessions, and (b) editable by a user, without going into the VBA code. One candidate is to use an Outlook Note. We can have a very simple syntax, where a test starts with ‘?’, and an action starts with ‘!’. For example:


This says that if a message is in (has Parent) ‘Inbox’, then save the attachment to /Temp and remove it. Multiple tests are ANDed (multiple Rules provide disjunction). Since we might have ‘ordinary’ Notes, and also so we can disable individual Rules, only pink notes are treated as Rules.

The RuleChain is responsible for going through the Notes folder, creating the Rules:

Private Sub Class_Initialize()    'for RuleChain
    Dim notesfolder As MAPIFolder
    Set notesfolder = ...
    ReDim rc_rules(1 To notesfolder.Items.Count)

    Dim note As NoteItem
    Dim ru As Rule
    Dim i As Integer
    i = 1
    For Each note In notesfolder.Items
        If note.Color = olPink Then  'it is a rule
            Set ru = New Rule
            ru.Load note
            Set rc_rules(i) = ru
            i = i + 1
        End If

    ReDim Preserve rc_rules(1 To (i - 1))
End Sub

The Rule.Load method is responsible for parsing NoteItem.Body, and setting up the Rule accordingly. Clearly you could get quite sophisticated here.

At present the only actions are saving and removing attachments, and I haven’t thought about adding links/ids, to allow traceability. More (maybe) anon.

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 = _
    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)


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
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!

July 2018
« Dec