Last reviewed on July 28, 2015   —  92 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.

Or you can use Regular Expressions.
Extract codes from email messages

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.
 

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

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


Comments

  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?

    Thanks,

    Tim

    • 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 ID : VBNSA-XXXXXX
    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?

    THANKS AGAIN!

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

    PO#
    --------
    123

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

    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 ?

    • 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

    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

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

    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

    • 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

    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

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

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

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

      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

  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,
    Alex

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

    • 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?

    Thanks
    Sun

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

    Thanks,
    Ramaswamy

    • 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
      .Save
      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?

    Thanks!

    • 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: http://www.slipstick.com/developer/vba-copy-outlook-email-excel-workbook/

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

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

      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"

    • 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?

      Thanks!

  20. Ryan says

    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

    • 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 - http://www.slipstick.com/developer/vba-copy-outlook-email-excel-workbook/

  21. Nathan says

    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)

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

  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
    E-Mail: rasmus@jeannedarcliving.dk

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

    • 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

    Next

    xlWB.Close 1
    If bXStarted Then
    xlApp.Quit
    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: http://www.slipstick.com/developer/word-macro-apply-formatting-outlook-email/ 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

    Hi,
    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?

    Regards,

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

  31. Bill Castoro says

    Diane, I am wondering if you can help. I receive order confirmations and the email address of my customer is in the body of the e-mail, I wanted to setup a rule that outlook will than email them back with a link to a specific website to go to. I am sure it can be done, I am just not that good with this stuff. The email address is always in the same place in the email. Instead of changing my whole ordering system to have autoresponders, I rather just have outlook send the messages.

    • Diane PoremskyDiane Poremsky says

      As long as its either the only email address or always either the first or last, you can use regex to find it. .Global = True tells the code to keep looking after the first instance is found; false tells it to stop.
      This page in my forum has a similar example. This edit of that code should do what you need it to do.

      Sub SendLink(Item As Outlook.MailItem)
      Dim Reg1 As RegExp
      Dim M1 As MatchCollection
      Dim M As Match
      Set Reg1 = New RegExp

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

      If Reg1.test(Item.Body) Then
      Set M1 = Reg1.Execute(Item.Body)
      For Each M In M1
      strAlias = M
      Next
      End If

      Dim objMsg As MailItem

      Set objMsg = Application.CreateItem(olMailItem)

      With objMsg
      .Recipients.Add strAlias
      .Subject = "New Subject Title"
      .Display '.Send

      End With

      Set objMsg = Nothing

      End Sub

  32. Sandy says

    Hi Ms Diane,

    Can you suggest a patter with with the info below:

    Shipping Line’s Cut-off Date/Time :
    20/05 (17:00H)

    For ESI submission?
    N

    For SI submission to SS/Line Website?
    Y

    For AMS? If yes, Last Port of call:
    N

    That is what my outlookitem.body have when I display it using the messagebox.
    I need to get the "Shipping Line’s Cut-off Date/Time" which is "20/05 (17:00H)"
    can you help me sort it out? here is the pattern I tried so far:
    .pattern = "Shipping Line’s Cut-off Date/Time\s*[:]+\s*(\w*)"

    • Sandy says

      I tried this but no luck

      Case 1
      .Pattern = "(Shipping Line's Cut-off Date/Time\s[:]\s(\w*))\n"
      .Global = False
      Case 2
      .Pattern = "(SHIPPING LINE'S CUT-OFF DATE/TIME\s[:]\s(\w*))\n"
      .Global = False
      Case 3
      .Pattern = "(DRAFT SHIPPING LINE CUT-OFF\s[-]\s(\w*))\n"
      .Global = False
      Case 4
      .Pattern = "(Shipping Line's Cut-off Date/Time[:]\s(\w*))\n"
      .Global = False
      End Select

    • Diane PoremskyDiane Poremsky says

      Is the text you want on a second line or the same line? Something like this should work. \s matches white space - including line breaks. If not, try \r or \n.
      .pattern = "Shipping Line’s Cut-off Date/Time"\s[:]\s(.*)"

  33. jonathandire says

    Howdy Diane,

    As always, love your work. Your Slipstick knowledge has taught me so much, making me excel beyond that of most Outlook Experts in the company I work for.

    People are now coming to be in droves to use my macros I adopted and modified off you.

    I have one last request regarding Regular Expressions.

    For the last 6 months I've been trying to figure it out by myself, how to extract expressions from the body of an email and change the subject line. Unfortunately, I get stuck with the result always just adding "; 2015." at the end of the subject line (rather than replace the subject.

    The email I've been working with:

    Shock Case Recipient: Awesometeam
    Correspondence Date (DD/MM/YYYY): 15/06/2015
    Customer concern(s): Pain
    Correspondence type: Phone
    Customer confirm Date (DD/MM/YYYY): 15/06/2015
    Due Date: 06/07/2015

    Customer's details:
    Name: PETER PETRELLI

    Address:
    123 Road Rd
    SUBURB, QLD, 6000

    Please review.

    Resources:
    Case Id 123456789

    What I am looking for is regex to extract the following fields and replace the subject line in capitals:

    Due date - Case recipient - Customer Concern - Name - Suburb (Case Id)

    So if the code were to work, the subject line would look like:

    06/07/2015 - SHOCK - PAIN - PETER PETRELLI - SUBURB (Case 123456789)

    As you can see, the 'Shock' Case recipient is before the actual field.

    I can paste the code I've made, however it is currently full of errors.

    Can you pretty please assist?

    • Diane PoremskyDiane Poremsky says

      Replacing the subject would use .Subject = testSubjectformat. The sample under the Get two (or more) values from a message heading shows how put the string together - you just need the right patterns to get the values you need. The cases will be in the order you want the values to appear in the subject. (Reverse order would work too.) If you want everything in caps, use UCase(testSubject). If you don't want Case capitalized, it's a little more difficult as you need to assign each pattern to a string then put them together.

    • Diane PoremskyDiane Poremsky says

      Use something like this to add the results to different strings then you can merge the strings .subject = UCase(strOne) & " - " & strTwo...

      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)
      If i = 1 Then strOne = strSubject
      If i = 2 Then strTwo = strSubject
      If i = 3 Then strThree = strSubject
      Debug.Print i & " " & strSubject
      Next
      End If

      you could make the subject less confusing by setting the case & adding the dashes when you assign the result to the variable
      If i = 1 Then strOne = ucase(strSubject) & " - "

      then the subject would be
      .subject = strOne & strTwo

  34. Eli Lancry says

    Hi Diane,

    Thanks for great article!
    Is it possible to capture the hyperlink of a string in a message?
    Usually I save attachments in incoming messages to my computer with a vba code that I found elsewhere that removes the attachment from the message and saves it to a prefered folder leaving the following text:
    "The attachment file(s) were saved to:
    image002.png
    image001.png
    ATT00001.txt"
    Each file name has a hyperlink. I am interested with capturing the hyperlink of each file name.
    Is it possible?

    Thanks in advance

    Eli

    • Diane PoremskyDiane Poremsky says

      Should be able to - i added Debug.Print olMail.Body right after the set olmail line and the results in the immediate window (turn it on in the View menu) included the underlying links. Using If Reg1.test(olMail.HTMLBody) would get you the raw HTML but as long as .Body works, I'd use it instead.

  35. Uday says

    Hi,

    I am using the following code in debug mode and see how it works so that later I can modify it as per my requirement but it throws "Object doesn't support this property...." at Set olMail = Application.ActiveExplorer().Selection(1) line, I guess I am doing something wrong, please help me. Will it not run if I use in debug mode?

    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

    • Diane PoremskyDiane Poremsky says

      Are you running it on messages? This references the selected item: Set olMail = Application.ActiveExplorer().Selection(1) and this Dim olMail As Outlook.mailItem says its a mailitem.

  36. viraj21viraj says

    hi
    i need help in using vba
    so vba can read my email subject and body and reply to the email and add an atachment from my desktop by doing a key word search
    inform me anout what actions have been taken
    which emails have been left out
    regards
    if any body does feel like helping me with the code please email me at reolite2012@gmail.com
    thanks in advance

Leave a Reply

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

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