Sending Mail from VBA
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. |
Sending Mail from VBA
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 -- HTH Bob Phillips "Bhupinder Rayat" wrote in message ... 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. |
All times are GMT +1. The time now is 02:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com