Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    link excel to outlook (excel2000)

    I have writtten a macro which goes into a folder within outlook and grabs contact information
    I want to pull all the contacts that from the list where the full name column is not blank.
    I have written this code

    If UCase(Trim(vUser2)) = "vFullNameCol" != null Then
    vro = vro + 1

    and it does not work

  2. #2
    2 Star Lounger
    Join Date
    May 2005
    Location
    Wilcox, Saskatchewan
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: link excel to outlook (excel2000)

    I think Igot it working using
    If UCase(Trim(vContactItem.FullName)) <> "" Then

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: link excel to outlook (excel2000)

    Quite a bit of code! A couple of things to consider.

    Defaulting every data type to Variant will slow your code down, and exposes you to errors. For more info, see http://www.cpearson.com/excel/variables.htm

    In this code:

    For Each vContactsItem In vContactsFolder.Items
    vUser2 = vContactsItem.User2
    ...
    Cells(vro, vFullNameCol) = vContactsItem.FullName

    you may want to check that the item is not a Distribution List before processing it, and it may run faster if you use With <object> to 'hold' the vContactsItem object, like this:

    For Each vContactsItem In vContactsFolder.Items
    If vContactsItem.Class = olContact Then ' it's not a DistList, proceed
    With vContactsItem
    vUser2 = .User2
    ...
    Cells(vro, vFullNameCol) = .FullName
    etc...
    End With ' vContactsItem
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Star Lounger
    Join Date
    Jul 2005
    Location
    Mandenville, Louisiana, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: link excel to outlook (excel2000)

    I am curious if this it the amount of code you need to run a function like this?

    After looking at this code I see this might be useful to add to a spreadsheet I have. Is there a much simpliar way of making a worksheet be able to retrieve a contact list and import it into excel?

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: link excel to outlook (excel2000)

    You can export the contact list from Outlook to a .csv file, then open the .csv file in Excel.
    Legare Coleman

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: link excel to outlook (excel2000)

    Probably; it might be simpler code to drop each Contact record field into an array and then Paste the array record into an Excel Row. Want to give it a go?

    Outlook will manually export a Contact Folder direct to an Excel spreadsheet, but I'm not sure that the export function in Outlook is exposed in the VBA model. (In case you haven't messed with Outlook VBA, the Outlook Object Model is not nearly as exposed as Word, Access or Excel, mostly because of Microsoft's fear of use of Outlook for spamming and virus propagation, but in some parts of the Model, such as Views, for no obvious reason at all.) Word has the GetAddress Method (and possibly others I don't know of) to get Address information directly from Contacts, and Access can open any Contact List or Global Address List, but Excel doesn't seem to have those capabilities built in.
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: link excel to outlook (excel2000)

    For posterity, attached is VBA code to import all Outlook Contact data from one user-selected Contacts Folder, including every Property for that Contact, into an Excel Sheet. It will trigger Object Model Guard warnings, unless you install ClickYes. It's -much- slower than the native manual export function, so don't run it against a Folder with a lotta Contacts unless you plan to go to lunch. <img src=/S/anigrin.gif border=0 alt=anigrin width=19 height=19>
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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