macro to automatically send email when due date approaches in XL spreadsheet.

Hello PTSO,

An outline of how I would try to deal with this issue. (thinking out aloud time commences here )

I assume you are in a "standard windows / outlook environment.

Eventually I would have a scheduled task that commences at say 01:00 each day that opens a workbook (your "sample")and then closes it.

The work book would have an "open workbook event" that fires up when the workbook is opened. This event would process each worksheet (tab) and generate the required emails.

I have seen many email issues, I personally have found the Ron de Bruin web site of invaluable help in this area. (http://www.rondebruin.nl/) so that is worth a look.

An example of how to cycle through all the worksheets in a workbook (in this case making the sheets all visible) would look something like

Please Login or Register  to view this content.

Smarter and more experienced people in this forum would suggest using a "filter" approach to only show the items that meet the custom criteria (something like cells in col K that are = 30 days after "today"). I am an old fashioned green screen person so I would probably construct a "for loop" to process all the rows in each tab, slower but easier to understand for a novice.

another tip, just below the input panel in the thread is a panel of "similar threads" where often you can find just the answer you are looking for (for example in this thread I see the following link http://www.excelforum.com/excel-prog...proaching.html

I apologise if this is at too high a level but sometimes "complete novice" doesnt actually cover the knowledge level of the poster and it isnt always clear how detailed the response should be

PTSO,

Thanks for the rep, not really deserved I suspect as I didn't actually help all that much.

So... the first question is how much standard formula based excel are you comfortable with (ie are you a novice excel user or only novice VBA user??)

Have you any experience with simply recording a macro and then looking at the code that was generated or is this a "dark art" for you??

The contributors here are all volunteers who try to help solve "road blocks" that fellow contributors are facing, generally we do not pretend to offer to build your solution for you (although like many others I have sometimes forgotten that in the "chasing of new knowledge opportunities" )

I find that it is always good to have a very clear plan of what you are trying to do before you get sidetracked with the actual solution.

For example, Excel can tell you what "TODAY" is, one fact. You have in col K the due date. Excel can tell you the difference between the two dates (datediff function), the site I pointed you to can show you how to email the results if the diff = 30.

Try to do one small aspect of the puzzle at a time manually (ie try to construct a filter sequence of actions manually, when you get the right answer then try to record a macro doing exactly those commands and see what is generated) and try to progress from there.

I am also mildly curious as to how you ended up with this task in front of you (zigged when you should have zagged is a plausible explanation in my world that I understand)

Importantly, do you have control over the workbook, could you add a new worksheet if you needed to simply to store important information at least while trying to build your solution.

I will try to take a further look at some aspects tomorrow (it is very close to 23:00 here and sleep is beckoning me) for you, no promises but I will try

Jmac

Hi PTSO,

Take a look at the attached workbook and the macro code it contains.

I fear I have not completely solved your problem but it should give you some idea and structure to work with.

I have not used your conditional formatting results although I see that it is possible to filter on colours to reduce the rows shown. Filtering is something I am just starting to experiment with in other macros so I haven't included it in this one.

(as an aside, how many rows would you expect in the real world)

I should also say this version is testing for exactly = 30 days and would only send a single alert email on that day which is how I understand your requirement.

A couple of explanations. You will see that a number of variables have been declared "public". This is so they can be "seen & used" across multiple "subs" in the one module. I use these to pass the details from the processing macro (PTSO_2) to the email macro (Mail_small_Text_Outlook) which actually sends the email.

The Mail_small_Text_Outlook macro came from the excellent Ron de Bruin site and is gratefully acknowledged.

At the moment I have it set to display the macro rather than send it, (easier for testing purposes) so you will need to alter the .Display line in the code to be a comment and the .Send to be active when you have finished testing and are ready to actually send emails.

I have included way more comments than I would normally use in an attempt to explain what I am doing at each step.

If you get stuck then load up a copy of your current workbook at that time and we can take a look at your problem.

Hope this is a good start for you on your journey into the world of excel & VBA. There are many brilliant people who volunteer their time in this forum and I am constantly learning from their answers to questions.

Good luck

PTSO-2.xlsm

John (aka Jmac)