Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VB Outlook Subject Line - Grab Number

Posted on 2007-07-24
16
Medium Priority
?
566 Views
Last Modified: 2008-02-01
Is is possible to grab a number from the subject of an Outlook message using VB Script? For example if I get a lot of messages that have the subject 10/02/123456 but the 123456 changes every message (it's an order number) but is always 8 characters in length and always follows that pattern.

If so, any one have an example or link. I want to take that number and the parse to another program but wasn't sure how to grab it first.

Thanks
0
Comment
Question by:thelarster
  • 6
  • 6
  • 4
16 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 19559615
Your example is not an 8 digit number :)

Does your subject have other text as well?
0
 

Author Comment

by:thelarster
ID: 19559651
Here's a 'real life' subject line example:  010/02/06869427 Elemica - Warning

The only thing that changes is the 8 digits after 02/

mathherwspatrick-that was a quck reply!
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 19559690
thelarster,

This function was written for VBA/VB6, but yu can adapt it for VBScript:



Function RegExpFind(LookIn As String, PatternStr As String, Optional Pos, _
    Optional MatchCase As Boolean = True)

    ' This function uses Regular Expressions to parse a string (LookIn), and return matches to a
    ' pattern (PatternStr).  Use Pos to indicate which match you want:
    ' Pos omitted               : function returns a zero-based array of all matches
    ' Pos = 0                   : the last match
    ' Pos = 1                   : the first match
    ' Pos = 2                   : the second match
    ' Pos = <positive integer>  : the Nth match
    ' If Pos is greater than the number of matches, is negative, or is non-numeric, the function
    ' returns an empty string.  If no match is found, the function returns an empty string
   
    ' If MatchCase is omitted or True (default for RegExp) then the Pattern must match case (and
    ' thus you may have to use [a-zA-Z] instead of just [a-z] or [A-Z]).
   
    ' If you use this function in Excel, you can use range references for any of the arguments.
    ' If you use this in Excel and return the full array, make sure to set up the formula as an
    ' array formula.  If you need the array formula to go down a column, use TRANSPOSE()
   
    Dim RegX As Object
    Dim TheMatches As Object
    Dim Answer() As String
    Dim Counter As Long
   
    ' Evaluate Pos.  If it is there, it must be numeric and converted to Long
    If Not IsMissing(Pos) Then
        If Not IsNumeric(Pos) Then
            RegExpFind = ""
            Exit Function
        Else
            Pos = CLng(Pos)
        End If
    End If
   
    ' Create instance of RegExp object
    Set RegX = CreateObject("VBScript.RegExp")
    With RegX
        .Pattern = PatternStr
        .Global = True
        .IgnoreCase = Not MatchCase
    End With
       
    ' Test to see if there are any matches
    If RegX.test(LookIn) Then
       
        ' Run RegExp to get the matches, which are returned as a zero-based collection
        Set TheMatches = RegX.Execute(LookIn)
       
        ' If Pos is missing, user wants array of all matches.  Build it and assign it as the
        ' function's return value
        If IsMissing(Pos) Then
            ReDim Answer(0 To TheMatches.Count - 1) As String
            For Counter = 0 To UBound(Answer)
                Answer(Counter) = TheMatches(Counter)
            Next
            RegExpFind = Answer
       
        ' User wanted the Nth match (or last match, if Pos = 0).  Get the Nth value, if possible
        Else
            Select Case Pos
                Case 0                          ' Last match
                    RegExpFind = TheMatches(TheMatches.Count - 1)
                Case 1 To TheMatches.Count      ' Nth match
                    RegExpFind = TheMatches(Pos - 1)
                Case Else                       ' Invalid item number
                    RegExpFind = ""
            End Select
        End If
   
    ' If there are no matches, return empty string
    Else
        RegExpFind = ""
    End If
   
    ' Release object variables
    Set RegX = Nothing
    Set TheMatches = Nothing
   
End Function


Use it like this:

MsgBox "The number is... " & RegExpFind(olMsg.Subject, "\d{8}", 1)

Regards,

Patrick
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 11

Expert Comment

by:L00M
ID: 19561605
Do you already have the code to extract the subject line? If so, we can provide the RegExp or other solution.
0
 

Author Comment

by:thelarster
ID: 19564606
No, I don't have the code to grab the subject. I am not sure how to implement the above code either. This seems a bit different than VB for Excel.

Thanks,
0
 
LVL 11

Expert Comment

by:L00M
ID: 19564706
Where do the emails reside? Are you using Outlook Express? Should we be looking to poll the DBX files? Or do you use Exchange? Or Hotmail? Etc...
0
 

Author Comment

by:thelarster
ID: 19564769
I'm using Outlook 2003 SP2 on my companies Exchange Server. The emails are in a sub folder of my Inbox.
0
 
LVL 11

Expert Comment

by:L00M
ID: 19564949
Let's start with trying the following script:

http://www.tek-tips.com/viewthread.cfm?qid=1147829&page=1

That should give you some important information, and some good starting points for creating your code. Let me know if that works for you.
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 19565058
thelarster,

Please give a detailed description of what you are trying to do.

Regards,

Patrick
0
 

Author Comment

by:thelarster
ID: 19565087
Patrick,

When I am in my Inbox and reading a message I want a script that grabs the order number (after I hit some key combo to run the script.) into a variable. Once I have that I can write some additional scripting to enter that order number into SAP and bring up the order. I get emails confirming there is a new order and this would save about 5 steps to bring that order up in SAP.

Here's a 'real life' subject line example:  010/02/06869427 Elemica - Warning

The only thing that changes is the 8 digits after 02/ which is the order number.
0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 1000 total points
ID: 19565254
thelarster,

This code is designed to run in Outlook when you have a mail item open.




Sub GrabSAP()

    Dim Msg As MailItem
    Dim OrderNum As String
   
    Set Msg = ActiveInspector.CurrentItem
   
    OrderNum = RegExpFind(Msg.Subject, "\d{8}", 1)
    If OrderNum = "" Then
        MsgBox "No order number found", vbCritical, "Invalid Entry"
        Exit Sub
    End If
   
    'put your SAP code here
   
    Set Msg = Nothing
   
End Sub

Function RegExpFind(LookIn As String, PatternStr As String, Optional Pos, _
    Optional MatchCase As Boolean = True)

    ' This function uses Regular Expressions to parse a string (LookIn), and return matches to a
    ' pattern (PatternStr).  Use Pos to indicate which match you want:
    ' Pos omitted               : function returns a zero-based array of all matches
    ' Pos = 0                   : the last match
    ' Pos = 1                   : the first match
    ' Pos = 2                   : the second match
    ' Pos = <positive integer>  : the Nth match
    ' If Pos is greater than the number of matches, is negative, or is non-numeric, the function
    ' returns an empty string.  If no match is found, the function returns an empty string
   
    ' If MatchCase is omitted or True (default for RegExp) then the Pattern must match case (and
    ' thus you may have to use [a-zA-Z] instead of just [a-z] or [A-Z]).
   
    ' If you use this function in Excel, you can use range references for any of the arguments.
    ' If you use this in Excel and return the full array, make sure to set up the formula as an
    ' array formula.  If you need the array formula to go down a column, use TRANSPOSE()
   
    Dim RegX As Object
    Dim TheMatches As Object
    Dim Answer() As String
    Dim Counter As Long
   
    ' Evaluate Pos.  If it is there, it must be numeric and converted to Long
    If Not IsMissing(Pos) Then
        If Not IsNumeric(Pos) Then
            RegExpFind = ""
            Exit Function
        Else
            Pos = CLng(Pos)
        End If
    End If
   
    ' Create instance of RegExp object
    Set RegX = CreateObject("VBScript.RegExp")
    With RegX
        .Pattern = PatternStr
        .Global = True
        .IgnoreCase = Not MatchCase
    End With
       
    ' Test to see if there are any matches
    If RegX.test(LookIn) Then
       
        ' Run RegExp to get the matches, which are returned as a zero-based collection
        Set TheMatches = RegX.Execute(LookIn)
       
        ' If Pos is missing, user wants array of all matches.  Build it and assign it as the
        ' function's return value
        If IsMissing(Pos) Then
            ReDim Answer(0 To TheMatches.Count - 1) As String
            For Counter = 0 To UBound(Answer)
                Answer(Counter) = TheMatches(Counter)
            Next
            RegExpFind = Answer
       
        ' User wanted the Nth match (or last match, if Pos = 0).  Get the Nth value, if possible
        Else
            Select Case Pos
                Case 0                          ' Last match
                    RegExpFind = TheMatches(TheMatches.Count - 1)
                Case 1 To TheMatches.Count      ' Nth match
                    RegExpFind = TheMatches(Pos - 1)
                Case Else                       ' Invalid item number
                    RegExpFind = ""
            End Select
        End If
   
    ' If there are no matches, return empty string
    Else
        RegExpFind = ""
    End If
   
    ' Release object variables
    Set RegX = Nothing
    Set TheMatches = Nothing
   
End Function



Regards,

Patrick
0
 
LVL 11

Expert Comment

by:L00M
ID: 19565342
Ah, I misunderstood.  Go with Patrick's code.
0
 

Author Comment

by:thelarster
ID: 19565526
Patrick - that worked! Thank you VERY much.

L00M - thanks for your contribution also.
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 19565565
thelarster,

Glad to help :)

Regards,

Patrick
0
 

Author Comment

by:thelarster
ID: 20176973
I'm trying to add to this script... is there a way to also grab the 1st 2 digit number in the body? The first line always reads Order :  010/02/06889132 for exaple. I need to grab the 02. I tried but it always grabs the 1st 2 digits (in this example 01)

Thanks!
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 20184712
thelarster,

With respect, you need to open a new question for this, and not try to wring more out of a
three month old question.

Regards,

Patrick
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
The viewer will learn how to user default arguments when defining functions. This method of defining functions will be contrasted with the non-default-argument of defining functions.
Suggested Courses
Course of the Month21 days, 1 hour left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question