Auto notify a list of people via email when excel file is closed

[Guest]

I have five columns...one called "action required" and under this column I
have a drop down that has four options "n/a" , "Grp1" , "Grp2", "Grp3" I
would input the data....choose "Grp1".....what I want to happen is when I
choose "Grp1" and close the file an email notification to be sent to 2
people. But when I choose "Grp2" and close the file an email notification
will be sent to 3 different people. And when I choose "Grp3" and close the
file a different group of people will be sent an email notification. When I
choose "n/a" no notification will be sent. How do I this?

What I have done so far (which isn't much)....

I have this macro (see below) that when you close the file a pop-up box
appears that ask you if you want to send an email notification to a list of
people saying that you've made an update to the file.

Macro:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

'define variables
Dim answer As String

'get user action
answer = MsgBox("Send update notice?", vbYesNo, "Confirmation")

'if user wants to send update, send; otherwise just close the document
If answer = vbYes Then
'open outlook type stuff
Set OutlookApp = CreateObject("Outlook.Application")
Set OlObjects = OutlookApp.GetNamespace("MAPI")
Set newmsg = OutlookApp.CreateItem(olMailItem)
'add recipients
'newmsg.Recipients.Add ("Melanie Hauser")
newmsg.Recipients.Add ("Liz Withnell")
newmsg.Recipients.Add ("Pat Myer")
newmsg.Recipients.Add ("Linda Burnim")
'add subject
newmsg.Subject = "Configurator Spreadsheet Update"
'add body
newmsg.Body = "The configurator spreadsheet has been modified."
newmsg.Display 'display
newmsg.Send 'send message
'give conformation of sent message
MsgBox "Update notice has been sent.", , "Confirmation"
ElseIf answer = vbNo Then
'give conformation of no sent message
MsgBox "No notice has been sent.", , "Confirmation"
End If

'save the document
'Me.Worksheets.Save

End Sub

Please help!

Melanie

 

[mcescher]

Melanie,
You might want to try vbSendMail. It's a VB add-on. (Your macro is
actually a VBA program)

I would suggest a 'Settings' tab. You could have the different email
lists there. This would allow easy updates to the lists, rather than
re-writing the code when a different person has to be added to the
list. Use a function like this to retrieve the addresses.

Public Function GetEmails()
Dim intRow As Integer, strEmails As String
intRow = 5: strEmails = ""
Do While Not IsEmpty(Sheets("Settings").Range("A" & intRow))
strEmails = strEmails & Sheets("Settings").Range("A" & intRow) & ";
"
intRow = intRow + 1
Loop
GetEmails = Left(strEmails, Len(strEmails) - 2)
End Function

My list is in column A starting at row 5. This will read all the
addresses until there is an empty cell

You might want to set that up with a button to process your choice
rather than when you close. The button could save the workbook, send
the email and then close it. Use these two commands with your code in
the onclick of the button.
ActiveWorkbook.Save
ActiveWorkbook.Close

Look into the Select Case statement in the help menu. This will help
you determine which selection was made. Something like this:
Select Case strChoice
case "N/A"
'No notification
case "Grp1"
'send email to first group
case "Grp2"
'send email to second group
case "Grp3"
'send email to third group
End Select

HTH,
Chris M.