+ Reply to Thread
Results 1 to 6 of 6

Create Outlook Appointments from Range

  1. #1
    Forum Contributor
    Join Date
    02-27-2007
    Posts
    155

    Create Outlook Appointments from Range

    I would like to create an "ALL DAY" appointment in OUTLOOK for each day in a range of Cells... The cell range will ALWAYS be the same and consist of 12 cells arranged side by side. The cell can be blank.

    16-Oct-07 15-Jan-08
    22-Oct-07 12-Feb-08
    20-Nov-07 10-Mar-08
    15-Dec-07
    20-Dec-07
    3-Jan-08

    What I would like to do is attach to a button a script of some kind that would look at these 12 cells and create 12 (or fewer) appointments in Outlook on each of the days.
    The Subject would be the value in Cells F10, C4, The location would be the value in Cell C6, the ALL DAY Event check box would be checked and the Reminder Checkbox would be UNCHECKED. Body of the appointment would be: "Canned TEXT"
    C3
    C4
    C5
    through C11 all on separate rows.

    I think it would also be a good idea that if the button is pressed to write a value to a cell in the spreadsheet that the script could check to see if the appointment button had already been used... to stop me from accidentally pressing it twice and creating duplicate entries. Maybe write to A99 a 1 if the appointments were already added and then have the script check this cell to see if there is a 1 in it.

    Any takers on how this might be done?

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,966
    You need to make a reference to the Outlook Object library.
    Record the Outlook actions as an outlook macro and then edit this within VBA to get the values from your cells.
    Martin

    If my solution has saved you time and/or money, please consider sponsoring my run in the 2020 London Marathon in aid of Cancer Research UK.

    https://uk.virginmoneygiving.com/MartinRice

  3. #3
    Forum Contributor
    Join Date
    02-27-2007
    Posts
    155
    How is an Outlook Macro going to be smart enough to know there are only 3 appointments instead of 12 or that I need to update a field in the spreadsheet?

  4. #4
    Registered User
    Join Date
    10-01-2007
    Posts
    23
    I think hes saying to use an outlook recorded macro so you can get an idea as to what the general syntax of the outlook code is.

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

    Here is the macro to create the Outlook "All Day" calendar item. I used the cell references you supplied. You can create a loop to pull the dates and supply them to the macro, The macro will then create the item, and save it in Outlook. Attach the looping macro to a Forms Toolbar button.

    Create "All Day" Appointment Item in Outlook
    Please Login or Register  to view this content.
    Adding the Macro to your Workbook
    1. Copy the macro above pressing the keys CTRL+C
    2. Open your workbook
    3. Right Click on any Sheet's tab
    4. Left Click on View Code in the pop up menu
    5. Press the keys ALT+I to activate the Insert menu
    6. Press M to insert a Standard Module
    7. Paste the code by pressing the keys CTRL+V
    8. Make any custom changes you need to the macro
    9. Save the Macro by pressing the keys CTRL+S

    Sincerely,
    Leith Ross
    Last edited by Leith Ross; 10-04-2007 at 10:31 AM. Reason: Changed "Loc", a keyword, to "Place"

  6. #6
    Registered User
    Join Date
    02-22-2011
    Location
    Santa Cruz, Ca
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Create Outlook Appointments from Range

    Quote Originally Posted by Leith Ross View Post
    Hello Bmasella,

    Here is the macro to create the Outlook "All Day" calendar item. I used the cell references you supplied. You can create a loop to pull the dates and supply them to the macro, The macro will then create the item, and save it in Outlook. Attach the looping macro to a Forms Toolbar button.

    Create "All Day" Appointment Item in Outlook
    Please Login or Register  to view this content.
    Adding the Macro to your Workbook
    1. Copy the macro above pressing the keys CTRL+C
    2. Open your workbook
    3. Right Click on any Sheet's tab
    4. Left Click on View Code in the pop up menu
    5. Press the keys ALT+I to activate the Insert menu
    6. Press M to insert a Standard Module
    7. Paste the code by pressing the keys CTRL+V
    8. Make any custom changes you need to the macro
    9. Save the Macro by pressing the keys CTRL+S

    Sincerely,
    Leith Ross
    Hi Leith,
    I have been trying to follow and do a similar thing, Here is a screen shot of the schedule I am working with along with a description. I am trying to use an excel schedule, where we put daily tonnage orders, and the type of mix, customer etc. I want to be able to have the excel sheet create a Microsoft outlook appointment when a cell for that day is filled.
    See attachment

    So if for example, someone enters a tonnage into Monday 2/14 (the 150 tons) it will create a outlook appointment that has
    the "Customer Name" for the Subject (GCCO)
    the "Project Name" for location (Elmwood correctional Facility) and the mix I.D. (1/2 Med no rap) tonnage
    for the date where the entry was made. It would also create an appointment for the 225 ton orders above too for that same day 2/14.

    Thanks for the help
    Dan

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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