Create mail when cells are filled?
Create mail when cells are filled?
(OP)
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.





RE: Create mail when cells are filled?
'***************************************************************************************************
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"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
RE: Create mail when cells are filled?
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.