automatically e-mail recipients depending on cell value criteria

[ascottishlad]

Afternoon all

I am a newby so please be easy on me with the actions to be taken, the more steps the better

I am trying to write a code that will automatically notify recipients when a cell criteria is met. To give an insight I am producing an internal audit tracker where staff are accountable for their specific reference, there are variable periods that audits are required to be carried out from 30 days frequency, to 60, 90, 180 and 330

From the attachment I am trying to ensure that when column B is within 30 days an e-mail is sent to the reference holder that will be named in cell I7.

Ideally the e-mail will be sent every time the program is opened if scale is within the 30 days time frame and/or until column E has "Yes" selected to that particualr row.

Hope the above makes sense, all for flexibilty and any tweaks suggested

Many thanks in advance

ascottishlad

[apo]

Hi..

What do you want the email to contain..?

You can programatically set the Subject, body and attachment if needed.

[ascottishlad]

Hello

Thanks for responding, ideally I would like the email to contain....

Subject...... Your TQM reference is due to expire in ?? days time
Body.......... Hello

Your TQM reference is on countdown, please action to ensure that you do not exceed your deadline date.

HoFS

For the attachment, it would be ideal if the worksheet in question can be sent as an attachment

As a newby to VBA coding, etc, can you kindly let me know how the coding would automatically send the worksheet to the correct/appropriate recipient, I will have about 45 worksheets in the TQM workbook, with different people responsible for each reference (10 staff in total for the 45 worksheets).

Cant thank you enough for the help in advance

[apo]

Hi..

Another quick clarification..

From the attachment I am trying to ensure that when column B is within 30 days

Is that within 30 days of the current days date or within 30 days of the date shown in Column A for the same row?

[ascottishlad]

Hello,

The e-mail notifications should be from the due date in column A as this is the dealine date to be met. If or when the date is entered into column B the notifications should ideally cease.

A query that I have thought of, would the e-mail notification be sent once or everytime the TQM tracker is opened if the countodwn criterias are being met.

Thanks again

[apo]

Hi..

This is what i have so far.. its late here.. so i will continue tomorrow..

Can you check it and see if i am on the right track..

Please Login or Register  to view this content.

Heres a test file attached..

[ascottishlad]

Hi Apo,

Wow, definetly on the right track, I can not stress enough how brilliant this is

Thanks very much on the assistance provided, have a great night

ascottishlad

[apo]

Hi..

No worries.. i was wondering a couple of things..

1. As the code is on the Change Event and the Workbook open event.. every time you open the Workbook or every time you make changes in the specified columns.. an email(s) will get sent .. is that an issue..? I mean.. do you want it so that only one email per day for any 'row' is sent ?


2. In the "Email Notification Sent" column.. do you want a Yes/No inserted if an email has been sent or do you want the Date that the Last email was sent?

I will add the attaching of the Worksheet in question tomorrow..

[ascottishlad]

Hi Apo,

In reponse to your questions,

1. If only one e-mail is sent per day regardless of the number of times the workbook is opened/closed
2. If it can be automated to completed the Yes/No for the e-mail that would be superb as I thought I would require to do that manually, now thinking ahead for audit purposes, would it be too extreme to have a column next to the e-mail sent "Yes/No" to count how many e-mails have actually been sent.

I have looked at the coding you have done and I have took some learning from it, again highly appreciated

ascottishlad

[mukeshbaviskar]

Hello apo,
Excellent! Can we send a automatic email using windows live mail on a particular date automatically? If so please provide me a code for it.
Waiting for your response.
Sorry for posting a request on others thread. I like it so I requested my desire. If it is possible I will post a new thread for it.
Thanking you in anticipation.

Mukesh

[ascottishlad]

Mukesh

It would be preferred if you start your own thread or refer to other simlilar posts as the one your requesting is of a different nature and detracts from the great work Apo is doing here

I have notice from other forum threads you have a habit of hi-jacking threads - please treat the threads posted with respect

ascottishlad

[mukeshbaviskar]

Hello ascottishlad,
First of all I would like to clear you that I'm not a hi-jacker. Recently I learned a computer and started using excel which I like most. It's a excellent program but due to my limited knowledge I'm unable to do all the functions easily/correctly. So I joined excel forum recently. If I like someone's idea and asked him to do some necessary changes as per my requirement in his program is it a hi-jacking?
I request you to be more patient while writing on a forum specially blaming someone. Ok! Now I will take care not to respond on others thread and follow his idea.
Sorry dear moderator, I'm feeling very sorry to write this answer to ascottishlad. Please forget my thread now.
Thank you.

Mukesh

[arlu1201]

Mukesh,

If you want someone to do changes to their code as per your requirement in someone else's thread, it is hijacking. Please create a thread of your own.

You can link back to this thread if required.

[mukeshbaviskar]

Dear arlu1201,
Ok! Now close this matter and forget.
To learn new things was my intension and not hi-jacking.
Sorry for that and I will try my level best to not repeat it again in future.
Thank you and good night.

Mukesh

[apo]

Hi..

I think this will do most of what you want.. let me know how you go with it..

It does the following:

1. Only allows 1 email for any 'row' per day.
2. Attaches a xlsm file to the email that contains the Sheet.. to do this. it needs to create and save a new workbook which will be stored in the same directory that the main workbook is.
3. The Workbook that it going to be attached is automatically overwritten as the code loops down through the rows without prompting using (Application.DisplayAlerts = False)....
4. Displays how many emails have been sent for any row.
5. Displays when the last email was sent.

These functions are initiated on the Workbook Open event and the WorkSheet Change event..

Please Login or Register  to view this content.

The WorkBook Open Event code is basically the same except it omits the following lines..

Please Login or Register  to view this content.

If any of the more experienced Excel guys can point out any ways that this can be made better/more efficient.. I would love to hear.. as I am just starting to learn VBA myself..