none
Export a specific part out of the email body RRS feed

  • Question

  • I want to export specific text out of the email's body into an excel file.

    For example:

    Good morning.

    I am XXX from this company.

    ----------------------------------------------------------------------

    Name: aaa

    Company: bbb

    Job scope: ccc

    ----------------------------------------------------------------------

    Thank you

    I only want to export the part after the "----------------" into the excel file.

    Thank you, Derrick

    Monday, July 27, 2015 1:28 AM

Answers

  • According to your description, You can create a new module in Outlook's vba editor then change the path of the worksheet as appropriate at the start of the macro. Select the messages you wish to process and run the macro.Note in Outlook 2010 you may have to use selfcert.exe to provide a digital signature in order to run the macro and need to set a reference (Menu: Tools->References) to the Microsoft Excel Object Library then you can access all Excel Objects.

    >>>Good morning.

    I am XXX from this company.

    ----------------------------------------------------------------------

    Name: aaa

    Company: bbb

    Job scope: ccc

    ----------------------------------------------------------------------

    >>>Thank you

    You can refer to below code, but you need to write your logic to handle the MainItem.Boby to get string value with String Functions (Visual Basic).

     Sub CopyToExcel()
    
     Dim xlApp As Excel.Application
    
     Dim xlWB As Excel.Workbook
    
     Dim xlSheet As Excel.Worksheet
    
     Dim olItem As Outlook.MailItem
    
     Dim vText As Variant
    
     Dim vPara As Variant
    
     Dim sText As String
    
     Dim vItem As Variant
    
     Dim oRng As Range
    
     Dim i As Long
    
     Dim rCount As Long
    
     Dim sLink As String
    
     Dim bXStarted As Boolean
    
     'change your path of the workbook
    
     Const strPath As String = "xxxx.xlsx"
    
     
    
     If Application.ActiveExplorer.Selection.Count = 0 Then
    
         MsgBox "No Items selected!", vbCritical, "Error"
    
         Exit Sub
    
     End If
    
     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)
    
     'name of Sheet
    
     Set xlSheet = xlWB.Sheets("SheetName")
    
     
    
     'Process each selected MailItem
    
     For Each olItem In Application.ActiveExplorer.Selection
    
         sText = olItem.Body
    
         vPara = Split(sText, Chr(13))
    
         'you could use string method to get data base on your requirement
    
         For i = 0 To UBound(vPara)
    
            'write string value to spreadsheet, you can refer to below code
    
            'xlSheet.Range("A1") = "Name value"
    
            '......
    
         Next i
    
         xlWB.Save
    
     Next olItem
    
     xlWB.Close SaveChanges:=True
    
     If bXStarted Then
    
         xlApp.Quit
    
     End If
    
     Set xlApp = Nothing
    
     Set xlWB = Nothing
    
     Set xlSheet = Nothing
    
     Set olItem = Nothing
    
     End Sub
    
    For more information, click here to refer about String Functions (Visual Basic)

    Thursday, July 30, 2015 8:22 AM