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