Log In
Register
Home
New Member
June 21, 2021
Offline
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 = "xxx.xxx@xxx.uk.com; xxx.xxx@xxx.uk.com"
.CC = "xxx.xxx@xxx.uk.com"
.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.
November 8, 2013
Offline
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
1 Guest(s)
