+ Reply to Thread
Results 1 to 14 of 14

Macro to send automated email reminders

  1. #1
    Registered User
    Join Date
    02-03-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    49

    Macro to send automated email reminders

    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!

  2. #2
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: Macro to send automated email reminders

    Would someone be responsible for opening the workbook to initiate the reminders?
    Acts 4:12
    Salvation is found in no one else, for there is no other name under heaven given to mankind by which we must be saved.

  3. #3
    Registered User
    Join Date
    02-03-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    49

    Re: Macro to send automated email reminders

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

  4. #4
    Registered User
    Join Date
    02-03-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    49

    Re: Macro to send automated email reminders

    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.

  5. #5
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: Macro to send automated email reminders

    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

  6. #6
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to send automated email reminders

    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.

  7. #7
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: Macro to send automated email reminders

    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.

  8. #8
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to send automated email reminders

    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.

  9. #9
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: Macro to send automated email reminders

    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.

  10. #10
    Forum Contributor
    Join Date
    12-09-2013
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    171

    Re: Macro to send automated email reminders

    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

  11. #11
    Registered User
    Join Date
    02-03-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    49

    Re: Macro to send automated email reminders

    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.

  12. #12
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: Macro to send automated email reminders

    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.

  13. #13
    Registered User
    Join Date
    02-03-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    49

    Re: Macro to send automated email reminders

    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

  14. #14
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: Macro to send automated email reminders

    Enable your macros.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Help with automated pop-up box and email reminders, and reminder list
    By dashcanon in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-09-2013, 09:28 PM
  2. Set Up Macro to send reminders to Outlook Calendar
    By slohman in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-20-2013, 11:21 PM
  3. command button VBA to send automated email not workbook or worksheet
    By lpratt in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-08-2012, 11:13 PM
  4. Automated email to send worksheet as report
    By wellseytd in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-10-2011, 10:28 AM
  5. Macro to send automated mail thru Outlook
    By Rajkumar in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-01-2010, 03:29 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1