Insert Link To Excel File in Auto-Generated Email

[CorrieAnn]

Team, I have the following code which, starting from a dashboard in Excel, auto-generates an email I send out each morning. Unfortunately, I have to manually insert the link to the report I have created (again, in excel). The report name is in the same location with the same file naming convention each day: Past Due Invoices for <today's date>. So, for today it would be "Past Due Invoices for 11-30-2020.xlsm." I have the following code, but cannot get the file link to generate.

Sub AR_EMAIL_Send_Past_Due_Report()
'
'Send Past Due report to distribution list
'
    Dim OutApp As Object
    Dim OutMail As Object
    Dim Signature As String
    Dim currTime As Date
   
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    On Error Resume Next
    With OutMail
        .Display
    End With
        Signature = OutMail.htmlbody
    With OutMail
        .To = "Emailaddy1@mycompany.com; Emailaddy2@mycompany.com; Emailaddy3@mycompany.com; Emailaddy4@mycompany.com"
        .CC = ""
        .BCC = ""
        .Subject = "Past Due Report for: " & Range("N4")
        .htmlbody = "<a href=" / "S:\CorrieAnn\Collections\Past Due Reports\currTime""></a> & " < br <> br > " & Signature"
        .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

The .htmlbody line is the one not working. Everything else works perfectly.

Any help would be greatly appreciated.

Thanking you in advance!

 

[GWteB]

How about ...

Sub AR_EMAIL_Send_Past_Due_Report()
'
'Send Past Due report to distribution list
'
    Dim OutApp As Object
    Dim OutMail As Object
    Dim Signature As String

    Dim sDate As String, sFile As String, sLink As String

    sDate = Format(Date, "mm-dd-yyyy")
    sFile = "S:\CorrieAnn\Collections\Past Due Reports\Past Due Invoices for " & sDate & ".xlsm"
    sFile = Replace(sFile, " ", "%20")
    sLink = "<a href=file:///" & sFile & ">Report File</a>"
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    With OutMail
        .Display
        Signature = .HTMLbody
        .To = "Emailaddy1@mycompany.com; Emailaddy2@mycompany.com; Emailaddy3@mycompany.com; Emailaddy4@mycompany.com"
        .CC = ""
        .BCC = ""
        .Subject = "Past Due Report for: " & Range("N4")
        .HTMLbody = sLink & "<br><br>" & Signature
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub