excel to send email when all dates in column are due

[hsolanki]

Hi I have an staff training record whereby in column F-Z starting from row 9 with the expiry date and in column A9 onward all the staff name and type of training in columns F9-Z9.

i want macro to send email with the all the Due date in each column to One send email with the name, due date and types of training are due for the column F, for example. and so on for different column to different people.

i would really appreciate if someone could help me with the above query.

btw Happy New Year 2021 to all.

 

[Logit]

.
Regarding the email :

#1 Do you have Outlook installed on your computer ?

#2 You need to make a "reference" to Outlook. You can reference either of the following :
Microsoft Outlook 12.0 Object Library
Microsoft Office 12.0 Object Library

Check or add an object library reference (VBA)

I'll look at the other issue of copying only the individual cell vs the entire row.

 

[hsolanki]

Hi Logit Sorry yes i do have an outlook and i managed to get it worked with the email.

only the 2nd issue whereby if can be done on basis of date +60days and copy only the cells that has triggered off something similar as picture attached on my previous post.

also on current sheet Email report it is only copying until the column Z and missing from AA - AN when copying on EmailReport.

also with the current it is duplicating rows

No problem, Thank you

 

[Logit]

.
Presently, copying duplicate rows ... that is because there are more than one red cell in the row. If there are two red cells, the row will be copied twice.
If there are three red cells, the row will be copied three times.

"only copying until col Z ..." you had indicated only the columns F:Z were of interest to you.

 

[hsolanki]

YH sorry but can it be changed to dates if more than 60days from the expiry date. I want all the information from the training matrix whenever it meets the criteria of 60days + and only copy of the column that has date matched with the name column.

 

[Logit]

 

[Logit]

.

Sub sintekJ3v16()
Dim Temp(), i As Long, cell As Range, Rng As Range
Set Rng = Sheets("TrainingMatrix").Range("E9:AM36"): ReDim Preserve Temp(1 To Rng.Cells.Count, 1 To 3)
For Each cell In Rng.Cells
    If cell <> "" And IsDate(cell) And cell >= Date And cell <= Date + 60 Then
        i = i + 1: Temp(i, 1) = Cells(cell.Row, 2): Temp(i, 2) = Cells(6, cell.Column): Temp(i, 3) = cell
    End If
Next cell

With Sheets("EmailReport"): .Range("A1").Resize(i, 3) = Temp: .Columns.AutoFit: End With

End Sub

Download workbook : Training Matrix 1-7-2021.xlsm

 

[hsolanki]

Hi Logit Thank you so much, i really appreciate for the excellent piece of work you have put together and it works, however can be tweeted little with the code if can be done. i have put the code in Private Sub Workbook_Open() therefore whenever file is open email will send automatically with the code you have done however now i have got 2 problems;

1- when email is open it will copy even the all blank rows from EmailReport range, can it be copy only the rows that has value within the EmailReport range?
2- Each time workbook is open email will repeat for the same names and dates therefore is there any way to avoid sending out duplicate emails?

 

[Logit]

I don't understand the issues you are experiencing there.

Running the macro/s here, it finds all pending expirations that will occur between the current date to 60 days into the future. It does not
copy "blank cells".

Your goal was to send the report to a single email address - the macro accomplishes that.

?????

 

[hsolanki]

Hi Logit Yes it does what i meant is that can it be avoid sending duplicate emails e.g. if expired date found then email will sent and against the column it will say Email "Sent" therefore it will skip that date and look for another expiry date if there. currently it will send out email each time command button is pressed or open. i was wondering if something like attached image showing "sent" therefore it will skip that.

 

[Logit]

From the images provided, you have changed your workbook layout. The version I have here is not the same as you are showing.

?????