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 Try the following code with the link copied into column C row 55

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 & vbNewLine

'sBody = "Worksheets("Home").Range ("C55") & vbNewLine & vbNewLine 

hyperlinkBody = Sheets("Home").Range("C55").Value & vbNewLine & vbNewLine

tBody = "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 & hyperlinkBody & tBody
.Display

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

[\code]

Paulxmw

 

[pyrrhicvictori]

Hi Paulxmw,
First- thank you for taking the time to respond. I appreciate it. I was not able to get this to work. After the update to the code, the email body now populates:


FalseThank you,
it does insert the user name correctly.

It appears that everything before tBody now will not populate in the email and reports back False. Any ideas? Below is a copy of the code as I have input it:

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 & _
sBody = Worksheets("Home").Range("C55") & vbNewLine & vbNewLine & _
hyperlinkBody = Sheets("Home").Range("C55").Value & vbNewLine & vbNewLine
tBody = "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 & hyperlinkBody & tBody
.Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing

End Sub




<o></o>

 

[paulxmw]

hi
is the sheet called Home if not you will need to change Home in the code to whatever the sheet is called

Paulxmw

 

[pyrrhicvictori]

Hi, yeah, the sheet is called Home and the location of the cell is correct also. I'm at a loss. Thanks for taking the time to try to help!

 

[Norie]

If you want a hyperlink in the body of an email I think you'll need to use HTMLBody instead of Body.

What is the hyperlink actually for?

Is it a website, a file on a network?

 

[pyrrhicvictori]

Hi Norie,
The link sends the file location of where the worksheet is saved. I will try converting to HTMLBody. Thank you,
Jess

 

[paulxmw]

hi

the code does work i think your going wrong on line if you try the code again and not make any changes to see if it works then make your changes

Paulxmw

 

[pyrrhicvictori]

I was able to piece together from various places something that works... thank you both!
EDIT: I noticed when I posted, it dropped some from the code: in the c00 body, in order to get the new line I used: "& <br> &" - but for some reason it doesn't come through here and shows as "&" [newline] "&"

Private Sub CommandButton1_Click()

Dim OutApp As Object
 
    c00 = "Hello," & "
" & "We are ready to being working on this event.  I have initiated the Event Planning Sheet, and will update event details as I receive them." & "
" & "
" & "You can access the Event Planning Sheet here:  EPS " & "
" & "
" & "Scheduling, please complete the Feedback at schedule for this event at the above location." & "
" & "
" & "**Please note that if you are an external team member you may not be able to access the file above.  Your Program Coordinator or PM will be able to provide you with information regarding this event.**" & "
" & "
" & "Thank you," & "
"
    tBody = Application.UserName
     
    With CreateObject("Outlook.Application").CreateItem(0)
        .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")
        .HTMLBODY = c00 & tBody
        .Display
    End With
  Unload Me
    
End Sub

 

[pyrrhicvictori]

Note that the above code altered when I posted... in the c00 message, every where the double quotes appear should show [<]br[>] not double quotes. Also, I had to include the brackets here to get it to show correctly, brackets are not included in code above. I may just not know how to get this to work on this message board.