outlook电子邮件的身体不复制到excel
问题描述:
以下的代码作品从指定的电子邮件打开指定的文件。但是,它不会将excel中的正文消息分离成不同的行,有什么建议吗?outlook电子邮件的身体不复制到excel
For i = LBound(MyAr) To UBound(MyAr)
'~~> This will give you the contents of your email
'~~> on separate lines
Debug.Print MyAr(i)
Next i
End With
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:\Users\ltorres\Documents\multiplier.xlsx")
'~~> Set the relevant output sheet. Change as applicable
Set oXLws = oXLwb.Sheets("Sheet1")
lRow = oXLws.Range("A" & oXLws.Rows.Count).End(xlUp).Row + 1
'~~> Write to outlook
With oXLws
Dim MyAr() As String
MyAr = Split(olMail.Body, vbCrLf)
For i = LBound(MyAr) To UBound(MyAr)
'~~> This will give you the contents of your email
'~~> on separate lines
Debug.Print MyAr(i)
Next i
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
末次
答
您可以设置lRow
在With
语句,但你还需要添加每次有由您规定的换行符1行,尝试:
With oXLws
lRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
Dim MyAr() As String
MyAr = Split(olMail.Body, vbCrLf)
For i = LBound(MyAr) To UBound(MyAr)
.Range("A" & lRow).Value = MyAr(i)
lRow = lRow + 1
Next i
End With
+1
谢谢,这应该让我指向我想要的方向 – Luis
应该'lRow = oXLws.Range(“A”&oXLApp.Rows.Count).End(xlUp).Row + 1'b e引用'oXLws'中的行而不是'oXLApp'?另外,从Debug.Print MyAr(i)'出现在直接窗口中的东西是什么? – Jordan
@jordan正确我已经修复了它,在excel中出现的是主题和发件人列A和B,但不是debug.print中的主体,它显示了电子邮件的主体。 ***请注意我想将电子邮件的正文分成不同的行 – Luis