Using Hyperlink from Excel cell in an Email

[JBSMichael]

Hi,

I am trying to extract the hyperlink in an Excel cell using VBA and use it in an email message created by the same procedure. The code I have so far is this (the link is to a Sharepoint hosted file, hence the '%20' substitution):



Set outApp = CreateObject("Outlook.Application")
Set outMail = outApp.CreateItem(0)
With outMail
.To = strOwner
.Subject = "[AUTOMATIC EMAIL] Controlled Document Due for Review"
.Body = "Dear " & strTo & "," & vbCrLf & vbCrLf & strBody & vbCrLf & vbCrLf & "Document name: " & sh.Cells(row, 4) & vbCrLf & "Review due date: " & sh.Cells(row, 7) & vbCrLf & "Link: " & Replace(sh.Cells(row, 3).Hyperlinks(1).Address, " ", "%20")
.votingoptions = "Reviewed, no change;Document updated, please update List"
.Display [OR USE '.Send' TO SEND AUTOMATICALLY WITH NO OPPORTUNITY TO CHECK]
End With


However, when it runs, I often (but not always) get an email with the relevant line reading like this: 'Link: ../../../../benefits/Working Folder/Benefits Framework' with no full path, and the text is not inserted as a hyperlink, just dumb text. Does anyone know why this might be?

TIA

[snb]

Are you familiar with .htmlbody ?

[JBSMichael]

Hi snb. No, I'm not. Can you expand?

[JBSMichael]

Okay, so I googled the .HTMLBody property and ended up amended the VB to read like this - the bit I can't get to work is in bold (I hope):



Set outApp = CreateObject("Outlook.Application")
Set outMail = outApp.CreateItem(0)
With outMail
.To = strOwner
.Subject = "[AUTOMATIC EMAIL] Controlled Document Due for Review"
.HTMLBody = "Dear " & strTo & ", <br><br>" & strBody & "<br><br> Document name: " & sh.Cells(row, 4) & "<br> Review due date: " & sh.Cells(row, 7) & "<br> Link: <a href=" & Replace(sh.Cells(row, 3).Hyperlinks(1).Address, " ", "%20") & "</a>"
'.Body = "Dear " & strTo & "," & vbCrLf & vbCrLf & strBody & vbCrLf & vbCrLf & "Document name: " & sh.Cells(row, 4) & vbCrLf & "Review due date: " & sh.Cells(row, 7) & vbCrLf & "Link: " & Replace(sh.Cells(row, 3).Hyperlinks(1).Address, " ", "%20")
.votingoptions = "Reviewed, no change;Document updated, please update List"
.Display '[OR USE '.Send' TO SEND AUTOMATICALLY WITH NO OPPORTUNITY TO CHECK]
End With


The result of the code above is the line that starts 'Link:...' now has NO hyperlink address text, hyperlinked or otherwise.

Going insane here..!

[snb]

Sub M_snb()
With CreateObject("Outlook.Application").CreateItem(0)
.To = "yyy@xxxxx"
.Subject = "[AUTOMATIC EMAIL] Controlled Document Due for Review"
.HTMLBody = "Dear " & strTo & ", <br><br>" & strBody & "<br><br> Document name: " & Cells(1, 4) & "<br> Review due date: " & Cells(1, 7) & "<br> Link: <a href=" & Sheet1.Hyperlinks(1).Address & ">" & Sheet1.Hyperlinks(1).Address & "</a>"
.Display
End With
End Sub

You'd better check the result of any concatenated string using


Msgbox "Dear " & strTo & ", <br><br>" & strBody & "<br><br> Document name: " & Cells(1, 4) & "<br> Review due date: " & Cells(1, 7) & "<br> Link: <a href=" & Sheet1.Hyperlinks(1).Address & ">" & Sheet1.Hyperlinks(1).Address & "</a>"

[JBSMichael]

snb, thank you. That did the trick!

[snb]

Thaat saves a lot of neuleptil. :yes