VBA code to copy Outlook Messages to excel

[Ironman]

Hello - - I have a question that I don't know if it's possible. I'm looking to copy the content of emails coming from the same email address (about 5 to 10 per day) and paste them into an Excel file. The goal is to compare records counts...day to day to day...and then flag ones where the variance is plus or minus x number of records.

The email body is very basic, 4 lines: Date, Filename, File size and Record Count.

Here's an example of the message:

Date: Thu 15 Sep 2011 09:01:09 AM EDT
Filename: AUTO_C12345_20012356874.dat
File size: 162887
Record Count: 475


Is there some starting code someone might have to share that would get me started?

As I think of my question, I think I'm about to make it a bit more complicated. In addition of puting this data into a specific excel file, the excel file would be the same day after day, where the newest data is placed in the first empty row. The data elements of the email: Date, Filename, File Size and Record Count become headers, so I think theres some sort of transpose thing I would need to consider as well. The emails should also be from the same address, but the ones that are unopened and ignore the already opened emails. Again, any thoughts and/or suggestions would be great.

Wasn't sure if should have marked it solved or not - - I moved on and tried something else. Basically I took the 4 lines from the email and pasted them into a spreadsheet - - from there I ran and recorded a series of steps like: text to columns, copy/paste special/transpose, moved cell values to final columns and rows, deleted empty rows, sorted the file by filename...etc. I put all my recorded code together and ran them all as one macro and it did what I needed. The only thing I do manually is copy the original email and paste it into this excel file. Then I run my code, then repeat. So it's fast enough for my situation.

If anyone is interested, here is my code from Excel:

Sub DataExchangeEmails()

' Section A - this section will format via text to columns plus uses copy/paste special to Transpose for columns
'             Saves the group of data to the 50th row

 Selection.TextToColumns Destination:=Range("I1"), DataType:=xlFixedWidth, _
        OtherChar:="_", FieldInfo:=Array(Array(0, 1), Array(15, 1)), _
        TrailingMinusNumbers:=True
    Range("I1:L4").Select
    Selection.Copy
    Range("A50").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Range("A49").Select
    Range("H1").Select
    
' Section B - this will remove or delete any blank rows between row 50 and the last blank row above

Dim bye As Long

With ActiveSheet
    For bye = .Cells.SpecialCells(xlCellTypeLastCell).Row _
        To 1 Step -1

        If WorksheetFunction.CountA(.Rows(bye)) = 0 Then
            ActiveSheet.Rows(bye).Delete
        End If

    Next
End With

' Section C - this deletes the newest data exchange entries header row, searches for DATE: in column A and deletes it, starting in A2
 
Dim x As String
Dim b As Long
x = "Date:"
y = Cells(Rows.Count, 1).End(xlUp).Row
        For b = y To 2 Step -1
            If InStr(Cells(b, 1), x) > 0 Then ' the number here represents the column 1 being col A, 2 would be col B
            Rows(b).EntireRow.Delete
            End If
        Next b
MsgBox "Delete Complete"

' Secion D - this removes the original copied data values from the email that was copyed in I1.

Range("I1:L4").Select
    Selection.ClearContents
    
' section E - This resorts the rows by Filename

Range("g1").Select
    Range("A1").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B:B") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A:D")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub