So sollte das Format besser rüberkommen. ;)
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title></title>
<style type="text/css">.origmessagetext_quote {margin:0; padding:0; border-left:2px solid blue; padding-left: 5px; }</style>
</head>
<body>
<p><br> </p>
<div id="qu_Dw1FCaKWganRXH5OsOAe" class="origmessagetext">
<p class="MsoNormal" style="margin-bottom: 12.0pt;"><span style="mso-ansi-language: EN-US;" lang="EN-US" data-mce-mark="1">Acceptance date : 06/10/2020<br> Request approuved for Masx Mustermann<br> Request Date : 06/10/2020</span></p>
<table class="MsoNormalTable" style="mso-cellspacing: 0cm; mso-yfti-tbllook: 1184; mso-padding-alt: 0cm 0cm 0cm 0cm;" border="1" cellspacing="0" cellpadding="0">
<tbody>
<tr style="mso-yfti-irow: 0; mso-yfti-firstrow: yes;">
<td style="width: 60.0pt; padding: 0cm 0cm 0cm 0cm;" width="80">
<p class="MsoNormal"> Startdate</p>
</td>
<td style="width: 60.0pt; padding: 0cm 0cm 0cm 0cm;" width="80">
<p class="MsoNormal"> Enddate</p>
</td>
<td style="width: 37.5pt; padding: 0cm 0cm 0cm 0cm;" width="50">
<p class="MsoNormal"> Duration (per day)</p>
</td>
<td style="width: 45.0pt; padding: 0cm 0cm 0cm 0cm;" width="60">
<p class="MsoNormal"> Starttime</p>
</td>
</tr>
<tr style="mso-yfti-irow: 1; mso-yfti-lastrow: yes;">
<td style="padding: 0cm 0cm 0cm 0cm;">
<p class="MsoNormal"> 12/10/2020</p>
</td>
<td style="padding: 0cm 0cm 0cm 0cm;">
<p class="MsoNormal"> 12/10/2020</p>
</td>
<td style="padding: 0cm 0cm 0cm 0cm;">
<p class="MsoNormal"> 1</p>
</td>
<td style="padding: 0cm 0cm 0cm 0cm;">
<p class="MsoNormal"> 08:00</p>
</td>
</tr>
</tbody>
</table>
<p class="MsoNormal"><span style="mso-ansi-language: EN-US;" lang="EN-US" data-mce-mark="1"><br> <br> Applicant's Note : This is a test for auto mail to calendar</span></p>
</div>
</body>
</html>
Damit könnte man jetzt seine eigene Beispielmail in Outlook erstellen. Ich nehm es einfach mal als Textdatei her.
Markiert ist oben der Teil mit der Tabelle. Am einfachsten kommt man da ran, indem man:
Option Explicit
Sub Test()
Dim strMailContent As String 'HTML aus objMailItem.HTMLBody (hier aus Datei)
'# ich hol mir hier das HTML aus einer Datei
With CreateObject("ADODB.Stream")
.Charset = "utf-8"
Call .Open
Call .LoadFromFile(Environ$("USERPROFILE") & "\Desktop\mail.html.msg")
strMailContent = .ReadText()
End With
'# HTML verarbeiten
Dim objHTML As MSHTML.HTMLDocument
Dim vntTable As Variant
Set objHTML = New MSHTML.HTMLDocument
'HTML laden
Call CallByName(objHTML, "writeln", VbMethod, strMailContent)
With objHTML.DocumentElement
With .getElementsByTagName("TABLE")
If .Length > 0 Then
vntTable = HTMLTable2Array(.Item(0))
'Ausgabe der Tabelle zur Veranschaulichung
Call DebugPrintTable(vntTable)
Else
Call MsgBox("Table not found")
End If
End With
End With
End Sub
Private Function DebugPrintTable(TableData As Variant) As Variant
Dim i As Long, n As Long
For i = 0 To UBound(TableData)
n = WorksheetFunction.Max(n, Len(TableData(i, 0)))
Next
For i = 0 To UBound(TableData)
Debug.Print TableData(i, 0); Tab(n + 4); TableData(i, 1)
Next
End Function
Private Function HTMLTable2Array(HTMLTable As MSHTML.HTMLTable) As Variant
Dim i As Long
Dim j As Long
On Error GoTo ErrHandler
i = HTMLTable.Rows.Length 'rows
j = HTMLTable.Rows(0).Cells.Length 'cols
'transposed array
ReDim vntData(0 To j - 1, 0 To i - 1) As String
Dim tableRow As MSHTML.HTMLTableRow
Dim tableCell As MSHTML.HTMLTableCell
For Each tableRow In HTMLTable.Rows
For Each tableCell In tableRow.Cells
vntData(tableCell.cellIndex, tableRow.RowIndex) = Trim$(tableCell.innerText)
Next
Next
HTMLTable2Array = vntData
Exit Function
ErrHandler:
vntData = Split("")
End Function
Ausgabe:
Startdate 12/10/2020
Enddate 12/10/2020
Duration (per day) 1
Starttime 08:00
Wie man jetzt das Datum zu deuten ist, keine Ahnung. 12.10. oder 10.12.?
Den anderen Kram über der Tabelle kann man natürlich auch noch auslesen. ;)
Grüße
|