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:

mailto:pkimmel@softconcepts.com

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:

1. Click the Office Button in Excel.
2. Click the Excel Options button.
3. Click the Popular item.
4. And, check the Show Developer tab in the Ribbon option (see Figure 4).
5. Click OK.
6. Click the Developer ribbon (now visible).
7. 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:

1. You already switched to the Visual Basic editor.
2. Click Tools|References and add a reference to the Microsoft Outlook 12.0 Object Library (see Figure 5).
3. Click OK to close the References dialog.
4. 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.

Listing 1: Adding an attachment to mail created by an Excel hyperlink.

Private Declare Sub Sleep Lib "kernel32" (ByVal mills As Long)

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

   On Error GoTo Ooops
   Dim Outlook As Outlook.Application
   Set Outlook = New Outlook.Application

   Dim obj As Inspector

   Set obj = Outlook.ActiveWindow

   Dim mail As MailItem

   Dim counter As Integer
   counter = 0
   While (obj.CurrentItem <> MailItem)
      Sleep 5
      DoEvents
      counter = counter + 1
      If (counter > 5) Then GoTo Ooops
   Wend

   Set mail = obj.CurrentItem

   mail.Subject = "Test"
   Dim path As String
   path = Sheet1.Cells(Target.Range.Row, 2)
   Dim attach As Outlook.Attachment

   Set attach = mail.Attachments.Add(path, olByReference, , path)
   attach.DisplayName = path
   mail.Send
   End

Ooops:
   MsgBox "Unable to create mail item", vbExclamation

End Sub

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.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
                                        Cancel As Boolean)

   Dim Outlook As Outlook.Application
   Dim mail As MailItem
   Set app = New Outlook.Application
   Set mail = app.CreateItem(olMailItem)

   mail.To = Target
   mail.Subject = "Test"
   Dim path As String
   path = Sheet1.Cells(Target.Row, 2)
   Call mail.Attachments.Add(path, olByReference)
   mail.Send

End Sub

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.

Copyright © 2008 by Paul T. Kimmel. All Rights Reserved.