StephenMatthews
New Member
- Joined
- Sep 26, 2013
- Messages
- 28
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.
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.
Code:
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
Last edited by a moderator:
