从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 = "helpdesk@test.com"
.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.
有没有办法这样做？
我的搜索已经空了，基本上建议人们创建从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屋！
