Excel script to populate form fields

Hi,

I am trying to create an Excel script that will launch an e-mail and populate the fields with the values of specific cells. My knowledge of the Outlook object model, let alone my rudimentary knowledge of VBA is letting me down.

So far, I have the following to launch the e-mail but now I'm a little stuck: -
-----------------------------------------------------------------------
Sub CommandButton1_Click()

Dim ol As Outlook.Application
Dim itm As Object

Set ol = New Outlook.Application
Set itm = ol.CreateItemFromTemplate _
("C:\Documents and Settings\Mike\Desktop\Test.oft")

With itm
.To = "mike_pe@hotmail.com"
.Subject = "Test"
.Display
End With

Set itm = Nothing
Set ol = Nothing

End Sub
-----------------------------------------------------------------------
I need to be able to reference the UserProperties of the form Items and specify the values of those fields.

Can anyone shed a little light, please? Your help, as always, would be most appreciated.

Mike.

itm.UserProperties("My Custom FIeld") -- see
http://www.outlookcode.com/d/propsyntax.htm for more information on Outlook
property syntax.
--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
http://www.outlookcode.com/jumpstart.aspx


"UKNewbie" <UKNewbie@discussions.microsoft.com> wrote in message
news:A5F2E5CB-C4EC-415C-AAF0-A396AE15BC57@microsoft.com...
> Hi,
>
> I am trying to create an Excel script that will launch an e-mail and
populate the fields with the values of specific cells. My knowledge of the
Outlook object model, let alone my rudimentary knowledge of VBA is letting
me down.
>
> So far, I have the following to launch the e-mail but now I'm a little
stuck: -
> -----------------------------------------------------------------------
> Sub CommandButton1_Click()
>
> Dim ol As Outlook.Application
> Dim itm As Object
>
> Set ol = New Outlook.Application
> Set itm = ol.CreateItemFromTemplate _
> ("C:\Documents and Settings\Mike\Desktop\Test.oft")
>
> With itm
> .To = "mike_pe@hotmail.com"
> .Subject = "Test"
> .Display
> End With
>
> Set itm = Nothing
> Set ol = Nothing
>
> End Sub
> -----------------------------------------------------------------------
> I need to be able to reference the UserProperties of the form Items and
specify the values of those fields.
>
> Can anyone shed a little light, please? Your help, as always, would be
most appreciated.
>
> Mike.

Sue,

Yet again, you've saved my bacon. I would buy you a drink if I could.

I don't very often take advantage of "with" statements and, along with your info below, I'm now able to populate an update e-mail with exactly the data required for distribution, entirely from within an excel spreadsheet (which gathers info from 3rd party software).

Many thanks as ever,

Mike.

"Sue Mosher [MVP-Outlook]" wrote:

> itm.UserProperties("My Custom FIeld") -- see
> http://www.outlookcode.com/d/propsyntax.htm for more information on Outlook
> property syntax.
> --
> Sue Mosher, Outlook MVP
> Author of
> Microsoft Outlook Programming - Jumpstart for
> Administrators, Power Users, and Developers
> http://www.outlookcode.com/jumpstart.aspx
>
>
> "UKNewbie" <UKNewbie@discussions.microsoft.com> wrote in message
> news:A5F2E5CB-C4EC-415C-AAF0-A396AE15BC57@microsoft.com...
> > Hi,
> >
> > I am trying to create an Excel script that will launch an e-mail and
> populate the fields with the values of specific cells. My knowledge of the
> Outlook object model, let alone my rudimentary knowledge of VBA is letting
> me down.
> >
> > So far, I have the following to launch the e-mail but now I'm a little
> stuck: -
> > -----------------------------------------------------------------------
> > Sub CommandButton1_Click()
> >
> > Dim ol As Outlook.Application
> > Dim itm As Object
> >
> > Set ol = New Outlook.Application
> > Set itm = ol.CreateItemFromTemplate _
> > ("C:\Documents and Settings\Mike\Desktop\Test.oft")
> >
> > With itm
> > .To = "mike_pe@hotmail.com"
> > .Subject = "Test"
> > .Display
> > End With
> >
> > Set itm = Nothing
> > Set ol = Nothing
> >
> > End Sub
> > -----------------------------------------------------------------------
> > I need to be able to reference the UserProperties of the form Items and
> specify the values of those fields.
> >
> > Can anyone shed a little light, please? Your help, as always, would be
> most appreciated.
> >
> > Mike.
>
>
>