Solved: Excel info to Outlook Form

[Zack Barresse]

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.

[jamescol]

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

[Zack Barresse]

Just did that, works like a breeze! James you're awesome!!!

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

[jamescol]

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

[Zack Barresse]

Preferrably it would be user-set. It would be something like Monthly, Quarterly, Semi-Annually, Annually. Something along those lines.

[jamescol]

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

[Zack Barresse]

So I wouldn't be able to call the form, with the code you provided, and then have them set the occurance?

[Zack Barresse]

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?

[jamescol]

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]

[Zack Barresse]

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.

[jamescol]

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]

[Zack Barresse]

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!!

[Zack Barresse]

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!