Hello!
I have written the below so I can send the excel as an attachment. Is there a way to only create the email when a date is due to expire. Column E = Email Date (in month-year)
Sub Certificate_Renewal_Programme()
'Step 1: Declare our variables
Dim OLApp As Outlook.Application
Dim OLMail As Object
'Step 2: Open Outlook start a new mail item
Set OLApp = New Outlook.Application
Set OLMail = OLApp.CreateItem(0)
OLApp.Session.Logon
'Step 3: Build our mail item and send
With OLMail
.To = "[email protected]; [email protected]"
.CC = "[email protected]"
.Subject = "Certification Renewal Programme"
.Body = "Please see attached and action and items highlighted in red. Please advise Quality once complete so they can change the document accordingly."
.Attachments.Add ActiveWorkbook.FullName
.Display
End With
'Step 4: Memory cleanup
Set OLMail = Nothing
Set OLApp = Nothing
End Sub
Thank you.
Hi Rachel,
What expire means for you?
The cell date reaches today's date, or is a few days before current date?
You can play with the below code, placed in ThisWorkbook module:
Private Sub Workbook_Open()
If CLng(ActiveSheet.Cells(2, "E").Value) >= CLng(Date) Then Certificate_Renewal_Programme
End Sub
