Adding Attachments to Excel 2007 Email Hyperlinks

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.

Page 3 of 3



This article was originally published on August 29, 2008