Macro to send automated email reminders

**Amber12** · 02-08-2014, 12:11 AM

I have the following scenario: based on a schedule, I need to remind people of their turn. In a spreadsheet I have the date the person is scheduled, For each day there are two people scheduled, each having an individual email address.

The scheduled date is always on a Sunday. The reminder has to be sent on the Thursday prior. Would be a set up text that would have to pick up the date from the spreadsheet. Let's say: "This message is to remind you that Sunday, "1/1/14" you are scheduled to be a volunteer in the church's nursery room. We value your participation and we ask you to let us know if you are not able to be present so a replacement can be assigned. Please contact Annie at 758-986-1111.

Children are a gift from the Lord;they are a reward from him. Psalm 123:3"

I am looking for a macro that would automate this task. What are my options?

Sunday Schedule.xlsx

Thank you!

**natefarm** · 02-10-2014, 12:50 PM

Would someone be responsible for opening the workbook to initiate the reminders?

**Amber12** · 02-10-2014, 03:28 PM

If needed, yes. Or maybe I don't understand the question right... Do you mean by launching the macro?

**Amber12** · 02-10-2014, 03:30 PM

I also looked for an app that could do this but couldn't find anything that is still working. Thought that maybe a macro would work easier.

**natefarm** · 02-10-2014, 04:23 PM

Yes, with a macro, someone would have to open the workbook and run it. Please try the attached and see if it works for you. I put in parameters on sheet1 so you can adjust whenever needed. The date adjusts itself. Just click the Create Reminder button. It can be run any day of the week, and will open an email message for the following Sunday (assuming you use Outlook).

Sunday Schedule.xlsm

**Leith Ross** · 02-10-2014, 04:30 PM

Hello Amber12,

Once the macro has been added to the workbook and setup to run when the workbook is opened, you could set the Windows Scheduler to open the workbook every Thursday.

The Scheduler is in the Control Panel under Administrative Tools.

**natefarm** · 02-10-2014, 04:36 PM

Good idea, Leith. The code I attached wouldn't work that way, because it just opens the message, allowing the user to verify and then send it. Question: If the code was moved to the workbook open event and changed to send the message, rather than just open it, would that work if run from the scheduler? (Would it trigger the Open event, and would it send the message? Who would the "sender" be?)

Thanks for chipping in.

**Leith Ross** · 02-10-2014, 04:42 PM

Hello natefarm,

The Scheduler will open the workbook on the day and time chosen. If your macro is in or called by the Workbook_Open event, it will execute. You need to change your macro to send the email rather than display it and also to close the workbook once it has finished. I am not sure about the "sender" but I would think it would be whomever the owner of the workbook is.

**natefarm** · 02-10-2014, 04:46 PM

Amber, considering the above info, you might want to try it as it is for now (you open the workbook and initiate the message), and if you want to change it later to be more automated, get back with us.

**satabp** · 02-10-2014, 04:47 PM

Hi,

One more way is, we can add this in the system/user login batch files. I mean, while login we can setup to run few commands. So, we can add this on to that part and inside the macro we need write one more validation for Thursday. If it is Thursday/required day then the macro will be sending the reminder mail.

Thanks,
Bhanu

**Amber12** · 02-10-2014, 06:32 PM

Thank you so much for the input.
I tried to run the macro and is not working. Also, if it would work, would it work only with outlook? With a regular yahoo email adress would not?
I will look into the scheduler shortly. I never worked with it but am willing to learn anything new that would work and would help to automate these messages. I am just not good with codes, that's why I asked for help here.

**natefarm** · 02-11-2014, 10:10 AM

You would need Outlook on your machine, but it will send to any address. Do you have Outlook? If not, someone else might be able to provide code adjustments to make it work. If you do have it, please provide more information other than, "it's not working". Thanks.

**Amber12** · 02-12-2014, 10:43 PM

Sorry for the delay in answering. I guess I could use the outlook. I have it installed, but it's the work address and I was trying to avoid this. So not to complicate things I would use outlook. However, I hope to be able to use the scheduler to open the document and generate all the actions.

I am attaching an image with the error that I get when I press the button.

Thank you

error message macro.jpg

**natefarm** · 02-13-2014, 11:22 AM

Enable your macros.

Macro to send automated email reminders

LinkBack

Thread Tools

Rate This Thread

Display

Macro to send automated email reminders

Re: Macro to send automated email reminders

Re: Macro to send automated email reminders

Re: Macro to send automated email reminders

Re: Macro to send automated email reminders

Re: Macro to send automated email reminders

Re: Macro to send automated email reminders

Re: Macro to send automated email reminders

Re: Macro to send automated email reminders

Re: Macro to send automated email reminders

Re: Macro to send automated email reminders

Re: Macro to send automated email reminders

Re: Macro to send automated email reminders

Re: Macro to send automated email reminders

Thread Information

Users Browsing this Thread

Similar Threads

Help with automated pop-up box and email reminders, and reminder list

Set Up Macro to send reminders to Outlook Calendar

command button VBA to send automated email not workbook or worksheet

Automated email to send worksheet as report

Macro to send automated mail thru Outlook

Bookmarks

Bookmarks

Posting Permissions