VBA, Send email before expiry date of license and colour cell red

samuelbellos

New Member
Joined
Jun 28, 2021
Messages
1
Office Version
  1. 365
  2. 2013
Platform
  1. 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.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top