Parsing text fields in Outlook

An Outlook user wanted to know how to get part of a field and insert it into the message body. This is actually easy to do.

How do I get the domain name from an email address and add it to the message body? I have an email list of clients in Outlook and I would like to send them email. In the body part of mail, I would like to add their website domain. For example, I need to send mail to bob@domain.com and want to insert "Reference: domain.com".

You just need to find the position where the delimiting characters (in this case, the @ sign) is and get the letters going forward or backward.

The good news: It's less complicated than it sounds. Once you get that, you can insert it into a message body using Item.Body = "Reference: " & string . See Send message to contact for an example.

You can also use RegEx to find strings. See Use RegEx to parse message text

You can use this for more than just parsing an email address. If you receive email containing clearly identified fields ("Email: me@domain.com First Name: Mary Last Name: Smith") you can use this method to save the data.

To do this you need to use the VBA functions Len, Right, Left, Mid, and InStr.

Len

Len gets the length of a string, beginning at a starting point.

If you use test = Len("diane"), test will return 5.

Left or Right

Left or right will get the left-most or right-most characters.

string = Left ("phrase", get this many characters)
string = right ("phrase", get this many characters)

In test = Left ("diane", 3), test will equal "dia", the first 3 letters. Right("diane", 3) would give us "ane".

Mid

Mid gets words from the middle of a phrase. You need to enter both the starting point and how many characters.

string = Mid("phrase", start at, get this many characters)

If we use test = Mid("diane", 2, 3), it starts at the second characters and gets the next 3, for "ian".

InStr

InStr gets the position of a character within a string.

integer = InStr(start at, "phrase", "character or string")

So, test = InStr(1, "diane", "i") tells us the position of the i in diane is 2. The 1 tells it to start at the beginning of the string or phrase.

If we know the character we want is in the string twice, we can tell it to start looking later:
test = InStr(4, "slipstick", "i") tells me that there is an i in position 7.

We can look for the starting position of multi-character strings too: test = InStr(1, "slipstick", "ic") returns a 7.

Match upper or lower case

You can ignore upper letters in a string by using the LCASE function: LCASE(String)

LCase(Item.Body) converts the message body to all lower case. This allows the code to work even if someone uses mixed case or all caps.

For example, this sample looks for the word "something" as the first word in the message body. Because it uses LCASE, it will match Something, something, SOmething, or SOMETHING.

If Left(LCase(Item.Body), 9) = "something" Then
'do something
End If

Combine the functions

By putting these functions together we can split Outlook fields apart, trim the length of fields, or look for specific characters in a field.

Below are some very basic examples of how to use these functions. Generally speaking, you want to get each variable separately to use in the Left, Right, or Mid functions as it makes it easier to read and find mistakes.

For example, this line works, but is harder to read and understand. This code gets the right most characters of the selected contacts email address, up to the position of the @ sign (i.e., the domain name):

strDomain = Right(oContact.Email1Address, Len(oContact.Email1Address) - InStr(1, oContact.Email1Address, "@"))

This code does the same as the version above but is easier to follow.

' get position of the @ sign 
intATsign = InStr(1, oContact.Email1Address, "@")

' get the length of the address, minus the position of the @ sign
intAddress = Len(oContact.Email1Address) - intATsign 

' get the right most characters from the phrase
strDomain = Right(oContact.Email1Address, intAddress)

This code shows how to use the Mid function to start counting from the position of a character and get the next xx characters.

intATsign = InStr(1, oContact.Email1Address, "@")
intAddress = Len(oContact.Email1Address) - intATsign 
strDomain = Mid(oContact.Email1Address, intATsign + 1, intAddress)

Trim

Although not one of the functions you'll use to find and parse data, the Trim function can be useful to remove non-printing and leading or ending spaces from text.

string = Trim(string)

Related Posts:

1. 

   Use RegEx to extract text from an Outlook email message
2. 

   Merge to email using only Outlook
3. 

   Create a deferred Birthday message for an Outlook Contact
4. 

   VBA UserForm sample: Select from a list of templates