Results 1 to 15 of 15
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Chicago, Illinois, USA
    Posts
    185
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Outlook VBA to VB COM Add-In (VBA/VB6, Office2Ksr1)

    GRRR. I have practically-perfectly-working code in a form and module in the vbaproject.otm file. I need to distribute this functionality as a COM Add-in. The problem I'm hitting at the moment (likely not the only one out there) is the properties of a listbox control on the form.

    In VBA, the listbox control has, among other items, a column property. When I fill the listbox, I can have two "columns" for each row, where each row represents a record. I add items to the listbox from my module using this code:

    If oContact.Email1Address <> "" Then
    frmSelect.lstContacts.AddItem oContact.FileAs
    newrownum = frmSelect.lstContacts.ListCount - 1
    frmSelect.lstContacts.List(newrownum, 1) = oContact.Email1Address
    End If

    and then return the selected data using this code on the form:

    For i = 0 To lstContacts.ListCount - 1
    If lstContacts.Selected(i) = True Then
    mdlAttyContacts.strDLTo = lstContacts.List(i, 0) 'HERE
    End If
    Next

    However, when I attempt to compile the code in Visual Basic, I'm getting an error on the line marked "HERE" above (specifically on the .list area)...the error reads "Compile error: Wrong number of arguments or invalid property assignment."

    I notice that in the properties for the listbox on the form in VB, the "columns" property is completely different, and controls how items in the list scroll, rather than the columns for each row of the list. Is there a comparable property or way to do this? Or, more importantly, is there a more straight-forward way to migrate code from the vbaproject.otm file into a VB project? I'm ready to pull my hair out...really...and I don't think bald will be a good look for me...

    Thanks in advance...

    Jen

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Outlook VBA to VB COM Add-In (VBA/VB6, Office2Ksr1)

    Hi Jen. You're doing just what I'll be doing next week: <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>
    My expert says that hairout is the only way, in fact, it is almost easier to create a COM add in from scratch as migrating your VBA stuff to VB. Not what either of us wanted to hear.

    Re: listboxes. As you noticed VB's listbox and MSForms listbox are two very different creatures. In VB, the list property is always a one-dimensional, zero-based array. Look at the code example in the help file to see how it works.

    However, I don't think that there is anything to stop you from using the MSForms controls in your VB project. In other words, right-click on the VB toolbox, select components, and add Microsoft Forms 2.0. Now you toolbox has two different choices for listboxes: choose the bottom one and you'll have the same kind that you had in VBA. Copy the properties from VBA to VB and your code will hopefully work.

    One warning, I'm speaking totally unencombered by knowledge. Please let me know if it really works, as I said, I'll be going through the same process next week. Just brainstorming, if this works, then it might be best to not use any VB controls: just use the same VBA (ie Forms 2.0) controls that are on your UserForm. BTW, in case you had the same thought that I had, you cannot copy controls from the UserForm and paste them into your VB form. Guess we have to write Bill a bigger check! Have fun and keep me posted. Thanks! --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Chicago, Illinois, USA
    Posts
    185
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook VBA to VB COM Add-In (VBA/VB6, Office2Ksr1)

    Thanks Sam...

    Well, starting over in VB is a bigger problem than it might be for some other people, as I don't know VB at all.

    That said, adding the Microsoft Forms 2.0 appears to work fine, because I get the properties I was looking for, which is good. The problem is that in the course of doing something else I somehow messed up the entire thing and am having to start over yet again, and I don't know how to do that. I can't even figure where to put the code to begin with! Argh. I really want to do this myself, but nothing makes sense, and I downloaded an Outlook COM Add-In template from microeye and quite frankly I'm just feeling more confused now than I was before. It doesn't even have all of the IDTExtensibility2 events that are supposedly required, and I don't know whether to add them in the class, the designer, or the module. From there I'm again confused as to where to add my code which at this point is about 20 lines, simply creating a commandbar in the Inspector item and adding 4 buttons. Figuring out where to put the code that goes *behind* the buttons is a bridge I haven't even crossed (specifically, I'm trying to figure out if its a better practice to have separate add-ins for each bit of functionality we're adding, plus an add-in for the toolbar, or if all of our in-house customization should go into one...

    I tell you, I no longer believe that VB is the "parent" of VBA. If it were, one wouldn't be so incorrect in assuming that VB could read VBA. GRRR.

    *taking deep breaths now to calm myself down*

    There has got to be a way to do this. Maybe I'll start over again, again.

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Outlook VBA to VB COM Add-In (VBA/VB6, Office2Ksr1)

    For an overview of adding the toolbar, work through the last two chapters of David Broctor's Microsoft Office
    2000 VBA Fundamentals
    . Then try to do the same thing with Outlook. For more information, get Reed
    Jacobson's Microsoft Excel 2002 Visual Basic for Applications Step by Step, John Walkenbach's Excel 2002 Power Programming with VBA, and Excel 2002 VBA Programmers Reference by John Green, Stephen Bullen, Rob
    Bovey and Robert Rosenberg. I've not used any of these books: not even sure if Reed's book is useful, so if possible look at them in a bookstore before purchasing them. Here's John's table of contents and Q230225 is Microsoft's instructions for creating a COM add in. Have fun & tell yourself "if the job was easy, everyone would be doing it." --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook VBA to VB COM Add-In (VBA/VB6, Office2Ksr1)

    Using a VBA Userform in VB, tho technically feasible, is not officially supported.
    You cannot distribute such a VB app to someone who is not licensed to use MSForms.

  6. #6
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook VBA to VB COM Add-In (VBA/VB6, Office2Ksr1)

    And, more appropriate for Outlook, Sue Mosher's Microsoft Outlook Programming book (ISBN: 1555582869) has recently been published. That book, in combination with Randy Byrne's book from MSFT Press should give you most of what you need.

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Outlook VBA to VB COM Add-In (VBA/VB6, Office2Ksr1)

    Just a couple of quick comments on Howard's posts:
    1) We're not using a Userform in VB, (don't think that can be done) we're just using the MSForms controls. Also, there won't be any licensing problems since we are developing add ins for MS Office which comes with MSForms.

    2) You definitely need to have Sue Mosher's book: she is the expert. I don't have the book but, looking at the table of contents I don't see any chapters on Com Add Ins. Howard, If you have the book, can you look in the index to see if there is anything on COM Add Ins? Also Howard, if you have any of the books that I mentioned, can you see how much they have on COM Add Ins? Thanks --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  8. #8
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Chicago, Illinois, USA
    Posts
    185
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook VBA to VB COM Add-In (VBA/VB6, Office2Ksr1)

    Thanks Sammy, thanks Howard. I guess my specific question is--I have the IDTExtensibility2 subroutines in my class module. My code for creating the toolbar is in the IDTExtensibility2_OnStartupComplete subroutine; here I create a toolbar and add four buttons to the toolbar (here's the code for that):

    '================================================= =========================
    Private Sub IDTExtensibility2_OnStartupComplete(custom() As Variant)

    Set objInspector = objApp.ActiveInspector
    Set colBars = objInspector.CommandBars
    Set objPBBar = colBars.Add("PB Command Bar", msoBarLeft, False, True)
    Set cbbGetContacts = objPBBar.Controls.Add(Type:=msoControlButton)
    Set cbbDocsCopy = objPBBar.Controls.Add(Type:=msoControlButton)
    Set cbbDocsLink = objPBBar.Controls.Add(Type:=msoControlButton)
    Set cbbSendFile = objPBBar.Controls.Add(Type:=msoControlButton)

    With cbbGetContacts
    .Style = msoButtonCaption
    .Caption = "Get Contacts"
    .ToolTipText = "Get Contacts from another folder"
    '.OnAction=point to the GetContacts code
    .Enabled = True
    .Visible = True
    End With

    With cbbDocsCopy
    .Style = msoButtonCaption
    .Caption = "DOCS Copy"
    .ToolTipText = "Send copy of docs file"
    '.OnAction=point to the DocsCopy code
    .Enabled = True
    .Visible = True
    End With

    With cbbDocsLink
    .Style = msoButtonCaption
    .Caption = "DOCS Link"
    .ToolTipText = "Send link to Docs file"
    '.OnAction=point to the Docs link code
    .Enabled = True
    .Visible = True
    End With

    With cbbSendFile
    .Style = msoButtonCaption
    .Caption = "Send&File"
    .ToolTipText = "Send the message and file it in an Outlook folder"
    '.OnAction=point to the send and file code
    .Enabled = True
    .Visible = True
    End With

    With objPBBar
    .Enabled = True
    .Visible = True
    End With
    End Sub
    '================================================= =========================

    My question, then, is regarding the "on action" commands for the buttons--where do I put the code for each of those? In another COM?

    And, one more question...would it make sense to add an IF statement for each With/End With that says, for example, if COMSendFile exists, this button calls it, but if COMSendFile does NOT exist, this button returns a msgbox stating that the COM isn't loaded?

    These may be very basic questions, but I'm just learning this, so any help is VERY appreciated. Thanks in advance...

    Jennifer

  9. #9
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Chicago, Illinois, USA
    Posts
    185
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook VBA to VB COM Add-In (VBA/VB6, Office2Ksr1)

    And, one more comment regarding books: I'm using Thomas Rizzo's Programming MS Outlook and Exchange, 2d. ed. ; Jeffrey Kent and David Jung's MS Outlook 2000 Programming Bible; Randy Byrne's Building Applications with Outlook 2000; and Sue Mosher's SAMS book on Outlook programming. The Mosher book was helpful in the VBA phase of various projects, but this move into VB and COM Add-ins is leaving me stumped. The others have all be helpful with various pieces, but this key piece--where does the code go??--seems to be something so basic that they all assume I should know it already.

    Like I said Friday, I am new to this, so it's been a struggle to say the least.

    As for the MSForms controls--I'm in the same situation as Sam, in that I'm building something that is only going to be used for Office. For that matter, it's only going to be used in Outlook.

    Thanks again for all of your comments...

    jen

  10. #10
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Chicago, Illinois, USA
    Posts
    185
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook VBA to VB COM Add-In (VBA/VB6, Office2Ksr1)

    And, in the spirit of continuously adding more to this thread, here's another query:

    Is there a "best practices" guide to Addins for Microsoft Outlook 2000, specifically relating to how many of them you should have? In this case, I have 4 types of functionality I want set up, plus a toolbar--should they all go into one addin, or should I have 5 separate addins?

    Any thoughts would, as usual, be gratefully appreciated...

  11. #11
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook VBA to VB COM Add-In (VBA/VB6, Office2Ksr1)

    Sue Mosher's book says little about COM Add-Ins, but then so do most other Office related books.
    Your best bet for Office COM info is the MSFT programmer's guide for the relevant version of Office.

  12. #12
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook VBA to VB COM Add-In (VBA/VB6, Office2Ksr1)

    OnAction is used to specify the macro to be run when the control is clicked.

  13. #13
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Outlook VBA to VB COM Add-In (VBA/VB6, Office2Ksr1)

    Jennifer, were you able to follow Microsoft's instructions for creating a COM add in, Q230225 I think that it answers some of your questions, especially in part II, Building an Outlook COM Add in.

    I would also think that some of this should be in Risso's book that you have. Dosn't it have a chapter on COM Add Ins? I can't comment on your Outlook code, because I've only done a few simple OLE Outlook programs and they were mind-boggling. I'm still waiting for a "real computer " (mine has Office 97) to start my Com project, but when I was playing around at home, it seemed to me that I needed to get a simple toolbar with a single button up and executing some simple app-related code and then based on that knowledge, redesign the existing Add In. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  14. #14
    Platinum Lounger
    Join Date
    Jan 2001
    Location
    Roanoke area, Virginia, USA
    Posts
    3,729
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook VBA to VB COM Add-In (VBA/VB6, Office2Ksr1)

    basically, you want to keep everything in one unit... so make one addin or you risk missing files and will drive the users nuts. unless you want to give users the option not to use one of the addins...

  15. #15
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Chicago, Illinois, USA
    Posts
    185
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Outlook VBA to VB COM Add-In (VBA/VB6, Office2Ksr1)

    No, that's perfect; thanks. None of this will do anything automatically; it is still up to the user to choose to click the button, so knowing that it is generally better to keep everything together is a good thing. Thanks again.

    jen

Posting Permissions

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