none
Export Outlook eMail content sorted in a table onto Excel RRS feed

  • Question

  • Hi, 

    I'm trying to automate an Excel tracker by using the data sent by email in a table format. So far, I've been able to write a simple code to extract the subject line, eMail date, sender, and eMail text. The code doesn't allow me to obtain specific data below from the table in my email. Would anyone be able to help me? 

    Email table format:

    ERP# 51635619
    DEL# 3896139839
    PO# VEO_CAN_0111557
    TOPIC Order Tracking


    Here's my VBA code:

                            

    Sub GetFromOutlook()

    Dim OutlookApp As Outlook.Application
    Dim OutlookNamespace As Namespace
    Dim folder As MAPIfolder
    Dim OutlookMail As Variant
    Dim i As Integer
    Dim Item As Object


    Set myolApp = CreateObject("Outlook.Application")
    Set OutlookApp = New Outlook.Application
    Set OutlookNamespace = OutlookApp.GetNamespace("MAPI")
    Set folder = OutlookNamespace.GetDefaultFolder(olFolderInbox).folders("Project")

        i = 1

        For Each OutlookMail In folder.Items
            If OutlookMail.ReceivedTime >= Range("From_date").Value Then
                Range("eMail_subject").Offset(i, 0).Value = OutlookMail.Subject
                Range("eMail_date").Offset(i, 0).Value = OutlookMail.ReceivedTime
                Range("eMail_sender").Offset(i, 0).Value = OutlookMail.SenderName
                Range("eMail_text").Offset(i, 0).Value = OutlookMail.Body

                i = i + 1
            End If
        Next OutlookMail

        'Text Wrap for Email text
        Columns("D:D").Select
            With Selection
                .HorizontalAlignment = xlGeneral
                .VerticalAlignment = xlBottom
                .WrapText = True
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .ReadingOrder = xlContext
                .MergeCells = False
            End With
            With Selection
                .HorizontalAlignment = xlGeneral
                .VerticalAlignment = xlBottom
                .WrapText = False
                .Orientation = 0
                .AddIndent = False
                .IndentLevel = 0
                .ShrinkToFit = False
                .ReadingOrder = xlContext
                .MergeCells = False
            End With

    Set folder = Nothing
    Set OutlookNamespace = Nothing
    Set OutlookApp = Nothing

    End Sub

    Thank you in advance!! Any tips would be appreciated. 

    Thursday, July 4, 2019 8:57 PM

All replies

  • I have covered how to do this several times in this forum. The salient points are covered at https://www.gmayor.com/extract_data_from_email.htm

    Graham Mayor - Word MVP
    www.gmayor.com

    Saturday, July 6, 2019 8:29 AM