none
Export data from Outlook in a specific format RRS feed

  • Question

  • Hi we receive tickets in the form of emails in a specific format.. i need to copy specific data (Marked in yellow) from outlook and create a excel file with respective headings.

    Subject: Sheldon S Misquitta

    ID : xxxxxxxx    *Movie Ticket* As of Date: 6/xx/xxxx 14:50:7                 

    TRDR/SLS   : XXXXXXX                    Settlement : 6/11/xxxx            

    BUYS       : 4xx (M)                            ISSUER:

    Security   : SCS 4 10/01/19                                                    

       Price   : xxx.292        Yield:0.069000    Yield to: 10/1/2019  at 100.00   

    Concession : 0                                                                  

                                                                                   

    Notes :                                                                        

                                                                                    

                                                      {83xxxxx    MI DES

    Principal             USD         453,168.00                                   

    Accrued         ( 89 days)          xxxx.56                                    

    Transaction Costs                       0.00                                   

    Total                 USD         xxx,xxx.56                                   

    Wednesday, July 2, 2014 8:53 PM

Answers

  • You can use standard text functions on the email body - create an array of what to look for and what the delimiters are:

    Sub TestMacro()
        Dim strBody As String
        Dim strFound As String
        Dim strFind() As Variant
        Dim i As Integer
        Dim j As Integer
        Dim k As Integer
        
        'Array of "what to find" followed by "delimiter" that determines end of value
        strFind = Array("Subject: ", Chr(10), "Date:", Chr(10), "TRDR/SLS   : ", "      ", "Settlement :", Chr(10))
        
        'strBody = objMail.Body
        'Example for testing
        strBody = "Subject: Sheldon S Misquitta" & Chr(10) & _
        "ID : xxxxxxxx    *Movie Ticket* As of Date: 6/xx/xxxx 14:50:7" _
        & Chr(10) & "TRDR/SLS   : Val TRDR/SLS                    Settlement : 6/11/2014            " & _
        Chr(10) & "BUYS       : 4xx (M)                            ISSUER:"
        
        For k = 0 To UBound(strFind) - 1 Step 2 'Things in order of needing to be found
            i = InStr(1, strBody, strFind(k))
            j = InStr(1, strBody, strFind(k + 1))
            strFound = Trim(Mid(strBody, i + Len(strFind(k)), j - i - Len(strFind(k))))
            'Get rid of the already found information
            strBody = Mid(strBody, j + 1)
            
            MsgBox strFind(k) & "      """ & strFound & """"
        Next k
      
    End Sub

    Thursday, July 3, 2014 3:58 PM
  • There's a long running thread in this forum -
     Export Content From Outlook 2010 Emails To Excel Spreadsheet

    which covers just about every option for extracting data from e-mail messages to Excel


    Graham Mayor - Word MVP
    www.gmayor.com

    Sunday, July 6, 2014 11:13 AM

All replies

  • You can use standard text functions on the email body - create an array of what to look for and what the delimiters are:

    Sub TestMacro()
        Dim strBody As String
        Dim strFound As String
        Dim strFind() As Variant
        Dim i As Integer
        Dim j As Integer
        Dim k As Integer
        
        'Array of "what to find" followed by "delimiter" that determines end of value
        strFind = Array("Subject: ", Chr(10), "Date:", Chr(10), "TRDR/SLS   : ", "      ", "Settlement :", Chr(10))
        
        'strBody = objMail.Body
        'Example for testing
        strBody = "Subject: Sheldon S Misquitta" & Chr(10) & _
        "ID : xxxxxxxx    *Movie Ticket* As of Date: 6/xx/xxxx 14:50:7" _
        & Chr(10) & "TRDR/SLS   : Val TRDR/SLS                    Settlement : 6/11/2014            " & _
        Chr(10) & "BUYS       : 4xx (M)                            ISSUER:"
        
        For k = 0 To UBound(strFind) - 1 Step 2 'Things in order of needing to be found
            i = InStr(1, strBody, strFind(k))
            j = InStr(1, strBody, strFind(k + 1))
            strFound = Trim(Mid(strBody, i + Len(strFind(k)), j - i - Len(strFind(k))))
            'Get rid of the already found information
            strBody = Mid(strBody, j + 1)
            
            MsgBox strFind(k) & "      """ & strFound & """"
        Next k
      
    End Sub

    Thursday, July 3, 2014 3:58 PM
  • There's a long running thread in this forum -
     Export Content From Outlook 2010 Emails To Excel Spreadsheet

    which covers just about every option for extracting data from e-mail messages to Excel


    Graham Mayor - Word MVP
    www.gmayor.com

    Sunday, July 6, 2014 11:13 AM