Share via

How to retain hyperlinks in email body vba code

Anonymous
2019-01-08T21:05:49+00:00

Hello,

I've created code to send email from selection on current worksheet in Excel. All working perfectly except the cell that contain hyperlinks are coming as plain blue text (and are not clickable). Please help!!

Here is the current code on my workbook:

Sub SendDailyReport_inOutlookEmail()

    Dim objSelection As Excel.Range

    Dim objTempWorkbook As Excel.Workbook

    Dim objTempWorksheet As Excel.Worksheet

    Dim strTempHTMLFile As String

    Dim objTempHTMLFile As Object

    Dim objFileSystem As Object

    Dim objTextStream As Object

    Dim objOutlookApp As Outlook.Application

    Dim objNewEmail As Outlook.MailItem

    Dim regDate As String

    'Copy the selection

    Set objSelection = Selection

    Selection.Copy

    'Paste the copied selected ranges into a temp worksheet

    Set objTempWorkbook = Excel.Application.Workbooks.Add(1)

    Set objTempWorksheet = objTempWorkbook.Sheets(1)

    'Keep the values, column widths and formats in pasting

    With objTempWorksheet.Cells(1)

         .PasteSpecial xlPasteValues

         .PasteSpecial xlPasteColumnWidths

         .PasteSpecial xlPasteFormats

    End With

    'Save the temp worksheet as a HTML file

    Set objFileSystem = CreateObject("Scripting.FileSystemObject")

    strTempHTMLFile = objFileSystem.GetSpecialFolder(2).Path & "\Temp for Excel" & Format(Now, "YYYY-MM-DD hh-mm-ss") & ".htm"

    Set objTempHTMLFile = objTempWorkbook.PublishObjects.Add(xlSourceRange, strTempHTMLFile, objTempWorksheet.Name, objTempWorksheet.UsedRange.Address)

    objTempHTMLFile.Publish (True)

    'Create a new email

    Set objOutlookApp = CreateObject("Outlook.Application")

    Set objNewEmail = objOutlookApp.CreateItem(olMailItem)

    'Read the HTML file data and insert into the email body

    Set objTextStream = objFileSystem.OpenTextFile(strTempHTMLFile)

    objNewEmail.HTMLBody = objTextStream.ReadAll

    objNewEmail.Display

    objNewEmail.Subject = "Daily Reports" & Date

    objNewEmail.To = ""

    objNewEmail.CC = ""

    objTextStream.Close

    objTempWorkbook.Close (False)

    objFileSystem.DeleteFile (strTempHTMLFile)

End Sub

Windows for home | Windows 10 | Accessibility

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments No comments

Answer accepted by question author

  1. Anonymous
    2019-01-09T11:59:19+00:00

    Hi Hillary,

    Thank you for writing to Microsoft Community forum.

    As per my research, this requires hardcoding of the server name and the site name, and then concatenation with the ID parameter in the following format:

    <a href=http://server/site/list/dispform.aspx?ID= <ID parameter>> Your link </a>


    For more information regarding the same, I suggest you to post your query in the MSDN forums, where our Moderators and Contributors are ready to assist you.

    Regards,

    0 comments No comments

0 additional answers

Sort by: Most helpful