Hyperlink with friendly name

[Gerrit.B]

I create in MS Excel an email with the tracking number of their shipment.
To create the hyperlink in VBA I use

URL = "http://wwwapps.ups.com/WebTracking/processRequest?HTMLVersion=5.0&Requester=NES&AgreeToTermsAndConditions=yes&loc=en_nl&tracknum=" & ActiveSheet.Cells(11, 1).Value

How can I create a hyperlink that only shows "ActiveSheet.Cells(11, 1).Value" instead of this long url.

Regards,

Gerrit

 

[smozgur]

You can normally use the TextToDisplay parameter as shown below:

ActiveSheet.Hyperlinks.Add anchor:=ActiveSheet.Cells(11, 2), Address:=URL, TextToDisplay:=CStr(ActiveSheet.Cells(11, 1).Value)

However, since you didn't tell us how you are using the Hyperlinks.Add method, I am not sure if this is the way you are using or creating a formula by using the HYPERLINK function in VBA, or something totally different.

 

[Gerrit.B]

Thanks Suat,

I use vba code below, and do not know were to place TextToDisplay.

Sub SendingSheetAttUPS()

Dim oMSOutlook As Object
Dim oEmail As Object
Dim sAttachment As String
Dim URL As String


Set oMSOutlook = CreateObject("Outlook.Application")
Set oEmail = oMSOutlook.CreateItem(olMailItem)

sAttachment = "H:\Full Order Documents\"

URL = "http://wwwapps.ups.com/WebTracking/processRequest?HTMLVersion=5.0&Requester=NES&AgreeToTermsAndConditions=yes&loc=en_nl&tracknum=" & ActiveSheet.Cells(11, 1).Value


With oEmail
.To = ActiveSheet.Cells(2, 2).Value
.CC = ActiveSheet.Cells(3, 2).Value
.BCC = ActiveSheet.Cells(4, 2).Value
.Subject = ActiveSheet.Cells(6, 1).Value & " " & ActiveSheet.Cells(9, 1).Value
.Body = ActiveSheet.Cells(7, 1).Value & Chr(13) & Chr(13) & URL

' You can add other files by uncommenting the following line.
If ActiveSheet.Cells(9, 3).Value = "PDF" Then
.Attachments.Add sAttachment & ActiveSheet.Cells(9, 1).Value & ".pdf"
Else
.Attachments.Add sAttachment & ActiveSheet.Cells(9, 1).Value & ".zip"
End If


.Display
End With


Set oMSOutlook = Nothing
Set oEmail = Nothing

End Sub

 

[smozgur]

I see. You want a clickable link in the email message with the tracking number as the displayed text. Please ignore my previous post, because it has nothing to do with the Hyperlinks in Excel.

In order to make it work as explained, you need to use .HTMLBody property instead of .Body.
Please disable the line starting with .Body in your code and use the one I wrote below and try it. Please note since we are using HTMLBody now, we need "<br>" instead of Chr(13) to create new lines.

'.Body = ActiveSheet.Cells(7, 1).Value & Chr(13) & Chr(13) & URL
.HTMLBody = ActiveSheet.Cells(7, 1).Value & "<br><br>" & "<a href=""" & URL & """>" & ActiveSheet.Cells(11, 1).Value & "</a>"

 

[Gerrit.B]

Thanks Suat,

It's working perfect now!

Gerrit

 

[smozgur]

Glad to hear it helps. Thanks for the feedback.

Please consider marking the post as solution that answered your question to help future readers. It is an easy click on the tick mark icon right next to that post but might be a huge help for someone else looking for a similar solution.