excel - Running VBA from a HYPERLINK()

Question

I'm trying to let my users send email easily from their Excel spreadsheets, which they like to use as front-ends for data entry and manipulation. My intention is to write a function:

generateEmail(name, manager, cc)

and make it so the function can be invoked as-pleased by the user.

In this, Excel calls Outlook, creates a new mail, and dumps the text containing the formatted email to send, which will contain some values changing dynamically per-line from the table they are currently working on. This is the VBA function:

Function generateEmail(name, manager, cc)
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    strbody = "To Service Desk:" & vbNewLine & vbNewLine & _
              "Please open a ticket for CS/oe/ns/telecom/dal to request a new extension. Please find the details attached:" & vbNewLine & vbNewLine & _
              "Name: " & name & vbNewLine & _
              "Manager: " & manager & vbNewLine & _
              "CC: " & cc

    On Error Resume Next
    With OutMail
        .To = "[email protected]"
        .cc = ""
        .BCC = ""
        .Subject = "New Extension Request"
        .Body = strbody
        .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing

End Function

Then, in the rightmost column of the table I'm adding this to, I make a column of links:

=HYPERLINK(generateEmail(H2, I2, M2), "Generate Email")

Where H2, I2 and M2 are the values that I need to inject into the email that will be generated. The users will also have data on H3, I3, M3, H4, I4, M4... and so on.

This actually works as expected, however when I make the hyperlink show up on the spreadsheet, it fires the event as soon as Excel detects a mouseover over the link. I'd wish to make it so the links are clickable and fire the function only after the user has clicked.

Is there any way to do this?

My search has come up empty, basically advising people to create links manually per cell from VBA itself. Since the dataset stored by my users will grow over time I need a way to create a new "Send Email" link every time the user adds a record.

Answer 1

To run the function only on a click you can try entering as a subaddress:

=HYPERLINK("#generateEmail(H2, I2, M2)", "Generate Email")

and then add an extra line in the code to return the current address:

Function generateEmail(name, manager, cc)

Set generateEmail = Selection
'Paste Outlook Code Here 

End Function

Note this method does not get executed on the main vba thread so to test you can insert statements in the code instead of stepping through, eg:

Range("A10:C10") = Array(name, manager, cc)