Results 1 to 14 of 14
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel to DL (XP)

    I have a list of names (& email addresses); e.g. in Excel.

    I want to build a Distribution-list (or contact list) from that (through some VBA code). Anyone who can get me started with some code??

    Erik Jan

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel to DL (XP)

    You must set a reference to the Microsoft Outlook 10.0 Object Library for the attached code to work. Since you are using code to manipulate the Outlook address book, you'll get lots of annoying prompts. See <post:=488,173>post 488,173</post:> for ways to avoid that.

    The code assumes that names are in column A and e-mail addresses in column B (in the active worksheet), starting in row 2. Adapt as needed. The name of the distribution list is hard-coded, you could prompt for it, of course.

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel to DL (XP)

    Works! Thanks!

    OK... one addition: if I run this various times, it creates multiple copies of the same list. Is there a test allowing me to check if the list already exist and delete it before it's re-created. I tried myself but could get this to work....

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel to DL (XP)

    Try this:
    <code>
    Dim olNsp As NameSpace
    Dim olFld As MAPIFolder
    Dim olItm As Object
    Set olNsp = olApp.GetNamespace("MAPI")
    Set olFld = olNsp.GetDefaultFolder(olFolderContacts)
    For Each olItm In olFld.Items
    If olItm.Class = olDistributionList Then
    If olItm.DLName = "My Distribution List" Then
    olItm.Delete
    End If
    End If
    Next olItm
    </code>
    (insert this in the existing code)

  5. #5
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel to DL (XP)

    I only now got to the final implementation and now get to two little problem, one of which I could already solve. Here's a piece from the code from Hans as I implemented:

    Set olDL = olApp.CreateItem(olDistributionListItem)
    olDL.DLName = DL
    ' Create dummy mail
    Set olMail = olApp.CreateItem(olMailItem)
    '
    lngMaxRow = Range("A65536").End(xlUp).Row
    For lngRow = 2 To lngMaxRow
    ' Create contact
    Set olCon = olApp.CreateItem(olContactItem)
    olCon.FullName = Range("A" & lngRow)
    olCon.Email1Address = Range("B" & lngRow)
    olCon.Save
    ' Create recipient
    Set olRec = olMail.Recipients.Add(olCon.Email1Address)
    ' olRec.Name
    If olRec.Resolve = False Then
    MsgBox "Failed to add recipient", vbInformation
    olMail.Recipients.Remove olMail.Recipients.Count
    End If
    Next
    ' Add recipients to distribution list
    olDL.AddMembers olMail.Recipients
    olDL.Save

    This code however creates BOTH (!) the contact AND puts it in the DL. By removing the line "olCon.Save" I got rid of that and now all is saved in the DL.
    Remaining problem is that the I want to set the NAME-field in the DL (that work(ed) for the contacts but doesn't seem to inherit to the DL.

    I guess in this line: Set olRec = olMail.Recipients.Add(olCon.Email1Address) I want to add the "FullName" info somewhere.

    Any clues?

    Thanks!

    Erik Jan

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel to DL (XP)

    If I remember correctly, I tried using olCon.FullName instead of olCon.Email1Address, but this caused Resolve to fail for some names it apparently found to be ambiguous. It won't hurt to try if it works for you. Otherwise, I have no ideas.

  7. #7
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel to DL (XP)

    Yep, I tried and it happened to me too. The fullname is created by me (and read from the sheet) and doesn't (always have to) resemble the email address so resolving the name doesn't work.

    Come to think of it... why do we resolved in the first place??? I'm reading names and email addresses straight from a sheet. Is resolving needed? (I'll test in a moment)

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

    Re: Excel to DL (XP)

    You could omit resolving the names here, but then you might run into a problem when you try to use the distribution list.

  9. #9
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel to DL (XP)

    Yep... just tested. It works but if I then add the FullName entry the list remains empty (seem to recognize and refuse if name doesn't resemble an email - and I don't e.g. have an @ in that name).

    So... back to Resolving then... still trying to find a way to add the fullname. I'll look some more... I'll try to add an argument to

    Set olRec = olMail.Recipients.Add(olCon.Email1Address)

    If a DL has two fields (name and email) there should be a way to populate that I guess...

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel to DL (XP)

    The Outlook object model seems to have been designed to frustrate the programmer...

  11. #11
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel to DL (XP)

    Though I had found it with something like:

    Set oNewMember = olDL.Members.Add("SMTP", name,email)

    That uses CDO and a PAB and is not what I want... I'll keep on searching <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  12. #12
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel to DL (XP)

    I'm still looking to find a way to programmatically (VB / VBA) add more than just email addresses to the Contacts Distribution List in Outlook.

    In the code I have now, only the emails are added (and automatically duplicated as name).

    Anyone?

  13. #13
    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: Excel to DL (XP)

    I haven't tried to read this whole thread. That said, the way I type names in Outlook's To box is as follows:

    John Doe (Company)[smtp:john.doe@domain.com]

    When I press Ctrl+K, it gets resolved to a display name of

    John Doe (Company)

    with the correct underlying address. Does the resolution work the same way in the function/method you're using?

  14. #14
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel to DL (XP)

    Great that works (almost)... I get:

    John Dow (Company

    (note the missing close ")" ). When I use {} instead it works fine however. Guess this is solved then. Thanks all for the code and the tips!

Posting Permissions

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