November 28, 2014
Hot Topics:

Adding Attachments to Excel 2007 Email Hyperlinks

  • August 29, 2008
  • By Paul Kimmel
  • Send Email »
  • More Articles »

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.



Click here for a larger image.

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



Click here for a larger image.

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.



Click here for a larger image.

Figure 3: Adding the name of the attachment in an adjacent cell.





Page 1 of 3



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Sitemap | Contact Us

Rocket Fuel