CODEWITHSUNDEEP
vbaexcel
Kzhel Farmer
Kzhel Farmer

Reputation: 112

Email notification on Expiry date

I want to send notification by email on Expiry date this code is sending email 30 days before expiry

    Sub reminder4()
 Dim lRow As Integer
 Dim i As Integer
 Dim toDate As Date
 Dim toList As String
 Dim eSubject As String
 Dim eBody As String
 Dim cList As String

 With Application
 .ScreenUpdating = False
 .EnableEvents = False
 .DisplayAlerts = False
    End With
    Sheets(2).Select
    lRow = Cells(Rows.Count, 4).End(xlUp).Row
    For i = 2 To lRow
     toDate = Replace(Cells(i, 3), ".", "/")

        If Left(Cells(i, 5), 4) <> "Mail" And toDate - Date <= 30 Then

     Set OutApp = CreateObject("Outlook.Application")
     Set OutMail = OutApp.CreateItem(0)
     Cells(i, 8) = "Mail Sent " & Date + Time

It is sending email 30 days before expiry i want to send email upon expiry.

Upvotes: 0

Views: 1392

Answers (1)

FreeMan
FreeMan

Reputation: 5687

replace this line:

If Left(Cells(i, 5), 4) <> "Mail" And toDate - Date <= 30 Then

with:

If Left(Cells(i, 5), 4) <> "Mail" And toDate <= Date Then

Upvotes: 1

Related Questions