Hi, I hope someone out there might be able to help, I am trying to write a code that will send an automated email advising Licence Expiry Due in 90 days and will also send an email once the licence has expired.
I'm using "If .Cells(RowNo, "M") <= Date + 90 Then" which works well for expired dates, but when I change it to +90, it sends everything within 90 days and expired pas today. Would really appreciate some help!
Thanks very much
I'm using "If .Cells(RowNo, "M") <= Date + 90 Then" which works well for expired dates, but when I change it to +90, it sends everything within 90 days and expired pas today. Would really appreciate some help!
VBA Code:
Sub SendEMail()
Dim Addr As String, Subj As String
Dim Msg As String
Dim LastRow As Long, NextRow As Long, RowNo As Long
Dim wsEmail As Worksheet
Dim OutApp As Object
Dim OutMail As Object
Dim Maildte As Date
Set wsEmail = ThisWorkbook.Sheets("Structural")
With wsEmail
LastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
For RowNo = 14 To LastRow
'Change "Date + 30" to suit your timescale
If .Cells(RowNo, "BM") <= Date + 90 Then
Maildte = True
If Maildte = True Then
On Error Resume Next
Set OutApp = GetObject("Outlook.Application")
On Error GoTo 0
If OutApp Is Nothing Then Set OutApp = CreateObject("Outlook.Application")
Do: Loop Until Not OutApp Is Nothing
Set OutMail = OutApp.CreateItem(0)
With OutMail
Addr = wsEmail.Cells(RowNo, "F") 'Change to cell containing e-mail address
Subj = "High Risk Licence Due" 'Change to cell containing subject or type subject
Recipient = wsEmail.Cells(RowNo, "E") 'Change to cell containing e-mail address
ExpiryDate = wsEmail.Cells(RowNo, "BM") 'Change to cell containing e-mail address
Msg = "Dear " & Recipient & "," & vbCrLf & vbCrLf
Msg = Msg & "Your High Risk Licence is due to expire on: " & vbCrLf & vbCrLf
Msg = Msg & ExpiryDate & vbCrLf & vbCrLf
Msg = Msg & "Please schedule this training with management or the training coordinator." & vbCrLf & vbCrLf
Msg = Msg & "Thank you," & vbCrLf & vbCrLf
Msg = Msg & "Emma Foster" & vbCrLf
Msg = Msg & "" & vbCrLf
Msg = Msg & ""
.To = Addr
.CC = ""
.Subject = Subj
.Body = Msg
.Display
.Send
End With
Set OutApp = Nothing
Set OutMail = Nothing
End If
End If
Next
End With
End Sub