CODEWITHSUNDEEP
excelvbscriptoutlook
eu127
eu127

Reputation: 123

Export Outlook e-mails content into Excel

I'm getting e-mail in format like: enter image description here

I used the code that someone sugested in other topic. Here is the code:

Const xlUp As Long = -4162

Sub ExportToExcel(MyMail As MailItem)
    Dim strID As String, olNS As Outlook.NameSpace
    Dim olMail As Outlook.MailItem
    Dim strFileName As String
    '~~> Excel Variables
    Dim oXLApp As Object, oXLwb As Object, oXLws As Object
    Dim lRow As Long

    strID = MyMail.EntryID
    Set olNS = Application.GetNamespace("MAPI")
    Set olMail = olNS.GetItemFromID(strID)

    '~~> Establish an EXCEL application object
    On Error Resume Next
    Set oXLApp = GetObject(, "Excel.Application")

    '~~> If not found then create new instance
    If Err.Number <> 0 Then
        Set oXLApp = CreateObject("Excel.Application")
    End If
    Err.Clear
    On Error GoTo 0

    '~~> Show Excel
    oXLApp.Visible = True

    '~~> Open the relevant file
    Set oXLwb = oXLApp.Workbooks.Open("C:\Sample.xlsx")

    '~~> Set the relevant output sheet. Change as applicable
    Set oXLws = oXLwb.Sheets("Sheet1")

    lRow = oXLws.Range("A" & oXLApp.Rows.Count).End(xlUp).Row + 1

    '~~> Write to outlook
    With oXLws
     Dim MyAr() As String

         MyAr = Split(olMail.Body, vbCrLf)

         For i = LBound(MyAr) To LBound(MyAr)
        '~~> This will give you the contents of your email
        '~~> on separate lines
         Debug.Print MyAr(i)
         Next i
        '
        '~~> Code here to output data from email to Excel File
        '~~> For example
        '
        .Range("D" & lRow).Value = olMail.Subject
        .Range("A" & lRow).Value = olMail.SenderName
        .Range("C" & lRow).Value = olMail.Body
        .Range("B" & lRow).Value = olMail.ReceivedTime
        '
    End With

    '~~> Close and Clean up Excel
    oXLwb.Close (True)
    oXLApp.Quit
    Set oXLws = Nothing
    Set oXLwb = Nothing
    Set oXLApp = Nothing

    Set olMail = Nothing
    Set olNS = Nothing
End Sub

I want to export the content of the email, subject, time of receiving, sender into an Excel file. I managed to export all those fields but I have problems with the content of the email. All those numbers are placed into one cell. Here is how it exports the content:enter image description here

And here is how I want to have the content: enter image description here

Or like this: enter image description here

Upvotes: 0

Views: 443

Answers (1)

Eugene Astafiev
Eugene Astafiev

Reputation: 49455

The Outlook object model provides three main ways for working with item bodies:

  1. Body - a string representing the clear-text body of the Outlook item.
  2. HTMLBody - a string representing the HTML body of the specified item.
  3. Word editor - the Microsoft Word Document Object Model of the message being displayed. The WordEditor property of the Inspector class returns an instance of the Document class from the Word object model which you can use to set up the message body.

You can read more about all these ways in the Chapter 17: Working with Item Bodies. It us up to you which way is to choose to parse the message body.

Upvotes: 1

Related Questions