Insert filepath as hyperlink Excel VBA

Question

I have a VBA set of code that generates an email and automatically sends it.

One of the things that I cannot get to work properly is putting a hyperlink to a specified folder location within the email.

Dim fpath As String

fpath = Worksheets("MS_JRNL_OPEN_TU_FR-4333635").Range("AD5").Value

"file://" & fpath & _

Essentially the user has to input a folder location when running the Macro which is in Cell AD5, but I want this is appear as the full folder location as a hyperlink once the email is generated.

Any help would be greatly appreciated

tjb1 tjb1 · Accepted Answer · 2016-10-26T12:01:00

If you are currently using HTMLBody in your email code, it's quite easy to do. I'll assume you are using code similar to below. Take note of strbody and .HTMLBody. Assuming your fpath is formatted like C:\Users\tjb1\Desktop\file.docx then you don't need to add anything else to it. The section creating the hyperlink is "<A href=" & fpath & ">test link</A>". You can change test link to say whatever you want or change the line to "<A href=" & fpath & ">" & fpath & "</A>" to use the path as the link text.

Sub MailURL()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    strbody = "<A href=" & fpath & ">test link</A>"
    On Error Resume Next
    With OutMail
        .To = "[email protected]"
        .Subject = "Testing URL"
        .HTMLBody = strbody
        .Send
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

I found the code above at MrExcel and just formatted it a bit to work with your variable.

Insert filepath as hyperlink Excel VBA

1 Answers