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.
-----Original Message-----
Hi
Please help:
I have a worksheet with a formula the changes a cell(J24)
to either
"ORDER" or "OK" ...i have used 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
.