How to insert a hyperlink and table into a cell in the Excel sheet?

Question

This is the code in outlook VBA

Sub Sendmail()
    Dim olItem As Outlook.MailItem
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSht As Excel.Worksheet
    Dim sPath As String
    Dim iRow As Long
    Dim strRFIitems As String
    Dim Signature As String

    sPath = "**"

    '   // Excel
    Set xlApp = CreateObject("Excel.Application")
    '   // Workbook
    Set xlBook = xlApp.Workbooks.Open(sPath)
    '   // Sheet
    Set xlSht = xlBook.Sheets("Sheet1")
    '   // Create e-mail Item
    Set olItem = Application.CreateItem(olMailItem)

    strRFIitems = xlSht.Range("E2")
    Signature = xlSht.Range("F2")

    With olItem
        .To = Join(xlApp.Transpose(xlSht.Range("A2", xlSht.Range("A9999").End(xlUp))), ";")
        .CC = Join(xlApp.Transpose(xlSht.Range("B2", xlSht.Range("B9999").End(xlUp))), ";")
        .Subject = xlSht.Range("C2")
        .Body = xlSht.Range("D2") & Signature
        .Attachments.Add (strRFIitems)
        .Display
    End With

    '   // Close
    xlBook.Close SaveChanges:=True
    '   // Quit
    xlApp.Quit

    Set xlApp = Nothing
    Set xlBook = Nothing
    Set xlSht = Nothing
    Set olItem = Nothing
End Sub

The code retrieves the data from the linked Excel sheet. Now the problem is with .Body = xlSht.Range("D2") & Signature this line of code, where the body of the mail is retrieved from the D2 cell of Excel sheet.

And as per my requirement, the body of the mail should contain a hyper link and table along with the text.

Example:

Hello All,
Please update the details in the portal
portal link :http://google.com.

Please contact me for any clarifications.

Below mentioned details needs to be updated:
table has to be inserted here

Suppose above mentioned text is inserted into a cell of Excel.

List item
This portal link: http://google.com. becomes a plain text not a hyper link. If I try to make it a hyper link the entire cell becomes hyper link. i.e even the text.
List item
How to insert table into a cell of Excel sheet and call it using Outlook VBA

Query:
How to insert a hyperlink and table along with the test message into a cell of Excel and retrieve it as it is using the above mentioned code and send a mail containing hyperlink and table.

A cell can be a hyperlink as a formula which is the entire cell or a hyperlink object which is the entire cell but you cannot have a hyperlink as part of other text (e.g. hyperlink within a sentence or paragraph) in a single cell. — user4039065
Usually the email apps like Outlook automatically turn text URLs into links if the email is received. Did you try that? — Pᴇʜ

Harsh Sharma Harsh Sharma · Accepted Answer · 2017-06-20T06:17:18

For hyperlink you can use the following code:

Range("K6").Select
ActiveCell.FormulaR1C1 = "test"
Range("K6").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
    "http://www.google.com", TextToDisplay:="test"
Range("K6").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True

How to insert a hyperlink and table into a cell in the Excel sheet?

1 Answers