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
