Try:
Option Explicit
Private Sub Workbook_Open()
Application.ScreenUpdating = False
Dim i As Long, ws As Worksheet
Dim OutApp, OutMail As Object
Dim strto, strcc, strbcc, strsub, strbody As String
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
For Each ws In Sheets
With ws
For i = 4 To .Range("C65536").End(xlUp).Row
If .Cells(i, 10) <> "Yes" Then
'If .Cells(i, 8) - 12 < Date Then
If .Cells(i, 8) - .Cells(4, 14).Value < Date Then
Set OutMail = OutApp.CreateItem(0)
'strto = .Cells(i, 15).Value 'email address
strto = .Cells(4, 15).Value 'email address
strsub = "Deposit/Insurancee " & .Cells(i, 2).Value & " is due for maturity / Expiry on " & .Cells(i, 8).Value 'email subject
strbody = "Dear Mrs / Mr " & .Cells(i, 3).Value & vbNewLine & vbNewLine & "Please note to renew the Deposit / Insurance on the due date which falls on the " & .Cells(i, 8) 'email body
With OutMail
.To = strto
.Subject = strsub
.body = strbody
'.Send
.display
End With
On Error Resume Next
.Cells(i, 16) = "Mail Sent " & Now()
.Cells(i, 17) = "Y"
End If
End If
Next i
End With
Next ws
Set OutMail = Nothing
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub
Bookmarks