Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    May 2003
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Writing values to custom Outlook form (Excel 97/Outlook 2000)

    Hi All,

    I am trying to populate a custom Outlook form from an Excel spreadsheet. Here's an extract of my spreadsheet:

    Recipient Ticket No Date Raised Time Raised
    Bob Smith 12345 12/11/03 04:01
    Pete Green 23122 13/11/03 15:45

    and so on.
    I am using the following code to populate the fields of the form:

    Option Explicit

    Sub KENotification()

    Dim olApp As Outlook.Application
    Dim olMailItem As Outlook.MailItem

    Set olApp = New Outlook.Application

    Set olMailItem = olApp.Createitemfromtemplate("C:custom form.oft")

    With olMailItem
    .Recipients.Add ActiveSheet.Range("A2").Value
    ????(ticket No)????? = ActiveSheet.Range("B2").Value
    ????(date raised)????? = ActiveSheet.Range("C2").Value

    .Save
    .display

    End With


    Set olMailItem = Nothing
    Set olApp = Nothing

    End Sub

    As you can see where I've put the ?????????'s, I have no idea how to reference my custom fields on the Outlook form. If I pull up the properties for each one, they are called things like Label2 but Excel refuses to recognise these.

    Can anyone help me?

    Thanks,

    Cunners

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Writing values to custom Outlook form (Excel 97/Outlook 2000)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Cunners

    OK what are the Ticket No, Date Raised, and Time Raised represent?

    I would suggest you go into Outlook VBA and check the object model of the Mail item.

    I know Recipient is one of them, but I could not find any equivalent to the others. Could they be .ReceivedTime?

    Outlook BTW does a poor job in exposing all the neat things about its elements.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    New Lounger
    Join Date
    May 2003
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing values to custom Outlook form (Excel 97/Outlook 2000)

    Hi Wassim,

    I've created a custom form in Outlook, so ticket number is a user defined field of several numbers, date raised is a date, time raised is a time field etc etc.
    Looking through the form, I think I've found the field names - things like date, desc, error, tickets, time1 and so on.
    But if I put in my code: .desc=Activesheet.range("A2").value , Excel doesn't seem to know what '.desc' is. Do I need to somehow declare the Outlook custom field names to Excel first?
    I also wondered whether I'm referencing the correct object. I'm defining olMailItem as Outlook.mailitem, when I'm actually trying to send data to a form. Does this make a difference? Or should I just forget the whole thing?!?

    Cheers,

    Cunners

  4. #4
    New Lounger
    Join Date
    May 2003
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Writing values to custom Outlook form (Excel 97/Outlook 2000)

    Hi again,

    I'm replying to myself now, but I thought I'd let you know I figured out a solution by myself.

    I used the 'UserProperties' function in VBA to reference my custom fields. So with the code above, where my custom field in my Outlook form is called 'RemApp' the ???????'s should read:

    .UserProperties("RemApp") = ActiveSheet.Range("D2").Value

    Works like a charm and has saved me about 2 hours a day!

    Have fun,

    Cunners

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •