Make selected file path a hyperlink

Office programs convert URLs into hyperlinks very easily, like with a space after the address. But they don't do this if the address is to a file path. I send file paths to colleagues in e-mails, Word documents, and Excel files frequently, so I wanted to make it easy to convert the text of a file path into a hyperlink to the file or folder. The quickest built-in way to do this is select the file path and use the keyboard shortcuts Ctrl + C, Ctrl + K, Ctrl +V, Enter. But now I've created a macro, so it's a click (or a single keyboard shortcut, if you like) away.

Fortunately for me, there's a simpler way to do this. Unfortunately, there are three different ways for the three different programs I want to use.

Word

Just use "Selection.Range" wherever you want to use it. I called my macro "Copy_Link_Paste."

Just paste this into Word's VBA editor:

Sub Copy_Link_Paste()
'
' Copy_Link_Paste Macro
'
'
    ActiveDocument.Hyperlinks.Add Anchor:=Selection.Range, Address:=Selection.Range, SubAddress:="", ScreenTip:="", TextToDisplay:=Selection.Range
End Sub

This turns the highlighted text into a hyperlink with an address identical to the highlighted text.

Outlook

In Outlook, use this language:

Sub test()

   Dim objOL As Outlook.Application
    Dim objNS As Outlook.NameSpace
    Dim objDoc As Word.Document
    Dim objSel As Word.Selection
  
    On Error Resume Next
    
    Set objOL = Application
    If objOL.ActiveInspector.EditorType = olEditorWord Then
        ' use WordEditor
        Set objDoc = objOL.ActiveInspector.WordEditor
        Set objNS = objOL.Session
        ' set current selection
        Set objSel = objDoc.Windows(1).Selection
            
        ' check selection
        ' Debug.Print objSel.Text
        objDoc.Hyperlinks.Add objSel.Range, Trim(objSel.Text), _
                          "", "", Trim(objSel.Text), ""

    End If
    
    Set objOL = Nothing
    Set objNS = Nothing
        
End Sub

 (from Microsoft user Daniel van den Berg at https://social.msdn.microsoft.com/Forums/en-US/122d560d-6153-4d2b-9c6e-6dccea964a27/vba-for-outlook-how-to-create-a-hyperlink-via-macro?forum=isvvba)

I used this icon because it has the link in a chain like the hyperlink button . Here it is in the Modify Button window:

NOTE: For the macro solutions in Word and Outlook, make sure you have "Microsoft Forms 2.0 Object Library" selected in Tools/References in the VBA window. If you don't see it, browse and select FM20.dll. That will add it, then you'll need to check it.

Excel

In Excel, there is a simpler, built-in solution. It's called the HYPERLINK formula. Put your hyperlinks in one column. In the next column, use the HYPERLINK formula and point to each cell you want to make a hyperlink. For example, if the text you want to change into a hyperlink is in A1, the formula in B1 (if that's where you want it) would be "=HYPERLINK(A1)". You can copy this all the way down the column to capture all the hyperlinks. If you want, you can hide the column with the text but no link. You can either leave the text of the hyperlink or add text to show for the link by adding a second, optional argument to the formula like this: "=HYPERLINK(A1,"link")". That would show the word link with a hyperlink with the address in cell A1. For more on this function, click here.

 When will I use this?

I'll use this most frequently in three situations:

1. E-mail.Hey Tonya, check out the filing we just got from opposing counsel. I've saved it on the shared network here: "F:/Client name/filings/2015_12_11 Outrageous Filing.pdf"I'll use Copy As Path to get that file path to the clipboard, paste it in my e-mail, highlight everything from F: to pdf (that is, the pasted text minus the quotation marks), and click on my macro.
2. Word documents.I might use this for memos that I want to include where the memo is stored, or a list of items that I want to include.
3. Excel workbooksFor example, we work with indexes of documents. For instance, we'll compile a list of all the documents produced in discovery. We'll also save those documents on our network. In the Excel workbook, we want the file path to each indexed document. Voilà!

Bonus: Paste the clipboard for selected text

A related macro doesn't copy the selected text to create the hyperlink. Instead, it uses whatever's in the clipboard to change selected text into a hyperlink. For instance, you might want to change the word "here" to a link to a file or web page. Here's the VBA language:

Dim MyData As DataObject
Dim strClip As String
Set MyData = New DataObject
MyData.GetFromClipboard
strClip = MyData.GetText
ActiveDocument.Hyperlinks.Add Anchor:=Selection.Range, Address:=strClip, SubAddress:="", ScreenTip:="", TextToDisplay:=Selection.Range

Thanks to Microsoft MVP Doug Robbins, who replied to my question here.

Have a question about writing, software, navigating courts, or resources that are helpful in any of those? I am compulsive about finding answers, so let me know if you have a question about something that might relate to all this. Post a question at www.facebook.com/TatumsTips/.