VBA Function to check if appointment is 2 days away or less

**ForbiddenOrc** · 08-17-2023, 03:44 PM

Hello,

Background: I have an Excel spreadsheet that I use to track appointments with clients. We are required to send e-mail reminders to the participants 48 hours prior to their appointment. I am still new to VBA coding but have spent a lot of time on this code and have made a lot of progress. I am able to trigger the macro and it will run through the schedule and generate e-mails based on a template. It pulls the date, time, names, e-mails, converts time zones, pre-fills the e-mail fields, etc...It's about 99% done but I'm encountering an issue and cannot for the life of me figure out how to fix it. I have two functions that are designed to check if a reminder e-mail should be sent. We need to send the reminders 48 hours or less prior to the appointment. But appointments and reminders are not scheduled/sent on Saturdays and Sundays. So this check needs to skip weekends when comparing the current date to the appointment date.

Intended Functionality:

Monday appointments should generate a reminder starting on Thursday the week prior
Tuesday appointments should generate a reminder starting on Friday the week prior
Wednesday appointments should generate a reminder starting on Monday the same week
Thursday appointments should generate a reminder starting on Tuesday the same week
Friday appointments should generate a reminder starting on the Wednesday same week

Issue: When I run my Macro, it mostly follows the rules I outlined above. But I seem to have an issue with the logic involving the Friday. Today, on a Thursday, if I trigger my macro. It will generate the reminder for tomorrow and for Monday as intended. BUT, it also generates a reminder for Friday the 25th which is NOT intended since that is much more than 48 hours away.

Does anyone know what change I need to make to stop this from happening?

Here are my functions:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**AliGW** · 08-17-2023, 04:17 PM

[I'm B]Administrative Note:[/B]

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important: https://excelguru.ca/a-message-to-forum-cross-posters/

(Note: this requirement is not optional. As you are new I shall do it for you this time: https://forum.ozgrid.com/forum/index...54#post1261154)

**ForbiddenOrc** · 08-17-2023, 07:36 PM

Thank you AliGW, that is good to know and I will keep that in mind in the future. This thread can be closed as it was answered on another forum. Have a great day.