Excel/VBA email notification

Hello warren0127,

Welcome to the Forum!

It would help to know which columns these dates are in and the name of the worksheet they are on. Do you also have a column that is used to mark the email has having been sent?

Hi Leith,

Thanks and I am glad to be registered!


Well, I have the expiration dates set which are in columns D34 (the month the product will expire).

For example, my spreadsheet looks something like this:

Date From Date To
D33 column D34 column


So it would be:

Date From Date To
23-Apr-09 22-Apr-10



I want to add a "3 month ahead of time" column so we know in advance when something will expire, so in this case I would make a separate column with the month of January in it (3 month notice)

The name of the worksheet tab is "0968" which is sheet #5.

I currently do not have a column where the email is marked to be sent.

Is this what you meant?

EDIT: Attached is an example spreadsheet. Items in Red are the dates, and the sent out date would be the date id like it to trigger to.

Thanks,

Warren

Hello warren0127,

Thanks for the example workbook. I don't see a column for email addresses. What do you want as the email subject line and body? What is the column "Send Out Date"? Does it have anything to do with the email?

Hello Warren0127,

Thanks for the explanation. I should have told you I do not have Excel 2007. The workbook will need to be in 2003 format before I can look at it.

Hello Warren0127,

Thanks for the 2003 version. I was thinking to signal that the email has been sent, the font color could be changed from red to some other color like green. What do you think?

Hello Warren0127,

I have added the macro shown here to the attached workbook which will run when the workbook opens.

Please Login or Register  to view this content.

ThisWorkbook Event Code
Remove the comment indicator (single quote) and save the workbook if you want the macro to run when the workbook is opened.

Please Login or Register  to view this content.

Leith,

I cannot say how much I appreciate you helping me with this. Thank you very much, I will try to run this when I get back into work and I will let you know how it goes!

Thanks again!!

Also, can you explain to me what you have done in this code logically? I do not understand excel coding.. like for example, how did you make it trigger the dates, etc?

Warren

Hello Warren,

Refer to the code below. The macro determines the area it will be scanning for dates by starting with cell "A5". The range is expanded to include the next 9 columns to the right using the Resize method. The last cell with data in column "A" is found by starting at the bottom of the worksheet. The first non empty cell found marks the end of the data. The row of the end of the range is not allowed to be less than the row of starting cell. This code is more complex than other versions for sizing a range, but it is easier to modify (only the starting cell has to changed) and safer since it will not overwrite any data before the starting cell's row.

Please Login or Register  to view this content.

The loop checks the "Date To" (column 4) against today's date + 90. If the dates are equal or "Date To" is greater than today + 90 days and the email has not been sent yet (cell in column "J" of the same row as "Date To" is empty) then send the notice.

Please Login or Register  to view this content.

Once the notice has been sent, column "J" of the same row as "Date To" is stamped with today's date.

Please Login or Register  to view this content.

I found a few typos in the macro and have corrected them. So use the workbook I have attached to this post. Here is the updated code.

Please Login or Register  to view this content.

Hello Dave,

You could use the "Send Date" column to hold the date you want the email to be sent out on. You would still need to flag that the email had been sent. The current code eliminates either adding another column or having to change font color to indicate the email was sent and still sends it 90 days ahead of expiration.

Hello Warren0127,

I have tested this version of the macro and it is working. Be sure you have a date that is is at least 90 days from today's date or the email won't be sent.

Please Login or Register  to view this content.

Hello Warrren0127,

Changing the starting the starting cell from A5 to A34 won't cause a problem since the starting cell is in the same column and all the columns and formats are the same.

This thread has been a huge help to me, I hope you don't mind me posting here. I have a similar database and have used this as an example. I've tried to change the code to suit the needs of my database, however it doesn't seem to be working.

I have a database with statutory and mandatory training that all staff must complete. I have a column to identify the date they completed the training course, and next to that I have a column with a date when the course is next due.

Here is the code I have currently:

Please Login or Register  to view this content.

I have also attached the database. At the minute I am only working on "Sheet 1" and the first 'due date' column even though there are several sheets and several columns, the idea is to have it on them all, but first it has work! Also each 'due date' column is designated to a different member of staff, would I need to have a different code for every person, indicating a different email address? If so, do I just put it under the above code?

Thanks in advance.

Jess

Jess,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

Also,

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature.

mmshai1,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.