How to extract content from an E-Mail (Outlook) to an excel table?

Question

I am trying to extract the content from an E-Mail in Outlook to an Excel table via VBA.
The E-Mail is for holiday management.
In the subject there are always the keywords "Accepted holiday - Mr. James" Mr. James is the name of the employee, which holidays were accepted. So the keywords "Accepted holiday" is always the same, but the name always changes.
The E-Mail contains a long table, but there is only the end needed. Maybe it'S the best, if it is searching for some keywords.
Datum von 18.12.2014
Datum bis 18.12.2014
Tage 1

I don't know, which possibilities there are, using VBA.
I am a totally newbie, this is the first time I am using VBA.
Thanks in forward for any help.

Sincerly, Sebastian

Excel file contains: The lines 1 and 2 are empty.
The line 3 contains the dates from the year.
Line 4 contains Mo, Tue, Wed, Thur, Fr, Sat, Sun
Line 5 is empty
Line A6, A7, A8, .... contains the workers names
And then in the lines 6, 7, 8,... there should be "X" for the days, in which the worker has holidays.

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.xls")

'~~> 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
    '
    '~~> Code here to output data from email to Excel File
    '~~> For example
    '
    .Range("A" & lRow).Value = olMail.Subject
    .Range("B" & lRow).Value = olMail.SenderName
    '
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

Sorry, I have only just noticed this question. This answer of mine might help. How to copy Outlook mail message into excel using VBA or Macros — Tony Dallimore

Eugene Astafiev Eugene Astafiev · Accepted Answer · 2015-01-09T13:13:55

It looks like you need to automate Excel from Outlook. The How to automate Microsoft Excel from Visual Basic article describes all the required steps.

How to extract content from an E-Mail (Outlook) to an excel table?

1 Answers