January 27, 2021
Hot Topics:

Adding Attachments to Excel 2007 Email Hyperlinks

  • By Paul Kimmel
  • Send Email »
  • More Articles »

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.

Click here for a larger image.

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.

Click here for a larger image.

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.

Page 2 of 3

This article was originally published on August 29, 2008

Enterprise Development Update

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

Thanks for your registration, follow us on our social networks to keep up-to-date