Search code examples
excelvbahyperlinkhtml-email

Add a hyperlink to a body of text

The following code to sends an email when a change is made to a workbook.

I would like to change the file location in the body of the text so that it is a hyperlink.

I tried click<a href=""

I removed email addresses for data protection.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim Outlook As Object, EMail As Object
    
    Set Outlook = CreateObject("Outlook.Application")
    
    Set EMail = Outlook.CreateItem(0)
    
    With EMail
        .To = " "
        .CC = ""
        .BCC = ""
        .Subject = "Billing Block Sheet"
        .Body = "Hi." & vbCrLf & vbCrLf & "A change has been made to the billing block spreadsheet." & vbCrLf & vbCrLf & "P:\Admin\Wilson Pre pack (billing block).xlsm"
        .Display   'or use .Send to skip preview
    End With
    
    Set EMail = Nothing
    
    Set Outlook = Nothing
End Sub
    
Private Sub Workbook_Open()
    
End Sub
Solution

  • You need to use .HTMLBody instead of .Body and <br> instead of vbCrLf and you need to double up on the quote marks since you are building a string and you want the quotes included in the string.

    Thy it like this:

    .HTMLBody = "Hi.<br><br>A change has been made to the billing block spreadsheet.<br><br><a href=""P:\Admin\Wilson Pre pack (billing block).xlsm"">click here</a>"