samuelbellos
New Member
- Joined
- Jun 28, 2021
- Messages
- 1
- Office Version
- 365
- 2013
- Platform
- Windows
Hey all,
I hope I can get some help here.
I have looked around for a solution and have gotten close but not there, it is a tricky one.
This is the closest I have gotten: Send email based on date
I have an excel spreadsheet stored on onedrive
It has 4 sheets with employee details, 3 sheets with current employees in 3 different sites and 1 sheet with past employees.
I have their name in the first column. Have columns next to that with info some columns are expiry dates for licenses related to different equipment (date format YYYY/MM/DD)
eg.
Full name column A, "Forklift license expiry date" column J, "Scissor lift expiry date" column L, "Drivers licence expiry date" column Q, Row 1 is the "titles".
Multiple people open the spreadsheet (Microsoft 365) so what I need the macro to do is
When spreadsheet is opened it checks a list of columns with dates
It checks all sheets
If a date is 60 or less days away from today and cell colour is not red (R255 G0 B0)
It takes the "full name" from column A the "title" from row 1 of the respective date and the "date" from the cell
It sends an email to a specific address and CCs 2 more email addresses with above details
It changes the cell colour for "date" to red to make sure it doesn't keep sending emails every time its opened.
Save
email address "example1@mrexcel.com"
CC "example2@mrexcel.com", "example3@mrexcel.com"
email subject: "License Expiry Reminder"
Content:
This is a reminder; "title" "date" for "full name"
An email per date/person is preferred.
Thanks in advance guys.
I hope I can get some help here.
I have looked around for a solution and have gotten close but not there, it is a tricky one.
This is the closest I have gotten: Send email based on date
I have an excel spreadsheet stored on onedrive
It has 4 sheets with employee details, 3 sheets with current employees in 3 different sites and 1 sheet with past employees.
I have their name in the first column. Have columns next to that with info some columns are expiry dates for licenses related to different equipment (date format YYYY/MM/DD)
eg.
Full name column A, "Forklift license expiry date" column J, "Scissor lift expiry date" column L, "Drivers licence expiry date" column Q, Row 1 is the "titles".
Multiple people open the spreadsheet (Microsoft 365) so what I need the macro to do is
When spreadsheet is opened it checks a list of columns with dates
It checks all sheets
If a date is 60 or less days away from today and cell colour is not red (R255 G0 B0)
It takes the "full name" from column A the "title" from row 1 of the respective date and the "date" from the cell
It sends an email to a specific address and CCs 2 more email addresses with above details
It changes the cell colour for "date" to red to make sure it doesn't keep sending emails every time its opened.
Save
email address "example1@mrexcel.com"
CC "example2@mrexcel.com", "example3@mrexcel.com"
email subject: "License Expiry Reminder"
Content:
This is a reminder; "title" "date" for "full name"
An email per date/person is preferred.
Thanks in advance guys.