excel to send email when all dates in column are due

hsolanki

Board Regular
Joined
Jan 16, 2020
Messages
153
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
Jan 16, 2020
Messages
153
Office Version
  1. 2010
Platform
  1. Windows
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

hsolanki

Board Regular
Joined
Jan 16, 2020
Messages
153
Office Version
  1. 2010
Platform
  1. Windows
Thank you logit as always appreciated for all your expertise
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,143
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

Board Regular
Joined
Jan 16, 2020
Messages
153
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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
 

hsolanki

Board Regular
Joined
Jan 16, 2020
Messages
153
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Aug 31, 2016
Messages
4,143
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

Board Regular
Joined
Jan 16, 2020
Messages
153
Office Version
  1. 2010
Platform
  1. Windows
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
 

Forum statistics

Threads
1,141,124
Messages
5,704,436
Members
421,349
Latest member
Santhosh3188

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
Top