Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Nottingham, UK, Nottinghamshire, England
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VB Script in Outlook (2000)

    Can anyone give me some VB Script to use on the Open event of an Outlook 2000 form which will open Excel, read a small spreadsheet and use the contents of column 1 to populate a combobox on the Outlook form? Thanks. I know VBA but am struggling in VB Script for this. Thanks again.

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: VB Script in Outlook (2000)

    VBScript is similar to VBA, but the variables are not strongly typed so everything is late bound, that is:

    dim xlApp as Object
    set xlApp = CreateObject("Excel.Application")

    and so forth. It would be easier to develop in the VBA environment using early binding (dim xlApp as Excel.Application) and then covert it to VBScript than to write VBScript from scratch. At least, it usually is for me. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Footnote: I don't know the object model for comboboxes on Outlook forms. Hopefully this can be found in the product's online help.

  4. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Nottingham, UK, Nottinghamshire, England
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Script in Outlook (2000)

    Oh - so something like this?

    Function Item_Open()
    dim intIndex as Integer
    dim MyApp as Object
    dim MyItem as Object
    Set MyItem = Item.Application
    Set MyApp = CreateObject("Excel.Application")
    MyApp.Workbooks.Open FileName:="S:MSCLIENTSEMSTRAMSAGDATATRAMS800Projec ts.xls"
    intindex = 1
    do while myapp.workbooks.sheets(1).cells(intindex,1) <> ""
    myitem.combobox1.additem myapp.workbooks.sheets(1).cells(intindex,1)
    intindex = intindex + 1
    loop
    End Function

    Thanks

  5. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,870
    Thanks
    0
    Thanked 79 Times in 75 Posts

    Re: VB Script in Outlook (2000)

    Keith,
    You will need to change
    <pre>do while myapp.workbooks.sheets(1).cells(intindex,1) <> ""</pre>

    to
    <pre>do while myapp.workbooks(1).sheets(1).cells(intindex,1) <> ""</pre>

    or dim another variable and set that equal to the workbook when you open it.
    Hope that helps.
    Regards,
    Rory
    Microsoft MVP - Excel.

  6. #5
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: VB Script in Outlook (2000)

    I think you're already way ahead of me. If you comment out the "as Integer" and "as Object " on the Dim statements, does it run?

  7. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Nottingham, UK, Nottinghamshire, England
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Script in Outlook (2000)

    Thanks Rory, though at the moment, having removed the 'As Integer' and 'As Object' from the Dim lines (they were objected to), I'm getting script errors on line 7, the Workbooks.Open statement - any ideas why? Thanks

  8. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Nottingham, UK, Nottinghamshire, England
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Script in Outlook (2000)

    Well, it certainly gets past the Dim statements without the very cryptic error messages it got before ("Expected Statement. Line number x"), but now it's getting an error message (same one) on the Workbooks.Open statement - any ideas why? Thanks.

  9. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,870
    Thanks
    0
    Thanked 79 Times in 75 Posts

    Re: VB Script in Outlook (2000)

    Keith,
    Off the top of my head, I'd guess it's objecting to the named parameter. Try just using workbooks.open "filenamehere" rather than workbooks.open filename:="filenamehere"
    Hope that helps.
    Regards,
    Rory
    Microsoft MVP - Excel.

  10. #9
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Nottingham, UK, Nottinghamshire, England
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Script in Outlook (2000)

    Sorry for late reply, Rory, but yes that worked (removing the "FileName:="). Now I'm battling with getting the AddItem statement to work! Objects to it with and without the MyItem. prefix. But if it's not an AddItem method to populate the ComboBox, what is it? Thanks for your help.

  11. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,870
    Thanks
    0
    Thanked 79 Times in 75 Posts

    Re: VB Script in Outlook (2000)

    Keith,
    I think it's because you've assigned the Application object to MyItem. Try using something like:
    <pre>Set MyItem = Item.Getinspector.modifiedformpages("tabnamehere - e.g. P.2")
    MyItem.controls("Listboxnamehere").additem "whatever"
    </pre>

    Hope that helps.
    Regards,
    Rory
    Microsoft MVP - Excel.

  12. #11
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Nottingham, UK, Nottinghamshire, England
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Script in Outlook (2000)

    Rory,

    You're on the button! I'd just sussed that out myself (found a script example on the net), so now I'm coding:
    Set CB = Item.GetInspector.ModifiedFormPages("Message").Com boBox7
    CB.AddItem myapp.workbooks(1).sheets(1).cells(intindex,1)
    and this works fine!
    So now I'm a happy bunny, and I'll be using this technique to populate comboboxes on a number of forms where the data changes occasionally, such as current project name, current releases etc. Beats the hell out of having to change the controls in each and every form!
    Thanks for all your help, Rory - once again, couldn't have done it without the Lounge! Thanks.

Posting Permissions

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