Adding Attachments to Excel 2007 Email Hyperlinks

Introduction

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:someaddress@whatzit.com 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:

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

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.

The statement that begins with Private Declare imports the Windows API Sleep method. Worksheet_FollowHyperlink will be called when a user clicks your hyperlink. The argument Target represents that link. The On Error GoTo lets the code bail out if something goes wrong.

The next two statements declare and create an instance of Outlook. The next statement declares an Inspector object, and the statement after that obtains the ActiveWindow from Outlook. The while loop throws in several attempts to get the MailItem (email) created when the mailto link is clicked. (Without this code sometimes VBA runs faster than Outlook and the CurrentItem will not be the email.)

Next, when the email has the focus, the code assigns it to the MailItem variable mail. Next, the Subject is set; this can be anything. The path is read from the worksheet cell by obtaining the desired cell relative to the hyperlink cell. In the example, the attachment is on the same row in the second column. The path is used to create the attachment and the DisplayName will also be the path. (The statement attach.DisplayName = path is not required.) Finally, MailItem.Send is called and the sub routine ends. Remember to place the End statement before the Ooops label, so that code is only run on an error.

I tested the code several times and it seems to work fine. You also have the option of not using a hyperlink in the worksheet and then you can shorten the code from Listing 1 with a basic click event shown in Listing 2.

Listing 2: If you want to create the mail completely manually, this code will work too.

Summary

This solution lets you effectively intercept an email generated by a hyperlink and stuff an attachment in it. In reality, Excel should simply have an attachment option for hyperlinks, but it doesn't seem to have this option.

If you figure out a simpler way to add attachments to hyperlinks in Excel worksheets, consider responding to this article on codeguru.com, writing your own article, or blogging about it (at least).

I will unabashedly tell you that I don't spend a lot of time using Excel anymore; I spend most of my time in UML and C# these days. Thus, I suspect at least one person out there has a better solution. I'd like to hear about it.

About the Author

Paul Kimmel is the VB Today columnist for www.codeguru.com and has written several books on object-oriented programming and .NET. Check out his upcoming book LINQ Unleashed for C#; order your copy today at Amazon.com. Paul Kimmel is an Application Architect for EDS. You may contact him for technology questions at pkimmel@softconcepts.com.

Top White Papers and Webcasts

U.S. companies are desperately trying to recruit and hire skilled software engineers and developers, but there is simply not enough quality talent to go around. Tiempo Development is a nearshore software development company. Our headquarters are in AZ, but we are a pioneer and leader in outsourcing to Mexico, based on our three software development centers there. We have a proven process and we are experts at providing our customers with powerful solutions. We transform ideas into reality.

When individual departments procure cloud service for their own use, they usually don't consider the hazardous organization-wide implications. Read this paper to learn best practices for setting up an internal, IT-based cloud brokerage function that service the entire organization. Find out how this approach enables you to retain top-down visibility and control of network security and manage the impact of cloud traffic on your WAN.