Sending an Outlook e-mail due to a condition in a cell..

  • Thread starter Thread starter vee
  • Start date Start date
V

vee

Hi

Please help:

I have a worksheet with a formula the changes a cell(J24) to either
"ORDER" or "OK" ...i have used (e-mail address removed) vb script with
modifications:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("J24"), Target) Is Nothing Then
If Target.Value = "ORDER" Then
Mail_with_outlook
End If
End If
End Sub

all works fine if i type ORDER into the cell(J24)..the e-mail is
generated...but when the formula generates the result ORDER based on
the formula...no e-mail is generated.

I suspect this does not work beacuse of the Change event....but i
don't know what else to use...as i am new to vb.

Thanks For your time
 
You could use the Worksheet_Calculate event- somethin g
like this:

Private Sub Worksheet_Calculate()
If Range("J24").Value = "ORDER" Then Mail_with_outlook
End Sub

The problem with this is that it will send the mail every
time the worksheet calculates- for example, if you enter a
formula in another cell.

To get round this, you could have a variable that
remembers if a mail has already been sent- like this:

Dim blnMailSent as Boolean

Private Sub Worksheet_Calculate()
If Range("J24").Value = "ORDER" And _
blnMailSent = False Then
Mail_with_outlook
blnMailSent = True
End If
End Sub

Obviously, this variable will only hold its value as long
as the workworrk is open. Next time it is openned another
mail would be sent.

Cheers,
Dave.
 
Dave Ramage said:
You could use the Worksheet_Calculate event- somethin g
like this:

Private Sub Worksheet_Calculate()
If Range("J24").Value = "ORDER" Then Mail_with_outlook
End Sub

The problem with this is that it will send the mail every
time the worksheet calculates- for example, if you enter a
formula in another cell.

To get round this, you could have a variable that
remembers if a mail has already been sent- like this:

Dim blnMailSent as Boolean

Private Sub Worksheet_Calculate()
If Range("J24").Value = "ORDER" And _
blnMailSent = False Then
Mail_with_outlook
blnMailSent = True
End If
End Sub

Obviously, this variable will only hold its value as long
as the workworrk is open. Next time it is openned another
mail would be sent.

Cheers,
Dave.
Click to expand...


Hi

Thanks Dave..it works...but just one more question .how do i make this
work on a range of cells:
Dim blnMailSent as Boolean
Private Sub Worksheet_Calculate()
If Range("J24:J30").Value = "ORDER" And _
blnMailSent = False Then
Mail_with_outlook
blnMailSent = True
End If
End Sub
Click to expand...

If i put the above("J24:J30") i get a error.

Thanks again
 
vee...

Try this...

Private Sub Worksheet_Calculate()
Dim rngR as Range

For Each rngR in Range("J24:J30")
If rngR.Value = "ORDER" And blnMailSent = False Then
Mail_with_outlook
blnMailSent = True
End If
Next rngR
End Sub

This structure loops through each cell in the range. In
each loop, rngR represents the cell.

Cheers,
Dave.
 
Back
Top