Run Time Error 13 - Type Mismatch - How to ignore Blank Dates??

[StephenMatthews]

Hi,

I keep getting a Run Time Error 13 – Type Mismatch on the below Highlighted Code below when a date field is blank, how can I add some additional code to ignore blank dates? The Macro is used so that when a Certificate date expires that 7 days prior to this a reminder e-mail is sent, some lines of data don’t have expiry dates hence they are blank and causing this error.

Thanks

Stephen.

Sub eMail()
Dim lRow As Integer
Dim i As Integer
Dim toDate As Date
Dim toList As String
Dim eSubject As String
Dim eBody As String
 
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .DisplayAlerts = False
End With
 
Sheets(1).Select
lRow = Cells(Rows.Count, 5).End(xlUp).Row
 
For i = 2 To lRow
toDate = Replace(Cells(i, 16), ".", "/")
  If Left(Cells(i, 18), 5) <> "Mail" And toDate - Date <= 7 Then
     Set OutApp = CreateObject("Outlook.Application")
     Set OutMail = OutApp.CreateItem(0)
 
        toList = Cells(i, 5)
        eSubject = "Certificate Out of Date - " & Cells(i, 1) & " is due on " & Cells(i, 16)
        eBody = "Hi Maz" & vbCrLf & vbCrLf & "Certificate is due for renewal."
        
        On Error Resume Next
        With OutMail
        .To = toList
        .CC = ""
        .BCC = ""
        .Subject = eSubject
        .Body = eBody
        .bodyformat = 1
        '.Display
        .Send
        End With
 
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
 Cells(i, 17) = "Mail Sent " & Date + Time
End If
Next i
 
ActiveWorkbook.Save
 
With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True
End With
End Sub

 

[StephenMatthews]

Sure. Move this line:

Set OutApp = CreateObject("Outlook.Application")

so that it is before this line:

For i = 2 To lRow

Hi Rory,

I've been asked by my colleague who I'm doing this for if we can expand this to include numerous searches in various other columns. Currently it pulls a date from column 16 which is now 20 on the example below:

10	11	12	13	14	15	16	17	18	19	20
H&S 1	H&S 2	H&S 3	H&S 4	H&S 5	H&S 6	H&S 7	H&S 8	H&S 9	Insurance - Public	Insurance - Employer
01/01/17	01/02/17	01/03/17	01/04/17	01/05/17	01/06/17	01/07/17	01/08/17	01/09/17	01/10/17	01/11/17

<tbody>
</tbody>

How do I expand the code to include these and to have a message saying which of the above has expired?

I know it's probably quite simple to do but thought I'd ask.

Thanks

Stephen.