Sending Mail from VBA

[Guest]

Hi all,

I have this bit of code that sends and email everytime the value in cell B1
is equal to 1

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Target.Cells.Count > 1 Then Exit Sub
On Error GoTo EndMacro
If Not Target.HasFormula Then
Set rng = Target.Dependents
If Not Intersect(Range("B1"), rng) Is Nothing Then
If Range("B1").Value = 1 Then Mail_with_outlook
End If
End If
EndMacro:
End Sub

How can I get the code to work for a range?

e.g if any 1 of the cells between B1:B10 is equal to 1, then activate the
Mail_with_outlook sub? I want to do this as I have a table (i.e. A1:B10),
which I will work my way down every week, and wenever a cell between B1:B10
is equal to 1 then an email is automatically sent.

Thanks

Bhupinder.

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Not Intersect(Target, Range("B1:B10")) Is Nothing Then
On Error GoTo EndMacro
If Not Target.HasFormula Then
If Target.Value = 1 Then Mail_with_outlook
End If
End If
EndMacro:
End Sub