我试图通过VBA将Outlook中的电子邮件内容提取到Excel表中 .
电子邮件用于度假管理 .
在主题中总是有关键字"Accepted holiday - Mr. James"詹姆斯先生是雇员的名字,接受哪些假期 . 因此关键字"Accepted holiday"始终相同,但名称始终更改 .
电子邮件包含一个长表,但只有最终需要 . 也许它是最好的,如果它正在搜索一些关键字 .
Datum von 18.12.2014
Datum bis 18.12.2014
Tage 1
我不知道,有哪些可能性,使用VBA .
我是一个全新的人,这是我第一次使用VBA .
在此向前任何帮助表示感谢 .
真诚地,塞巴斯蒂安
Excel文件包含:第1行和第2行为空 .
第3行包含年份的日期 .
第4行包含Mo,Tue,Wed,Thur,Fr,Sat,Sun
第5行是空的
A6,A7,A8,......行包含 Worker 姓名
然后在第6,7,8行......那天应该有"X", Worker 有假期 .
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
结束子
1 回答
看起来您需要从Outlook自动化Excel . How to automate Microsoft Excel from Visual Basic文章描述了所有必需的步骤 .