i want to add a print and send email macro to a worksheet
the column is set for data type date
i have filled the column with dates as well as withdrawn, moved, pending, deceased etc. (its text in a date column)
i want to send an email and print only if i enter a new date into the column - column - column "F"
i dont want the macro to trigger if i fill with anything else
how do i do an isdate in a macro? this is my code so far, the orange is where i am stuck
the column is set for data type date
i have filled the column with dates as well as withdrawn, moved, pending, deceased etc. (its text in a date column)
i want to send an email and print only if i enter a new date into the column - column - column "F"
i dont want the macro to trigger if i fill with anything else
how do i do an isdate in a macro? this is my code so far, the orange is where i am stuck
VBA Code:
Private Sub TermDate(ByVal Target As Range)
If Target.Cells.Count = 1 Then
If Target.Column = 6 And Target.value [COLOR=rgb(243, 121, 52)]???????[/COLOR]
result = MsgBox("pressing OK will send email to notify", vbOK + vbExclamation, "there is a termination")
If result = vbOK Then
Set OutlookApp = CreateObject("Outlook.Application")
Set OlObjects = OutlookApp.GetNamespace("MAPI")
Set newmsg = OutlookApp.CreateItem(olMailItem)
With newmsg
.Recipients.Add ("mail@mail.org") ' Add Recipients
.Subject = Cells(Target.Row, "A").Value & ", " & ", has terminated services." ' Add Subject
.Body = "Termination date is" & " " & Cells(Target.Row, "F").Value ' Email Body
.Display 'Display Email
.Send 'Send Email
End With
MsgBox "Outlook message sent", , "Outlook message sent" ' Confirm Sent Email
End If
End If
End If
End Sub