none
get data from email body

    Question

  • hi guys,

    i have a code which looks in for data in my email body and gets them out into an excel page, it works great, now i need the same thing, but for another kind of email, & do other kind stuff, and its not working, seems to me that the email body is a jpg mixed with links & text, i really dont know how to deal with it, in basic, its a email that i get from UPS with tracking & shipment information, in my view the body of the email looks like an image, but when i go to some parts in the middle of it, it looks like text fields,

    what i need is, to look in the body of the email for my po number which is right after "Reference Number 1:"

    then take the number, and search in my excel sheet for that po number, when find, paste the tracking number on the next cell.

    here is some of my code, and it dont get anything,

    For a = 1 To Len(MyMail.Body)
         If Right(Left(MyMail.Body, a), 1) = "Reference Number 1:" Then
         MsgBox "FOUND PO"
         End If
         Next a

    now, i tried a different style of code and it didnt get either, here you go my next code,

    Dim MyTracking As String
    Dim vText As Variant
    Dim sText As String
    Dim vItem As Variant
        sText = MyMail.Body
        vText = Split(sText, Chr(13))
    
    
        'Check each line of text in the message body
        For i = UBound(vText) To 0 Step -1
            If InStr(vText(i), "Reference Number 1:") > 0 Then
                 MsgBox "FOUND PO"
         End If
    next i
    

    can anyone help me with this?

    maybe there is a way to convert the whole body email to text, and then look up for the value?

    Tuesday, December 25, 2012 3:42 AM

Answers

  • Hi Auto2,

    I don't consider that you will use String value in your body, it my mistake. OK, Let's see whether following code will work for you:

    Sub test()
        Dim olMail As Outlook.MailItem
        Dim Reg1 As Object
        Dim M1 As Object
        Dim M As Object
            
        Set olMail = Application.ActiveExplorer().Selection(1)
        Debug.Print "==========Begin Test=========="
        
        Set Reg1 = CreateObject("VBScript.RegExp")
        
        With Reg1
            .Pattern = "(Reference Number\s*(\d*)\s*[:]+\s*([\S \s]+?)\s*\n+)"
            .Global = True
        End With
        If Reg1.test(olMail.Body) Then
            Set M1 = Reg1.Execute(olMail.Body)
            For Each M In M1
                'The M.SubMatches(1) is the first number and M.SubMatches(2) is the second
                'Please write the code to populate Excel documnent by yourself
                Debug.Print M.Value
                Debug.Print M.SubMatches(1) & "->" & M.SubMatches(2)
            Next
        End If
        Debug.Print "----------End   Test----------"
    End Sub

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, December 31, 2012 4:47 AM
    Moderator

All replies

  • simplest thing would be getting .Body property of mailitem and do simple string search there for your keywords (Reference Number 1:). Next thing will be your number value. As for excel part i assume you everything is working?
    Tuesday, December 25, 2012 10:42 AM
  • thats my question, how do i play around with the email property? i never played with it, can you give me some example?
    Tuesday, December 25, 2012 3:28 PM
  • ok, i see now where the problem is, lets see if i can get any help,

    when i use this code;

      Dim vText As Variant
    Dim sText As String
    Dim vItem As Variant
        sText = MyMail.Body
        vText = Split(sText, Chr(13))
        'Find the next empty line of the worksheet
    
    
    
        'Check each line of text in the message body
        For i = UBound(vText) To 0 Step -1
            If InStr(1, vText(i), "Reference Number 1:") > 0 Then
              MsgBox "found po"
                vItem = Split(vText(i), Chr(58))
                xlSheet.Range("J2") = Trim(vItem(1))
                
            End If
        Next i
    

    as you see i put in the msgbox to see if it finds what i am looking for, and the good news is that it finds it, but the bad news is that it returns no value, i checked out why, and i saw that the po number what i need, which is after Reference Number 1: is not in the same array, it uses a different column, so thats why i get an empty line, how can i handle that?

    Tuesday, December 25, 2012 7:49 PM
  • here is what i figured, after taking one example like 

    Reference Number 1:

    3027

    i copyed it, and paste it into excel, i saw that it came in Reference Number 1: in cell A1 & 3027 came in cell A2,

    so i tried doing offset(0,1) & it didnt help, i tried doing this code;

        For i = UBound(vText) To 0 Step -1
            If InStr(1, vText(i), "Reference Number 1:") > 0 Then
              MsgBox "found po"
              
              
               
                xlSheet.Range("J2") = vText(2)

    i got the message that it found the po, but the value still dont come in, but if i do

    xlSheet.Range("J2") = vText(i)

    then i get " refrence number 1:" in excel, but i cant get the number,

    hope someone can get me some explanation how i can get the value from the other array,


    • Edited by Auto2 Wednesday, December 26, 2012 1:47 AM
    Wednesday, December 26, 2012 1:47 AM
  • Hi Auto2,

    Thanks for posting in the MSDN Forum.

    I would recommend you use Text Regular Expressions to handle your issue. It will let your issue more easier. For example: It your Body formatted like "Reference Number 1: xxxx" I will use following code to retrieve the value:

            Dim str As String = "Reference Number 1: test" & Chr(13) & "Reference Number 2: Maybe"
            Dim regex As Regex = New Regex("(Reference Number (\d): (\S+))")
            For Each m As Match In regex.Matches(str)
                'Group 2 point to the number of the reference, Group 3 refernece to the content after :
                Console.WriteLine(m.Groups(2).Value & " " & m.Groups(3).Value)
            Next

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, December 26, 2012 7:06 AM
    Moderator
  • thanks Tom_xu for your respond, 

    i paste it in to my code and the whole code got red, meaning there is errors on the code, i start playing around with it the much i can, but still getting errors, i start searching for that, as far i understand is VBA not working with regex, i think its a vbscript argument, i might be wrong, i am not familiar with this code, maybe a reference will help?

    but i think i need to create an object for it, like

    regex = CreateObject("VBScript.RegExp")

    but since i dont have vbscript, did you have any solution?

    Wednesday, December 26, 2012 2:47 PM
  • Hi Auto2,

    My code is comes from a Outlook add-in due to I suppose you will handle your issue under that. It seems that you need a VBA code snippet for this issue, is it right?

    Sub test()
        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
        
        With Reg1
            .Pattern = "(Reference Number\s*(\d*)\s*[:]+\s*(\d*)\s*)"
            .Global = True
        End With
        If Reg1.test(olMail.Body) Then
            Debug.Print "Match"
            Set M1 = Reg1.Execute(olMail.Body)
            For Each M In M1
                'The M.SubMatches(1) is the first number and M.SubMatches(2) is the second
                'Please write the code to populate Excel documnent by yourself
                Debug.Print M.SubMatches(1) & "->" & M.SubMatches(2)
            Next
        End If
        
    End Sub

    Before you run this macro, please remember add "Microsoft VBScript Regular Expressions 5.5" Library from VBA IDE's menu "Tools"->"References..."

    If you won't use that, the thing will be more complex. You can see the expression I write to match the text, you will see there have some "\s*" to reflect to the invisible characters such as line break, blank space character, tabs character etc. in order to write correct condition to access the value which you want, you need write sub to find out such kind of invisible characters from the position which I figure out in the expression.

    I hope it can help you.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Thursday, December 27, 2012 7:26 AM
    Moderator
  • thanks again tom_xu, it was the refrence as i mentioned before, now things are moving, still getting some errors, but i will try to work around with it, it seems this will do the work, i will keep here updated with my code when i finnish,

    p.s. and if course i will mark you as answer when its done. but let me just make sure everything is working.

    Thursday, December 27, 2012 2:11 PM
  • i did some workaround with the code, but it return only  " refrence number 1:" but i dont get the number after that ????

    still like i was before

        Dim Reg1 As RegExp
        Dim M1 As MatchCollection
        Dim M As Match
            
        Debug.Print MyMail.Body
        
        Set Reg1 = New RegExp
        
        With Reg1
            .Pattern = "(Reference Number\s*(\d*)\s*[:]+\s*(\d*)\s*)"
            .Global = True
        End With
        If Reg1.test(MyMail.Body) Then
            Debug.Print "Match"
            Set M1 = Reg1.Execute(MyMail.Body)
            For Each M In M1
                'The M.SubMatches(1) is the first number and M.SubMatches(2) is the second
                'Please write the code to populate Excel documnent by yourself
            Debug.Print M.SubMatches(1) & "->" & M.SubMatches(2)
            xlSheet.Range("J2") = M.Value
            Next
        End If

    Thursday, December 27, 2012 6:11 PM
  • Hi Auto2,

    It will make more sense to share your sample mail for further research. the expression which I provide was fit on the sample which created on my side. It might have some different with yours.

    Following is the screen shooting for my sample mail:

    Hope it can help you for trouble shooting.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, December 28, 2012 5:32 AM
    Moderator
  • here is my email, its coming in as an Html.

    Friday, December 28, 2012 6:13 PM
  • Hi Auto2,

    I don't consider that you will use String value in your body, it my mistake. OK, Let's see whether following code will work for you:

    Sub test()
        Dim olMail As Outlook.MailItem
        Dim Reg1 As Object
        Dim M1 As Object
        Dim M As Object
            
        Set olMail = Application.ActiveExplorer().Selection(1)
        Debug.Print "==========Begin Test=========="
        
        Set Reg1 = CreateObject("VBScript.RegExp")
        
        With Reg1
            .Pattern = "(Reference Number\s*(\d*)\s*[:]+\s*([\S \s]+?)\s*\n+)"
            .Global = True
        End With
        If Reg1.test(olMail.Body) Then
            Set M1 = Reg1.Execute(olMail.Body)
            For Each M In M1
                'The M.SubMatches(1) is the first number and M.SubMatches(2) is the second
                'Please write the code to populate Excel documnent by yourself
                Debug.Print M.Value
                Debug.Print M.SubMatches(1) & "->" & M.SubMatches(2)
            Next
        End If
        Debug.Print "----------End   Test----------"
    End Sub

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, December 31, 2012 4:47 AM
    Moderator
  • hi, Tom xu,

    i am sorry i didnt had time to read your post till now, i went overseas for 10 days, i just checked the forum & i read your post, i tried it out, and yes, it works now like a charm!

    thank you very much for your help, and for your time & efforts,

    have a happy new year!

    Wednesday, January 02, 2013 2:39 AM