Macro to add a hyperlink to a mail message

Today I wrote a mail to someone and wanted to add several links. It always annoyed me that Office’s Add Hyperlink is so bloated it takes some time to open, and even more time to switch back to it from another application, where all I wanted was to make the selected text link to the URL I had in my clipboard. So I set out to write a macro that did it.

Since Outlook has no macro recorder, I started searching Outlook’s Object Browser and discovered that there is no method to add a hyperlink or to get the content of the clipboard. So I borrowed the “Getting clipboard content” functionality from a VBS script I already had, and I turned to Word to record a macro that adds a hyperlink to the selected text. Once I had this Word macro, I found out I can use ActiveInspector.WordEditor to issue the Word macro I created.

The final macro result is this:

Public Sub AddLinkFromClipboard()
    If ActiveInspector.EditorType = olEditorText Then
        MsgBox "Can't add links to textual mail"
        Exit Sub
    End If
    Dim objHTM As Object
    Dim ClipboardData As String
    Dim doc As Object
    Dim sel As Object
    Set objHTM = CreateObject("htmlfile")
    ClipboardData = objHTM.ParentWindow.ClipboardData.GetData("text")
    Set doc = ActiveInspector.WordEditor
    Set sel = doc.Application.Selection
    doc.Hyperlinks.Add Anchor:=sel.Range, _
        Address:=ClipboardData, _
        SubAddress:="", _
        ScreenTip:="", _
        TextToDisplay:=sel.Text
End Sub

A solution to quickly moving mail items from Outlook inbox

My usual way of handling e-mails is reading them in the inbox which collects both work-related e-mails from the Exchange server and private e-mails from my Gmail inbox using SMTP and POP3., and then dragging them to the the appropriate folder in one of the PST I have.

I know that since I index my mails and it is lightning fast to find any e-mail filing each e-mail in a specific related folder is less important, but kind of used to it, and I still find it useful to be able to go to a folder and see all the mails that I decided that belong to that folder, and not all the files that match some search. This has prove itself useful mainly in customer related data, where searching the customer name would have bring way too many mails, but looking at the customer folder quickly revealed the old case I was looking for. By the way, these is one of the reason I prefer Outlook to Gmail. Outlook can easily help me categorize my mails in hundreds of hierarchical folders, where Gmail provides a flat list of labels, which might not be useful for more than a few dozens.

My problem was that whenever I wanted to move a mail to a folder, I would have to find the folder in the folders tree, in order to drag the mail into it. This might be annoying for commonly used  folders like the folder that keeps mails from CodeProject (I’m subscribed to it although I rarely develop in Microsoft technologies. I enjoy skimming through it looking at what’s new in that area. Occasionally I even find an interesting article).

When a mail is opened in Outlook editor, which has the new ribbon, the ribbon has a clever Move to Folder item which opens a list of recently used folders. However, if I don’t want to open the mail, and only want to move it to a folder from the index, I have to find the folder in the tree and drag it to it.

One solution is to use the Favorite Folders to put shortcut to commonly used folders in a fixed place, and then drag mails to that folder shortcut, and not to its location in the tree. This has two drawbacks:

1. The list tends to grow all the time, and I end up with a big list of “Favorite Folders” which I rarely ever navigate to – I only use them as drag targets.
2. Sometimes Outlook crashes and the list of Favorites folder is reset, and I have to start building it from scratch.

Today I came up with a new solution – use macros.

I wrote the following VBA procedure:

Private Sub MoveItemsToFolder(folderID As String)
    Dim item As MailItem
    Dim targetFolder As Folder
    ' Folder ID can be obtained by selecting the folder
    ' in Outlook and then typing in the immediate window:
    '   ?ActiveExplorer.CurrentFolder.EntryID
    Set targetFolder = GetNamespace("MAPI").GetFolderFromID(folderID)
    If ActiveExplorer.CurrentFolder = targetFolder Then
        MsgBox "You are already in '" & targetFolder.Name & "'.", vbCritical
        Exit Sub
    End If

    For Each item In ActiveExplorer.Selection
        item.Move targetFolder
    Next item
End Sub

and then I wrote small procedures like the following:

Sub MoveToPrivate()
    MoveItemsToFolder "0000000045DC3C0FFF1EA54CBAD9147BB26AF269A2800000"
End Sub

Sub MoveToCodeProject()
    MoveItemsToFolder "00000000A7E4D138E838B7489BA3F839949B055122860000"
End Sub

Then I created a new menu in Outlook toolbar, and added these procedures as items to this toolbar menu. So now, when I want to move a mail to a folder, I simply select it and click the appropriate button.

As the comment says, if a new folder is needed, I select it and open the VBA editor, and type

ActiveExplorer.CurrentFolder.EntryID

in the immediate window. I didn’t have an Outlook crash yet, so I don’t yet know if crashes that reset the favorite folders also reset toolbar customization, but at least the first problem is solved – my favorite folders only contains folder I navigate to regularly, and not all the folder I periodically get mails that should be filed under them.

VBA Macro Arguments

After we started to use Remedy for our customer support, I got annoyed by the way Remedy displays the history of customer cases (Remedy calls them issues), so I wrote a VBA script in Microsoft Word that takes the history generated in Remedy and displays it in a readable format (let me know if you’re interested in this script – it is really great if you are working with Remedy to support customers)

Since I intended this macro to be used by others, and not only by me, I tried to make its installation as simple as possible. I had no choice but asking my users to open the VBA editor and import my Remedy.bas module (I really didn’t want to go into developing a Word add-in with an installation program), and then users would have to add a toolbar button calling the macro. Since this was quite a complex script, it had many internal functions which I didn’t want users to call from a toolbar button – I only wanted them to add one or two buttons that called some of the public functions. To make sure they only use the public functions, I prefixed all the internal functions names with “RAH_” (RAH stands for Remedy Activity History), so they’ll know they shouldn’t look at macros with that name.

Later, I added a new procedure (“Sub” as it is called in VBA) that do some functionality. This function could be called from within other macros, in which case a document argument can be passed, or it could be called from a toolbar button, in which case the active document should be used. I noticed, however, that the Customize dialog didn’t show any macro function or procedure that has an argument, even though this argument was optional, and it was valid to call the function without arguments. This was annoying at first, and to bypass it, I had to create another proxy procedure that simply calls the internal procedure without any parameters, and only this proxy procedure could be added as the action for a new toolbar button.

However, later I realized that this feature of the Customize dialog can be put to good use and solve the internal procedures problem I had – I simply added an optional dummy argument to any internal procedure that didn’t have any argument. On one hand, it didn’t change the meaning of my code, and any code that called this internal procedure didn’t have to change. On the other hand, all these internal procedures don’t appear in the Customize dialog any more, since they now have an argument.

Update: This trick of hiding the internal procedures has one problem. My script uses the Application.OnTime procedure when the Remedy application haven’t finished opening the history window to make the script sleep and then check again one second later. It appears that the Application.OnTime procedure behaves like the Customize dialog, and is only willing to accept a procedure without arguments in its Name argument. If the procedure specified in the Name argument has at least one argument, the OnTime procedure won’t run it. So the macro has to have at least one internal function with no arguments.

One cumbersome way to solve it is to set some global flag before calling Application.OnTime, and then make Application.OnTime call a public procedure which will first check for that flag and if it is set, call the internal procedure and exit, but I’m not sure that the goal of hiding this one internal function worth the effort of making the code less readable.

Update 2: Thinking about it some more, I came to the conclusion that the dummy argument is a nice trick, but is not really needed. Setting the internal procedures as Private also hides them from the Customize dialog. How didn’t I think about it before…?

This, however, doesn’t solve the Application.OnTime problem, as the OnTime procedure cannot call Private procedures, so the procedure it calls must be public with no arguments, so it will appear in the Customize dialog.

Update 3: I found another reason for having non-private functions. My macro tries to hide unimportant parts of the case history, such as “original message” parts and confidentiality signatures. When doing so, it adds a link that when clicked, shows the hidden text. This is done by adding a MACROBUTTON field that calls the appropriate macro. It appears that this macro must also obey the rules of the Customize dialog for the field double-click to work – it has to be non-private, and with no arguments.

Setting Word’s default suggested filename

Our company recently moved to use Remedy for bug management and customer support.

One of the things that bothered me the most in Remedy was its inconvenient way of showing issues’ (a.k.a cases) activity history.

So I sat down to write a Word macro that manipulates Remedy windows to get the history data as a flat text and conveniently format it as a Word document. (If you are using Remedy and would like to get this script, leave me a note)

I had several problems in writing this macro, and I learned some more about Word VBA while overcoming them.

One of the problems I encountered was that I couldn’t find a way to control, from the VBA code, what will be the default name of the document when the user first saves it. Word seemed to insist on using Doc1.doc (on Word 2003) or 01.doc (on Word 2007). At first, I left it as an known issue, since I didn’t think this would really hurt anyone. But I got some user that has some more requests from that macro, and he also urged me to solve this problem.

I then realized that Word actually takes the first line to be the file name, so I simply added a header to the file with the details of the issue (removing all special characters which Word sees as delimiters) and this indeed solved the problem.

While writing this post, I search for questions on the web on how this can be done, since I can’t be the only one who needs it, and found that it can be done from script. However, since the header I added actually make the generated document look nicer, I think I’ll leave it that way.

FixQuote macro for MS Word

Bar llan Responsa CD has a funny way to show bibliographical quotes – it puts it surrounded by plus sign before the quote. This bothered my wife so I wrote her a Word macro to fix it. All she has to do to use it is to move the selection to the end of the quote, and run the macro. Here it is in case it somehow gets deleted, as it did today:

Sub FixQuote()

Dim quoteRange As Range
Dim quote As String
Set quoteRange = Selection.Range
quoteRange.Find.Execute FindText:="+*+ ", Forward:=False, MatchWildcards:=True
quote = quoteRange.Text
quoteRange.Text = ""
quote = " (" + Mid$(quote, 2, Len(quote) - 3) + ")"
Selection.InsertAfter quote
Selection.Collapse wdCollapseEnd

End Sub