Add hyperlink on a word in textbox

[Gaurangg]

Hi Friends,

Please, If anyone can help me in this scenario. I have created an email template into excel. When I had build a macro to copy this template to clipboard to copy it into outlook body, it comes with table format. Then I had tried to copy this template through adding a textbox and it works perfectly. However It copies the template as plain text. In a template there is a sentence i.e. "Please visit our website" in which website word has an hyperlink which I also want in the outlook body. Please help me with the coding. Below is my cell wise email template


Cell Sentence

C1 Hi,
C3 Thank you for contacting us. We will surely look into your concern.
C5 your queries can be answered in 48 working hours. Thank you for your patience.
C7 For any further information, Please visit our website

 

[Kenneth Hobson]

It would be better to use a Word object I would think.

Otherwise, Replace() might suffice. e.g.

Sub EmailIt()
  Dim s As String, url As String
   'Add reference: Microsoft Outlook xx.x Library, where xx.x is 14.0, 15.0, 16.0, etc.
  Dim olApp As Outlook.Application, olMail As Outlook.MailItem
  
  s = "For any further information, Please visit our website"
  url = "https://www.mrexcel.com/forum/excel-questions/1025591-add-hyperlink-word-textbox.html"
  s = Replace(s, "website", "<a href=""" & url & """>website</a>")
 
  Set olApp = New Outlook.Application
  
  Set olMail = olApp.CreateItem(olMailItem)
  With olMail
    .To = "ken@gmail.com"
    .Subject = "Test Hyperlink"
    .HTMLBody = s
    .Display
    '.Send
  End With
  
  Set olMail = Nothing
  Set olApp = Nothing
End Sub

See this link for the actual html coded string for the s= line. https://www.dropbox.com/s/iozud9re5l0noj8/ReplaceWithURL.xlsm?dl=0