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.

What do you have so far? Have you tried anything? – SierraOscar Feb 13 '15 at 12:55 — SierraOscar, Feb 13 '15 at 12:55

score 1 · Accepted Answer · answered Feb 13 '15 at 14:50

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

I appreciate the help, where, how does this save to? also I get a compile error at dim objdoc as word.document — Denslat, Feb 13 '15 at 16:04
You need to set reference to Word and Excel object libraries — Jeanno, Feb 13 '15 at 17:33
no in the VBA editor under tools > references> Check Microsoft Word Object Library and Microsoft Excel Object Library. See the following link https://www.google.com/search?q=vba+editor+reference&source=lnms&tbm=isch&sa=X&ei=4lfeVKqLDIOAUfHSgbAB&ved=0CAgQ_AUoAQ&biw=1920&bih=969#imgdii=_&imgrc=ZBvUMtpOUzc4UM%253A%3Bb_xwoxr3aMtsYM%3Bhttp%253A%252F%252Fpragmateek.com%252Fwp-content%252Fuploads%252F2013%252F02%252Fvba_tools_references.png%3Bhttp%253A%252F%252Fpragmateek.com%252Fexcel-addins-troubleshooting-guide%252F%3B760%3B432 — Jeanno, Feb 13 '15 at 20:02
Got back to work today, and followed your advice, worked perfectly, thank you — Denslat, Feb 16 '15 at 12:44

score 0 · Answer 2 · answered Feb 13 '15 at 13:13

The Outlook object model doesn't recognize lines in the body. You can try to resize any inspector window in Outlook and see how the body lines are changed.

Anyway, you may try to use the Word object model to get the exact lines. Outlook uses Word as an email editor. The WordEditor property of the Inspector class returns an instance of the Document class which represents the message body. You can read more about all possible ways in the Chapter 17: Working with Item Bodies article.

The How to automate Microsoft Excel from Visual Basic article explains how to automate Excel from any external application.

Extract Outlook body to Excel VBA

2 Answers2