VBA change condition and send email using loop?

[Jonesy27]

Good afternoon,

I have 2 separate bits of code that work for a specified row, but I don't want to have to write it 100 times.

the first is this one:
Sub worksheet_change(ByVal target As Range)
Set target = Range("G3")
If target.Value = Range("O1") Then
Call sendtest2
End If
Set target = Range("G3")
If target.Value = Range("o1") Then
target.Value = "Requested"
End If
Set target = Range("G3")
If target.Value = Range("o2") Then
Call sendtest2
End If
Set target = Rang
If target.Value = Range("o2") Then
target.Value = "Cancelled"
End If

End Sub

which looks at a specific cell and runs a separate module (to compile and send the email) and then changes the value of that cell.

is there anyway to loop this for each row.


secondly, the email code looks at the given row. again, is there anyway to loop this?



Sub sendtest2()

Dim cell As Range
With CreateObject("outlook.<wbr>application").Createitem(0)
.to = "user@email.com"

.Subject = Range("u1") & "***TEST***-Airfield Request:- " & Range("a3") & " " & Range("U1")
.body = "Airfield Request:- " & Range("u1") & vbNewLine & "Requested by:- " & Range("F3") & vbNewLine & "ICAO:- <wbr> " & Range("a3") & vbNewLine & "Dest/Alt:- <wbr> " & Range("b3") & vbNewLine & "Type of operation:- " & Range("c3") & vbNewLine & "Intended month of operation:- " & Range("d3") & vbNewLine & vbNewLine & "Comments:- " & Range("e3")
.display
End With

End Sub


many thanks in advance