Automatic Macro to send a Reminder during a given time frame

[Jakson]

Hello everyone, this is going to take a bit of background to explain the issue in detail. I've been working on this project for so long I'm going cross-eyed. It wouldn't surprise me if there's obvious problems that I can't see because my nose is too close to it - y'know?

Anyway.

The purpose: I've created a spreadsheet that has a series of event dates, deadline dates, and events. When a given event date is within a series of time frames (Event Date is 60 days from today - Event Date is 15 days from today...) VBA scoops up the name of the event lead, the event title, the event deadline date, and the date of the event. VBA then composes an email with this information in it and sends the email to the event lead. The event lead reads the email at what ever time (hence the several day buffer), and responds to the email with a series of answers, basically, "Yes", "No", or "Maybe"... VBA-Outlook listens to a designated email client for return responses and then decompiles the response body text into the useful tidbits. Who its from, what they responded to, and what their response was. This is then sent BACK to the Excel spreadsheet containing all the event data. The answers are marked in a designated spot and the next reminder ready to fire on the next event-deadline-window.

The problem: I can't get VBA to recursively perform this operation head to tail. IE determine next eligible event > email event lead > listen for response > mark response > determine next eligible event.

For this project to be worthwhile at all it must be entirely automatic. No human interaction necessary to make it work except when the wheels come off the wagon.

Every part of this project is built except whatever mechanism to cause VBA-Excel to repeat the macro again to cause an email to be sent. I can get from send an email to receive a response once per run. Simply calling it back into execution isn't working because there isn't a date range that fits criteria that happens every single day.

Tl;dr: I need my macro to run every time a date meets a criteria without having to interact with the spreadsheet. It also needs to be able to interact with Outlook to pass values back and forth. I don't know how to accomplish this with what I've built.

I can supply code as requested.

Guidance is deeply appreciated.

Thank you for your time and any assistance.

 

[GWteB]

Because some Outlook specific (background) tasks are performed asynchronously, I would limit the use of Outlook VBA to a minimum and let Excel VBA do most of the work.

In Outlook, using an event handler, I would just mark the eligible email item, for example by adding a unique attribute to the subject line and then writing the text of this subject line in a text file.

In Excel I would use the OnTime method to determine with a certain time interval whether such a text file is present, and if so, then parse the body of the associated email item for the relevant content and include it in your worksheet. The time interval shouldn't be to small. In addition, this task must guard itself in the context of non-reentrancy to prevent unexpected behaviour.

Also with a certain time interval I would determine in Excel whether an event is imminent and whether an email should be sent.

 

[Jakson]

Because some Outlook specific (background) tasks are performed asynchronously, I would limit the use of Outlook VBA to a minimum and let Excel VBA do most of the work.

In Outlook, using an event handler, I would just mark the eligible email item, for example by adding a unique attribute to the subject line and then writing the text of this subject line in a text file.

In Excel I would use the OnTime method to determine with a certain time interval whether such a text file is present, and if so, then parse the body of the associated email item for the relevant content and include it in your worksheet. The time interval shouldn't be to small. In addition, this task must guard itself in the context of non-reentrancy to prevent unexpected behaviour.

Also with a certain time interval I would determine in Excel whether an event is imminent and whether an email should be sent.

I love your suggestion of using a text file as my trigger. Currently I'm using an Application.NewMail event trigger in Outlook for when a new email arrives. It checks the Subject line for a certain trigger phrase. If the trigger phrase is found, then it parses the body and turns those strings into variables. In my current method, I'm passing the parsed .body vars to Excel via a Call command(from Outlook) to call the macro that enters the vars into the sheet directly by passing control back to Excel VBA.

As for the time interval to determine which item to send for,

When an email is made the macro writes a value into a cell that it checks every time it fires a new email. If the email for that date and event has already been sent it won't do anything more with that item, instead, it will grab the next eligible item. If the reminder has already been sent and the next event date is too far away I've also got it coded to prevent sending every single reminder in one shot. Simultaneous to that, if the event date is too near to today to send the first or second reminder, it will skip those in favor of the most appropriate one.

The macro makes a determination for which emails to send only once. This ultimately is my need for recurrance. Should I use an OnTime method with my For Loop to make the determination repeatedly?

Would you like some of my code? It might make it a little easier to communicate the specifics LOL

Also, thank you very much, @GWteB, for your guidance.

 

[GWteB]

Should I use an OnTime method with my For Loop to make the determination repeatedly?

Nope (btw, imo in this regard there's no need for a For-Next Loop). Scheduling the next invocation is done at the end of that particular VBA procedure.

Just some thoughts. I assume you are using a specific column containing the dates which determine in relation to the current date whether an email should be composed and sent or not. If both the "current" record and the next record would be inspected at the same time, the interval could be adjusted accordingly, i.e. reduced in a way the next eligible record will be processed almost immediately thereafter. The moment all eligible records have been processed, the next invocation of this VBA procedure can be scheduled the next day at 12.01 AM.
Of course, this approach only works if the records are sorted by event date.

 

[Jakson]

Nope (btw, imo in this regard there's no need for a For-Next Loop). Scheduling the next invocation is done at the end of that particular VBA procedure.

Just some thoughts. I assume you are using a specific column containing the dates which determine in relation to the current date whether an email should be composed and sent or not. If both the "current" record and the next record would be inspected at the same time, the interval could be adjusted accordingly, i.e. reduced in a way the next eligible record will be processed almost immediately thereafter. The moment all eligible records have been processed, the next invocation of this VBA procedure can be scheduled the next day at 12.01 AM.
Of course, this approach only works if the records are sorted by event date.

I've got two columns with two sets of dates. It's a tiny bit involved, but basically one set of dates, set A, is 45 days from the deadline, set B, of the event. The reminder is sent based on the value of set A. I can sort set A by date, but I lose the higher formatting of my data. My data is organized by last name alphabetically in the case that anything goes haywire, I can easily pick out the row where brown notes seeped.

I don't see much of a reason to keep it alphabetically sorted if I could replace the format for more expedient code.

Records are inspected one at a time by the For-Next Loop. Each record that meets criteria is processed until the email for that record is sent. When the email is sent, it loops back to the For-Next Loop and continues until there are no more eligible records.

If I'm following your advice correctly, it's right here at the end of the For-Next Loop's processing records that I should stick my OnTime method to cause it to circle back around and do more For-Next determinations at a later date?

 

[GWteB]

I can sort set A by date, but I lose the higher formatting of my data. My data is organized by last name alphabetically in the case that anything goes haywire, I can easily pick out the row where brown notes seeped.

Ok, leave it the way it is, the performance penalty would be hardly noticeable.

If I'm following your advice correctly, it's right here at the end of the For-Next Loop's processing records that I should stick my OnTime method to cause it to circle back around and do more For-Next determinations at a later date?

Yes, but not necessarily a later date. I could imagine that the worksheet is subject to (manual) changes. After all, it must be prevented that an event record that is added at the last minute is skipped.

 

[Jakson]

Ok, leave it the way it is, the performance penalty would be hardly noticeable.


Yes, but not necessarily a later date. I could imagine that the worksheet is subject to (manual) changes. After all, it must be prevented that an event record that is added at the last minute is skipped.

Thank you very very much for your help @GWteB. You're awesome!

 

[GWteB]

You're most welcome