Search code examples
excelvbaoutlook

Open file & call sub-procedure in email hyperlink

I'm looking to open an Excel file and run a sub-procedure entitled "LoadCSV" when you click a hyperlink in an email.

I've attempted this using the code beneath. The mail body contains a link which when clicked *should *call the OpenExcel function which *should *open the file and run LoadCSV. However, I see the following when I click the hyperlink.

1

When I select "Yes", nothing happens.

`

Sub EmailWithAttachement()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim EmailAddress As String
    Dim AttachmentPath As String
    Dim MailSubject As String
    Dim MailBody As String
    
    ' Define email address, attachment path, and mail subject
    EmailAddress = ThisWorkbook.Sheets("Write").Range("H2").Value
    AttachmentPath = ThisWorkbook.Path & "\" & ThisWorkbook.Sheets("Write").Range("I2").Value & ".pdf"
    MailSubject = Worksheets("Write").Range("J2").Value
    ' Create hyperlink with onclick event to trigger OpenExcel function
    MailBody = "Please click <a href='javascript:void(0);' onclick='OpenExcel()'>here</a> to open Excel and load the CSV file."
   
    ' Check if the attachment file exists
    If Dir(AttachmentPath) = "" Then
        MsgBox "Attachment file not found.", vbExclamation
        Exit Sub
    End If
    
    ' Create Outlook application and email
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
    ' Create and send email
    With OutMail
        .To = EmailAddress
        .Subject = MailSubject
        .HTMLBody = MailBody
        .Attachments.Add AttachmentPath
        .Send ' Display email to the user
    End With
    
    ' Clean up
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

' Function to open Excel and load the CSV file
Sub OpenExcel()
    ' Open Excel file and call LoadCSV subroutine
    Dim xlApp As Object
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Workbooks.Open "O:\SYDDEPT-Finance\Sales\Work Tools - DOS\AU PC.xlsm"
    xlApp.Visible = True
    xlApp.Run "LoadCSV"
End Sub`
Solution

  • Email client applications including Outlook prevent any scripts from running in the message body for security reasons. The best what you could do is to insert a link to the file for downloading it (or opening).