Send hyperlinks in email as links extracted from excel cells

I have a cell which asks for location of documents. I want to send an email with that document location as a clickable link by specifying the href as the document location.

I extracted document location from the cell :

document_location = Range("F22").Value

I got the document_location as string.

Now, the thing is that I want to send an email and the Email body should consist of a link with the document_location as href.

I tried this :

olMail.HTMLBody = "<BODY style=font-size:10pt;font-family:Verdana>" & "<a href=" & document_location & ">" & document_location_as_clickable_link & "</a>" & "</BODY>"

But this shows the document_location_as_clickable_link as text, not reading the href statement.

I also tried this :

olMail.HTMLBody = "<BODY style=font-size:10pt;font-family:Verdana>" & "<a href=document_location>" & document_location_as_clickable_link & "</a>" & "</BODY>"

this converts the document_location_as_clickable_link into a hyperlink, but the href part is taken as "document_location", not the value which is extracted from cell. By saying this I meant, when i click document_location_as_clickable_link, it opens in a new tab, but the address turns out to be : http://document_location/ , which is not the value extracted from the cell.

Please help. Thanks.

1 answer

  • answered 2018-05-16 09:13 EvR

    Try:

    Sub MailLink()
    With CreateObject("Outlook.Application").CreateItem(0)
        .To = Cells(1, 1)
        .CC = Cells(2, 1)
        .Subject = "Test"
        .HTMLBody = "<BODY style=font-size:10pt;font-family:Verdana>" & "<a href=""" & Range("F22") & """ >" & Range("F22") & " </a>" & "</BODY>"
        .display
    End With
    

    End Sub