Create mail when cells are filled? 1

[Westerfield]

Is there a way to get excel to automaticlly email me when someone in my network fills in 3 or 4 cells? What I am currently doing is Routing a product Change Notice to various people (in my company) and when they close the workbook it routes on to the next person. I am the last one on the list. I would like to send people a link to a location and when everyone has signed it, then it would send me a message. We use Outlook for our Mail.

 

[analogkid2digitalman]

This VBA code was triggered in one of my apps when a button was pressed. It grabs the email address from a cell and generates the Outlook email with a header and a message with a hyperlink to a network file. You may have to look a mods to allow multiple recipients

'***************************************************************************************************
Private Sub SendEmail(filename As Variant)

Dim aOutlook As Object
Dim aEmail As Object
Dim targetaddress As String
Dim subjectstring As String
Dim hyperlinkstring As String

' communicates with Microsoft Outlook to send notification email
' inserts a hyperlink into the message body for quick file opening

' prevent crash if outlook closed or not available
On Error GoTo errhandler

' limited test on recipient
targetaddress = cmboemailto.Text
If Not (targetaddress Like "*@*") Then
MsgBox ("Invalid EMail Recipient specified, may need to send notice of RFQ finalization manually")
Exit Sub
End If

' strip U:\RFQ FILES\ from filename for subject
subjectstring = Mid(filename, 14)

' create hyperlink, need to strip U:\ from filename, use <> in link since nonalphanumerics and potential spaces
hyperlinkstring = "<\\PMT-PX02.corp.polymicro.com\data\" & Mid(filename, 4) & ">"

Set aOutlook = GetObject(, "Outlook.Application")
Set aEmail = aOutlook.CreateItem(0)

' set importance to normal
aEmail.Importance = 1
' Set Subject
aEmail.Subject = filename & " Fiber RFQ Finalized"
' Set Body for mail
aEmail.Body = "Please note that an RFQ, saved as:" & Chr(13) & Chr(13) & hyperlinkstring & Chr(13) & Chr(13) & "has been finalized. Click on blue hyperlink above to open."
' Set Recipient
aEmail.Recipients.Add targetaddress
' Send Mail
aEmail.Send

' disable error handler
On Error GoTo 0

Exit Sub

errhandler:
MsgBox ("EMailing Error, may need to send notice of RFQ finalization manually (Outlook needs to be Active) " & Chr(13) & Err & ": " & Error(Err))

End Sub

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"It's the questions that drive us"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[joerd]

You could probably modify the above code and put it in the Workbook_Close event procedure. If you check for the signature fields to be filled, the last person to sign & close the workbook will trigger the event to send you an email - assuming he/she has Outlook running.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.