Use RegEx to extract text from an Outlook email message

Last reviewed on December 7, 2014   —  68 comments

Getting text out of a message body isn't as hard as it sounds, provided there is something in the body to search on. For example, if you are looking for a tracking code in an email and it's always identified as "Tracking code:" you can use InStr, Len, Left or Right functions to find and extract the tracking code.

Extract codes from email messagesOr you can use Regular Expressions.

For example, to extract the UPS tracking numbers for packages sent by Amazon.com and formatted as shown in the screenshot, I need to look for the words "Carrier Tracking ID", followed by possible white space and a colon (:).

.Pattern = "(Carrier Tracking ID\s*[:]+\s*(\w*)\s*)"

This returns the next alphanumeric string, or in my example, 1Z2V37F8YW51233715. (There are two tracking numbers in the email message and both are returned.)

Use \s* to match an unknown number of white spaces (spaces, tabs, line feeds, etc)
Use \d* to match only digits
Use \w* to match alphanumeric characters, such as are used in UPS tracking codes.

To use this code sample, open the VBA Editor using Alt+F11. Right-click on Project1 and choose Insert > Module. Paste the following code into the module.

Set a reference to VBScript ExpressionsYou'll need to set a reference to the Microsoft VBScript Regular Expressions 5.5 library in Tools, References.

Note: if VBScript Expressions 1 is selected, deselect it. You can't use both v1 and v5.5.

Don't forget, macro security needs to be set to low during testing.

Sample macros using regex are at the following links. Use Copy to Excel code sample to copy a row of text to a row of cells in Excel and Select a name in a Word document then create a Contact to create a contact from a resume or similar file.

Sub GetValueUsingRegEx()
 ' Set reference to VB Script library
 ' Microsoft VBScript Regular Expressions 5.5
 
    Dim olMail As Outlook.MailItem
    Dim Reg1 As RegExp
    Dim M1 As MatchCollection
    Dim M As Match
        
    Set olMail = Application.ActiveExplorer().Selection(1)
   ' Debug.Print olMail.Body
    
    Set Reg1 = New RegExp
    
    ' \s* = invisible spaces
    ' \d* = match digits
    ' \w* = match alphanumeric
    
    With Reg1
        .Pattern = "Carrier Tracking ID\s*[:]+\s*(\w*)\s*"
        .Global = True
    End With
    If Reg1.test(olMail.Body) Then
    
        Set M1 = Reg1.Execute(olMail.Body)
        For Each M In M1
            ' M.SubMatches(1) is the (\w*) in the pattern
            ' use M.SubMatches(2) for the second one if you have two (\w*)
            Debug.Print M.SubMatches(1)
            
        Next
    End If
    
End Sub

If we look for just the colon (.Pattern ="([:]+\s*(\w*)\s*)" ), we get just the first word in the results:

UPS
May
Standard
1Z2V37F8YW51233715
Diane

This is because (\w*) tells the code to get the next alphanumeric string, not the entire line, and strings do not include spaces.

Get two (or more) values from a message

If you need to use 2 or more patterns, you can repeat the With Reg1 through End if for each pattern or you can use Case statements.

This sample code looks for 3 patterns, creates a string and adds it to the subject field of a message.

Each case represents a different pattern. In this sample, we want just the first occurrence of each pattern; .Global = False instructs the code to stop when it finds the first match.

The data we are looking for is formatted like this:

Order ID : VBNSA-123456
Order Date: 09 AUG 2013
Total $54.65

\n at the end of the pattern matches a line break, and strSubject = Replace(strSubject, Chr(13), "") cleans any line breaks from the string.

Sub GetValueUsingRegEx()
    Dim olMail As Outlook.MailItem
    Dim Reg1 As RegExp
    Dim M1 As MatchCollection
    Dim M As Match
    Dim strSubject As String
    Dim testSubject As String
         
    Set olMail = Application.ActiveExplorer().Selection(1)
     
    Set Reg1 = New RegExp
    
For i = 1 To 3

With Reg1
    Select Case i
    Case 1
        .Pattern = "(Order ID\s[:]([\w-\s]*)\s*)\n"
        .Global = False
        
    Case 2
       .Pattern = "(Date[:]([\w-\s]*)\s*)\n"
       .Global = False
       
    Case 3
        .Pattern = "(([\d]*\.[\d]*))\s*\n"
        .Global = False
    End Select
    
End With
    
    
    If Reg1.test(olMail.Body) Then
     
        Set M1 = Reg1.Execute(olMail.Body)
        For Each M In M1
            Debug.Print M.SubMatches(1)
            strSubject = M.SubMatches(1)
            
         strSubject = Replace(strSubject, Chr(13), "")
         testSubject = testSubject & "; " & Trim(strSubject)
         Debug.Print i & testSubject
         
         Next
    End If
          
Next i

Debug.Print olMail.Subject & testSubject
olMail.Subject = olMail.Subject & testSubject
olMail.Save

Set Reg1 = Nothing
     
End Sub

Use a RegEx Function

This function allows you to use the regex in more than one macro.

If you need to use more than one pattern with the function, set the pattern in the macro regPattern = "([0-9]{4})" and use this in the function: regEx.Pattern = regPattern. Don't forget to add Dim regPattern As String at the top of the module.


Function ExtractText(Str As String) ' As String
 Dim regEx As New RegExp
 Dim NumMatches As MatchCollection
 Dim M As Match
 
'this pattern looks for 4 digits in the subject
 regEx.Pattern = "([0-9]{4})"

' use this if you need to use different patterns. 
' regEx.Pattern = regPattern

 Set NumMatches = regEx.Execute(Str)
 If NumMatches.Count = 0 Then
      ExtractText = ""
 Else
 Set M = NumMatches(0)
     ExtractText = M.SubMatches(0)
 End If
 code = ExtractText
 End Function

This simple macro shows how to use the Regex Function. If the subject matches the regex pattern (in function example, a 4-digit number), a reply is created; if it does not contain a 4 digit number, a message box comes up. To use the function with different macros, uncomment the lines containing regPattern.

Dim code As String
'Dim regPattern As String

Sub RegexTest()

Dim Item As MailItem
Set Item = Application.ActiveExplorer.Selection.Item(1)

' use this to pass a pattern to the function
'regPattern = "([0-9]{4})"

ExtractText (Item.Subject)
 
If Not code = "" Then
Set myReply = Item.Reply
myReply.Display

Else
MsgBox "The subject does not contain a 4 digit number"
End If

End Sub

About Diane Poremsky

Diane Poremsky
A Microsoft Outlook Most Valuable Professional (MVP) since 1999, Diane is the author of several books, including Outlook 2013 Absolute Beginners Book. She also created video training CDs and online training classes for Microsoft Outlook. You can find her helping people online in Outlook Forums as well as in the Microsoft Answers and TechNet forums.

Please post long or more complicated questions at Outlook forums by Slipstick.com.

68 responses to “Use RegEx to extract text from an Outlook email message”

  1. carrolltd47

    Diane, this is a great tip, however, can you tell me how to point to a specific folder in Outlook and where the output file is created?

    Thanks,

    Tim

    1. Diane Poremsky

      The code works on the selected message, so its not limited to any folder. It's a concept macro, so it only outputs to the immediate window (Ctrl+G to open the immediate window in the VB Editor)
      Debug.Print M.SubMatches(1)

      what type of file did you want to create?

  2. carrolltd47

    Wanted to output to a text file.

    1. Diane Poremsky

      Crude version of the necessary code, taken from the second macro here. This code woulds go after End If, before End Sub.
      Set objFile = objFS.CreateTextFile(strFile, False)
      For Each oMail In ActiveExplorer.Selection
      With objFile
      .Write M.SubMatches(1)
      End With
      Next
      objFile.Close

      I have a macro that uses regex to write to Excel at Use a macro to copy data in Outlook email to Excel workbook.

  3. Corby Hicks

    Hi. Thanks for your articles! They have been very helpful. I am wondering if there is a way to capture multiple pieces of information and copy them to the subject. For example, on receipts I receive regularly, I need to extract three pieces of info:
    Order ID : VBNSA-XXXXXX
    Order Date: 09 AUG 2013
    Total $XX.XX

    Is it possible to find these and copy into the subject field?

    1. Diane Poremsky

      It is possible. This gets the order and date: .Pattern = "([:]([\w-\s]*)\n)" The money patterns I tried aren't working. I'll see if i can get one that works for it.

    2. Diane Poremsky

      I added a code sample to the page that get multiple patterns from a message. If you need to process the messages as they arrive, you can convert it to a run a script rule by changing the sub name to Public Sub GetValueUsingRegEx(Item As Outlook.MailItem) and creating a rule.

  4. Corby Hicks

    Thanks, Diane! Really helpful! I played around with it this morning a bit but haven't had much success. I'm getting the following result "Order VBNSA-025230; .; ." I just realized the Date string is longer than I wrote previously. It actually appears as "09 AUG 2013 14:00:05"... I don't need / want the time. I'm assuming that would be the reason for the date not appearing in the results?

    THANKS AGAIN!

    1. Diane Poremsky

      That would be the reason. Try this as case 2 - .Pattern = "(Date[:]([\w-\s]*))\s\d*[:]"

  5. verdantly

    Hi Diane -

    Thanks again for the good info. I wonder how you would use something similar to the above to find a number in an email when it's label is on the line before or even two lines before. For example, something like this would appear in the body of the email along with items and a total:

    PO#
    --------
    123

    1. Diane Poremsky

      If you can figure out the pattern to use, it will work. - I'll see if i can figure it out.

  6. Richard Sutter

    I have a similar situation, where I have large number of form responses from which I need to extract the same types of info and rearrange them into a set pattern. Typically I receive a message like this:
    ---

    SLA:
    Date Entered: 03-OCT-2013 13:43 (GMT -7) Mountain Time.
    External response time (SLA): 03-OCT-2013 22:43.(GMT -7) Mountain Time.

    SR Description: I would like to start troubleshooting to find out why the analyzer reports etc. etc. etc.
    Company: CUSTOMER COMPANY NAME.
    Top Account: Non GAP.
    Customer Sub Region: West Coast.

    Site:
    Site Name: CUSTOMERSITEALPHA#280501.
    Address: 1234 ACADEMY BLVD,POD A, 1ST FLR, LOC# 1007,.
    City: COLORADO SPRINGS.
    State: Colorado.
    Country: United States.
    Division Name: S&R.

    Opened Via: WEB.
    Contact: John Smith.
    Contact's EMAIL: john.smith@customername.com.

    Contract Number: 12345.
    Service Level: Gold Lite
    Serial #: ABC-12345.
    Service Provider: .

    Priority: .
    Severity: Low.

    SR Status: Open.
    Group Assigned: T1 Americas.
    SR Owner: .
    Owner Mail: .
    Owner Phone: .
    SR Type: Malfunction.

    SR Categorization:
    Solution division: S&R.
    Solution name: ABCD Product.
    Solution version: ABCD Product 4.1.
    Solution UP: 25.
    ---

    And I need to extract info and rearrange it into this:

    External response time (SLA): 03-OCT-2013 22:43.(GMT -7) Mountain Time.
    Serial #: ABC-12345.
    Solution name: ABCD Product.
    Solution version: ABCD Product 4.1.
    Solution UP: 25.
    SR Description: I would like to start troubleshooting to find out why the analyzer reports etc. etc. etc.
    Contact: John Smith.
    john.smith@customername.com.

    I've tried several ways to get this info in this order into a text file or a msg box and just keep failing at the find step.... Any suggestions ?

    1. Diane Poremsky

      Try something like this - you'll need 1 case statement for each line you need to capture, in the order you want them. When you get everything, Try putting it into a message body - olMail.body= testSubject - once you know that part is working then you can change the output.

      For i = 1 To 7

      With Reg1
      Select Case i
      Case 1
      .Pattern = "(External response time (SLA)[:]([\w-\s]*)\s*)\n"
      .Global = False

      Case 2
      .Pattern = "(Serial #[:]([\w-\s]*)\s*)\n"
      .Global = False

      Case 3
      .Pattern = "(Solution name:[:]([\w-\s]*)\s*)\n"
      .Global = False
      End Select

      ' repeat for the additional case's

      End With

  7. Nathan King

    Hi
    I receive regular emails from a specific sender with an attachment with a generic name. I would like to parse the string in the message and use the string to save my attachment. For example, the attachment is named generic.bmp, and the string I would like to extract from the message is
    Dear user-abc@domain:
    I would like to extract user-abc@domain (without the colon at the end) to save the attachment as user-abc@domain.bmp
    How do I incorporate this script with the save attachment script?
    BTW, this is for Outlook 2010
    Thanks

    1. Diane Poremsky

      Is this a message you are cc'd on? If so, and if the address is the only one in the To field, you can pull it out with .to. If, for example, you are using code that refers to the message objMsg, you'd use objMsg.To.

      Something like this:
      Set objAttachments = objMsg.Attachments
      strFile = objMsg.To & objAttachments.Item(i).FileName
      strFile = strFolderpath & strFile
      objAttachments.Item(i).SaveAsFile strFile

      Or, you can use this pattern in the macro on this page:
      .Pattern = "(([\w-\.]*\@[\w-\.]*)\s*)"
      and use something like
      strFile = M.SubMatches(1)
      where Debug.Print M.SubMatches(1) is. Use the Dim statements and the code from Set Reg1 line on, replacing olMail.Body with the same name you are using to identify the message (objMsg in my first example).

  8. terence

    Input text at email :

    BAML (BANK OF XXX)
    Total Amount: 1000 Total Price: 22

    CSFB (CREDIT XXX HK)
    Total Amount: 11000 Total Price: 2.2

    How can i achieve

    BAML - 1000
    CSFB - 11000

    1. Diane Poremsky

      Will the XXX vary with each message? If it's always BAML (BANK OF XXX) and CSFB (CREDIT XXX HK), you can get the total amount line and hard code the BAML and CSFB. If you need to pick both up too, you'd look for a pattern of (\w*) (\((\w-\s*) and (Total Amount[:]+\s(\d*) - use two case statements and global = true to pick up all entries.

  9. anired

    Hi,

    How to extract the latest mail body from a chain of mails,(i.e the topmost mail body txt from a mail chain) .thanks much

    1. Diane Poremsky

      I would try look for the first unique separator - probably From:

      With Reg1
      .Pattern = "(.*)From[:]+\s*(\w*)\s*"
      .Global = False ' stops at the first match
      End With

      If it works (I haven't tested it), the (.*) should get the message down to the first message header.

    2. Diane Poremsky

      Hmm. its not picking up the content before the From.

  10. anired

    Thanks Diane for the reply,actually what am trying to implement is to write a pattern which takes in single mails/chain of mails as input and return the latest mail body txt as output.Yeah even i have tried similar patterns like you have mentioned From separator or signatures like
    thanks,regards but i found these patterns are not fully solving my purpose ,i was wondering is thr any way like outlook separates the mails with a line when we look at mails in outlook client ,so that i can just return the part before the first line separator.any pointers will be a great help .Thanks much.

    PS:the mails are retrieved from exchange server using EWS services with "bodytype.text" as thr format .

    1. Diane Poremsky

      It's going to be tough. If the message is HTML, you can read the source code - the css is different for previous replies. That won't help if RTF or plain text though.

  11. zack

    Can you please help. I have very limited knowledge of VB and I have 2007 Outlook running on windows 7 PC. I want to acheive the following:

    1 - I get the generic email from a marketing source. and i need to extract the following information in the message body.
    - Item number:
    - Item title
    - Name
    - email
    - phone number

    2 - I need to use outlook to auto reply to to the email from the message with BCC to another person(fixed) the message and use a template for that item number. I normally use the signature to reply to message. I have created several signatures with a predefined message to each item number.
    3 - i would like to use the -item title as the subject.

    that will make my life very easy and instead of sitting and replying email with a predefined signatures, everything will automated. Your help will be greatly appreciated.

  12. Kevin

    Hi Diane,

    I have a similar situation where I need to create a task from an newmail. Here is the code used for that use:

    Sub ConvertMailToTask(Item As Outlook.MailItem)
    Sub ConvertMailToTask(Item As Outlook.MailItem)
    Dim objTask As Outlook.TaskItem
    Set objTask = Application.CreateItem(olTaskItem)
    With objTask
    .Subject = Item.Subject
    .StartDate = Item.ReceivedTime
    .Body = Item.Body
    .DueDate = (This is the information I am looking for)
    .Save
    End With
    Set objTask = Nothing
    End Sub

    This is activated through a rule running the script. How can I extract the duedate from the body of that mail and apply the value to the previous script?

    Here is the format of the text in the body: Due Date : 2013-12-06

    Thank you!

    1. Diane Poremsky

      Try this pattern:
      .Pattern = "Due Date\s*[:]+\s*([\w-]*)"

  13. Frans

    Hi Diane,

    I have a situation where i receive an email from an internal system with pdf document attached. I then need to forward it to the email mentioned after Email Address: I do this by using your suggested code and it works fine:

    With Reg1
    .Pattern = "(Email Address:\s*(\d*)\s*)"
    .Global = True
    End With

    If Reg1.test(Item.Body) Then
    Set M1 = Reg1.Execute(Item.Body)
    For Each M In M1
    strCode = M.SubMatches(1)
    'MsgBox "reg1: " & strCode
    Next
    End If

    With Reg2
    .Pattern = "([a-z0-9.]*)@([a-z0-9.]*)"
    .Global = True
    End With

    See below what the body of the email looks like.

    Please Find attached report for patient: MRS L HOWSON.
    Email Address: fttest@emaildomain.com
    Reference number: D0630422

    I then need to extract the patient name and the reference number and use that in the new Subject but i dont get it to work. Can you maybe lend a helping hand please.

    Many thanks

    1. Diane Poremsky

      if the message is plain text, this will work (the email address needs cleaned if html) - rather than looping twice, it uses case statements and an array to pick up the second match in each line.

      Sub PatientInfo()
      Dim olMail As Outlook.MailItem
      Dim Reg1 As RegExp
      Dim M1 As MatchCollection
      Dim M As Match
      Dim strResult(3) As String
      Dim strTest(3) As String
      Dim strName, strRef, strTo As String

      Set Reg1 = New RegExp

      Set olMail = Application.ActiveExplorer().Selection(1)

      For i = 1 To 3
      strResult(i) = ""
      With Reg1
      Select Case i
      Case 1
      .Pattern = "(Please(.*)[:](.*)).\s*\n"
      .Global = False
      Case 2
      .Pattern = "(Email(.*)[:]\s*(.*)).\s*\n"
      .Global = False

      Case 3
      .Pattern = "(Reference(.*)[:](.*)).\s*\n"
      .Global = False
      End Select

      End With

      If Reg1.Test(olMail.Body) Then

      Set M1 = Reg1.Execute(olMail.Body)
      For Each M In M1
      strTest(1) = M.SubMatches(1)
      'second match
      strResult(i) = M.SubMatches(2)

      Debug.Print i & strTest(i)
      Debug.Print i & strResult(i)

      strName = strResult(1)
      strTo = strResult(2)
      strRef = strResult(3)

      Next
      End If

      Next i
      Debug.Print "Subject: " & strName & " " & strRef & "email: " & strTo
      Set Reg1 = Nothing
      Set olMail = Nothing
      Set M1 = Nothing
      Set M = Nothing

      End Sub

  14. Alex

    Hi Diane,

    Run into a problem with capturing a date and a decimal:

    At: 4/04 12:20:17
    Last: 105.9805

    Cannot figure out how to do the slash. Dicimal does not work either.

    I tried

    .Pattern = "(At\s*[:]\s*(\w*)\s*)"

    but it captures only the 4, not the rest. Played with several ways but nothing works.

    With the decimal tried

    .Pattern = "(Last\s*[:]\s*(\d*)\s*)"

    but again it only captures 105, not the rest.

    Could you probably give me some tips how to make it work?

    Many thanks in advance,
    Alex

    1. Diane Poremsky

      decimals (periods) need to use \. because . is used in regex to signify any character.
      Last: 105.9805 - try .Pattern = "(Last\s*[:]\s*(\d-\.*)\s*)" that should get you 105...555 too, so if you want to look for just digits decimal digits, try something like ([\d*]\.[\d*])
      At: 4/04 12:20:17 - try .Pattern = "(At\s*[:]\s*(\d*-/:)\s*)"
      They might not be correct - I can't test them right now and I'm not a regex expert by any means - but hopefully that will point you in the right direction.

  15. Alex

    Thanks a lot, Diane. It did not work for some reason but at least pointed me in the right direction. This is what worked for me
    .Pattern = "(Last\s*[:]\s*(\d+\.\d+)\s*)"

    .Pattern = "(At\s*[:]\s*(\d*[-/.]\d*)\s*)"

  16. David

    I was wondering if there is a way to save emails as names from data you parse from inside the email. We have a very generic email that is sent from the same address for user management. I would like to be able to parse each email for the users nt id and in turn save each email as a text file as the nt id. Is this possible? I have scripts that are currently working that save each one with a generic name from a shared folder.

    1. Diane Poremsky

      if the NT id is in the message and easily identifiable (so regex can find it), yes, you can grab it and use it in a file name.

    2. Saurabh

      Hi Diane,

      I am not able to get the pattern for below:
      File_name Count
      ABCDEFGH_1_1_20140615014004.dat.gz:302548
      ABCDEFGH_2_1_20140513084790.dat.gz:168749
      ABCDEFGH_3_4_20140613098489.dat.gz:500023

      I am receiving these kind of records in mail body.
      I want to get these in excel, but I am able to get only File_name.
      Please help me on this.

      I am using this :
      .Pattern = "((ABCDEFGH(.*))([:]+\s*))"

      and what I received in excel is this:
      FILE_NAME Record Count
      ABCDEFGH_1_1_20140615014004.dat.gz _20140615014004.dat.gz

      Also, each time i run this rule, it fetches only 1 record in excel.
      I have more then 500 records in each mail body to be loaded in excel.

      Thanks and Regards
      Saurabh

    3. Diane Poremsky

      to get the count as a separate value, try something like
      .Pattern = "(ABCDEFGH(.*)[:](.*)+\s*)"
      If the values will always be numerical, you can use ([0-9]+) instead of (.*)

    4. Diane Poremsky

      Oh, and for retrieving each value, you need to use global=true and write the value within the loop (which the example does). Global=False quits as soon as it finds a match.

  17. sun

    HI Diane,

    I have a requirement like identifying a particular tag in xml and replace it with default text

    ex- 123

    i need to replace 123 with xxx.

    Can we achieve it using regex? Can you please advice?

    Thanks
    Sun

    1. Diane Poremsky

      Yes, i believe you can. I don't have any samples that do it though. Will you always be replacing 123 with xxx or some other value.

  18. Ramaswamy Ramalingam

    Hi Diane,

    Thanks for the wonderful article.
    Is it possible that we extract the details from the message and automatically create and save a contact in outlook.

    Thanks,
    Ramaswamy

    1. Diane Poremsky

      It is possible, as long as the fields are the same - if the formats are different in each message, a commercial product that grabs details is cost effective. Basically, grab the values and put them in variables then create a contact using those variables in the fields.

      Set oContact = Application.CreateItem(olContactItem)
      With oContact
      .Email1Address = strEmail
      .Email1DisplayName = strName
      .FullName = strName
      .Body = strNotes
      .Save
      End With

  19. Dave Upston

    Hi Diane, this article has helped me learn a lot, thanks for putting it together.

    I'm trying to play around with the "Get two (or more) values from a message".

    I've got emails coming in with content like:

    Fax received from TSID: 0297346529
    Line CallerID: 297346529
    Date and Time Received: Mon, 29 Sep 2014 12:42:09 EST
    Number of Pages: 3
    Send to: T1100555
    Connect Time: 1:12
    Date and Time Processed:
    Telstra Job Number: 1718432594

    My perfect end result, would be changing the subject on arrival to:

    Fax from CustomerName - 3 Pages

    Where CustomerName is determined by the number in Line CallerID

    I have a full list of CallerID = CustomerName that can be referenced (somewhere)

    how possible is this?

    1. Diane Poremsky

      you'll need to do a lookup in the file to get the name. how many names/numbers are in the list? does it change much? you can use an array within the macro - if it is large or changes you can use a macro to load the array each time it runs.

    2. Dave Upston

      I have about 100 customers to list at the moment, and the list will increase as more come on board.

      I've had an issue with the code i was playing with and nothing seems to work at all now. Are you able to help out? Happy to pay/donate somewhere for your time.

    3. Diane Poremsky

      Finally getting through oodles and oodles of comments after a few trips this falls. I am available for small projects if you are still stuck on this.

  20. Andrew

    Hello Diane,

    I'm trying to use this code to pull from a selection of Excel cells, and move the acquired data into cells on a new sheet.

    For example, all of the data I want to parse is located in column C on Sheet 1. I want to search for two things, "Name:" and "Position:" and put the values that come after the colon into Cells D2 and D3 respectively on Sheet2.

    How would you advise I do this?

    Thanks!

    1. Diane Poremsky

      Excel is not my area of expertise (and I don't work with Excel VBA too much these days so I'm a bit rusty), but you'll get the values then pass them to a cell, something like this:
      You'll need to set the range to be on sheet2.
      xlSheet.Range("d" & rCount) = StrName
      xlSheet.Range("d" & rCount+1) = StrPos

      I think you could use Sheets("Sheet2").Cells("d", rCount) too.

      This macro shows how to pass values in Outlook to Excel: http://www.slipstick.com/developer/vba-copy-outlook-email-excel-workbook/

    2. Andrew

      Thanks Diane!

      I was just using Excel because I'm slightly more familiar with it. There's probably an easier way to do what I'd like using just Outlook. I'm trying to schedule a meeting invite in reference to information that appears in the body of a separate email. Like so:

      Receive request to schedule a meeting.
      Open outlook meeting with subject "Name - Position"
      Body of meeting message says:
      Name: First Last
      Position: President

      I want to pull the Name and Position out of the initial request, and then have it show up in the subject line and body of the new meeting invite.

      I'm using the following code to create the Outlook meeting, and this part has no problem. It's just getting the actual values of Name and Position to populate automatically and remove the need for manual copy/paste.

      Sub SendMeetingRequest()
      Dim objOL 'As Outlook.Application
      Dim objAppt 'As Outlook.AppointmentItem
      Const olAppointmentItem = 1
      Const olMeeting = 1

      Set objOL = CreateObject("Outlook.Application")
      Set objAppt = objOL.CreateItem(olAppointmentItem)
      With objAppt
      .Subject = "NAME - " + "POSITION"
      .Location = "Location"
      .Body = "Hello," & Chr(13) & Chr(13) & "This is an invite for a meeting with the person listed below." & Chr(13) & Chr(13) & "Person: " + "NAME" & Chr(13) & "Title: " + "POSITION" & Chr(13) & "Location: Your Office"
      .Start = Now + 1
      .End = DateAdd("h", 1, .Start)

      ' make it a meeting request
      .MeetingStatus = olMeeting
      .RequiredAttendees = ""
      .Display
      End With

      Set objAppt = Nothing
      Set objOL = Nothing
      End Sub

      Do you know how I can combine the RegEx code with this meeting invite code so that I can pull the person's NAME and POSITION out of the body of an email?

    3. Diane Poremsky

      The regex code on this page (or one of the other regex code pages I have) should do it. (THis is outlook vba)

      Dim strName As String
      Dim strPos As String

      Sub GetValueUsingRegEx()
      Dim olMail As Outlook.MailItem
      Dim Reg1 As RegExp
      Dim M1 As MatchCollection
      Dim M As Match
      Dim strResult(2) As String

      Set olMail = Application.ActiveExplorer().Selection(1)

      Set Reg1 = New RegExp
      For i = 1 To 2

      With Reg1
      Select Case i
      Case 1
      .Pattern = "(Name[:]([\w-\s]+))\s*\r"
      .Global = False

      Case 2
      .Pattern = "(Position[:]([\w-\s]+))\s*\r"
      .Global = False
      End Select

      End With

      If Reg1.test(olMail.Body) Then

      Set M1 = Reg1.Execute(olMail.Body)
      For Each M In M1

      strResult(i) = Trim(M.SubMatches(1))
      Debug.Print strResult(i)

      strName = Replace(strResult(1), vbCrLf, "")
      strPos = Replace(strResult(2), vbCrLf, "")

      Next
      End If

      Next i

      Set Reg1 = Nothing
      SendMeetingRequest
      End Sub

      in the meeting macro, use strName and strPos for the name and position fields -
      .Body = "Hello," & Chr(13) & Chr(13) & "This is an invite for a meeting with the person listed below." & Chr(13) & Chr(13) & "Person: " + strName & Chr(13) & "Title: " + strPos & Chr(13) & "Location: Your Office"

    4. Andrew

      Hi Diane,

      That worked great, thanks! Now, since the object is an appointment, is there any way to set the body to accept HTML?

      If not, is there any other way to set the formatting of specific text in the body, and could I add a table as well?

      Thanks!

    5. Diane Poremsky

      No, you can't use HTML in appointments. You can use Word VBA to format - record a macro in Word to get the code that is used ot create the elements you want then put it into outlook. The macro at http://www.slipstick.com/developer/word-macro-apply-formatting-outlook-email/ shows how to use Word code in Outlook.

  21. Ryan

    Diane,
    Your post is amazing and if I can get this to work for a project I'm involved with, it can save me many hours of manual entry.

    I have never used VBA before, but I'm not afraid to try. I receive +200 emails that are formatted exactly the same as below example. I want to parse the info that follows Vehicle, Event, Time and Coordinates. I would assume the same logic would be used as in your code using the colon, but what would this do for the date/time field which contains a colon.

    I really don't know where to begin, but I would appreciate any help! Thank you.

    Actual Email:

    GEOSERVICES EVENT NOTIFICATION

    EQUIPMENT
    Vehicle: 11848

    EVENT
    Event: Departure
    Time: 10/22/2014 16:00 EDT
    Landmark Type: Port
    Landmark: Georgia Ports Authority
    Address: 2 Main St
    Garden City, GA 31408

    EQUIPMENT POSITION @ EVENT
    Coordinates: 32.126296,-81.158426
    Nearest City: 5.70 miles NW of Savannah, GA Nearest Town: 1.32 miles N of Garden City, GA

    1. Diane Poremsky

      Use the second macro and change the case statements:

      At the top:
      Dim strResult(4) As String ' use # of cases
      Dim strVehicle, strEvent, strTime, strCoord As String

      Replace the case statements:

      For i = 1 To 4 ' # of cases
      With Reg1
      Select Case i
      Case 1
      .Pattern = "(Vehicle[:]\s*([\d]+))\n"
      .Global = False

      Case 2
      .Pattern = "(Event[:]\s*([\w-\s]*))\n"
      .Global = False

      Case 3
      .Pattern = "(Time[:](.*))\n"
      .Global = False

      Case 4
      .Pattern = "(Coordinates[:](.*))\n"
      .Global = False
      End Select

      End With

      In the Matches chunk:
      strResult(i) = M.SubMatches(1)

      ' put the values in strings, which can be pushed into excel or whatever
      strVehicle = strResult(1) 'match # to case
      strEvent = strResult(2)
      strTime = strResult(3)
      strCoord = strResult(4)

      A code sample that sends the data to Excel is here - http://www.slipstick.com/developer/vba-copy-outlook-email-excel-workbook/

  22. Nathan

    Hi,

    I have successfully written a script that can insert a new line to an existing email, change the subject and then forward to a new recipient.
    My current script is as such
    Set FwdMsg = item.Forward
    With FwdMsg
    NewLine = "Dear users,Please note that data is also affected by the incident below and will be corrected after systems recovers. Please email the team for more information. Thank you."

    FwdMsg.HTMLBody = NewLine & FwdMsg.HTMLBody
    FwdMsg.Recipients.Add "john@gmail.com"
    FwdMsg.Subject = "Incident affecting you" & Format$(Now, " dd-mm-yyyy hh.mmam/pm")

    FwdMsg.sEnd

    However I now need to remove certain sentences in the email and replace them with " plase call me with this number.

    This is my email body

    Impact:
    ABCD EFG
    RSTUV

    Corrective Action

    I need to remove the ABCD EFG and RSTUV and replace with a sentence "contact me" such that the new email body is
    Impact:

    please call me with this number

    Corrective Action

    How do I do that with regex?

    I tried this but it doesnt seem to work

    Dim pattern As String = "(\p{Sc}\s?)?(\d+\.?((?<=\.)\d+)?)(?(1)|\s?\p{Sc})?"
    Dim input As String = FwdMSg.HTMLBody
    Dim replacement As String = " please call me with this number
    Dim rgx As New Regex(pattern)
    Dim result As String = rgx.Replace(input, replacement)

    1. Diane Poremsky

      To replace using regex, the replace line goes in the block with the pattern. It's not very good with HTML messages though.

      With Reg1
      .Pattern = "([a-z]{6})"
      .Global = True
      text1 = .Replace(oReply.Body, "hello")
      End With
      If Reg1.Test(oReply.Body) Then

      Set M1 = Reg1.Execute(oReply.Body)
      For Each M In M1
      Debug.Print M.SubMatches(0)
      Debug.Print text1
      oReply.Body = text1
      Next
      End If

      You can also use Word VBA to select and replace the lines. The code sample at http://www.slipstick.com/developer/word-macro-apply-formatting-outlook-email/ shows how to do something with the selection.

  23. Pranab

    I want a code for the following information which i receive in my outlook mail box to be extracted to a excel file in
    Zebra/Orion Printer: 10
    Account no.: 517626023
    Company: Jeanne d'Arc Living ApS
    First Name: Rasmus
    Last Name: Olsen
    Address 1: Houmarksvej 92
    City: Randers NV
    Postal Code: 8920
    Country: Denmark
    Phone no.: 36 93 20 14
    E-Mail: rasmus@jeannedarcliving.dk

    1. Diane Poremsky

      Use a case statement to get the values and use the method here - http://www.slipstick.com/developer/vba-copy-outlook-email-excel-workbook/ - to write it to excel.

  24. Ainsley

    Hi Diane,

    How would I be able to use this RegEx to read a few different patterns and if the subject satisfy one of the pattern, then a code will be execute (e,g, auto reply code). For example, if the email subject is "ABCD" OR "1234" OR "AB12" then the code to autoreply will run.

    Would looping with IF....ELSE...ELSE...ELSE...END IF helps?

    1. Diane Poremsky

      You can use | (pipe character) as an OR. ([A-D]{4}|[0-9]{4}|[A-Z]{2}[0-9]{2})

  25. Mitch

    Hi Diane,

    Is there a code I can use to track if someone forwards my email to another person?

    Tracking_beacon????

    Thanks

    1. Diane Poremsky

      Well unless you use IRM, the only option would be to use a tracking beacon. But, its not going to tell you a lot - it wont show who it was forwarded to, or guarantee it was forwarded. It will only show the IP addresses that opened it, and even then, only if they aren't blocking downloads.

  26. Mitch

    What about a native_foward?

    1. Diane Poremsky

      Not sure what you mean by native_forward - outlook won't block forwarding on its own.

  27. Mitch

    To provide more information of what I am looking for. I believe an IRM might prevent a forward. I actually want to track my emails forwarded to another employee of who the email is not intended.

    1. Diane Poremsky

      IRM will prevent forwarding.

  28. Kiara

    Hi Diane,

    Thank you so much for this wonderful tutorial. I'm trying to modify your code to match URLs, but am having a hard time with the Regex for this. Are you aware of any good regex url matchers? Or do you think another method would be better?

    Thank you!

    1. Diane Poremsky

      RegEx should work fine. Assuming you are matching any http:// url and there is no other text on the line, the pattern should be something like "(https?(.*))" (s? in the pattern matches http or https.. If the url is within text, this pattern should work - .pattern = "(https?\://[a-zA-Z0-9\-\.]+\.[a-zA-Z]{2,3}(/\S*))?>"

      Oh, and use 0 in the m.submatches: Debug.Print M.SubMatches(0)

  29. Neal

    Hi Diane, very helpful article! My question is how can I turn this into a script to run as a part of a rule for forwarding messages and use the multiple cases of text as information to populate a form email?

    1. Diane Poremsky

      to use the first macro in a rule, change this:
      Sub GetValueUsingRegEx()
      ' Set reference to VB Script library
      ' Microsoft VBScript Regular Expressions 5.5
      Dim olMail As Outlook.MailItem
      to
      Sub GetValueUsingRegEx(olMail As Outlook.MailItem)
      ' Set reference to VB Script library
      ' Microsoft VBScript Regular Expressions 5.5

      and delete Set olMail = Application.ActiveExplorer().Selection(1)

      Or you could use something like this with the original macro
      Public Sub ForwardSomething(Item As Outlook.MailItem)
      GetValueUsingRegEx olmail
      End Sub
      the regex macro would need a little tweaking, beginning with removing the line that sets olmail. The advantage of this method is you use several short macros with rules rules and can pass different patterns to it. I thought I had an example somewhere either in comments or at outlookforums - i'll see if i can find it.

    2. Neal

      Very helpful for using in a rule. When using the two or more values macro above, how can I insert the results in a formatted email? For example, "The Order ID XXXXX was placed on XXXXX for a total of XXXXX" where the values are pulled from an email using the macro?

Leave a Reply

If the Post Comment button disappears, press your Tab key.

This site uses XenWord.