PDA

View Full Version : Solved: Excel info to Outlook Form



Zack Barresse
08-16-2004, 09:59 AM
Hi,

I've got an Excel UserForm. What I'm looking to do, is take the data from a Combo Box (ComboBox1) in this UserFrom from Excel, and place it in a Custom Form in Outlook. This would be going to ComboBox1 in the Outlook form. I'm attempting to call this Form from Excel.

Let me know any additional information that should be posted, I'm an Outlook doof and know next to nothing. :bink:

Anne Troy
08-16-2004, 12:25 PM
Question: Why use Excel at all?
IOW, wouldn't it be easier to store the data from the userform to a cell, and then use the cell to dump to Outlook form?

You *do* know you can't just go sending Outlook forms *out there*, right? They're really best with internal tasks. Unless you're talking about an Outlook TEMPLATE (oft)

Zack Barresse
08-16-2004, 12:33 PM
Oh, yeah, that's what I'm talking about. :o: It is an OFT file. And I can always add a temp sheet to write the data to or something, that wouldn't be a problem. It's just getting the info TO the template, is where I'm lost.

Anne Troy
08-16-2004, 12:35 PM
And the OFT has something that a regular message doesn't?
I'm trying to figure out what the message has that can't be done with a straight email...

:)

smozgur
08-16-2004, 01:49 PM
Hi Zack, Anne

Zack, I assumed that you have a custom message form in OL named as NewForm. And this form has a combobox control on it named as ComboBox1.

Please look at the attachment to see if it might help.

It basically loads that custom form in OL and fills in the combobox1 on OL form with data currently in Excel userform's combobox1 and selects the selected item in Excel userform then display the form.

I hope I understood it correctly.

Suat

Zack Barresse
08-16-2004, 01:56 PM
Suat,

That sounds EXACTLY like what I have. :yes But I couldn't extract your zip, it says there were no files to extract. ??

smozgur
08-16-2004, 01:58 PM
Strange, I just downloaded it and it seems fine.

??

Zack Barresse
08-16-2004, 01:59 PM
Just noticed I can't unzip ANY file from the board. This is frustrating..

smozgur
08-16-2004, 02:01 PM
Shall I send via email ??

Zack Barresse
08-16-2004, 02:02 PM
sure, thanks.

firefytr@ here .com

Anne Troy
08-16-2004, 02:04 PM
Yep. It worked for me, dude.
Might check your Winzip.

Suat!! How was your vacation??:beat

smozgur
08-16-2004, 02:04 PM
sent

Zack Barresse
08-16-2004, 02:06 PM
Got it. Same message, 'No files to extract'. Grrrr. Any ideas? I've not seen/delt with this one before.

smozgur
08-16-2004, 02:08 PM
Suat!! How was your vacation??:beat

I got back on Wednesday unexpectedly and terrible just after vacation, Anne. Terrible... Recently too bad mood.

smozgur
08-16-2004, 02:08 PM
Sending xls, Zack.

smozgur
08-16-2004, 02:10 PM
sent

Anne Troy
08-16-2004, 02:13 PM
I sent without the Zip, Zack.

I'll talk to you on YIM, Suat. :)

Zack Barresse
08-16-2004, 02:17 PM
Got it, from both. Thanks! Not sure what that was all about.

So Suat, in your code the line ...

Set objItem = objFolder.Items.Add("IPM.Note.NewForm")

Would I have to specify a path (could I) to the location of this Form? Currently it's in a seperate folder along with an Excel add-in from which it will be called. I can get the path no problem, but how would I go about telling where to find this specific form?

smozgur
08-16-2004, 02:22 PM
Hmm.. Zack, I assumed that form is saved in OL Personal Forms Library.

I missed that it is a standalone file in a real folder. I'll see what I can do about it.

Suat

smozgur
08-16-2004, 03:01 PM
Zack, did you try the code in a form that is saved in OL instead as an OFT file ?

Just curious. I will be able to modify it for the OFT file tomorrow.

Zack Barresse
08-16-2004, 03:06 PM
No, I didn't. I can try that though. I'm an OL doof, so it will be slow going for me. Thanks for all your help Suat, it's very much appreciated. This is a work project, a small one really. I'll try that out and let you know.

The premise for putting the form in another folder was that this was going to be used over a network (w/ possible department distribution) and wanted others to be able to access the file and set reminders on their Outlook as well. I'm not even sure if this is the best way to go about this or not. I'll post the results back here. Thanks again. :)

smozgur
08-16-2004, 03:10 PM
no no, it is no problem to test it Zack. I just wondered if it is working as you needed. If it would take your time then forget it.

I would be very happy if I could give a little help. :)

Suat

jamescol
08-16-2004, 05:10 PM
Zack,
Maybe these articles will help. From my experience, storing the forms as an OFT on a disk is not an optimal solution. Consider publishing the form your Organizational Forms Library.

http://support.microsoft.com/default.aspx?scid=kb;en-us;322145&Product=out

http://support.microsoft.com/default.aspx?scid=kb;en-us;290802&Product=out

http://support.microsoft.com/default.aspx?scid=kb;en-us;179506&Product=out

James

Zack Barresse
08-16-2004, 06:03 PM
Thanks for those James!

So i surmise from those articles that I want to publish this form to my Organizational Forms Library, I will acquire the permissions needed. What, in that case, would be the best method of retreiving from Excel?

The biggest reason that this needs to be in Excel, is the user's of this program (add-in) are just not computer Savy. They need a point-n-click type of operation. They will be predominantly using Excel and only be using Outlook for their email. The idea was to call a custom form from within Excel and set up a re-occuring event to pop up reminders for the entered tasks. The add-in will be used over a network, the email is setup through an exchange-server. Does this sound like the best route, or am I making things difficult on myself again?

Anne Troy
08-16-2004, 06:07 PM
Hm. Sounds okay to me. :)

Most people that ask about Outlook forms aren't aware that they're really no good outside of the exchange server. But it sounds like you're staying inside it. :)

jamescol
08-16-2004, 08:04 PM
Zack,
Once the form is published, you can use it much like any other Outlook form. So from within Excel, you would construct a procedure like:


Dim objTasks As Items
Dim objTask As TaskItem 'Dim the appropriate item you are using

'Set the location to save the custom form items
Set objTasks = objOL.Session.GetDefaultFolder(olFolderTasks).Items

' Change the message class as appropriate to the target custom form.
Set objTask = objTasks.Add("IPM.Task.myTaskFormName")

'Set the values of your combo box here
objTask.GetInspector.ModifiedFormPages("myPage").("myField").AddItem "Item 1"

'Display the form
objTask.Display


Set objTasks = Nothing
Set objTask = Nothing


Of course, you first will need to create an instance of the app, namespace, etc. and qualify them with Outlook.<object> to differentiate them from Excel objects.

Now, you may end up with a user opening an item in Outlook directly and having the information displayed on the wrong form. Check out the following two-part paper on working with custom forms. This paper will help you understand what's happening under the hood and prevent common problems.

Part 1
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnout2k2/html/odc_olcustfrm1.asp?frame=true

Part 2
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnout2k2/html/odc_olcustfrm2.asp?frame=true

Cheers,
James

Zack Barresse
08-16-2004, 10:57 PM
Well, at this point I'm debating whether or not I want a custom form or not. The one I was thinking of seems to be a default form in Outlook anyway. I still want to call from Excel.

There is a slight possibility that this could go to other locations which may not have an exchange server. They would all have a network though, at least those wishing to use this on multiple machines. If this is the case, I (think) I'd want to publish the form to a Folder - or would I even need to publish the form if it's already in Outlook as a default?

jamescol
08-17-2004, 08:33 AM
Publishing the form to a folder would work - you just need to work through the permissions for the users from other locations.

If you publish the form to the Organizational library, those other uses wouldn't have access to it. There is an option to save the form along with the data. This helps users who don't have the form, but increases the size of the message quite a bit.

Alternately, you can send the remote users a copy of the OFT along with instructions to publish it to their Personal Forms library. Of course, then you have a maintenance issue when you make updates to the form.

James

Zack Barresse
08-17-2004, 08:46 AM
Okay, so I think I just want to use a default Outlook Appointment form. Would this be easily accessed via Excel from their own Library? I think this would just simplify things a bit.

The ComboBox1 from Excel should go into the Location field, is that field still called ComboBox1 in Outlook?

jamescol
08-17-2004, 09:44 AM
Couldn't explain it better - so here is the text rom OL Help about using OL Form fields:

When you need to access the fields in an item, the method you use depends on whether the field is a standard, built-in Microsoft Outlook field, or a custom field.

In either case, you do not access the field directly. Instead, you refer to the field as a property of the item you? re working with.

For example, to retrieve the text from the Subject field of a mail message, you use the Subject property of the item, as shown in the following VBScript example.

mySubject = Item.Subject

If the field is a custom (user-defined) field, you access it using the UserProperties property of the item, as shown in the following VBScript example. This example assumes that the item already contains a custom field named ReferredBy.

MyReferral = Item.UserProperties("ReferredBy

So in your case, you use Item.Location, which is the name of the Location field. Set your form to the default olAppointmentItem.

You can set the value of Location, but unfortunately, there is no way (that I am aware) to add items to the combobox of the default AppointmentItem form :( That's one reason for creating a custom form.


Private Sub Command1_Click()
Dim myOLApp As New Outlook.Application
Dim myOLItem As Outlook.AppointmentItem
Set myOLItem = myOLApp.CreateItem(olAppointmentItem)
With myOLItem
.Location = "MyLocation"
End With
myOLItem.Display
End Sub


James

Zack Barresse
08-17-2004, 11:38 AM
I error out on that, it says 'Compile error: User-defined type not defined'. If I set the two variables as (Outlook) objects it brings up a standard email message form. :dunno

jamescol
08-17-2004, 08:34 PM
Do you have a reference to the Outlook object model (Tools | References | Outlook X Object Model)? :)

Also, Sue Mosher at Outlookcode.com verified that you cannot populate the drop down list of the default Appointment form. Apparently OL stores these values as the user enters them as a binary object in the registry.

If you are planning for the user to create the maintenance form from Excel, then maybe you don't need to populate the combobox with a list of values. Is it feasible for you to simply set the Location via Excel to a specific item each time the user creates a maintenance form?

Cheers,
James

Zack Barresse
08-17-2004, 10:32 PM
I don't think I had referenced the Object model. I was kind of hoping to use late binding for this, especially for the type of application and user's it will be going to.

I have currently set the location via Excel. I just want to take that value from Excel and populate the location value in the outlook form - whichever one is used. I've got it on a custom userform right now.

jamescol
08-17-2004, 11:07 PM
Zack,
I'm curious - why the desire to use late binding?

If you use early binding, then the sample will work OK with the default Appt form, and you can set the location from an excel value.

James

Zack Barresse
08-17-2004, 11:24 PM
This could possibly be going to about three dozen different municipalities. I have absolutely no desire to set all of these references to almost computer illiterate user's. I don't really have the time to do that (would be nice if I did!). :)

So my thinking was, use late binding, then I won't have to worry about doing any kind of tech-service of any sort. ;)

Zack Barresse
08-19-2004, 08:38 AM
... Is it feasible for you to simply set the Location via Excel to a specific item each time the user creates a maintenance form?

Ok, so this is basically what I want to do. Would that be easiest? What I've got so far..

An Excel userform w/ a ComboBox1 with a 'Location' value in it.
An OFT file where that ComboBox1 is also a 'Location'.
Both files are in the same path (always).

If the OFT doesn't have to be published, that would be better.

Zack Barresse
08-19-2004, 09:18 AM
Oooo! Oooo! I got it to work! :) Using Suat's method, it works pretty good!

Ok, so I can live with this. The one thing I'd still like to know is, would it just be easier to walk people through publishing the form to their library, or not publish it and just access it from the directory of the Excel add-in?

Btw everyone, thank you so very much for all your help with this one. It's been a long and crazy journey for me, and this whole project is rounding completion. So I'm very ecstatic that this even works! :D Thanks!!!

jamescol
08-19-2004, 09:24 AM
Zack,
Yep - you can do that. Just change the "MyLocation" to your Excel value.
Since you don't need a custom form there is no need to publish anything. You're just using the built-in Appt form.

James


Private Sub Command1_Click()
Dim myOLApp As New Outlook.Application
Dim myOLItem As Outlook.AppointmentItem
Set myOLItem = myOLApp.CreateItem(olAppointmentItem)

With myOLItem
.Location = "MyLocation"
End With
myOLItem.Display
End Sub

Zack Barresse
08-19-2004, 09:49 AM
Very cool James!

Ok, so for the $64,000 question: Which way do you think would be the least troublesome?

1) Use the custom form.

2) Use the default form.

Issues (I see):
1) Form will need to be published and customized. Could run into problems in naming the published form (as it's called from the code) by the user(s). Could be published in the wrong location.

2) Reference will need to be made to the Outlook Library.

On another related thought, could you make it so that when the Add-in is created, automatically enable the reference? If that is possible (I'm sure it is) then that would seem like the least painful way to go. Then it's just a matter of installing the add-in (which they'll have to do anyway).

Whatcha think?

jamescol
08-19-2004, 10:09 AM
Use the built-in default form. It provides all the functioanlity you need without the maintenance issues.

If you are going to use an Add-in, my understanding is that you will not need to set the reference on the client computer. The Add-in will get what it needs when you compile it.

Cheers,
James

Zack Barresse
08-19-2004, 10:22 AM
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. :dunno

jamescol
08-19-2004, 10:26 AM
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
08-19-2004, 10:34 AM
Just did that, works like a breeze! James you're awesome!!!

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

jamescol
08-19-2004, 10:51 AM
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
08-19-2004, 10:53 AM
Preferrably it would be user-set. It would be something like Monthly, Quarterly, Semi-Annually, Annually. Something along those lines.

jamescol
08-19-2004, 11:02 AM
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
08-19-2004, 11:09 AM
So I wouldn't be able to call the form, with the code you provided, and then have them set the occurance?

Zack Barresse
08-19-2004, 11:27 AM
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
08-19-2004, 11:42 AM
Here ya go:

Sub showcb()
Dim cbar As CommandBar
Dim cbarName As String

cbarName = "Standard"

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

End Sub

Zack Barresse
08-19-2004, 12:12 PM
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? ...



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



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

jamescol
08-19-2004, 12:41 PM
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


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

Zack Barresse
08-19-2004, 02:07 PM
Very cool James! You are a god!!! :D

I just had to change this line ..


Set olItem = myOLApp.CreateItem(olAppointmentItem)

to ...



Set olItem = olApp.CreateItem(olAppointmentItem)


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
08-19-2004, 03:34 PM
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!