Update custom outlook form from within Excel

[Guest]

Hi,

A bit of a long shot but I am hoping that someone can help with creating a macro that will take cell values and write them to the fields of a custom outlook form.

In VBScript, for example, I can set the values of any field on a custom outlook form using -
Item.UserProperties("MyField")=Value

What I need to be able to do is access the user-form from within Excel and then programatically update the required fields. Once done, it would just be a matter of sending the mail.

I hope I am not expecting a little too much of the current abilities of Office automation? I'm pretty new to scripting. If someone has a a little time to at least point me in the right direction, I would be most grateful.

Thanks in advance,

Mike

Platform - WinNT4 SP6(I think), Excel97, Outlook 2000 SP1

 

[Dick Kusleika]

Mike

I don't know much about forms in Outlook, but I created a form based on the
standard Message form and I was able to access the normal MailItem
properties.

Sub CustomForm()

Dim ol As Outlook.Application
Dim itm As Object

Set ol = New Outlook.Application
Set itm = ol.CreateItemFromTemplate _
("C:\Windows\Application Data\Microsoft\Templates\OtherMail.oft")

With itm
.To = "(e-mail address removed)"
.Subject = "Test"
.Display
End With

Set itm = Nothing
Set ol = Nothing

End Sub

If that's not even close to what you're doing, let me know how I can get a
similar situation on my machine and I'll see what I can find out.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

Hi,

A bit of a long shot but I am hoping that someone can help with creating a

macro that will take cell values and write them to the fields of a custom
outlook form.

In VBScript, for example, I can set the values of any field on a custom outlook form using -
Item.UserProperties("MyField")=Value

What I need to be able to do is access the user-form from within Excel and

then programatically update the required fields. Once done, it would just be
a matter of sending the mail.

I hope I am not expecting a little too much of the current abilities of

Office automation? I'm pretty new to scripting. If someone has a a little
time to at least point me in the right direction, I would be most grateful.

 

[Guest]

Dick,

The Script worked perfectly. With the addition of: -

itm(from "With" statement).UserProperties("My Custom FIeld") ="My Value"

I have achieved exactly what I was after.

This is a lot more useful way of sending information in a standardised form,
which we use a lot of in my organisation, as it saves duplicating the work.

Thanks for your help.

Mike