please help me fix my code.i would like to change the requirements
a- if column 8 is target and not blank (no longer has to check if ists a date, it will be a date) then sterminations = target
b= if taget is column 7 and not blank then sterminations = target
i have column 8 ok for now as the target is a date anywayu
when i try to add the clause for column 7 it breaks
a- if column 8 is target and not blank (no longer has to check if ists a date, it will be a date) then sterminations = target
b= if taget is column 7 and not blank then sterminations = target
i have column 8 ok for now as the target is a date anywayu
when i try to add the clause for column 7 it breaks
Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sMail As String, sSubj As String, sBody As String
Dim sTermination As String
Dim emailstring As String
If Target.Cells.Count = 1 Then
If Target.Column = 8 And Target.Value <> "" Then
sTermination = Target.Value
If IsDate(sTermination) Then
sMail = "mail@mail.org"
emailstring = " current status is/or has been terminated on: "
ElseIf Target.Column = 7 And Target.Value <> "" Then
sTermination = Target.Value
sMail = "mail@mail.org"
emailstring = " current status is: "
End If
result = MsgBox("pressing OK will send email to notify", vbOK + vbExclamation, "Termination Status")
If result = vbOK Then
sSubj = Cells(Target.Row, "A").Value & " current status/termination"
sBody = Cells(Target.Row, "A").Value & emailstring & " " & Cells(Target.Row, "F").Value ' Email Body
Call SendMail(sMail, sSubj, sBody)
End If
MsgBox "Outlook messages sent", , "Outlook message sents" ' Confirm Sent Email
End If
End If
End Sub
Sub SendMail(sMail, sSubj, sBody)
Dim OutlookApp As Object
Set OutlookApp = CreateObject("Outlook.Application").CreateItem(0)
With OutlookApp
.To = sMail
.Subject = sSubj
.Body = sBody
.Display 'Display Email
.Send 'Send Email
End With
End Sub