Page 1 of 2 12 LastLast
Results 1 to 15 of 23

Create Outlook appointment

  1. #1
    NISMOJim is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2010
    Posts
    253

    Create Outlook appointment

    Hello All,

    This is my first time trying to control Outlook through Access VBA, and I have no idea what I'm doing. I have created a database for users to reserve building space, and I would like the command button on the form to enter their scheduled usage time on an Outlook calendar.

    So far, I have created a new calendar (ATCG8S1) in Outlook, which shows up just below my personal calendar (LoveJ), under My Calendars. When everything is up & running with the database, my plan is to send a share invite for the calendar to my boss. He will be the person entering all of the usage times through the database instead of me. I don't know if this is the best way to make this happen, but being unfamiliar with Outlook calendars, it's all I've been able to come up with. Any other suggestions are welcome.

    The Access form I'm starting with contains text boxes for the user to enter; txtUser, txtGrp, txtStartDate, txtEndDate. It also has txtStartTime and txtEndTime, but these boxes default to 0:00:01 AM & 11:59:59 PM respectively, as all usage will be an all day event. These last two textboxes will not be visible on the form.

    I found the following code on one of the forums, and changed the names to fit my needs (I think)...

    Private Sub cmdReserve_Click()
    Dim oApp As Object
    Dim oCalendar As Outlook.MAPIFolder
    Dim oNameSpace As Outlook.NameSpace
    Dim oAppt As Outlook.AppointmentItem
    Dim oAcct As Outlook.Recipient
    Dim ApptNotes As String
    Dim strName As String
    Dim calName As String

    On Error Resume Next

    calName = "ATCG8S1"

    Set oApp = CreateObject("Outlook.Application.16")
    Set oNameSpace = oApp.GetNamespace("Mapi")


    Set oAcct = oNameSpace.CreateRecipient(calName)
    Set oCalendar = oNameSpace.GetSharedDefaultFolder(oAcct, olFolderCalendar)
    Set oAppt = oCalendar.Items.Add(olAppointmentItem)

    'Save Appointment

    With oAppt

    .Subject = Me!txtUser & " " & Me!txtGrp
    .Start = Format(Me.txtStartDate, "Short Date") &" " & Format(Me.txtStartTime, "Short Time")
    .End = Format(Me.txtEndDate, "Short Date") & " " & Format(Me.txtEndTime, "Short Time")
    .ReminderSet = False
    .AllDayEvent = AllDay
    .Save
    .Close (olSave)

    End With

    MsgBox "Appointment Added!", vbInformation

    'Release the Outlook object variable
    Set oCalendar = Nothing
    Set oNameSpace = Nothing
    Set oApp = Nothing

    Err_Exit:

    Set oCalendar = Nothing
    Set oNameSpace = Nothing
    Set oApp = Nothing

    Exit Sub

    End Sub

    Having no idea what any of the code means, I don't know to get this to work. The appointment does not get added to the calendar, but I am not getting any error messages. The only thing that does work is the popup message box at the end that says "Appointment Added"

    As always, any help is appreciated.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,316
    For starters, delete or comment out this line, which is bad practice and hiding any error you may be getting:

    On Error Resume Next

    or change it to the normal:

    On Error GoTo Err_Exit

    Here's more on error handling:

    http://www.baldyweb.com/ErrorTrap.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,316
    Actually just delete it for now, or change to the method in the link. At this point you want to see every error.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    NISMOJim is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2010
    Posts
    253
    Thanks for the quick reply. I commented out the error handling & got a Run-time error'-2147352567 (80020009) Outlook does not recognize one or more names.

    Here is the line that hangs up

    Set oCalendar = oNameSpace.GetSharedDefaultFolder(oAcct, olFolderCalendar)

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,029
    I admit, no experience getting Access to populate Outlook so this might be a dumb question, but wouldn't it be easier to use Outlook directly, and if you needed that data in tables, pull it in from Access? You can set up rooms as resources and invite them to meetings (thus book them) so why try to do it from Access? There are also Access based appointment calendars already out there.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    Sometimes I talk to myself - like when I need expert advice.

  6. #6
    NISMOJim is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2010
    Posts
    253
    When this project is done, there will be around 16 different spaces that can be booked, thus 16 different calendars. The boss liked the layout I have so far, since the user interface is so easy, so I'm just running with it. Gotta keep the boss happy! I've never been known to do things the easy way, but the simplicity of the booking with everything at their fingertips seemed like a good way to go. Much more user friendly than fumbling through 16 calendars. But only if it works!

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,029
    So you not only want to see the schedule of 16 rooms for a given date all at the same time, you want to do the booking from Access as well?
    Hope you're not leaving there too soon!

  8. #8
    NISMOJim is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2010
    Posts
    253
    That will be the nice part. They won't have to stumble through 16 calendars. When the building is selected, only the calendar for that building will show up.

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,316
    I've added appointments to Outlook from Access but never using a shared calendar, so some of that code is new to me. The error seems to imply that the account isn't recognized. Have you set a breakpoint and seen what that resolves to?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    NISMOJim is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2010
    Posts
    253
    I set the break point at the line where the error occurs;

    Set oCalendar = oNameSpace.GetSharedDefaultFolder(oAcct, olFolderCalendar)

    When I hover over the oCalendar part, it says "oCalendar = Nothing.
    When I hover over the oAcct part, it says "oAcct = ATCG8S1"
    When I hover over the olFolderCalendar part, it says "olFolderCalendar = 9"

    Not sure if it makes a difference, but in Outlook in the Calendar tab on the left side of the screen, I have folders listed as My Calendars and Shared Calendars. ATCG8S1 is under My Calendars. Maybe it is supposed to be under Shared Calendars, but I don't seem to have the option to set that up. I can only create a new calendar to be placed in My Calendars. Is that something I need some kind of administrative privileges to do?

  11. #11
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,029
    It equals nothing because the line hasn't been executed if it's highlighted. You can't reliably check variables on the left side of = on a highlighted line. You have to move off of it by at least one, or whatever it takes to assign the value. As for the rest, I don't know, but I figured this answer would allow you to do over and report on what oCalendar is equal to.

  12. #12
    NISMOJim is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2010
    Posts
    253
    Ok, I moved the break up one line where it says "Set oAcct = oNameSpace.CreateRecipient(calName)". oCalendar (declared as Dim oCalendar As Outlook.MAPIFolder), still says " = Nothing", and olFolderCalendar still says "= 9". No idea what that means. Of course the line now with the break is where it sets oAcct, so nothing is there yet.

    Of course I can't set the break to the line below the error line. Not sure where to go from here.

  13. #13
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,029
    If you moved it up how does that allow where you had the break to be processed? If you can't move a break down one line because the next line isn't suitable then you add something in between and put the break there. A simple msgbox or debug.print will do.

  14. #14
    NISMOJim is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2010
    Posts
    253
    Ok,I added Debug.Print between the lines

    Set oAcct = oNameSpace.CreateRecipient(calName)
    Set oCalendar = oNameSpace.GetSharedDefaultFolder(oAcct, olFolderCalendar)

    but I still get the same thing, oCalendar = Nothing, oAcct = ATCG8S1 & olFolderCalendar = 9.
    Could this be because the calendar has not been shared with anyone yet? Something to do with the GetSharedDefaultFolder?

  15. #15
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,029
    As I mentioned in post 5, I'm not the one who can help with automating Outlook. My contribution on the subject would be limited to basic code behaviour or techniques. Sorry.
    I do think your assumption has merit though. In cases like this, I try to examine the members of a collection or set of properties, depending on which I'm trying to find. If what you're after is an object, then maybe along the lines of
    Code:
    Dim obj As Object
    For Each obj in oNameSpace.GetSharedDefaultFolder(oAcct, olFolderCalendar)
       debug.print obj.Name
    Next
    If that produces a list of items in a collection you're looking for, then you can find out if a certain value is present. I kind of doubt that you're looking for an object here, though.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    Sometimes I talk to myself - like when I need expert advice.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Adding an appointment to Outlook shared calendar
    By simon123 in forum Programming
    Replies: 1
    Last Post: 08-03-2018, 11:14 AM
  2. Replies: 4
    Last Post: 09-24-2014, 09:26 AM
  3. Access - Outlook Appointment shared calendar
    By Guerra67 in forum Access
    Replies: 1
    Last Post: 09-21-2014, 07:26 PM
  4. Replies: 1
    Last Post: 05-31-2013, 02:04 AM
  5. Replies: 1
    Last Post: 03-21-2013, 11:50 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums