Excel可以发送电子邮件给不同的用户的单元格值吗？ [英] Can Excel send an email to different users based cell value?

问题描述

我有一个网络驱动器上共享的文件，约20-30个人工作/更新。

有多个字段，多个表格。

在一些表格中有一个USER列和一个STATUS列。

  ROW ITEM USER STATUS 
 ---------- ------------------------------------------ 
 1网络作业1 John进行中
 2 Web服务A Mike Delivered 
 3 WPF作业2 Amy正在进行
 4测试作业1 Brian Delivered 
  

当使用Delivered更新状态行时（再次，在此工作簿上工作的30个人中的任何一个可以更改状态），是否有可能使VBA宏发生（在这个例子中）迈克和布莱恩说：你的工作已经交付？ / p>

我担心的是，有这么多的手搅拌锅，可以这么说，根据正在更新的工作簿自动化通知过程是不现实的。 >

是值得追求还是应该放弃这个？

解决方案

是的，这是可能的，但正如mehow所说，它可能不是一个共享的工作簿的最好的想法。如果您希望继续下去，我将如何做。为了解决这个问题，我假设每个用户都在他们的机器上安装了Outlook。

首先打开VBA IDE，点击工具 - - >引用...，并选中Microsoft Outlook 14.0对象库旁边的框（您可能有不同的版本号）来添加对Outlook COM的引用。

其次，您可以使用以下代码的一些变体来生成电子邮件。我使用 HTMLBody 属性，因为我通常使用html标签来自动生成电子邮件格式，但您可能只想使用纯文本。创建一个模块并添加此代码。

  Public Sub sendMail（strTo As String，_ 
 strSubject As String ，_ 
 strBodyText As String，_ 
可选strCC As String =，_ 
可选oAttachments As Collection = Nothing）
'此函数创建一个电子邮件并立即发送。 
 
 Dim Attachment As Variant 
 Dim oMailItem As Outlook.MailItem 
 
'创建电子邮件
设置oMailItem = Outlook.Application.CreateItem（olMailItem）
 
'填充电子邮件属性
带oMailItem 
 .Subject = strSubject 
 .To = strTo 
'添加CC收件人，如果有
 .CC = strCC 
 .HTMLBody = strBodyText 
 .BodyFormat = olFormatHTML 
 
 
'添加附件（如果有）
如果没有（oAttachments是没有）然后
每个附件在oAttachments 
 .Attachments.Add（附件）
下一个附件
结束如果
 
'发送！ 
。发送
结束
 
'释放对象
设置oMailItem = Nothing 
 End Sub 
  / pre> 
 
 
第三，您需要在每个包含状态列的工作表中添加一个 Worksheet_Change 事件处理程序应该触发电子邮件。我建议使用VBA代码窗口上方的下拉菜单，通过在左下拉菜单中选择工作表，然后在右侧选择更改来获取正确的功能声明。在该函数中，您需要确保 Target 在STATUS列中，并且与您要查找的字符串值相匹配。我把它作为一个练习，把它放在一起，但让我知道，如果你有任何问题。
 
 
 
有一些需要注意的事情： p> 
 
 
 
 
 
更改事件在进行更改后离开目标单元格后立即触发，电子邮件也是如此。这意味着即使有人不小心更改了状态列，电子邮件仍然会发送。
 
 
 
 
代码正在每个用户的个人计算机上运行，​​因此如果两个用户更改相同的STATUS单元格，电子邮件将从两台机器启动（不太确定Excel多用户更改冲突解决方式如何影响此功能）。
 
 
 
 
 I相信如果Outlook没有运行，它将被启动以发送电子邮件。在这种情况下，Excel可能会挂起。
 
 
 
 
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.

There are multiple fields, multiple sheets.

Across a number of sheets there is a USER colum and a STATUS column.
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'?

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?
解决方案
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.

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.

Second, you can use some variation of the below code to generate an email.  I've used the HTMLBody property 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.
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_Change event 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 Target is 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.

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.


                        
这篇关于Excel可以发送电子邮件给不同的用户的单元格值吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！