VBA Code To Send Automatic Mail Reminder For Meeting X Days In Advance

[exceldumby]

Hi all,

I'm looking for a macro to put into Excel that will send a reminder email to a corresponding staff member one day before the appointment. Email is through Outlook.

Is it possible for it to automatically populate the client name, time of appointment and location in the body of the email? All information will be in the excel spreadsheet.

I've attached an example sheet. Payment will be through PayPal. Thanks for the help in advance.

[Excel Fox]

Will you have Excel and Outlook open all the time? Or did you have any other idea?

[exceldumby]

Yes, Excel and Outlook is open all day when I'm in the office.

One more thing, is there a way for it to not send the email if the appointment is cancelled? Cancelled appointments are in red.

[Excel Fox]

That can be done. But how does one make it red? Do you update the Excel file once you get to know it is cancelled?

[exceldumby]

Yes, the staff will make it red the moment we find out its cancelled.

[exceldumby]

Anyone? Let's up the money. $50!

[Excel Fox]

Will certainly work on this and get back.

[exceldumby]

Excel Fox, any progress?

[Excel Fox]

Yes. Targeting tomorrow evening for a working version

[exceldumby]

Thank you so much! I'll keep an eye out

[Excel Fox]

The working draft version is ready. Test this please.

[exceldumby]

Excel Fox, thank you so much. I will try it tomorrow. Please pm me your paypal so I can send payment. If there is anything that needs to be changed, I will let you know. Thanks!

[Excel Fox]

Please test the file and let me know if it's fine. Will share payment detail after that.

[exceldumby]

Ok. Here are a few things that needs to be corrected. The email should be sent to the interpreter, in column I. The client name should be referenced so the interpreter will know who they will be interpreting for.

For example, right now the email says: Dear Thomas Jefferson, you have a meeting scheduled. Please find details below: Time and date of appointment: 5 December 2014, 12:15am. Venue: 1234 street ABCD avenue, seattle, wa 12345.

It should say: Dear Pam Doe, you have an appointment scheduled. Please find details below: Client: Thomas Jefferson. Time and date of appointment: 5 December 2014, 12:15am. Venue: 1234 street ABCD avenue, seattle, wa 12345.

Also, our department has a department email, 'interpreters@whatever.com'. Is there a way to have that address send the email? That way when the interpreters email back it goes into the department inbox and the other staff members can see the email instead of it sending it to my own inbox where only I can see it.

I've noticed that the email is sent 24 hours in advance, therefore the macro is running basically a couple times an hour throughout the day. Can we just have it send an email at the beginning of the day for all of tomorrow's appointment regardless of time?

Thanks in advance, Excel Fox, you've been great.

[Excel Fox]

Made the modification. Code is ready. Will send payment details. Once payment is done, I will post the file here.

[Excel Fox]

By the way, to be able to send from another account, you'll need to have access to that. In other words, you should be able to do this manually, for this to work using VBA.

[exceldumby]

Great. Please PM your paypal acct. Thanks!

[exceldumby]

PayPal sent. Thanks

[Excel Fox]

Will send the code and file right away

[Excel Fox]

Here's the updated file with code.

[Excel Fox]

Here's the updated file with code.

[exceldumby]

Excel Fox, everything works great! I need something else added to the email, per my supervisor. The date of birth, column D, and the transport, column G. Could you do it where if the transport column is empty then it doesn't need to be in the email. If there is something in there, then it needs to be in the email. Thanks!

[Excel Fox]

Sure. Can you share the exact format of the mail for both cases

[exceldumby]

Sure.

Dear John Doe:

You have an appointment scheduled. Please find details below:

Client: William Harrison. DOB: 04/04/08
Time and date of appointment: 13 December 2014, 9:00 AM. Venue: 5678 President Circle, Seattle, WA 12345
Note: Please Transport

And if column G doesn't have anything in it:

Dear John Doe:

You have an appointment scheduled. Please find details below:

Client: William Harrison. DOB: 04/04/08
Time and date of appointment: 13 December 2014, 9:00 AM. Venue: 5678 President Circle, Seattle, WA 12345
Note:

[exceldumby]

Excel Fox? Any chance you had a chance to work on it?

[DInesh Mudgal]

Hi Excel Fox,
if u will allow then i want to Post your file with this little part to complete the requirements.

I have done this and file is ready with me.

Thanks//

[Excel Fox]

Dinesh, why don't you post it here. If its in line with what exceldumby wanted, great. If not, I will finish it after exceldumby's revert

[DInesh Mudgal]

1712

Thanks Excel fox,

Hi exceldumby,
please find attached file check and let us know if anything missing.

[exceldumby]

Hi DInesh,

That macro is perfect! Everything looks great. I have another question, can I use this macro in a shared worksheet? I've tried to run the macro in a shared workbook but it doesn't do anything.

[DInesh Mudgal]

Hi,
i have not tested it for that. i will try in free time. can u share error description and screenshots?

[exceldumby]

Hi, I have tested this for about 2 weeks now and everything is great. It works in a shared worksheet also! Right now I am manually executing the macro. Can I have it setup where it sends the email out everyday at 6:00pm for all of tomorrow's appointments automatically? Any help will be great! Thanks

[exceldumby]

Can anyone help? I'm willing to pay $10 for this change.

[Excel Fox]

Are you using the same file shared by Dinesh

[exceldumby]

Yes.