Consulting

Page 3 of 3 FirstFirst 1 2 3
Results 41 to 53 of 53

Thread: Solved: Excel info to Outlook Form

  1. #41
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Ok, I see. That works wonderful.

    Now with that default form, is there a way to setup the Appointment as reoccuring? I can't seem to find it anywhere.

  2. #42
    VBAX Tutor jamescol's Avatar
    Joined
    May 2004
    Location
    Charlotte, NC
    Posts
    251
    Location
    Zack,
    I just tested the code as an Excel Add-in. When you compile the Add-in, ensure you have a reference set to the OL object library. The add-in will save the class information in the XLA.

    Create the XLA and close your file. Go back to VBE and make sure the OL Object library is not selected. Now load your add-in and you should see that it works fine.

    James
    "All that's necessary for evil to triumph is for good men to do nothing."

  3. #43
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Just did that, works like a breeze! James you're awesome!!!

    So, you know aobut the reoccuring appointment, from my above question?

  4. #44
    VBAX Tutor jamescol's Avatar
    Joined
    May 2004
    Location
    Charlotte, NC
    Posts
    251
    Location
    Setting recurrence should be possible - I'll need to verify using the built-in form, though.

    Setting recurrence gets complicated programmatically if you have many differing patterns. Do you know what the recurrence patterns will be, or will they change based on some criteria?


    James
    "All that's necessary for evil to triumph is for good men to do nothing."

  5. #45
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Preferrably it would be user-set. It would be something like Monthly, Quarterly, Semi-Annually, Annually. Something along those lines.

  6. #46
    VBAX Tutor jamescol's Avatar
    Joined
    May 2004
    Location
    Charlotte, NC
    Posts
    251
    Location
    When you set a recurrence via code, you have to specify all the patterns for it. Since the user needs to select the pattern, I think your best bet is to teach them how and not bother with setting it programmatically. If you set it, they'll just have to either check what you set or change it anyway.

    James
    "All that's necessary for evil to triumph is for good men to do nothing."

  7. #47
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    So I wouldn't be able to call the form, with the code you provided, and then have them set the occurance?

  8. #48
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Okay, scratch what I said. It's already there, the feature I'm asking about. I couldn't see it because the Standard toolbar was disabled.

    Which leads me to my next question: How do you force the Standard toolbar to be visible on the Appointment form?

  9. #49
    VBAX Tutor jamescol's Avatar
    Joined
    May 2004
    Location
    Charlotte, NC
    Posts
    251
    Location
    Here ya go:
    [vba]
    Sub showcb()
    Dim cbar As CommandBar
    Dim cbarName As String

    cbarName = "Standard"

    Set cbar = Application.ActiveExplorer.CommandBars(cbarName)
    cbar.Visible = True

    End Sub
    [/vba]
    "All that's necessary for evil to triumph is for good men to do nothing."

  10. #50
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Well, I"m a little unsure where to put it, and how to call. This is just one of the variations I've tried, can you take a look? ...


    [vba]
    Private Sub Command1_Click() 'To open Outlook Appointment
    Dim olApp As Object, cbar As CommandBar
    Dim cbarName As String
    Dim myOLApp As New Outlook.Application
    Dim myOLItem As Outlook.AppointmentItem
    cbarName = "Standard"
    Set myOLItem = myOLApp.CreateItem(olAppointmentItem)
    On Error Resume Next
    Set olApp = GetObject(, "Outlook.Application") 'Will error out if OL isn't open
    If Err Then
    MsgBox "Please launch Outlook.", vbOKOnly + vbExclamation, "Error"
    Exit Sub
    End If
    Set cbar = olApp.ActiveExplorer.CommandBars(cbarName)
    cbar.Visible = True
    With myOLItem
    .Location = ComboBox1.Value
    End With
    myOLItem.Display
    End Sub
    [/vba]


    It opens, but if the toolbar (Standar) is not activated, it won't set it.

  11. #51
    VBAX Tutor jamescol's Avatar
    Joined
    May 2004
    Location
    Charlotte, NC
    Posts
    251
    Location
    Zack,
    Try this example. My previous sample was turning on the standard OL toolbar, not the Appointment Item's toolbar. I placed everything in the correct order and it tests OK on my OL XP and OL 2003.

    You were getting the error because you need to first create a namespace and set the default folder location. Now that the code is doing this, you should consider changing your error handler to return more specific messages.

    Cheers,
    James

    [vba]
    Sub showcb()
    Dim olApp As Outlook.Application
    Dim olNS As Outlook.NameSpace
    Dim objFolder As MAPIFolder
    Dim olItem As Outlook.AppointmentItem
    Dim cbar As CommandBar
    Dim cbarName As String


    cbarName = "Standard"

    On Error Resume Next


    'Since you are interacting with stored data, you need to create an OL Namespace and set
    'the default folder to Calendar.
    'Then you can work with the actual Appointment item.
    Set olApp = New Outlook.Application
    Set olNS = olApp.GetNamespace("MAPI")
    Set objFolder = olNS.GetDefaultFolder(olFolderCalendar)

    'Probably should either change to a generic error handler or use a case statment
    'to detect specific errors.
    If Err Then
    MsgBox "Please launch Outlook.", vbOKOnly + vbExclamation, "Error"
    Exit Sub
    End If

    'Creat the appt item
    Set olItem = myOLApp.CreateItem(olAppointmentItem)

    'Set the cbar to the local appointment item's inspector
    Set cbar = olItem.GetInspector.CommandBars(cbarName)

    'Make the cbar visible
    cbar.Visible = True

    olItem.Location = ComboBox1.Value
    olItem.Display

    'Housekeeping
    Set olApp = Nothing
    Set olNS = Nothing
    Set objFolder = Nothing
    Set olItem = Nothing
    Set cbar = Nothing

    End Sub
    [/vba]
    "All that's necessary for evil to triumph is for good men to do nothing."

  12. #52
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Very cool James! You are a god!!!

    I just had to change this line ..

    [vba]
    Set olItem = myOLApp.CreateItem(olAppointmentItem)
    [/vba]
    to ...

    [vba]

    Set olItem = olApp.CreateItem(olAppointmentItem)
    [/vba]

    Works beautifully!!! Thank you so much for your hard work and persistence, and just flat sticking with me on this! I can't thank you enough - THANK YOU!!

  13. #53
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    I'm marking this one as Solved. If I need anything else, I'll start a new thread.

    James, Suat - THANK YOU!!! I appreciate all your help!

Posting Permissions

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