×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Create mail when cells are filled?

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?

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"
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

RE: Create mail when cells are filled?

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources