JBSMichael
02-09-2015, 10:59 AM
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
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