mail sheet triggered by date

[g48dd]

I have uploaded a sheet, that tracks drivers lic. expiration dates, I need it to send e-mail when the drivers lic is 30 days from expiration. I have been here

http://www.rondebruin.nl/mail/folder2/mail2.htm

I read through how to copy and paste this code in my workbook but I don't understand where / how I point this at a cell so when the cell = 30 it sends the mail. I can see all the other where the email address goes, TO, CC, Subject, Body, but van't figure out the trigger part or where it is or if I have to get more code for that.

Thank you
Ken

[royUK]

You have two columns of expiries & no email addresses.

Which list would the email be sent from & where would the email address be stored?

[danb1985]

If you wanted the subroutine you already have for mailing the sheet to active when one of the cells in columns F or I contains a value of 30, you could use the following code. I've assumed a name for your mailing subroutine as 'MailSubRoutine'.

Please Login or Register  to view this content.

It should be fairly obvious, but the above code simply looks in columns F and I for any cell with a value of 30. If it finds one, it will trigger the subroutine called MailSubRoutine.

Is that what you were looking for? Apologies if I've misinterpreted, but I'm new here and am keen to help others as much as I ask for help myself!

Edit: With regards to RoyUK's comment above, I assumed you simply required the email to be sent to yourself or a single other individual. If you wanted it sent to the person whose licence is expiring, you'd obviously need to have their contact details located somewhere in the spreadsheet too. The short section of code I've put in above can be used to look in a single column, as well as the two columns of expiries.

[g48dd]

Hi thanks for getting back, I have two different drivers Lic. I must track. I realize that makes things difficult and the I decided that if I move Columns H, I, J under E, F, G then your code would only have to look in one column for the value 30. So I have attached new spread sheet and I would like a break beteen the two groups of driver Lic but if that will screw with the code, I could place them so there is no break and just color code them to make it easier to read.

I do not have a mailsubroutine, I don't even know what it is, I am guessing a mail macro. Do I need this code? There are no e-mail address on the spreadsheet, is it easier if I do that? That is not problem some of these coloums are going to be hidden anyway, F, H. I columns will be hidden, will the code work if column is hidden? I guess I am asking what do I do next? I have not pasted your code into the sheet need to find out if the way it is now works better, I have uploded a new sheet.

Thank you
Ken

[danb1985]

Hi Ken,

I'll answer the second paragraph first! The 'mailsubroutine' I referred to in my previous post was the mail macro you had spoken of in your original post. You had said that you had read through how to paste the code into your workbook, so I assumed it had been pasted in as a single subroutine. For the purposes of my code, I named it 'mailsubroutine'. In the page you linked to, it's defined as Mail_ActiveSheet.

You still haven't specified exactly who the spreadsheet is to be sent to - if it's just being sent to yourself, then you can specify a single email address in the mail macro. However, if it's to be sent to the individual whose licence is expiring, then you need to have the email addresses on the sheet so the macro can select the relevant address for that individual. These addresses can be placed in hidden columns, yes.

I personally don't see a need to put all the licences in a single column - the original code was fine for searching both columns, and doing it this way prevents your needing to have the same name entered twice. I assumed you'd need to mail the spreadsheet when either column had a value of 30 present.

[g48dd]

Thank you for getting back, I am sorry I forgot to tell you who it goes to. I wanted it sent to me, but if the CC: will also work then I would put three people in that. I will move the columns back the way they original were because that is actually better for me. The Value 30 is for either driver’s lic, this gives me time to alert the individual so they can make plans to get it renewed. I thought maybe when you said mailsubroutine that was what you were talking about. I have not pasted it yet, mostly because I am not sure how it works. I can see where my e-mail goes and where the cc: people go. I have your code. I have the mail active sheet code. I know where to paste it but what I don't know is in what order, if that matters and how the mailsubroutine will be link, or know when to send?

I think I understand part of it [Dim DateLimit As Range] I do not expect you to teach me programming here but I recognize [Range] so you are telling what it the range is limited? And then what it is limited to [Set DateLimit = Range("F:F, I:I")] column’s F & I only? Look for the value 30 [Find("30", LookIn:=xlValues)] ok here is the part I am not sure about [If Not DateLimit Is Nothing] if the value 30 is not found in the specified range do nothing? [Then Call MailSubRoutine] if the value 30 is found then call MailSubRoutine? Now if I am right about that, is it looking for code named MailSubRoutine, and if so where do I name the code so it can find it?

I hope I am not asking too much but I would like to understand how it works.

Thank you,
Ken

[g48dd]

Well I have pasted the code in but I am not doing something correctly? I have never pasted code in before in Excel so I realy don't know what I am doing.

here is the code:

Please Login or Register  to view this content.

Sheet attached

Thank you
Ken

[g48dd]

Bump no response