VBA Code To Read Email

[MikeG]

I receive an email each day that has a table of data in the body of the email. The table has been created by some application - I'm not sure which (not Excel or Access). What I do every day is to copy the table using Ctrl c and then paste it into Excel using Ctrl v.

What I would like to do is automate this such that I hit a button in Excel and a macro would then go to the email, recognize the table, copy it, and paste to Excel.

But is this even possible? For example, can VBA code go to Outlook and locate a specified email? (I could give it the sender name and the subject line). Could the macro then find the table - (it always has the same header titles)? Also, the table length can vary, so the macro would have to deal with that.

If anyone can tell me if this is possible, that would be great.

Thanks,

MikeG

 

[Trevor G]

Mike I do believe it is possible, but of course where to find the extra code to specify from who and manage the table is the key.

Here is some code to list in Excel all emails in the Inbox.

Sub ListAllItemsInInbox()
'Set References to Outlook via the Tools Menu
'References and search down the list for Microsoft Outlook XX .Object Library
'The below code will Add new workbook and Add subject Headings
'Date time received, State if they have attachments,and indicate if Read
Dim OLF As Outlook.MAPIFolder, CurrUser As String
Dim EmailItemCount As Integer, i As Integer, EmailCount As Integer
Application.ScreenUpdating = False
Workbooks.Add ' create a new workbook
' add headings
Cells(1, 1).Formula = "Subject"
Cells(1, 2).Formula = "Recieved"
Cells(1, 3).Formula = "Attachments"
Cells(1, 4).Formula = "Read"
With Range("A1:D1").Font
.Bold = True
.Size = 14
End With
Application.Calculation = xlCalculationManual
Set OLF = GetObject("", _
"Outlook.Application").GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
EmailItemCount = OLF.Items.Count
i = 0: EmailCount = 0
' read e-mail information
While i < EmailItemCount
i = i + 1
If i Mod 50 = 0 Then Application.StatusBar = "Reading e-mail messages " & _
Format(i / EmailItemCount, "0%") & "..."
With OLF.Items(i)
EmailCount = EmailCount + 1
Cells(EmailCount + 1, 1).Formula = .Subject
Cells(EmailCount + 1, 2).Formula = Format(.ReceivedTime, "dd.mm.yyyy hh:mm")
Cells(EmailCount + 1, 3).Formula = .Attachments.Count
Cells(EmailCount + 1, 4).Formula = Not .UnRead
End With
Wend
Application.Calculation = xlCalculationAutomatic
Set OLF = Nothing
Columns("A:D").AutoFit
Range("A2").Select
ActiveWindow.FreezePanes = True
ActiveWorkbook.Saved = True
Application.StatusBar = False
End Sub

Possible good link

http://www.techrepublic.com/blog/msoffice/quickly-export-outlook-e-mail-items-to-excel/744

 

[MikeG]

Mike I do believe it is possible, but of course where to find the extra code to specify from who and manage the table is the key.

Here is some code to list in Excel all emails in the Inbox.

Thanks Trevor - that puts me on the way.

MikeG