I write a lot of books. More importantly, I support my books through email, sometimes long after they were published. I enjoy solving puzzles when you guys ask me to help, and if it helps sell books, great.
Five years ago, I wrote an Excel VBA book, Excel 2003 VBA Programmer's Reference. More succinctly, I updated a second edition of this book, so only some of the content was mine. In short, this book is five years old and I don't have Excel 2003 anymore. (I upgraded to Excel 2007 a year or so ago.) But, a reader wrote me and asked how he could use an email address hyperlink in his spreadsheet and add an attachment based on a filename in an adjacent cell. Thinking this would be easy, I tried to tackle it. (Like everyone else, I am busy and get a lot of requests from readers, so easy is better.) As it turns out, it doesn't appear to be easy.
It appears quite easy to add a hyperlink to an Excel cell; just type an URL or mailto:firstname.lastname@example.org and Excel turns it into a hyperlink. Right-click on the hyperlink and you can do a variety of things, such as add a subject or make the link an internal link to a cell, but there is no attachment option. Bummer. After an exhaustive search, it appears that several people have this problem and there didn't seem to be any real solutions. Hence, this article.
The example in this article shows you how to use VBA (and Excel) to add an attachment to a hyperlink email from a worksheet cell and includes a quick reminder on Windows API declarations. Now, if some of you very clever readers know a simpler way then I encourage you to share it (and let me know).
Creating a MailTo Hyperlink Cell
To add a mailto hyperlink—one that will generate an email—pick a cell and type the moniker mailto:, and an email address, for example:
The result should look something like Figure 1.
Figure 1: Use the mailto moniker to create a hyperlink that will generate an email address.
If you right-click on the hyperlink and select Edit Hyperlink, the Edit Hyperlink dialog will appear (see Figure 2). (The recently used email addresses show you all of the ways I tried to tease the attachment in easily. They didn't work.)
Figure 2: The Edit Hyperlink dialog supports subjects but not attachments. (This should probably be changed at Microsoft. Hint. Hint.)
When you click on the link in Figure 1, your default mail provider should pop up with the email address filled in.
What you want to do next is complete the email along with an attachment and send it. The next piece of information to add is the name of the attachment in an adjacent cell. The results might look like those shown in Figure 3.
Figure 3: Adding the name of the attachment in an adjacent cell.
Adding the Attachment with VBA
The next step is to write some code that grabs the mail document from Outlook and adds the attachment. You'll use VBA for this. The first thing you may need to do in Excel 2007 is enable the Developer tab so you can get at the VBA editor. To enable the developer tab and start the Visual Basic editor, follow these steps:
- Click the Office Button in Excel.
- Click the Excel Options button.
- Click the Popular item.
- And, check the Show Developer tab in the Ribbon option (see Figure 4).
- Click OK.
- Click the Developer ribbon (now visible).
- Click the Visual Basic button on the Developer ribbon.
Figure 4: Turning on the Developer ribbon in Excel 2007.
After Step 7. you should be in the VBA editor. In the object dropdown, select the Worksheet item. In the Procedure dropdown, select the FollowHyperLink procedure. This will generate an event handler that is called when someone clicks on one of your hyperlinks (the mailto link).
To prepare the environment correctly, follow these steps:
- You already switched to the Visual Basic editor.
- Click Tools|References and add a reference to the Microsoft Outlook 12.0 Object Library (see Figure 5).
- Click OK to close the References dialog.
- You already added the FollowHyperlink method. Now, you are ready to write some code.
Figure 5: Add a reference to Outlook.
The code will need to basically intercept the mail item created by the hyperlink and stuff some data in it and send the email. To summarize, you need to create (or obtain) an instance to Outlook, grab the MailItem created by the hyperlink, add the attachment, and send the email. The code in Listing 1 should work. The Sleep API method was added because sometimes the VBA code runs faster than Outlook, so Sleep is used to wait for the MailtItem to get the focus.