Change text email address ( into a clickable hyperlink address RRS feed

  • Question

  • I use a macro to supply text to the body of an email.  The text includes an email address.

    Dim sText As String

    sText = ""

    However, after the macro inserts the text, the address is not clickable unless I select the location immediately following the address and hit space or enter.  How do I accomplish this within the macro itself so I don't need to manually add the space.

    Wednesday, February 24, 2016 7:44 PM

All replies

  • You need to change the body of the email to HTML first:

    Sub CreateHTML()
        Dim strHTML As String
        Dim objMail As Outlook.MailItem
        'Create the string that is the body contents
        strHTML = "<HTML><BODY>"
        strHTML = strHTML & "<a href=''>Example</a>"
        strHTML = strHTML & Chr(10) & "<FONT SIZE=2></FONT></BODY></HTML>"
        strHTML = Replace(strHTML, Chr(10), "</p><p>")

        Set objMail = Application.CreateItem(olMailItem)
        With objMail
            'Set body format to HTML
            .BodyFormat = olFormatHTML
            .HTMLBody = strHTML
        End With
        Exit Sub
        MsgBox "Some sort of error occurred."

    End Sub

    Wednesday, February 24, 2016 9:40 PM
  • You could also use the Outlook Inspector to edit the e-mail as a Word document e.g.

    Option Explicit
    Sub AddHyperlink()
    Dim olEmail As Outlook.MailItem
    Dim olInsp As Outlook.Inspector
    Dim wdDoc As Object
    Dim oLink As Object
    Dim oRng As Object
    Dim strLink As String
    Dim strLinkText As String
        'The texts before and after the link
    Const strText1 As String = "If you wish to download or view our latest catalogue, please simply follow this link: " & vbCr & vbCr
    Const strText2 As String = vbCr & vbCr & "Should you wish to review or enquire about any of our products, please do not hesitate to get in touch."
    strLink = ""        ' the link address
    strLinkText = "Click here for Graham Mayor's Web Site "        ' the link display text
        On Error Resume Next
        Set olEmail = CreateItem(olMailItem)
        With olEmail
            .BodyFormat = olFormatHTML
            Set olInsp = .GetInspector
            Set wdDoc = olInsp.WordEditor
            Set oRng = wdDoc.Range(0, 0)
            oRng.Text = strText1
            oRng.collapse 0
            Set oLink = wdDoc.Hyperlinks.Add(Anchor:=oRng, _
                                             Address:=strLink, _
                                             SubAddress:="", _
                                             ScreenTip:="", _
            Set oRng = oLink.Range
            oRng.collapse 0
            oRng.Text = strText2
        End With
        Exit Sub
    End Sub

    Graham Mayor - Word MVP

    Thursday, February 25, 2016 6:06 AM