Parse Names with Outlook

In Parsing Names, I pointed to Chip Pearson’s page for parsing names.  While I was entering a new contact in Outlook, I realized that Outlook already has a parse names algorithm.  This sub parses all the names in a range.  It has the distinct disadvantage of NOT being function like Chip’s, but is probably a more reliable algorithm.  (Automation in functions stinks because it’s so slow, so I went with a sub here.)

Sub ParseNames()

    Dim olApp As Outlook.Application
    Dim olCi As Outlook.ContactItem
    Dim rCell As Range
   
    Set olApp = New Outlook.Application
    Set olCi = olApp.CreateItem(olContactItem)
   
    For Each rCell In Sheet1.Range(“A2:A14”).Cells
        olCi.FullName = rCell.Value
        rCell.Offset(0, 1).Value = olCi.FirstName
        rCell.Offset(0, 2).Value = olCi.MiddleName
        rCell.Offset(0, 3).Value = olCi.LastName
        rCell.Offset(0, 4).Value = olCi.Suffix
    Next rCell
   
    olCi.Close olDiscard
    olApp.Quit
   
    Set olCi = Nothing
    Set olApp = Nothing
   
End Sub

If you couldn’t tell, I stole the example names from Chip’s site.  So thanks Chip.

Posted in Uncategorized

14 thoughts on “Parse Names with Outlook

  1. I’ve been visiting this blog for a few months and would like to learn more VBA. When I tried this, with a smaller range, I get an error telling me “Compile error: User defined type not defined.” What does this mean?

    Thanks,
    Brett

  2. Brett: You need to set a reference to Microsoft Outlook in Tools > References. I guess that’s the problem. Click “Debug” when you get those errors and see which part of the code that causes trouble.

  3. Parsing a typical name around here (Spain) can be tricky.

    Try:
    Maria Jesus Sanchez-Robano Gonzalez

    Double 1st name – 1st last naqme – 2nd last name

  4. Hi Dick

    Finally tested it. Fantastic idea. Can I use it in an inhouse, noncommercial app ? Will buy you beers on next occasion if so.

    Best wishes Harald

  5. Harald: Certainly. You can even use it to make money if you want. I’ll take the beer anyway, though.

  6. Dick, Outlook (EspaƱol version) cops out by still having only 3 fields:
    Name, 2nd Name and then Last Names (note the plural on last nameS>).
    It usually fails to parse them correctly when left on its own; putting just one name in the “last names” part, one name in the “1st name” part and all the rest (0 to 3 or so) in the “middle name” part.
    Just a slight variant from the N.Armerica version.

    Locally developed applications often have 4 fields; including “1st last name” and “2nd last name”.

  7. For those of us using Thunderbird… is there an equivalent reference to Outlook Express??

  8. How does this start (north american version)? I have the columns in a worksheet “sheet1?, made the reference as noted but when it runs I don’t get any output – column A has Full Names as the title the vb does not error?
    Do you have to declare a range as range or something?

  9. Jay: Make sure the sheet’s codename is Sheet1, not its name. In the vbe’s project explorer (Ctl+R), the sheets are listed like

    DisplayName (CodeName)

    If you had a blank sheet whose codename was Sheet1, you wouldn’t get an error or any output.

  10. Dick: Awesome approach!! I’ve used some of your code to directly import contacts in Excel to Outlook. I have a question: Where do you find the field names for Outlook, i.e. FirstName. Yours are practically about the only ones I can get to work. Strangely, I can’t get Business Phone (s) or faxes to work. I’ve tried a couple different variants, but I just can’t get it on trial and error. Is there any location were these field names are published? I’d like to add some more fields. Thanks for the code and any help!!

    .FirstName = “Dick”
    .LastName = “Kusleika”
    .MobileTelephoneNumber = “(402) 555-1212?
    .Email1Address = “dick@dicks-clicks.com”
    .HomeAddressStreet = “111 S 1st”
    .HomeAddressCity = “Omaha”
    .HomeAddressState = “NE”
    .HomeAddressPostalCode = “68100?
    .SelectedMailingAddress = olHome
    .Categories = “Business, Personal”

  11. Thanks! Sue Mosher provided me with the answer! In case anyone else is intrested here is how to find these:
    1. Start the Visual Basic Editor Alt + F11
    2. Press F2
    3. Select Outlook in the List Box on the Upper Left
    4. Scroll down to the: ContactItem : object and these fields will open on the right!
    Too cool!

  12. There’s another, lower-tech, way to use Outlook names. In my excel app, I issue email to a number of users. I had spent alot of time trying to figure out how to map user names with their email address using extracts out of the Outlook Global Address List or from Distribution Lists.

    The alternative is to use names in the list that the Outlook email will resolve at SEND time.

    For example, in my Excel tables I keep a name like “Joe.Blo”. I address the email with that, NOT with the looked-up correct email address. When I create the email, “Joe.Blo” gets populated into the SENDTo parameter, but then Outlook resolves the address to “Joe.Blo@ourcompanyname.com”, or whatever the “Display As” parameter in Outlook is. Much easier.

    Of course, this takes advantage of a structured email address mechanism within our company. For external addresses, my address just lists their full email address, or I create an outlook contact for them.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.