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 email@example.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: firstname.lastname@example.org 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 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 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 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
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)
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)
Extract data from a structured text block Outlookcode.com example that extracts the data from any label-data pair that appears in a block of text, where all the label-data pairs are on separate lines. For example, get the data from an email message sent using a web form, where the message has multiple lines each with a different Label: Data pair.
The following pages contain macros that use these functions.
Print Outlook email attachments as they arrive
Add a Category to Contacts in a Contact Group (DL)
Saving All Messages to the Hard Drive Using VBA
Check for missing attachments before sending an Outlook email message
Adding Birthdays and Anniversaries to Outlook's Calendar
Removing Birthdays and Anniversaries from the Calendar
Disable Outlook 2010's No Subject Warning using VBA
Find the Distribution Lists a Contact Belongs to
Remove Cancelled Meeting Requests from Resource Calendar
Assign a keyword to a message field for tracking