do only if is date

rjmdc

Active Member
Joined
Apr 29, 2020
Messages
392
Office Version
  1. 365
Platform
  1. Windows
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

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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

mse330

Well-known Member
Joined
Oct 18, 2007
Messages
715
Office Version
  1. 365
Platform
  1. Windows
Try something like this

Code:
If Target.Column = 6 And IsDate(Target.Value) Then
 

rjmdc

Active Member
Joined
Apr 29, 2020
Messages
392
Office Version
  1. 365
Platform
  1. Windows
i did it this way: is it ok?
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count = 1 Then
    If Target.Column = 6 And Target.Value <> "" Then
    If IsDate(ActiveCell) Then
        
            result = MsgBox("pressing OK will send email to notify", vbOK + vbExclamation, "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 been terminated."    ' Add Subject
                    .Body = Cells(Target.Row, "A").Value & "   has been terminated on" & " " & 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 If
 

Watch MrExcel Video

Forum statistics

Threads
1,127,554
Messages
5,625,474
Members
416,109
Latest member
TripleA00123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top