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.
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.
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.
Finally, I wrap the path as follows:
- Prepend <file:// to the beginning of the path
- Append > at the end of the path
I paste this string into an email and press Enter and POOF! Instant 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
7 Comment(s) on Create Hyperlinks for Email:
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?
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
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.PutInClipboard End SubUse GetOpenFileName to let the user select the path/file, rather than make the user change the code.
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…..
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.
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.