Inserting Hyperlink into body of email sent from excel

[pyrrhicvictori]

Hi all, I have tried searching threads and I am still struggling to find an answer that works for me, so I am hoping that you can help.

I have a complicated workbook that is used for event planning purposes. The template is saved to various project folders before use- the project folders are always different by necessity.

Once the PM completes the account team contact information on the Home Tab, I have a button that emails each account team member (listed on the home tab) and tells them the current location of the workbook so they can all contribute. I cannot find a way that inserts a hyperlink of the current save location.

To get the current save location path in the email, I have done the following:
On the "HOME" tab, in cell C55 I have inserted the following formula: =LEFT(CELL("filename"),FIND("[",CELL("filename"),1)-1). This provides the save location of the individual form.
This cell is not hyperlinked (I haven't figured out how)

In the body of the email, I simply refer to that cell to pull the file path over. EMAIL Code:

Private Sub CommandButton1_Click()

Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)


strbody = "Hi," & vbNewLine & vbNewLine & _
"We are ready to begin work on this event." & vbNewLine & _
"I will update the event details as I receive them. Please refer to this form for your event information." & vbNewLine & vbNewLine & _
"Scheduling: Could you please complete the Feedback Schedule located at:" & vbNewLine & _

Worksheets("Home").Range("C55") & vbNewLine & vbNewLine & _ '<---- this is the line that inserts the file path (additional line space added to call this out for this forum, not in original)

"Thank you," & vbNewLine & _
Application.UserName

On Error Resume Next

With OutMail
.To = Worksheets("Home").Range("C26") & ";" & Worksheets("Home").Range("C30") & ";" & Worksheets("Home").Range("C34") & ";" & Worksheets("Home").Range("I8") & ";" & Worksheets("Home").Range("C38")
.cc = Worksheets("Home").Range("C42") & ";" & Worksheets("Home").Range("C46")
.BCC = ""
.Subject = "Link to EPS for " & Worksheets("Home").Range("C4") & " " & Worksheets("Home").Range("C7")
.Body = strbody
.Display

End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing

End Sub


The email works great- no problems there. I am just wondering if there is a way to make this line from the strBody come through as a hyperlink to the save location: Worksheets("Home").Range("C55") & vbNewLine & vbNewLine & _

I thank you in advance for any and all help and/or ideas you can provide.

pv

 

[paulxmw]

hi

the code wont post in its original format as its HTML, i have been told you need to do an advanced reply to post HTML.

Paul