Last reviewed on March 8, 2015   —  77 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 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)
    End If
End Sub

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


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
    End If
Next i

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

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 = ""
 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

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

End Sub


  1. carrolltd47 says

    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?



    • Diane Poremsky says

      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. Corby Hicks says

    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 Date: 09 AUG 2013
    Total $XX.XX

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

    • Diane Poremsky says

      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.

    • Diane Poremsky says

      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.

  3. Corby Hicks says

    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?


    • Diane Poremsky says

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

  4. verdantly says

    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:


  5. Richard Sutter says

    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:

    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.
    Top Account: Non GAP.
    Customer Sub Region: West Coast.

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

    Opened Via: WEB.
    Contact: John Smith.
    Contact's EMAIL:

    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.

    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 ?

    • Diane Poremsky says

      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

  6. Nathan King says

    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

    • Diane Poremsky says

      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).

  7. terence says

    Input text at email :

    Total Amount: 1000 Total Price: 22

    Total Amount: 11000 Total Price: 2.2

    How can i achieve

    BAML - 1000
    CSFB - 11000

    • Diane Poremsky says

      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.

  8. anired says


    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

    • Diane Poremsky says

      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.

  9. anired says

    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 .

    • Diane Poremsky says

      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.

  10. zack says

    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.

  11. Kevin says

    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)
    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!

  12. Frans says

    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
    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:
    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

    • Diane Poremsky says

      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)

      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

  13. Alex says

    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,

    • Diane Poremsky says

      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.

  14. Alex says

    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*)"

  15. David says

    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.

    • Diane Poremsky says

      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.

    • Saurabh says

      Hi Diane,

      I am not able to get the pattern for below:
      File_name Count

      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

    • Diane Poremsky says

      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 (.*)

    • Diane Poremsky says

      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.

  16. sun says

    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?


    • Diane Poremsky says

      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.

  17. Ramaswamy Ramalingam says

    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.


    • Diane Poremsky says

      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
      End With

  18. Dave Upston says

    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?

    • Diane Poremsky says

      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.

    • Dave Upston says

      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.

    • Diane Poremsky says

      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.

  19. Andrew says

    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?


    • Diane Poremsky says

      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:

    • Andrew says

      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 = ""
      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?

    • Diane Poremsky says

      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, "")

      End If

      Next i

      Set Reg1 = Nothing
      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"

    • Andrew says

      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?


  20. Ryan says

    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:


    Vehicle: 11848

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

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

    • Diane Poremsky says

      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 -

  21. Nathan says


    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 ""
    FwdMsg.Subject = "Incident affecting you" & Format$(Now, " dd-mm-yyyy hh.mmam/pm")


    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


    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

    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)

    • Diane Poremsky says

      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
      End If

      You can also use Word VBA to select and replace the lines. The code sample at shows how to do something with the selection.

  22. Pranab says

    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

  23. Ainsley says

    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?

    • Diane Poremsky says

      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.

  24. Mitch says

    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.

  25. Kiara says

    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!

    • Diane Poremsky says

      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)

  26. Neal says

    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?

    • Diane Poremsky says

      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
      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.

    • Neal says

      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?

  27. Nick Syrax says

    I have a case where I need to search for particular text, then copy the entire line of text where the search found a result. Can this accomplish that?

    • Diane PoremskyDiane Poremsky says

      As long as the search term is either always the same or fits a pattern, it will work.

  28. Dan says

    Dear Diana,

    I am trying to locate a function to extract only 1st paragraph of email body. It seems I cannot locate the proper function. Here is the macro I was able to locate at Internet, however I need to modify the code sot that "strColG = olItem.Body" is something like that "strColG = olItem.Body.1st paragraph" text only

    Option Explicit
    Sub ExportToExcel()
    Dim xlApp As Object
    Dim xlWB As Object
    Dim xlSheet As Object
    Dim rCount As Long
    Dim bXStarted As Boolean
    Dim enviro As String
    Dim strPath As String

    Dim currentExplorer As Explorer
    Dim Selection As Selection
    Dim olItem As Outlook.MailItem
    Dim obj As Object
    Dim strColB, strColC, strColD, strColE, strColF, strColG As String

    ' Get Excel set up
    enviro = CStr(Environ("USERPROFILE"))
    'the path of the workbook
    strPath = enviro & "\Documents\test.xlsx"
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If Err 0 Then
    Application.StatusBar = "Please wait while Excel source is opened ... "
    Set xlApp = CreateObject("Excel.Application")
    bXStarted = True
    End If
    On Error GoTo 0
    'Open the workbook to input the data
    Set xlWB = xlApp.Workbooks.Open(strPath)
    Set xlSheet = xlWB.Sheets("Test1")
    ' Process the message record

    On Error Resume Next
    'Find the next empty line of the worksheet
    rCount = xlSheet.Range("B" & xlSheet.Rows.Count).End(-4162).Row

    ' get the values from outlook
    Set currentExplorer = Application.ActiveExplorer
    Set Selection = currentExplorer.Selection
    For Each obj In Selection

    Set olItem = obj

    'collect the fields
    strColB = olItem.senderName
    strColC = olItem.Application.ActiveExplorer.Caption
    strColD = olItem.Subject
    strColE = olItem.To
    strColF = olItem.ReceivedTime
    strColG = olItem.Body

    'write them in the excel sheet
    xlSheet.Range("B" & rCount) = strColB
    xlSheet.Range("C" & rCount) = strColC
    xlSheet.Range("D" & rCount) = strColD
    xlSheet.Range("E" & rCount) = strColE
    xlSheet.Range("F" & rCount) = strColF
    xlSheet.Range("G" & rCount) = strColG

    'Next row
    rCount = rCount + 1


    xlWB.Close 1
    If bXStarted Then
    End If

    Set olItem = Nothing
    Set obj = Nothing
    Set currentExplorer = Nothing
    Set xlApp = Nothing
    Set xlWB = Nothing
    Set xlSheet = Nothing
    End Sub

    • Diane PoremskyDiane Poremsky says

      It's hard to get the first paragraph, especially if you want the first real paragraph and not the first line.. well, not that hard, but you need to use word vba code to get it, which means you need to open the message. It'll flash on the screen for a split second as the macro opens it, reads the paragraph and closes it. The macro here: has the basics for using word objects.

      if you just wanted the first nn number of characters, you could do that using the Left function. It's definitely easier. Find function might work too - then you could find the first line break.

    • Diane PoremskyDiane Poremsky says

      Ok.. the Find function is not supported in Outlook. I'm not sure it would help much anyway - instr and len would be better to find the position of the first line break and get the text preceding it. This would avoid the need to open the message and eliminate the screen flash.

  29. Suhail Shaikh says

    I am trying to search for specific words in the attachement in an email & then move the email with those specific words in a folder. I tried Rules however it does not search the attachment but only the body of the email.

    Is RegEx an option to do this?


    • Diane PoremskyDiane Poremsky says

      No, not really - you need to open and search the attachment - once it's open, you could use other methods to search for the words.

  30. Suhail Shaikh says

    Many thanks for your prompt response.
    But when we search for the words in the searchbox, it also checks the attachment. Is there a way to do the same via a macros?

    • Diane PoremskyDiane Poremsky says

      No, not to my knowledge. Instant search using the Windows search index - the methods used in the macro search the folder or items directly.

Leave a Reply

Please post long or more complicated questions at OutlookForums by

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