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 no email sent 2 image it is the same layout and format it is the same version you have. Whereby email sent image 1 it is different workbook but I just used that image as example to make you understand what i am trying to achieve. On the version sheet you have can we add extra columns to each column and stamp the email as Sent when as well using if Expiry date has 'Sent' then look for next expiry date if not send email

 

[Logit]

Let me look at this ...

 

[hsolanki]

Thank you logit as always appreciated for all your expertise

 

[Logit]

The current design of your tracking system will not permit adding email data. There are too many columns and too many different dates
for each employee. You could change the design/layout so there is a blank column adjacent to each training date that could be used
for email tracking but that would need to be accomplished manually (entering the date the email was sent).

I would suggest adding to the CONDITIONAL FORMAT of each cell and assigning a unique color that would indicate an email has been
sent.

Regarding the data list that is pasted in the email body .... you could review the EMAILSENT sheet listings prior to clicking the EMAIL
button to make certain only the required data is sent.

I'll continue to think about other options and advise later ...

 

[hsolanki]

Hi thanks can we split codes and have 3 modules e.g. columns V-W, another X-Z and another for columns AE-AG?

Yh ok no problem thank you

 

[Logit]

What about all the other columns/dates ?

 

[hsolanki]

all the other columns are for visual reference purposes which is for internal use only however the main dates are which requires annual refreshers training and i have to send an email. main dates are in column between V - Z and AE-AG and AI-AJ which are the main columns which requires the attention and have to send email. to specific department. for example all the dates in column V will be sent to H&S@mail.com, columns W-Z will be sent to headoffice@mail.com, Columns AE-AG will be sent to training@mail.com and columns AI-AJ will be sent to me@mail.com

sorry for the confusion. i was going to use your code to split in to above for each department by having 4 different command button to send email.

 

[Logit]

You should understand your approach to requesting assistance is not productive.

You provide a small snippet of what you are seeking ... believing you are giving sufficient information for the total solution ... which is not the case.
This type approach makes it very, very difficult to assist. Your volunteer begins to cull together a solution only to discover it requires something
different or it requires a whole lot more that may or may not head off in a different direction.

This is non-productive and very frustrating.

Suggestions :

I believe the best approach is to add another sheet entitled EmailsSent. This sheet will always contain ALL data that was placed in the EmailReport sheet.
When data is copied from the TrainingMatrix sheet and pasted in the EmailReport sheet ... that information will be checked against the data contained
in the EmailsSent sheet. If the row/s of data in the EmailReport sheet match with the row/s of data in the EmailSent sheet ... the row will be deleted from
the EmailReport sheet. Then the email will be sent.

It is possible to identify the specific Certification (Col B) that belong to the different email addresses. Only the appropriate rows of data will be emailed
to each address as required.

At this point I can not provide any further assistance. Hopefully someone else on the Forum will step in to assist. Best wishes.

 

[hsolanki]

Hi Logit i understand your frustration but i am really hopeless as i would not know how to put all the code together as you have done for me which i really appreciate for all your time and efforts you have put in.

i really like your suggestion and it would work perfect but however i wont have any clue on how to put your suggestion together.

i would really appreciate for last time if you would kindly be able to write me code for your suggestion as you have detailed knowledge of my workbook and how it works and all the work you have done so for me.

i just wanted to say massive thank you

 

[Logit]

Final project available :

Workbook

Directions / Notes