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.

 

[hsolanki]

Hi Logit Thank you so much for all your kindness, effort and time. wowww, i am really impressed this stuff is really good Thank you once again.

 

[hsolanki]

Hi Logit Sorry to be pain but i was wondering is there any way to avoid sending duplicate email if email already has been sent in past for same name and date?

 

[Logit]

Replacement Workbook :

Matrix Search Dates Email.xlsm

 

[hsolanki]

Morning Logit this is perfect. i cannot enough of your appreciation. Thank you for all your time, effort and kindness.

 

[hsolanki]

Hi Logit sorry just a quick question how can i put a message box if no new due date is found in any of the department, e.g. YOU button pressed and there no new due dates and i press Email button it will still sends out an blank email can we avoid this. i done below whereby user will need to select each department prior to sending out an email.

If ActiveSheet.AutoFilterMode = False Then
    MsgBox "Select the department First", vbExclamation, "Select dept."
    Exit Sub
    End If

however below only works if filter is off and nothing in first cell, mainly it works when i clear all data

If Sheet2.Range("A2").Value = "" Then
    MsgBox "There is no data to send email", vbExclamation, "Confirm Department first"
    Exit Sub
    End If

i am really sorry once again if m i being a pain.

 

[Logit]

Sub CopyRows()
Dim i As Integer

If Sheet2.Range("A2") = "" Then
    MsgBox "Nothing to email.", vbInformation, "NO DATA"
    Exit Sub
End If

TopNRows

Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("EmailReport")
    ws1.Range("A1:C36").Copy
    
Sheets("EmailReport").Range("A1").Select

Mail_Selection_Range_Outlook_Body
    
End Sub

 

[hsolanki]

Hi Logit it still doing the same whereby message box only comes up if i clear all data however if Filter is OFF then it works but If filter is on then you can still be able to send blank email. i uploaded 2 pic with Filter On and Filter Off

 

[hsolanki]

Sorry i meant if Filter is On and there is no Due dates you can still sends blank email.

 

[hsolanki]

Hi Logit i am sorry i wonder if you can help me one more time as i have got an issues. for example when you filter the department lets say Head then it will send a email and copy all those names to EmailsSent however when you filter another department again let say Training it will send an email with all all names and copy all the names in Training however when it pastes on to EmailsSent sheet it will overwrite on the very previous name row on the EmailsSent instead of pasting it on to the next blank row. i have tried so much but did not go too far.