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

 

[Norie]

The problem line doesn't appear to be highlighted, which line is it?

 

[StephenMatthews]

toDate = Replace(Cells(i, 16), ".", "/")

 

[Norie]

Stephen

If you want to skip creating/sending the email if the date cell is blank try this.

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
    
        If Cells(i, 16) <> "" Then
        
            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
            
        End If
        
    Next i

    ActiveWorkbook.Save

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .DisplayAlerts = True
    End With
    
End Sub

PS Are you sure you need to create a new instance of Outlook for every email?

Try moving this outside the loop.

Set OutApp = CreateObject("Outlook.Application")

 

[StephenMatthews]

This worked, great thanks and in answer to your last point, I require separate e-mils for each event, thanks for the Super quick reply.

Regards

Stephen.

 

[RoryA]

A separate email doesn't require trying to start Outlook inside the loop. I suggest you follow Norie's advice and move that line before the loop.

 

[RoryA]

A separate email doesn't require trying to start Outlook inside the loop. I suggest you follow Norie's advice and move that line before the loop.

 

[StephenMatthews]

Hi Rory,

Before the Loop?? Can you advise where that is, Stupid question but only have basic knowledge of Macros etc.

Thanks and appreciate All your help.

Regards

Stephen.

 

[RoryA]

Sure. Move this line:

Set OutApp = CreateObject("Outlook.Application")

so that it is before this line:

For i = 2 To lRow

 

[StephenMatthews]

Thanks Rory, the previous code worked fine but changed it as suggested.

Stephen.