How to send HTML attachment including (hyper)link in email-body with Outlook

Question

Scenario:

From an Access-form with selected recordset, a report is filled with data, this is converted via VBA into HTML format and sent with Outlook. The content then appears as HTML in the email-body of the recipient. This contains a link that points to the recordset in this form. So assuming the recipient also has ACCESS and also the exact same database (and same location & content), the link would open his database file when clicked, then open the form, then open that exact record by using the query.

Based on this...

...I added a text field into my report with this content and marked it as a hyperlink:

Click here#C:\MainDatabase.accdb#Query frmForm01

Click the hyperlink in the report works (opens Access & file), the link in the locally stored HTML-tempfile also works.

Problem:

Although the link is sent, also appears in the recipient email-body as link "Click here", but displayed link is not clickable, just blue underlined text!

After a lot of experimenting I found out that it's all Outlook! The link is intact in the Outlook-mail-body preview before sending....it must be an Outlook-setting that converts the link into text during/for sending. In the Outlook menu I have already checked the format settings: convert to HTML is OK!(not plain text). So what else could be the cause??

Would be very grateful for solutions. Thanks!

My code:

Sub CreateHTMLMail()

Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim FSO As Object
Dim AttchFile As String, EmailTo As String, strSig As String

Set OutApp = New Outlook.Application
Set OutMail = OutApp.CreateItem(olMailItem)

    AttchFile = "C:\AttachmTemp.htm"
    EmailTo = "abcd@efgh.com"

    DoCmd.OpenReport "NewReport", acViewReport, , "[ID]='" & Me.ID & "'", acHidden
    DoCmd.OutputTo acOutputReport, "NewReport", acFormatHTML, AttchFile
    
Set FSO = CreateObject("Scripting.FileSystemObject")

strSig = FSO.OpenTextFile(AttchFile).ReadAll
        
    With OutMail
    .TO = EmailTo
    .Subject = "New Message"
    .BodyFormat = olFormatHTML
    .HTMLBody = strSig
    .send
    End With

End If

Kill AttchFile

Set OutApp = Nothing
Set OutMail = Nothing

End Sub

Answer 1

I do this using a custom URL protocol handler.

First, create (and distribute after testing) a .reg file like this (replace myapp by your application name):

Windows Registry Editor Version 5.00

; Andre, 2019

[HKEY_CLASSES_ROOT\myapp]
@="URL:myapp-Open"
"URL Protocol"=""
"EditFlags"=hex:02,00,00,00

[HKEY_CLASSES_ROOT\myapp\DefaultIcon]
@="C:\\path\\myapp\\myapp.ico"

[HKEY_CLASSES_ROOT\myapp\shell]
@="open"

[HKEY_CLASSES_ROOT\myapp\shell\open]

[HKEY_CLASSES_ROOT\myapp\shell\open\command]
; using a .vbs to start my app
; @="wscript C:\\path\\myapp\\Startmyapp.vbs \"%1\""
; (untested) starting Access directly, needs the /cmd switch
@="\"C:\\Program Files (x86)\\Microsoft Office\\Office16\\msaccess.exe\" C:\\path\\myapp\\Myapp.accde /cmd \"%1\""

; If you use Outlook, prevent the Security warning
; https://stackoverflow.com/a/34660198/3820271
; Note: the whole subtree starting with (at least) "Security" will be created.
; This path is for Office 2016.
[HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\16.0\Common\Security\Trusted Protocols\All Applications\myapp:]

Note that I use a .vbs script to start my Access application, it should work as well with the path to msaccess.exe plus your local frontend.
See comments in myapp\shell\open\command

Then in your HTML email, use URLs like this:

url:myapp:ID:2357     

ID:2357 will be passed as command-line parameter to your application.

See Opening Microsoft Access with parameters on how to read this string, then parse it in your Autoexec function and open your form.

To test this, simply type myapp:ID:2357 in Start/Run or in a Cmd window.