1

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

Improve this question

1 Answer 1

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thats brilliant, It was the using the path as the link text that I was trying to get sorted. I've looked at MrExcel before but it never came up when I was searching for a solution. Thank you
Using HTMLBody will also include the users signature when it generates the email. I'm not sure how it works with .Send because when I use it I am using .Display so the user can enter extra information if needed. Note that the sample code has .Send so you will not see a preview of the email.
Just something to keep in mind, I've encountered this warning message when making Excel call up Outlook - support.office.com/en-us/article/… The fix involved changing the security of the file but I'm not sure how or what as someone else did it.
I do use .Send and it still attaches the users email signature at the bottom of the email through the exchange server. Haven't encountered that issue before, but if I do I thank you in advance!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.