Can Excel send an email to different users based c

Question

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?

Answer 1

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.