+ Reply to Thread
Results 1 to 8 of 8

How do I write a macro or formula to send multiple outlook appointments/reminders

  1. #1
    Registered User
    Join Date
    12-20-2021
    Location
    Orkney, Scotland
    MS-Off Ver
    2016
    Posts
    7

    How do I write a macro or formula to send multiple outlook appointments/reminders

    I've attached the spreadsheet (Office 2016) I use to track the due dates of apprentice reviews. When a date is entered in column H, columns K - Z populate with dates. row 6 shows the date reviews are actually due, and row 7 shows the window within which the review must be carried out. I have it set up so that when today's date falls within the review window (row 7), a formula prompts me to click in the data row (the attachment shows this in row 15). Clicking the prompt, produces a prepopulated email ready to send to the named assessor (email address) who will appear in column G. There will be various assessors named in column G.

    I would like to add a formula/macro that will send calendar appointments to the email address in column G at the time when I populate the start date (Column H). I would like to send 8 appointments for the dates in row 7, columns K, M, O, Q, S, U, W and Y. I would want to repeat the process for each start date I enter, as I move down the spreadsheet. I just want to put dates in someone else's calendar (column G) as a first reminder of when reviews will be due. The automated email, will then give a second reminder when the review window opens.

    I have found possible solution to this online, but I'm not knowledgeable enough to edit macros to suit my own spreadsheet. I'd really appreciate some help on this please.
    Attached Files Attached Files

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: How do I write a macro or formula to send multiple outlook appointments/reminders

    Quote Originally Posted by Mr Wilks View Post
    I would like to send 8 appointments for the dates in row 7, columns K, M, O, Q, S, U, W and Y.
    I assume you mean that if you put in a date in H5, you want appointments for the dates in row 7. To generalize, if a date is entered in column H, you want appointments to be sent for the "Review Period" row for that date.

    What is the subject of the appointment?
    What is the text for the body of the appointment?
    Do you want this to be an "all day" appointment?

    When creating an email in VBA you have two options: 1) Display the email so the user can review it. Then the user has to press Send to actually send it. 2) Send the email immediately. The user never sees it displayed. Which of those would you like?

    The automated email, will then give a second reminder when the review window opens.
    Not sure what you mean here. What automated email is that? Do you mean the one you have already implemented?
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    12-20-2021
    Location
    Orkney, Scotland
    MS-Off Ver
    2016
    Posts
    7

    Re: How do I write a macro or formula to send multiple outlook appointments/reminders

    Thanks for your response.

    If a date is entered in H5, I would like an appointment generated for the dates in K7, M7, O7, Q7, S7, U7, W7 and Y7, with the appointments being sent to the email address I'll have in G5. Likewise, a date entry in H10 will generate appointments for K12, M12... etc., with those appointments being sent to G10.

    What is the subject of the appointment? "MA Review Due"
    What is the text for the body of the appointment? "The 6wk review window has opened for one of your MAs. Please consult the shared spreadsheet to see which of your MAs need reviewed."
    Do you want this to be an "all day" appointment? The time period doesn't matter so much but all day would be fine.

    You can probably ignore my reference to the automated email. I have one set up at the moment as I managed to figure out how to do that (you can see it activated in M5 and K15 "Review Due"). However, if I can automate a system that sends calendar appointments when a start date is entered, I won't need to use the email reminder so I'll probably get rid of it.

    I'm happy for the VBA to 'display' the appointment. Once I'm confident it's working the way I need it to, I will be able to change it to send. I don't know much about VBAs, but that much I can manage.

    Because I want it to work for each respective entry I make in column H, I've wondered if it would need to be used in such a way that when the macro is run, it might prompt me to select the dates I want the reminders sent for. Doing this would be absolutely fine, but I'm happy for it to be set up in whichever way you think it might work best.

    I hope this has answered the questions you asked and that it makes some kind of sense to you.

    Thanks for looking at this.

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: How do I write a macro or formula to send multiple outlook appointments/reminders

    This assumes that column G has only an email address in standard format like

    [email protected]

    not a name tag like

    Joe Sample <[email protected]>

    I enhanced your text a little to include the name of the person being reviewed and the milestone number for review.

    The worksheet module detects changes in column H and sends the emails. Module1 has the utility code to send an appointment notice.

    I suggest you get rid of merged cells which can cause a lot of problems, and use Center Across Selection instead.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-20-2021
    Location
    Orkney, Scotland
    MS-Off Ver
    2016
    Posts
    7

    Re: How do I write a macro or formula to send multiple outlook appointments/reminders

    Thank you so much for this, it works a treat.

    I would like to learn to understand more about the construction of VBAs as their functionality is amazing.

    thanks again and Merry Christmas

  6. #6
    Registered User
    Join Date
    12-20-2021
    Location
    Orkney, Scotland
    MS-Off Ver
    2016
    Posts
    7

    Re: How do I write a macro or formula to send multiple outlook appointments/reminders

    6 String Jazzer - I've managed to expand the worksheet you kindly coded for me so that I can use it in real time. However, I've encountered a problem for moving to next year. All I've done is to get rid of the merged cells as you advised and copied the selection you worked on, down the sheet another 4 times. When I enter data in randomly selected rows, the VBA runs successfully. However, if I create a new worksheet for academic year 2022-23 the VBA doesn't run on the newly created sheet.

    I thought this would be obvious in the code so I went looking for anything that referred to 'worksheet' so I could edit it to read 'workbook' but changing the only reference to worksheet I can see, stops the VBA working.

    Is there a way I can easily update the code that would allow me to copy and past the blank template onto new worksheets within the same workbook?

    Thanks in advance

  7. #7
    Registered User
    Join Date
    12-20-2021
    Location
    Orkney, Scotland
    MS-Off Ver
    2016
    Posts
    7

    Re: How do I write a macro or formula to send multiple outlook appointments/reminders

    On playing around a little more I realised I would work if I made a copy of the worksheet and then re-named it. What I was doing before that was creating a new sheet and then doing select all, the copy and paste.

  8. #8
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: How do I write a macro or formula to send multiple outlook appointments/reminders

    Yes. If you copy the worksheet itself, the code will also be copied. That won't happen if you just copy and paste all the cells.

+ 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. Add multiple reminders to outlook calendar using VBA
    By ShakJames in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-07-2017, 06:00 AM
  2. [SOLVED] VBA Outlook to Write, but NOT send email
    By A Ru in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 01-30-2017, 05:06 PM
  3. send reminders to outlook
    By plongberry in forum Excel General
    Replies: 1
    Last Post: 03-20-2015, 12:53 PM
  4. Macro to send automated email reminders
    By Amber12 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 02-13-2014, 11:22 AM
  5. Excel Macro/VBA to create multiple outlook appointments with required attendees
    By bradliggett in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-28-2013, 03:10 PM
  6. 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
  7. [SOLVED] Can I record or write a macro to send a worksheet via Outlook?
    By Catherine in forum Excel General
    Replies: 1
    Last Post: 09-14-2005, 12:05 AM

Tags for this Thread

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