Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Lounger
    Join Date
    Jul 2002
    Location
    Hampshire, Illinois, USA
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Outlook Automation (VB-VBA) (2003)

    I have been trying to post contacts and calendar items to public folders but can only post to the user's default calendars and contacts in Outlook. The code:

    ================================================== =====================
    Public Function GetPublicFolder(strFolderPath As String) As MAPIFolder

    Dim objApp As Outlook.Application
    Dim objNS As Outlook.NameSpace
    Dim colFolders As Outlook.Folders
    Dim objFolder As Outlook.MAPIFolder
    Dim arrFolders() As String
    Dim I As Long
    On Error Resume Next

    strFolderPath = Replace(strFolderPath, "/", "")
    arrFolders() = Split(strFolderPath, "")
    Set objApp = Application
    Set objNS = objApp.GetNamespace("MAPI")
    Set objFolder = objNS.Folders.Item(arrFolders(0))
    If Not objFolder Is Nothing Then
    For I = 1 To UBound(arrFolders)
    Set colFolders = objFolder.Folders
    Set objFolder = Nothing
    Set objFolder = colFolders.Item(arrFolders(I))
    If objFolder Is Nothing Then
    Exit For
    End If
    Next
    End If

    Set GetPublicFolder = objFolder
    Set colFolders = Nothing
    Set objNS = Nothing
    Set objApp = Nothing

    End Function
    ================================================== =====================

    Public Function AddOutlookAppt()
    'On Error GoTo Add_Err

    Dim olNs As Outlook.NameSpace
    Dim olapp As Outlook.Application
    Dim olappt As Outlook.AppointmentItem
    Dim olItem As Object
    Dim olItems As Outlook.Items
    Dim olFolders As Outlook.Folders
    Dim olFolder As Outlook.MAPIFolder
    Dim olMail As Outlook.MailItem

    If theFolder = "" Then
    MsgBox "Invalid Company (TCC/TCS) Selected. Appointment cannot be added.", vbInformation
    Exit Function
    End If

    Set olapp = CreateObject("Outlook.Application")
    Set olFolder = GetPublicFolder("Public FoldersAll Public Folders" & theFolder)
    Set olappt = olapp.CreateItem(olAppointmentItem)

    'MsgBox olFolder

    With olappt
    .Start = ApptDate & " " & ApptTime
    .Duration = ApptLength 'minutes...
    .Subject = Appt

    If Not IsNull(ApptNotes) Then .Body = ApptNotes 'not populated as of 11/15/06 epm.
    If Not IsNull(ApptLocation) Then .Location = ApptLocation
    If ApptReminder Then
    .ReminderMinutesBeforeStart = ReminderMinutes
    .ReminderSet = True
    End If

    .Save

    .Close (olSave)
    End With

    Set olappt = Nothing
    Set olapp = Nothing
    Set olFolder = Nothing
    Set olFolders = Nothing
    Set olNs = Nothing

    ApptAdded = True

    Exit Function

    Add_Err:
    MsgBox "Error " & Err.Number & vbCrLf & Err.Description
    Exit Function

    End Function

    ================================================== =====================

    I don't know what I'm doing wrong. Any help would be appreciated!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Outlook Automation (VB-VBA) (2003)

    CreateItem always creates an item in the default folder for the specified type, i.e CreateItem(olAppointmentItem) creates an appointment in the user's Calendar, CreateItem(olTaskItem) creates a task in the user's Tasks folder etc.
    To create an item in a specific folder, use the Add method of the Items collection of that folder:

    ...
    Set olFolder = GetPublicFolder("Public FoldersAll Public Folders" & theFolder)
    Set olappt = olFolder.Items.Add(Type:=olAppointmentItem)
    ...

  3. #3
    Lounger
    Join Date
    Jul 2002
    Location
    Hampshire, Illinois, USA
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook Automation (VB-VBA) (2003)

    Hey Hans! Thank you for your help. I am not getting an error where indicate:

    ================================================== ================================================== ==

    Public Function AddOutlookAppt()
    'On Error GoTo Add_Err

    Dim olNs As Outlook.NameSpace
    Dim olapp As Outlook.Application
    Dim olappt As Outlook.AppointmentItem
    Dim olItem As Object
    Dim olItems As Outlook.Items
    Dim olFolders As Outlook.Folders
    Dim olFolder As Outlook.MAPIFolder
    Dim olMail As Outlook.MailItem

    If theFolder = "" Then
    MsgBox "Invalid Company (TCC/TCS) Selected. Appointment cannot be added.", vbInformation
    Exit Function
    End If

    Set olapp = CreateObject("Outlook.Application")
    Set olFolder = GetPublicFolder("Public FoldersAll Public Folders" & theFolder)
    Set olappt = olFolder.Items.Add(olAppointmentItem) <----------- ERROR: Object variable or With block variable not set (Error 91)



    With olappt
    .Start = ApptDate & " " & ApptTime
    .Duration = ApptLength 'minutes...
    .Subject = Appt

    If Not IsNull(ApptNotes) Then .Body = ApptNotes 'not populated as of 11/15/06 epm.
    If Not IsNull(ApptLocation) Then .Location = ApptLocation
    If ApptReminder Then
    .ReminderMinutesBeforeStart = ReminderMinutes
    .ReminderSet = True
    End If

    .Save

    .Close (olSave)
    End With

    Set olappt = Nothing
    Set olapp = Nothing
    Set olFolder = Nothing
    Set olFolders = Nothing
    Set olNs = Nothing

    ApptAdded = True

    Exit Function

    Add_Err:
    MsgBox "Error " & Err.Number & vbCrLf & Err.Description
    Exit Function

    ================================================== ================================================== ==
    GetPublicFolder ("Public FoldersAll Public FoldersTCC CALENDAR")

    End Function
    Public Function GetPublicFolder(strFolderPath As String) As MAPIFolder

    Dim objApp As Outlook.Application
    Dim objNS As Outlook.NameSpace
    Dim colFolders As Outlook.Folders
    Dim objFolder As Outlook.MAPIFolder
    Dim arrFolders() As String
    Dim I As Long
    On Error Resume Next

    strFolderPath = Replace(strFolderPath, "/", "")
    arrFolders() = Split(strFolderPath, "")
    Set objApp = Application
    Set objNS = objApp.GetNamespace("MAPI")
    Set objFolder = objNS.Folders.Item(arrFolders(0))
    If Not objFolder Is Nothing Then <------------------------------- objFolder Is Nothing HERE!
    For I = 1 To UBound(arrFolders)
    Set colFolders = objFolder.Folders
    Set objFolder = Nothing
    Set objFolder = colFolders.Item(arrFolders(I))
    If objFolder Is Nothing Then
    Exit For
    End If
    Next
    End If

    Set GetPublicFolder = objFolder
    Set colFolders = Nothing
    Set objNS = Nothing
    Set objApp = Nothing

    End Function

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Outlook Automation (VB-VBA) (2003)

    Your note "objFolder Is Nothing HERE" shows that "Public Folders" is not recognized as a valid folder. Are you sure that you're connected to Exchange Server when you try this?

  5. #5
    Lounger
    Join Date
    Jul 2002
    Location
    Hampshire, Illinois, USA
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook Automation (VB-VBA) (2003)

    I can open the folder(s) from within my Outlook. Does that not show I'm connected?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Outlook Automation (VB-VBA) (2003)

    Yes, if you can see and open them in the Outlook interface, you're connected to Exchange Server.

    I won't be able to test your code until Monday (I have no connection to Exchange at home); if nobody else replies before that, I'll look at it then.

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Outlook Automation (VB-VBA) (2003)

    I have never been able to set an Outlook folder as object in VBA using the folder string, such as:

    Set myFolder = Folders("Public FoldersAll Public FoldersTCC CALENDAR")

    ... If you find out how to do it, I'm interested. Instead I have always used one of two techniques:

    1. Name the entire folder path as in <post#=461,432>post 461,432</post#>.

    2. Recursively iterate through all the folders such as Hans does in <post#=685,076>post 685,076</post#>. See the function he calls recursively to examine folders, do not use the part of the code that then loops through every item within the folder. Use the same technique to locate your target folder "TCC Calendar".
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Outlook Automation (VB-VBA) (2003)

    The GetPublicFolder function posted by Eileen parses the folder path and sets a reference to each folder starting at the top...

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Outlook Automation (VB-VBA) (2003)

    Thank you. It was very remiss of me not to look carefully at Eileen's code. I hope the function below, with test subroutine, will make amends, it's working for me at present, company folder misspellings and all <img src=/S/grin.gif border=0 alt=grin width=15 height=15>:

    Sub testgetpf()
    Dim f As MAPIFolder
    On Error Resume Next
    Set f = GetPublicFolder("Public FoldersAll Public FoldersOps IntergrationAbsences")
    ' next is an alternative test line to make sure I could set the folder
    ' Set f = Outlook.Session.GetDefaultFolder(olPublicFoldersAl lPublicFolders).Folders("Ops Intergration").Folders("Absences")
    Debug.Print f.FolderPath
    End Sub

    Public Function GetPublicFolder(ByVal strFolderPath As String) As MAPIFolder
    Dim arrFolders() As String
    Dim intI As Integer

    strFolderPath = Replace(strFolderPath, "Public FoldersAll Public Folders", "")
    strFolderPath = Replace(Replace(strFolderPath, "/", ""), "", "")
    arrFolders() = Split(strFolderPath, "", , vbTextCompare)
    Set GetPublicFolder = Outlook.Session.GetDefaultFolder(olPublicFoldersAl lPublicFolders)

    For intI = 0 To UBound(arrFolders)
    Set GetPublicFolder = GetPublicFolder.Folders(arrFolders(intI))
    Next intI
    End Function

    The only thing clearly wrong I saw with Eileen's code is that Split() returns a zero based array and she started iterating at one (1), but it may be also necessary to correctly resolve the parent information store for Public Folders before iterating the folders, and my "cheat" in the function handles that.
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Outlook Automation (VB-VBA) (2003)

    Eileen, as you'll see from my embarrassed reply to Hans explanation to me, we may have a code solution for you. The other thing to check is that you have Exchange server permissions to create an item in the target public folder; you can test that manually just as you did navigating to the folder.
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Outlook Automation (VB-VBA) (2003)

    The GetPublicFolder function is from OutlookCode.com :: Get Public Folder.
    The line

    Set objFolder = objFolder.Folders.Item(arrFolders(0))

    gets the first item (index = 0) of the array returned by Split.

  12. #12
    Lounger
    Join Date
    Jul 2002
    Location
    Hampshire, Illinois, USA
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook Automation (VB-VBA) (2003)

    Thank you both so much for your help. I can post to the public calendars and contact lists... so it's not a rights issue I don't think.

    You say you have a code solution -- does that mean the last post you left is the code I should use -- or did you mean it would be forthcoming.

    Let me know -- and AGAIN -- thank you.

    Sincerely, Eileen

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Outlook Automation (VB-VBA) (2003)

    John meant the GetPublicFolder funcion in <post:=729,243>post 729,243</post:> as a replacement for the version that you posted. The testgetpf macro is intended to test whether his version works.
    You should keep the AddOutlookAppt code.

  14. #14
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Outlook Automation (VB-VBA) (2003)

    You are right again. I'm having a really dense day, I guess. <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15> The folders have to be looped that way because the first is set as Parent and the rest as subfolders. And now I see my approach is almost the same as Sue's, as she sets a reference using the same GetDefaultFolder method. only using the value 18 instead of the named argument I use. Sue's FolderPath function in version is not intended to have "Public FoldersAll Public Folders" passed to it, just the child folders.
    -John ... I float in liquid gardens
    UTC -7ąDS

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Outlook Automation (VB-VBA) (2003)

    I have tested this code at work, with the path to a public folder in which I have sufficient permissions.
    It successfully set a reference to the public folder, and successfully created an appointment with the specified properties in this folder.
    So at leat you know that the code is correct. I don't know why it doesn't work for you, so you should try JohnBF's code.
    That also worked for me, but I had to change the line

    Set olFolder = GetPublicFolder("Public FoldersAll Public Folders" & theFolder)

    to

    Set olFolder = GetPublicFolder("Public FoldersAll Public Folders" & theFolder)

    Note the backslash at the beginning of the path.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •