Archive for the ‘Office’ Category

Macro to add a hyperlink to a mail message

February 13, 2008

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:="", _
End Sub

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

June 20, 2007

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).

A solution to quickly moving mail items from Outlook inbox

June 12, 2007

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


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.

Macro keyboard shortcuts in Excel 2007

June 10, 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…


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
        MyData.SetText Selection.Value
    End If
End Sub

KatMouse enables mouse wheel for VB6 and Remedy

May 7, 2007

Via LiefHacker, I just found KatMouse.

The main goal of this small utility is to enable scrolling with the mouse wheel in windows that don’t have the keyboard focus.

But I mainly find it useful to use the mouse wheel in applications that don’t handle mouse wheel event. Up until now, I found two such applications that I use – Visual Basic 6, in which I still fill at home for some quick tasks like testing our COM class library, and Remedy User which we recently switched to for customer support and bug tracking.

KatMouse simply sends the correct message to those application and makes the mouse wheel works for them. I did find some problems with it – it disabled the mouse wheel in Excel 2007 worksheets and in Word 2007 Document Map sidebar. Fortunately, KatMouse provides an ultra-easy way to configure it to ignore specific window classes, using a Spy++ like windows finder, so the problem was quickly and easily solved.

The only problem that still remains is that my Dell D620 mouse-pad doesn’t have a mouse wheel… :(

Update: There is one problem I found with KatMouse which I couldn’t resolve. In Excel 2007, when you do data filtering, and click the arrow at one of the title cells, you get a complex menu with some options like sort and advanced filtering, and also a list of all values in the column so you can select from it. This list doesn’t handle mouse wheel events. It didn’t bother me until now – like any window with no wheel support, I’d try using the mouse wheel, and when it didn’t work, I would scroll with the scrollbar. However, when KatMouse is active, using the mouse wheel causes the special filter menu to disappear. The problem is I can’t find out how to tell KatMouse not to handle mouse wheel events for this menu, as it disappears on every mouse click or key down, so I can’t get its window class. Not such a big nuisance, but nevertheless…

Update 2: One additional place I found KatMouse useful is with DiskFrontier which does not support mouse wheel which tends to be really annoying. A quick class customization in KatMouse solved the old problem.

Update 3 (25/12/2008): When I recently showed KatMouse to a colleauge, I went to KatMouse homepage, and noticed that version 1.04 was released shortly after this post was published. This version adds support to Office 2007, and solves the annoying problem mentioned in the first update and adds mouse wheel support to Excel 2007 filter menu.

VBA Macro Arguments

April 26, 2007

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.

Email Indexing – Moving Back to X1

April 18, 2007

Ever since I found e-mail indexing, I cannot really live without it. I store almost all my e-mails, and I need to have immediate access to it. If I can’t search my entire corpus of history e-mail and get the results, I feel somewhat crippled.

I guess my first attempt was Google Desktop Search (GDS), and then I found something that annoyed me later in other Google applications (e.g. Picasa), but was best demonstrated in GDS – Google seems to think they know what’s best for you, and let you have very little control of the process. I really didn’t like it. I think, and this is a general note about application design, that you should have an interface that on one hand is as simple as possible so dumb users can use it without much questions, but on the other hand, you should provide as much as possible choice for advanced users to change things to their liking. GDS, for example, didn’t really let me decide what mail folders or files folders I want it to index.

I also didn’t like it that GDS shows results in the browser. I’m only interested in finding e-mails, but when I do find an e-mail, I want it to open in Outlook so I can have all the Outlook behavior I’m used to.

Then I switched to Microsoft’s Windows Desktop Search (WDS), which was nice, but I couldn’t really understand when it is indexing and what. It said it has finished, but still couldn’t find a message that I was looking for. I think GDS had the same problem. I just couldn’t trust it when it said no messages met my search criteria.

And then I found X1 Enterprise Client (XEC) and really liked it – it was fast, reliable and had a nice UI. It also let me fully integrate with Outlook, e.g. open the folder in which a message reside. I’d continue using it but then I installed Office 2007 beta, and XEC didn’t work well with that version of Outlook, so I had to revert to WDS.

Today, doing a regular search (about once in a month or two) to see if XEC already supports Outlook 2007, I found out it probably does support it, so I downloaded and installed it, and once it finishes building its index, I’ll start enjoying it and getting used to be spoiled.

The problem I found in both WDS and XEC is that in doesn’t store search history (at least not that I found) so if you search for a ticket number, and then for something else, and you would like to get that number you just searched for, it won’t let you. I hope it will be added some time soon. in XEC it is a little more problematic, since because it is so fast, it can allow itself to do “find as you type”, but that means you don’t have a specific event of submitting a search in which the term you searched for should enter the history list. Nevertheless, I’m sure its developer can find a way to solve it if they really want to.

Muting PowerPoint music

April 10, 2007

Here is a quick tip:

When a friend sends you a pps file which by default starts slide-showing the presentation in PowerPoint (Ehud Kenan complained about such attachments in his Hebrew blog, and I commented on how this can be easily avoided).

Often, these presentations has an accompanied background music, which can be rather annoying, especially if you have other people sitting near you.

I often thought Microsoft should have added a mute button to slide shows of such presentations, but until they do, I found out that the easiest solution to silent these slide shows (other then temporarily muting the computer or turning the speakers off) is simply to Alt-Tab to another application and then back to PowerPoint. These makes PowerPoint stop playing the music, until the presentation’s slide show is started again.