Create Hyperlinks for Email

hyperlink

Often I'll need to email a hyperlink to an Excel workbook to a group of people. I want them to be able to click on the link to open the file. There's the manual way and the VBA way. Here I'll show you both.

The manual way

This is the way I used to do it before I came up with a VBA procedure for Excel and Word. It's also the way I do it for MS Access, since there's no equivalent to "personal.xls" for storing oft-used procedures in Access (short of creating an add-in, I suppose).

First I open Windows Explorer (Windows key+E) and browse to the directory that contains the file.

windows explorer

I open Notepad (in Windows XP, Start » Run » type "notepad" and click OK), copy the path from the address bar in Windows Explorer and paste it into Notepad.

notepad

Then I click on the filename I want to link to, press F2 and Ctrl+C to copy the filename to the Clipboard, and press Esc to finish editing. I switch to Notepad and paste in the filename at the end of the path.

notepad 2

Finally, I wrap the path as follows:

  • Prepend <file:// to the beginning of the path
  • Append > at the end of the path

notepad 3

I paste this string into an email and press Enter and POOF! Instant hyperlink.

email hyperlink

Create Hyperlinks with VBA

I wrote a small VBA procedure that works in Excel (and Word, with slight mod) that creates the hyperlink for you.

It creates a file on your desktop with the hyperlink already formatted. Just place the code into your PERSONAL.XLS workbook, or your NORMAL.DOT template for Word and run the CreateEmailHyperlink procedure. Then check your desktop for "file.txt" which contains the hyperlink. Cut and paste into an email.

It runs on the active workbook or document. I didn't bother with any error checking.

Sub CreateEmailHyperlink() ' For Word, change 'ActiveWorkbook' to 'ActiveDocument' Call CreateFile(environ("userprofile") & "\desktop\file.txt", _ "<file://" & ActiveWorkbook.FullName & ">")End Sub

Visit MSXML Object Library Routines to grab the CreateFile function.

About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »


Related Articles:


Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

comment bubble 7 Comment(s) on Create Hyperlinks for Email:

  1. AlexJ writes:

    JP,
    For me, links in Outlook have always been a problem.

    I have finally stumbled on a solution that makes use of the [Insert File] function, but instead of using the [Insert] button in the dialog box, select the small dropdown arrow on the [Insert] button to choose a link. (Outlook 2003)

    The problem is that I can make this work when the email format is Rich Text, but is not available in Plain Text. Not sure if html works.

    Why is there not an effective and consistent link addition method in Outlook, without resorting to text file manipulation and VBA?

  2. Sebastien Labonne writes:

    In Outlook 2007, I hit CTRL-K to insert an hyperlink and I click on the "Recent Files" button where I can access recently opened documents. Alternatively, I can click the small folder button at the top right portion of the same dialog and browse to the document.

    Sébastien

  3. In Word 2007 I use this piece of code to paste the documents fullname to the clipboard and then use the CTRL+K in Outlook to insert a hyperlink and just paste the text from the clipboard by CTRL+V in the address field and press enter. Or I guess it could be modified so that it includes the <> and then paste it directly into the email.

    Sub CopyDocumentFullName() Dim MyData As DataObject Dim strClip As String strClip = ActiveDocument.FullName Set MyData = New DataObject MyData.SetText strClip MyData.PutInClipboardEnd Sub
  4. Use GetOpenFileName to let the user select the path/file, rather than make the user change the code.

  5. chrisham writes:

    JP, thanks for the brilliant time-saver, and combining Jon's GetOpenFileName function, would make it an even better tool. Just a question, I read the Help on FreeFile, and as always it never makes much sense to me (& it no fault of Microsoft!). Appreciate if you could explain what this function is supposed to be doing…..

  6. When using VB's I/O, the files being manipulated are referenced by an index (like 1, 2, 3, etc.). FreeFile simply finds the next unused file index. This usually doesn't make much difference, but if you're working with multiple files it's crucial.

  7. Rick Rothstein (MVP - Excel) writes:

    As Jon, VB references files that have been opened by the Open statement using "file numbers" that you assign. If you have a lot of files open in a program, keeping track of which number are in use and which are not can be a pain. The FreeFile function makes life simple in two ways… one, it returns the **next** unused file number for you automatically, so you no longer have to track which numbers are in use and which are not; and, second, it allows you to use a "friendly name" for each file that you open. For example, you could do this…

    MyBankAccount = FreeFile
    Open PathAndFileName For Output As #MyBankAccount
    MyStocksAccount
    Open PathAndFileName For Output As #MyStocksAccount

    Now, with both of these still open, you can write info to either one of them just using the "friendly name"…

    If X < 100000 then
    Print #MyBankAccount, SomeValue
    Else
    Print #MyStocksAccount, SomeValue
    End If

    And you can keep referencing them this way until you finally close them…

    If X = 0 Then Close #MyStocksAccount

    Now imagine you had 10 files open… it is much easier to reference them using the "friendly name" than tracking them by the file number.

    Remember, though, the **key** to using FreeFile (when assigning it to a variable) is to use that returned value in an Open statement BEFORE you call FreeFile again. If you did this in code

    MyBankAccount = FreeFile
    MyStocksAccount = FreeFile

    before using MyBankAccount in an Open statement, both MyBankAccount and MyStocksAccount would contain the same variable because UNTIL the value is used, it remains unused and FreeFile always returns the next unused filenumber. The key word in that last sentence is the word UNUSED.

This article is closed to any future comments.
excel school learn dashboards