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.
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
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.
Harald: That was it! Thanks so much. I can see how VBA could be addicting!
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
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
Ed: Does the Spanish language version of Outlook do that for you? I’d be interested to know.
Harald: Certainly. You can even use it to make money if you want. I’ll take the beer anyway, though.
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”.
For those of us using Thunderbird… is there an equivalent reference to Outlook Express??
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?
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
If you had a blank sheet whose codename was Sheet1, you wouldn’t get an error or any output.
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”
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!
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.