Adding an appointment to Outlook from Excel with VBA

  • Hello,
    I am trying to figure out how to create an Outlook appointment with Excel VBA. I will be entering shipments into an Excel sheet and it would be very handy to just be able to create an appointment at the appropriate time with a click. I have my code working to post to the default calendar, but I need to post to a shared calendar specifically for shipments. Here is my code so far:


    Anyone have an idea of how to do this?


    I have posted this at two other forums with no response:
    http://www.excelforum.com/exce…tml?p=2992995#post2992995
    http://www.mrexcel.com/forum/e…l-basic-applications.html

  • Re: Adding an appointment to Outlook from Excel with VBA


    That is a great link, thank you. I might need to study up on my Dutch a bit. I have slimmed down my code and made it much easier to use, but am still having trouble getting it into the non-default calendar. Here is my code now:


    When I try to use the code for moving an appointment to another folder, Excel doesn't even recognize the syntax:

    Code
    1. .GetDefaultFolder(9).Items(c00).move ..GetDefaultFolder(9)


    Any thoughts?

  • Re: Adding an appointment to Outlook from Excel with VBA


    I'm sorry, my code includes

    Code
    1. .Move (olfolder)

    which does not work, I just forgot to take it out before posting.

  • Re: Adding an appointment to Outlook from Excel with VBA


    You have to use another method


    Code
    1. With CreateObject("Outlook.application").GetNamespace("MAPI").Folders(2).Folders("Agenda").items.add
    2. .Start = ws1.Range("A5").Value + TimeValue(time1 & ":00:00")
    3. .Duration = 15
    4. .Subject = ws1.Range("D2").Value & " - " & ws1.Range("A2").Value & " " & ws1.Range("D5").Value
    5. .Location = ws1.Range("G5").Value
    6. .Body = ws1.Range("H11").Value
    7. .Save
    8. end with
  • Re: Adding an appointment to Outlook from Excel with VBA


    I tried the code as is, and came up with an object not found error. Is there something I need to put in place of "Agenda"?

  • Re: Adding an appointment to Outlook from Excel with VBA


    I assume I should be looking in Documents and Settings\uername\Application Data\Microsoft\Outlook? my outlook file is Outlook.NK2, the other outlook file has the same extension. I guess I'm not sure how to alter the code to do this.

  • Re: Adding an appointment to Outlook from Excel with VBA


    I too am trying to figure this out. I've been unable to do so. If I come up with a solution I will gladly post my code here and share anything I have come to realize. Until then... more searching.

    Aren't you too short to be a Storm Trooper?

  • Re: Adding an appointment to Outlook from Excel with VBA


    I found the solution!!!!! :idea: First there is some code you need to run to determine your final calendar location. Before running this code, please make sure Microsoft Outlook Object Library is active in your Excel VBA Editor. To make it active, open VBA editor, select TOOLS -> REFERENCES and then put a checkmark next to Micorsoft Outlook Object Library. then run the following code. Take note that the calendar location will be returned as a long number. The code has two options for displaying this number. You can see the MsgBox display is currently commented to not display.





    Once you have the long location number, you can create the appoinntment then move it to the location you want. I have taken the guts of my code below so that you can modifiy it to your own needs.



    The code works as I have tested it. but I am curious if some of it can still be slimmed down. I feel there may be a few duplicate commands or something. But, at least it works! :)

    Aren't you too short to be a Storm Trooper?

  • Re: Adding an appointment to Outlook from Excel with VBA


    What happens if you don't know where the folder is in the file structure?


    Someone has given me permission to view their calendar with full access. When I click on Calendar on the bottom left of Outlook (Mail, Calendar, Contacts, Tasks), on the left toolbar there is my calendar, and then a bunch of others I've put in folders. But I don't know how that file structure fits in to the one that opens with your GetOutlookFolderID() (thanks, btw!). Any idea how to get that information if that calendar is active?


    TIA!


    PS - admins, let me know if I need to start a new thread for this. I will do so if needed. Thanks!

  • Re: Adding an appointment to Outlook from Excel with VBA


    Hi Bibleguy
    Did you eventually sort out this issue? I am new to VBA but am trying to create an appointment in Outlook from Excel. I just want to be able to select the cell containing the appointment date then create the appointment using the data in the rest of the row which has been formatted with the Outlook appointment fields. My calendar is a shared calendar and I believe is located on my company server. I would be thrilled to converse directly with you if acceptable to you.
    Blessings
    Bibleteacher