Solved: Excel info to Outlook Form

[Zack Barresse]

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]

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]

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...45&Product=out

http://support.microsoft.com/default...02&Product=out

http://support.microsoft.com/default...06&Product=out

James

[Zack Barresse]

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]

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]

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:

[vba]
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
[/vba]

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/de...asp?frame=true

Part 2
http://msdn.microsoft.com/library/de...asp?frame=true

Cheers,
James

[Zack Barresse]

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]

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]

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]

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.

[vba]
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
[/vba]

James

[Zack Barresse]

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.

[jamescol]

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]

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]

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]

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]

... 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]

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

[jamescol]

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

[vba]
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
[/vba]

[Zack Barresse]

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]

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