Extract Outlook body to Excel VBA

Question

after searching multiple things, and getting errors How do I upon pressing "f5" in a vba script copy the body of an email into an excel sheet /csv where every line = a new cell below.

Thanks

Sorry, this is causing me nothing but trouble. What I have tried so far http://smallbusiness.chron.com/export-outlook-emails-excel-spreadsheets-41441.html

How to copy Outlook mail message into excel using VBA or Macros

http://www.vbforums.com/showthread.php?415518-RESOLVED-outlook-the-macros-in-this-project-are-disabled

http://www.ozgrid.com/forum/showthread.php?t=181512

and a few more, last year.

Jeanno Jeanno · Accepted Answer · 2015-02-13T14:50:53

This will work for you. we are basically splitting the email body into an array based on a new line. Notice that this will yield blank cells if you had a blank line in the email body.

Public Sub SplitEmail() ' Ensure reference to Word and Excel Object model is set
    Dim rpl As Outlook.MailItem
    Dim itm As Object
    Set itm = GetCurrentItem()
    If Not itm Is Nothing Then
        Set rpl = itm.Reply
        rpl.BodyFormat = olFormatHTML
        'rpl.Display
    End If
    Dim objDoc As Word.Document
    Set objDoc = rpl.GetInspector.WordEditor
    Dim txt As String
    txt = objDoc.Content.text
    Dim xlApp As Excel.Application
    Set xlApp = CreateObject("Excel.application")
    xlApp.Visible = True
    Dim wb As Excel.Workbook
    Set wb = xlApp.Workbooks.Add
    Dim i As Long
    For i = LBound(Split(txt, Chr(13)), 1) To UBound(Split(txt, Chr(13)), 1)
        wb.Worksheets(1).Range("A" & i + 1).Value = Split(txt, Chr(13))(i)
    Next i
End Sub
Function GetCurrentItem() As Object
    Dim objApp As Outlook.Application
    Set objApp = Application
    On Error Resume Next
    Select Case TypeName(objApp.ActiveWindow)
    Case "Explorer"
    Set GetCurrentItem = objApp.ActiveExplorer.Selection.Item(1)
    Case "Inspector"
    Set GetCurrentItem = objApp.ActiveInspector.CurrentItem
    End Select
    GetCurrentItem.UnRead = False
    Set objApp = Nothing
End Function

Extract Outlook body to Excel VBA

2 Answers