Archive for October, 2009

Adding HTML Help – 2

I was previously wondering how to link a VBA project to HTML Help (that is, linking to a page in a .chm file). I’m thinking of the Help as pages in a User Manual, not control-level Help (“The Delete button deletes the current item”, etc).

Having got some compiled Help (a CHM file), we need to link into it. The simplest way seems to be to use the Application.Help method. This takes a path to a CHM file, and a numerical key – for example:

Application.Help “C:\…….\myhelp.chm”, 1002

There are a couple of issues with this. The first is that each call to this method (on the same file) opens a new Help Viewer window. I haven’t found a way round this. The second issue is that the numerical keys have to be defined in a header file prior to compilation – something like this:

#define VBA_Creating_a_Widget     1001
#define VBA_Filtering_Widgets        1002

where the names are those of the topic files in the HTML Help project (minus the .html suffix). This mapping is compiled into the help project to produce the CHM file. However, generation from DITA (XML) source goes straight to the compiled CHM, so there’s no chance of adding in a map. (You could decompile the CHM and then add the map before recompiling).

Fortunately, it doesn’t matter, because there’s another way of linking to the Help from VBA. This is to use a more general function, HtmlHelp, available from the Help OCX (HHCtrl.ocx).

You need to declare this function in a public module:

Declare Function HtmlHelp Lib "HHCtrl.ocx" _
   Alias "HtmlHelpA" _
   (ByVal hwndCaller As Long, _
   ByVal pszFile As String, _
   ByVal uCommand As Long, _
   dwData As Any) As Long

You also need a couple of constants (just to give us meaningful names):


The constant declarations  need to be in each module that calls the function.

We can now call the function from a Command button handler, or whatever:

Private Sub HelpCommand_Click()
  Call HtmlHelp (0, Dotslash("myhelp.chm"), _
      HH_DISPLAY_TOPIC, ByVal "VBA_Filtering_Widgets.html")
End Sub

The zero argument is to do with the windowing – the Help opens in a sibling window. Note the ByVal passing of the string (because it’s to an external library). Dotslash is just my shorthand for an absolute path (which is recommended):

Public Function Dotslash(filename As String) As String
  Dotslash = ActiveWorkbook.Path + "\" + filename
End Function

The other mode argument, HH_HELP_CONTEXT, is for the mapped-id lookup, which is what Application.Help does.

This is just scratching the surface. There’s more information on MSDN.

Adding HTML Help

We’re all familiar with HTML Help for applications – usually in the form of .chm files (with the yellow question-mark icon). Typically, constructing these this involves getting to grips with the Microsoft HTML Help Workshop. This includes a tool for building Help projects, and compiling them into .chm files.

This is okay for fine-grained context-sensitive Help, at the level of individual operations and controls. However, if the material is more in the nature of a User Manual, this is probably not the form in which we want to author it. And we will probably want to generate different forms of output, such as general HTML pages and PDF.

A few posts ago, I mentioned producing documentation using the XML-based DITA framework. One of the benefits of this is that content is held in a generic XML-based format, from which outputs of different types can be generated: XHTML, PDF and CHM (and also Java Help). DITA allows us to select and combine various topics (chunks of content) from our User Manual – perhaps the lower-level task or reference topics – and compile these into a .chm file. Apart from the compiler, which is invoked automatically, this does not involve the HTML Help Workshop.

Note on tools: There is a DITA Open Toolkit, but I have been using tools from XMLMind – the free XML Editor (Personal Edition) and the open-source DITA Converter. And very good they are too.

The selection, structuring and ordering of topics is done using a DITA Map file. HTML Help is specified as the output format via options to the DITA Converter.

Having got our .chm file, the next step is to access it from a VBA project. I’d been vaguely aware that you could do this, but had never investigated further. More tomorrow…

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.

October 2009
« Sep   Nov »