OR Condition for automatic emails based on specific cell change

[maxson]

Hi all,

I have a code that automatically drafts an email when a cell in range changes to "Closed Won", but would like it to also respond to "Closed Lost". Any ideas how to make it work?
Code below, appreciate your suggestions.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim KeyCells As Range




    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("U2:U1000")




    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then
           If Range(Target.Address) = "Closed Won" Then
           


        ' Display a message when one of the designated cells has been
        ' changed.
        ' Place your code here.
Dim answer As String
Dim SubmitLink As String


SubmitLink = Target.Offset(, -18).Value


answer = MsgBox("Do you wish to save this change. An Email will be sent to the User", vbYesNo, "Save the change")


If answer = vbNo Then Cancel = True
If answer = vbYes Then
'open outlook type stuff
Set OutlookApp = CreateObject("Outlook.Application")
Set OlObjects = OutlookApp.GetNamespace("MAPI")
Set newmsg = OutlookApp.CreateItem(olMailItem)
'add recipients
'newmsg.Recipients.Add ("Name Here")
newmsg.Recipients.Add ("test@test.com")
'add subject
newmsg.Subject = "Reservation " & Target.Offset(, -18).Value & " - " & Target.Offset(, 0).Value
'add body
newmsg.Body = "Dear User," & vbLf & vbLf & "Status of enquiry " & SubmitLink & " was changed to " & Target.Offset(, 0).Value & "." & vbLf & vbLf & "Kind regards," & vbLf & "The Machine"


newmsg.Display 'display
'newmsg.Send 'send message
'give conformation of sent message
MsgBox "Email Sent", , "Confirmation"






End If
   '     MsgBox "Cell " & Target.Address & " has changed."


End If


End If
End Sub

 

[Special-K99]

Isn't this just a case of

 If Range(Target.Address) = "Closed Won"  or  Range(Target.Address) = "Closed Lost" Then

 

[maxson]

Isn't this just a case of

 If Range(Target.Address) = "Closed Won"  or  Range(Target.Address) = "Closed Lost" Then

I could have sworn I tried it and it didn't work. Works now. Thanks a lot!