vba Excel 可以根据单元格值向不同用户发送电子邮件吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19770110/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can Excel send an email to different users based cell value?
提问by LuFaMa
I wanted to know if it's possible/viable/logical before attempting to do it.
在尝试这样做之前，我想知道它是否可能/可行/合乎逻辑。
I have a shared document on a network drive that about 20-30 people work on/update.
我在网络驱动器上有一个共享文档，大约有 20-30 人在处理/更新。
There are multiple fields, multiple sheets.
有多个字段，多个工作表。
Across a number of sheets there is a USER colum and a STATUS column.
在多个工作表中有一个 USER 列和一个 STATUS 列。
ROW ITEM USER STATUS
----------------------------------------------------
1 Web Job 1 John In Progress
2 Web Service A Mike Delivered
3 WPF Job 2 Amy In Progress
4 Test Job 1 Brian Delivered
When a status row is updated with 'Delivered' (again, any of the 30 people working on this workbook can change the status), is it possible for a VBA macro so fire off an email to (in this example) Mike and Brian saying 'Your work items have been delivered'?
当状态行更新为“已交付”时（同样，处理此工作簿的 30 个人中的任何一个都可以更改状态），VBA 宏是否有可能因此向（在本例中）发送电子邮件给 Mike 和 Brian 说“您的工作项目已交付”？
My concern is that there are so many hands stirring the pot, so to speak, that it's not practical to automate the notification process based on the workbook being updated.
我担心的是，有这么多的手在搅拌锅，可以这么说，根据正在更新的工作簿自动化通知过程是不切实际的。
Is it worth pursuing or should I forgo this altogether?
值得追求还是我应该完全放弃它？
回答by Blackhawk
Yes, it's possible, but as mehow says, it may not be the best idea with a shared Workbook. If you do wish to go ahead with it, here's how I would do it. For the purposes of this solution, I am assuming that each of the users have Outlook installed on their machines.
是的，这是可能的，但正如 mehow 所说，共享工作簿可能不是最好的主意。如果您确实希望继续进行，这就是我的方法。出于此解决方案的目的，我假设每个用户的计算机上都安装了 Outlook。
First, open the VBA IDE, click "Tools" ---> "References..." and check the box next to "Microsoft Outlook 14.0 Object Library" (you may have a different version number) to add a reference to Outlook COM.
首先，打开VBA IDE，点击“工具”--->“引用...”并勾选“Microsoft Outlook 14.0 Object Library”旁边的框（你可能有不同的版本号）添加对Outlook COM的引用.
Second, you can use some variation of the below code to generate an email. I've used the HTMLBodyproperty because I generally use html tags to format automatically generated emails, but you may just want to use plain text. Create a module and add this code to it.
其次，您可以使用以下代码的一些变体来生成电子邮件。我使用了该HTMLBody属性，因为我通常使用 html 标签来格式化自动生成的电子邮件，但您可能只想使用纯文本。创建一个模块并将此代码添加到其中。
Public Sub sendMail(strTo As String, _
strSubject As String, _
strBodyText As String, _
Optional strCC As String = "", _
Optional oAttachments As Collection = Nothing)
'This function creates an email and immediately sends it.
Dim Attachment As Variant
Dim oMailItem As Outlook.MailItem
'Create the email
Set oMailItem = Outlook.Application.CreateItem(olMailItem)
'Populate the email properties
With oMailItem
.Subject = strSubject
.To = strTo
'Add the CC recipients, if any
.CC = strCC
.HTMLBody = strBodyText
.BodyFormat = olFormatHTML
'Add the attachments, if any
If Not (oAttachments Is Nothing) Then
For Each Attachment In oAttachments
.Attachments.Add (Attachment)
Next Attachment
End If
'Send it!
.Send
End With
'Release the object
Set oMailItem = Nothing
End Sub
Third, you will need to add a Worksheet_Changeevent handler to each worksheet that contains a status column that should trigger an email. I recommend using the pull-downs above the VBA code window to get the correct function declaration by selecting "Worksheet" in the left pull-down and "Change" in the right one. In the function, you need to make sure that the Targetis in the STATUS column and that it matches the string value you are looking for. I leave it as an excercise to you to put it all together, but let me know if you have any questions.
第三，您需要向Worksheet_Change包含应触发电子邮件的状态列的每个工作表添加一个事件处理程序。我建议使用 VBA 代码窗口上方的下拉菜单，通过在左侧下拉菜单中选择“工作表”和在右侧下拉菜单中选择“更改”来获得正确的函数声明。在该函数中，您需要确保Target位于 STATUS 列中并且它与您要查找的字符串值匹配。我把它作为练习留给你把它们放在一起，但如果你有任何问题，请告诉我。
There are some gotchas to be aware of:
有一些问题需要注意：
The change event fires immediately after the user leaves the target cell after making the change, meaning so will the email. This means that even if someone accidentally changes the status column, the email will still send.
The code is running on each individual users' machine, so if two users change the same STATUS cell, the email will fire from both machines (not quite sure how the Excel multi-user change conflict resolution affects this).
I believe that if Outlook is not running, it will be started up for the email to be sent. Excel may appear to hang while that's happening.
在用户进行更改后离开目标单元格后，更改事件会立即触发，这意味着电子邮件也会如此。这意味着即使有人不小心更改了状态列，电子邮件仍会发送。
代码在每个用户的机器上运行，因此如果两个用户更改同一个 STATUS 单元格，电子邮件将从两台机器上触发（不太确定 Excel 多用户更改冲突解决方案对此有何影响）。
我相信如果 Outlook 没有运行，它将启动以发送电子邮件。发生这种情况时，Excel 可能会挂起。
