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.
Will you have Excel and Outlook open all the time? Or did you have any other idea?
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.
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
http://www.eileenslounge.com/viewtopic.php?p=324457#p324457
http://www.eileenslounge.com/viewtopic.php?p=324064#p324064
http://www.eileenslounge.com/viewtopic.php?p=323960#p323960
https://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgyZCnNfnZRfgwzDlQF4AaABAg
https://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgyZCnNfnZRfgwzDlQF4AaABAg. ADd4m2zp_xDADd6Nnotj1C
s://www.youtube.com/watch?v=7VwD9KuyMk4&lc=UgySdtXqcaA27wQLd1t4AaABAg
http://www.eileenslounge.com/viewtopic.php?p=323959#p323959
http://www.eileenslounge.com/viewtopic.php?f=30&t=41784
http://www.eileenslounge.com/viewtopic.php?p=323966#p323966
http://www.eileenslounge.com/viewtopic.php?p=323959#p323959
http://www.eileenslounge.com/viewtopic.php?p=323960#p323960
http://www.eileenslounge.com/viewtopic.php?p=323894#p323894
http://www.eileenslounge.com/viewtopic.php?p=323843#p323843
http://www.eileenslounge.com/viewtopic.php?p=323547#p323547
http://www.eileenslounge.com/viewtopic.php?p=323516#p323516
http://www.eileenslounge.com/viewtopic.php?p=323517#p323517
http://www.eileenslounge.com/viewtopic.php?p=323449#p323449
http://www.eileenslounge.com/viewtopic.php?p=323226#p323226
http://www.eileenslounge.com/viewtopic.php?f=25&t=41702&p=323150#p323150
http://www.eileenslounge.com/viewtopic.php?p=323085#p323085
http://www.eileenslounge.com/viewtopic.php?p=322955#p322955
http://www.eileenslounge.com/viewtopic.php?f=30&t=41659
http://www.eileenslounge.com/viewtopic.php?p=322462#p322462
http://www.eileenslounge.com/viewtopic.php?p=322356#p322356
http://www.eileenslounge.com/viewtopic.php?p=321984#p321984
https://eileenslounge.com/viewtopic.php?f=30&t=41610
https://eileenslounge.com/viewtopic.php?p=322176#p322176
https://eileenslounge.com/viewtopic.php?p=322238#p322238
https://eileenslounge.com/viewtopic.php?p=322270#p322270
https://eileenslounge.com/viewtopic.php?p=322300#p322300
http://www.eileenslounge.com/viewtopic.php?p=322150#p322150
http://www.eileenslounge.com/viewtopic.php?p=322111#p322111
http://www.eileenslounge.com/viewtopic.php?p=322086#p322086
https://stackoverflow.com/questions/33868233/shell-namespace-not-accepting-string-variable-but-accepting-string-itself/77888851#77888851
http://www.eileenslounge.com/viewtopic.php?p=322084#p322084
http://www.eileenslounge.com/viewtopic.php?p=321822#p321822
http://www.eileenslounge.com/viewtopic.php?p=322424#p322424
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
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?
Yes, the staff will make it red the moment we find out its cancelled.
Anyone? Let's up the money. $50!
Will certainly work on this and get back.
Excel Fox, any progress?
Yes. Targeting tomorrow evening for a working version
Thank you so much! I'll keep an eye out
The working draft version is ready. Test this please.
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!
Please test the file and let me know if it's fine. Will share payment detail after that.
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.
Made the modification. Code is ready. Will send payment details. Once payment is done, I will post the file here.
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.
Great. Please PM your paypal acct. Thanks!
PayPal sent. Thanks
Will send the code and file right away
Here's the updated file with code.
Here's the updated file with code.
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!
Sure. Can you share the exact format of the mail for both cases
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:
Excel Fox? Any chance you had a chance to work on it?
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//
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
Attachment 1712
Thanks Excel fox,
Hi exceldumby,
please find attached file check and let us know if anything missing.
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.
Hi,
i have not tested it for that. i will try in free time. can u share error description and screenshots?
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
Can anyone help? I'm willing to pay $10 for this change.
Are you using the same file shared by Dinesh
Yes.