How to Upload to Excel via Hyperlink

Introduction

I write a lot of books. More importantly, I support my books through email, sometimes long later on they were published. I relish solving puzzles when yous guys enquire me to assistance, and if it helps sell books, great.

5 years ago, I wrote an Excel VBA book, Excel 2003 VBA Programmer's Reference. More than succinctly, I updated a second edition of this book, so merely some of the content was mine. In short, this volume is five years old and I don't have Excel 2003 anymore. (I upgraded to Excel 2007 a twelvemonth or so ago.) But, a reader wrote me and asked how he could use an email address hyperlink in his spreadsheet and add together an attachment based on a filename in an adjacent jail 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 like shooting fish in a barrel is better.) As information technology turns out, it doesn't appear to be piece of cake.

It appears quite easy to add a hyperlink to an Excel cell; but type an URL or mailto:[email protected] and Excel turns it into a hyperlink. Right-click on the hyperlink and you can practise a variety of things, such as add a bailiwick or make the link an internal link to a prison cell, but there is no attachment option. Bummer. After an exhaustive search, it appears that several people have this trouble and in that location didn't seem to be whatever existent solutions. Hence, this article.

The case 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 lot very clever readers know a simpler mode and so I encourage you to share it (and let me know).

Creating a MailTo Hyperlink Cell

To add a mailto hyperlink—one that volition generate an email—pick a cell and type the moniker mailto:, and an email address, for example:

Figure 1: Utilise the mailto moniker to create a hyperlink that will generate an electronic mail accost.

If you lot right-click on the hyperlink and select Edit Hyperlink, the Edit Hyperlink dialog will announced (see Figure 2). (The recently used electronic mail addresses show yous all of the ways I tried to tease the attachment in easily. They didn't work.)

Figure two: The Edit Hyperlink dialog supports subjects simply non attachments. (This should probably be changed at Microsoft. Hint. Hint.)

When y'all click on the link in Figure 1, your default postal service provider should popular up with the email address filled in.

What you want to practice side by side is complete the electronic mail along with an attachment and transport it. The next piece of information to add is the name of the attachment in an adjacent cell. The results might look similar those shown in Effigy 3.

Figure iii: 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 zipper. You'll employ VBA for this. The beginning thing you may need to do in Excel 2007 is enable the Developer tab then you tin can get at the VBA editor. To enable the programmer tab and get-go the Visual Basic editor, follow these steps:

1. Click the Office Button in Excel.
2. Click the Excel Options button.
3. Click the Pop particular.
4. And, check the Show Programmer tab in the Ribbon pick (see Effigy four).
5. Click OK.
6. Click the Developer ribbon (now visible).
7. Click the Visual Basic push on the Programmer ribbon.

Figure 4: Turning on the Developer ribbon in Excel 2007.

After Pace vii. you should be in the VBA editor. In the object dropdown, select the Worksheet particular. In the Procedure dropdown, select the FollowHyperLink process. This will generate an event handler that is chosen 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 Bones editor.
2. Click Tools|References and add a reference to the Microsoft Outlook 12.0 Object Library (encounter Figure five).
3. Click OK to close the References dialog.
4. You already added the FollowHyperlink method. At present, you are ready to write some code.

Figure 5: Add a reference to Outlook.

The code will need to basically intercept the mail particular created by the hyperlink and stuff some data in it and ship the email. To summarize, y'all need to create (or obtain) an example to Outlook, grab the MailItem created past the hyperlink, add the attachment, and send the email. The code in Listing i should piece of work. The Sleep API method was added considering sometimes the VBA code runs faster than Outlook, and so Sleep is used to wait for the MailtItem to become the focus.

List 1: Adding an attachment to postal service created by an Excel hyperlink.

Private Declare Sub Sleep Lib "kernel32" (ByVal mills As Long)  Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)     On Fault GoTo Ooops    Dim Outlook Equally Outlook.Application    Set Outlook = New Outlook.Application     Dim obj As Inspector     Gear up obj = Outlook.ActiveWindow     Dim post As MailItem     Dim counter As Integer    counter = 0    While (obj.CurrentItem <> MailItem)       Slumber 5       DoEvents       counter = counter + ane       If (counter > 5) Then GoTo Ooops    Wend     Set mail = obj.CurrentItem     mail service.Subject = "Test"    Dim path As String    path = Sheet1.Cells(Target.Range.Row, two)    Dim attach As Outlook.Zipper     Prepare attach = mail.Attachments.Add(path, olByReference, , path)    attach.DisplayName = path    mail service.Transport    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 lawmaking bail out if something goes wrong.

The adjacent two statements declare and create an example of Outlook. The adjacent argument 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 non be the email.)

Next, when the e-mail has the focus, the lawmaking assigns information technology to the MailItem variable mail. Next, the Field of study is fix; this can be anything. The path is read from the worksheet cell past 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 volition also exist the path. (The statement attach.DisplayName = path is not required.) Finally, MailItem.Send is chosen and the sub routine ends. Remember to place the End argument before the Ooops label, so that lawmaking is only run on an error.

I tested the code several times and information technology seems to work fine. You also accept the option of not using a hyperlink in the worksheet and then you tin shorten the code from Listing ane with a basic click event shown in Listing 2.

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

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _                                         Cancel Equally Boolean)     Dim Outlook As Outlook.Application    Dim mail As MailItem    Set app = New Outlook.Application    Set mail = app.CreateItem(olMailItem)     mail.To = Target    postal service.Subject = "Examination"    Dim path Every bit String    path = Sheet1.Cells(Target.Row, 2)    Call mail.Attachments.Add(path, olByReference)    mail.Send  End Sub        

Summary

This solution lets you lot effectively intercept an electronic mail generated by a hyperlink and stuff an attachment in it. In reality, Excel should simply have an zipper pick for hyperlinks, but information technology doesn't seem to take this option.

If you lot effigy 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 well-nigh it (at to the lowest degree).

I volition unabashedly tell yous that I don't spend a lot of fourth dimension using Excel anymore; I spend most of my time in UML and C# these days. Thus, I suspect at least ane person out there has a better solution. I'd like to hear most information technology.

About the Author

Paul Kimmel is the VB Today columnist for world wide web.codeguru.com and has written several books on object-oriented programming and .Internet. Check out his upcoming book LINQ Unleashed for C#; guild your copy today at Amazon.com. Paul Kimmel is an Application Architect for EDS. You lot may contact him for technology questions at [email protected].

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

rosselicted.blogspot.com

Source: https://www.developer.com/guides/adding-attachments-to-excel-2007-email-hyperlinks/

Share this post