Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    2 Star Lounger
    Join Date
    May 2003
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ActiveX Controls for Outlook (OfficeXP)

    Hello.

    I was wondering if there are any ActiveX Controls that can be used to link VBA with Outlook Contacts. I would like to create a form that allows someone to select the Contacts in an address book.

  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
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,506
    Thanks
    3
    Thanked 141 Times in 134 Posts

    Re: ActiveX Controls for Outlook (OfficeXP)

    Are you sure you need an ActiveX solution? This code which originally came from Woody years ago shows a method of getting the contacts list displayed. You can probably hack this to do what you require. The GetAddress command is the one that does all the heavy lifting.
    <pre>Sub InsertAddressFromOutlook()
    Dim strCode, strAddress As String
    Dim iDoubleCR As Integer

    'Set up the formatting codes in strCode
    strCode = "<PR_DISPLAY_NAME>" & vbCr
    strCode = strCode & "<PR_COMPANY_NAME>" & vbCr
    strCode = strCode & "<PR_STREET_ADDRESS>" & vbCr
    strCode = strCode & "<PR_LOCALITY>, <PR_STATE_OR_PROVINCE> <PR_POSTAL_CODE>" & vbCr
    strCode = strCode & "<PR_COUNTRY>" & vbCr

    'Let the user choose the name in Outlook
    strAddress = Application.GetAddress("", strCode, False, 1, , , True, True)
    'Strip away the final "Australia", if any
    If Right(strAddress, 10) = "Australia" & vbCr Then
    strAddress = Left(strAddress, Len(strAddress) - 10)
    End If

    'Eliminate blank lines by looking for two carriage returns in a row
    iDoubleCR = InStr(strAddress, vbCr & vbCr)
    While iDoubleCR <> 0
    strAddress = Left(strAddress, iDoubleCR - 1) & Mid(strAddress, iDoubleCR + 1)
    iDoubleCR = InStr(strAddress, vbCr & vbCr)
    Wend

    'Insert the modified address at the current insertion point
    Selection.TypeText strAddress
    End Sub</pre>

    Andrew Lockton, Chrysalis Design, Melbourne Australia

  4. #3
    2 Star Lounger
    Join Date
    May 2003
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ActiveX Controls for Outlook (OfficeXP)

    This is very helpful. However, can the GetAddress method be used for multiple recipients, or just for selecting one person at a time?

  5. #4
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,506
    Thanks
    3
    Thanked 141 Times in 134 Posts

    Re: ActiveX Controls for Outlook (OfficeXP)

    The GetAddress command has a SelectDialog argument which can allow this.

    If you explore the GetAddress help page you may find that modifying the key line of the code to
    strAddress = Application.GetAddress("", strCode, False, 1, 1, , True, True)
    allows you to select multiple recipients via a slightly different dialog.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  6. #5
    2 Star Lounger
    Join Date
    May 2003
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ActiveX Controls for Outlook (OfficeXP)

    This has been most helpful. I have 2 more questions,

    a) if someone selects a distribution list from this dialog box, is there a (programmatic) way to list all the members of that group?
    [img]/forums/images/smilies/cool.gif[/img] how can you select names from a public or personal Contact folder? This dialog box only allows you to select from the Global Address List. (Perhaps I should post this question in the Outlook folder).

    Thanks!

  7. #6
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,506
    Thanks
    3
    Thanked 141 Times in 134 Posts

    Re: ActiveX Controls for Outlook (OfficeXP)

    I don't know about the first question but I suspect you can get at it because you can view the properties easily enough to see the members of the group. You may need to create a link to Outlook first though so this would require further code.

    On the address book question, I can select which address book to look in as shown in the screen capture. Do you not get this too?
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  8. #7
    2 Star Lounger
    Join Date
    May 2003
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ActiveX Controls for Outlook (OfficeXP)

    Andrew

    Thanks for that. Someone on the Outlook board directed me to Knowledge Base Article 287563 which I need to set up to be able to view Outlook Address Books. So I can now see the Address books.

    I am still interested to find out how to access the members of a distribution list, as I don't really understand the Outlook model. If you know anything, that would be great.
    Marie-Therese

  9. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: ActiveX Controls for Outlook (OfficeXP)

    There is a reply by JohnBF in the thread starting at <post#=323190>post 323190</post#> in the Outlook forum that may be helpful - look for the reply with VBA code.

  10. #9
    Super Moderator
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,507
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Re: ActiveX Controls for Outlook (OfficeXP)

    Marie-Therese, reading through the thread I'm not clear on whether you are looking for street addresses or e-mail addresses. I don't think Address Books include street addresses. If you want street addresses, you'll need to work from the Contacts folders. As you process Contacts or AddressList Items, check the Class of each item to determine how to handle it (air code):

    If thisItem.Class = olDistributionList Then ...
    ' loop thought DistList members by count as in the code Hans referred to
    Else If thisItem.Class = ' olContact if Contacts, or olAddressEntry if an AddressList Then ...
    strAddress = 'thisItem.BusinessAddress if Contact or thisItem.Address if AddressList, or other applicable Property
    -John ... I float in liquid gardens
    [acronym title="Gday mate!"][/acronym] [acronym title="What up Dude?"][/acronym] UTC -7±DS

  11. #10
    2 Star Lounger
    Join Date
    May 2003
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ActiveX Controls for Outlook (OfficeXP)

    Andrew, I am having some trouble extracting the addresses from the strAddress string. Do you know what symbol or character delimits each address? I tried the CR and it almost works. But each entry has a ", " preceding it. Also, is there a way to determine which addresses are CC, and which are TO?

    Thanks!

  12. #11
    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: ActiveX Controls for Outlook (OfficeXP)

    Can you post the snippet of code you're using to get the string? Then we can test it against our own address books and maybe give you an answer. (Or you could save the string to a file and post that, but I'm not sure it will remains unchanged in that process.)

  13. #12
    2 Star Lounger
    Join Date
    May 2003
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ActiveX Controls for Outlook (OfficeXP)

    The code calls the GetAddress dialog with dialog of 2, which allows the user to enter multiple addresses

    sAddress = Application.GetAddress _
    (addressproperties:=cTags, _
    useautotext:=False, _
    displayselectdialog:=1, selectdialog:=2)

    The result looks like this
    Bear;Pooh;;;;;;;;;;;/;
    , Piglet;;;;;;;;;;;;;
    , Eeoor;;;;;;;;;;;

    Each address is separated with a CR and a ", ". Unfortunately, it limits the result to 255 characters, which is not very helpful. Also, there seems to be no way to determine if it is returning a CC or a TO address.

  14. #13
    Super Moderator
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,507
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Re: ActiveX Controls for Outlook (OfficeXP)

    Marie-Therese, I don't have a problem with valid addresses, I get a return like this mix of addresses and unaddressed contracts:

    Michael Beatnik
    77 Drink Beer Drive,
    Nogales, CA 99999

    , Mary Jones
    ,
    , Ivy Walls
    ,

    Are you running your code in a country other then the US, where addressing conventions may be more flexible?

    (I hope Michael is adequately censored. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)
    -John ... I float in liquid gardens
    [acronym title="Gday mate!"][/acronym] [acronym title="What up Dude?"][/acronym] UTC -7±DS

  15. #14
    2 Star Lounger
    Join Date
    May 2003
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ActiveX Controls for Outlook (OfficeXP)

    My address formatting may be different to yours. However, I think you have answered my question. The next address always begins with a CR ", ".

    There appears to be no way to distinguish between CC and TO addresses though, or is there? and your result is probably limited to 255 characters also, or is it?

    Thanks

  16. #15
    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: ActiveX Controls for Outlook (OfficeXP)

    What is the format of cTags in your example? I tried this for a little test against my PAB:

    <pre>cTags = "<PR_DISPLAY_NAME>" & vbCr & "<PR_PRIMARY_TELEPHONE_NUMBER>" & vbCr & _
    "<PR_EMAIL_ADDRESS>" & Chr(254)</pre>

    This gave me pickthorn+comma+space between records within the To box, and pickthorn+comma+space between records within the CC box, but in between the two sets, there is no comma. Instead, I get pickthorn+vbCr. So to parse this, you could:

    <pre>Dim strAll() As String, strTo() As String, strCC() As String
    strAll = Split(sAddress, Chr(254) & vbCr)
    strTo = Split(strAll(0), Chr(254) & ", ")
    If UBound(strAll) > 0 Then
    strCC = Split(strAll(1), Chr(254) & ", ")
    End If</pre>

    I can't provide any assurances that this is the most efficient approach, but it does seem to work in my little test. Hope this helps.

Page 1 of 2 12 LastLast

Posting Permissions

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