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

Wish: AutoComplete add-in for Word that suggests words from current document

UltraEdit has a nice feature in which when you press Ctrl-Space, an auto-complete list is opened, suggesting you to complete the word you just started to type. The nice thing is that its suggestions come not only from the list of keywords of the edited file’s language, but also from the actual words in the file (actually, the words above you current location).

This is great as you often use the same words several times in a file, and it can be handy for your editing tool to use that and suggest options from your current work, which probably knows the words in the domain more then any general keywords dictionary file.

I really wish someone would have written an add-in to Microsoft Word that does it. I might get to it some time when I have time (as if… ha).

Macro keyboard shortcuts in Excel 2007

I’ve just written a new macro in Excel, and wanted to assign Ctrl-Shift-F to it, as it does some advanced finding. However, the Shortcut key field that appears in the Record Macro dialog and also in the Options… dialog opened from the Macros dialog, it seems to only be possible to assign Ctrl-<something> to a macro, and not Ctrl-Shift-<something>.

Disappointed, I choose Ctrl-E instead. Cursing Microsoft again for not putting a decent keyboard customization feature in Excel 2007 (and also in Outlook Editor), like they did in Word 2007.

But then I recalled that I already had a macro that copied the content of the selected cell, and I’m used to activate it using Ctrl-Shift-C. I want back to the Options… dialog and found out that although it says Ctrl+<something>, if you type Shift-<something> in that field, the Ctrl label changes to Ctrl+Shift.

I’d say that’s not the most discoverable feature I’ve seen…

PS

The Macro I use for copying a cell (or cells) content is as follows:

Sub CopyCellContent()
    Dim MyData As DataObject
    Dim s As String
    Dim i As Long
    Set MyData = New DataObject
    If IsArray(Selection.Value) Then
        s = Selection.Cells(1).Value
        For i = 2 To Selection.Cells.Count
            s = s & ", " & Selection.Cells(i).Value
        Next i
        MyData.SetText s
    Else
        MyData.SetText Selection.Value
    End If
    MyData.PutInClipboard
End Sub

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.