从 HYPERLINK() 运行 VBA [英] Running VBA from a HYPERLINK()

问题描述

我试图让我的用户从他们的 Excel 电子表格轻松发送电子邮件，他们喜欢将其用作数据输入和操作的前端.我的意图是写一个函数:

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.

在此过程中，Excel 调用 Outlook，创建新邮件，并转储包含要发送的格式化电子邮件的文本，其中将包含一些从他们当前正在处理的表格中每行动态更改的值.这是 VBA 函数:

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 = "[e-mail]"
        .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")

其中 H2、I2 和 M2 是我需要注入到将生成的电子邮件中的值.用户还将拥有H3、I3、M3、H4、I4、M4...等数据.

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.

这实际上按预期工作，但是当我在电子表格中显示超链接时，它只要 Excel 检测到鼠标悬停在链接上就会触发事件.我希望使链接可点击并仅在用户点击后触发该功能.

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?

我的搜索结果是空的，基本上是建议人们从 VBA 本身手动创建每个单元格的链接.由于我的用户存储的数据集会随着时间的推移而增长，我需要一种方法来在用户每次添加记录时创建一个新的发送电子邮件"链接.

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.

推荐答案

要仅通过单击运行该功能，您可以尝试输入子地址:

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

注意这个方法不会在主 vba 线程上执行，所以为了测试你可以在代码中插入语句而不是单步执行，例如:

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)

这篇关于从 HYPERLINK() 运行 VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！