我有一个写好的excel macro是用于读取outlook邮件内容并书写进excel的。Excel Macro 手动触发没有任何问题,运行完美。
现在我又写了一个outlook macro 用于自动触发这个excel Macro。 当指定邮件发送至outlook, 该excelMacro 就会被触发。
问题是 当这个excelMacro经outlook vba script 触发 他就只能读取邮件内容至251行, 可单独手动触发excel Macro 邮件全部内容可被读取。有没有大神能教教我如何解决这个问题啊?
下面是monitor邮件的部分,当指定邮件进入邮箱 运行function: run_excel_macro()
Option Explicit
Private WithEvents Items As Outlook.Items
Private Sub Application_Startup()
Dim olApp As Outlook.Application
Dim objNS As Outlook.NameSpace
Set olApp = Outlook.Application
Set objNS = olApp.GetNamespace("MAPI")
' default local Inbox
Set Items = objNS.GetDefaultFolder(olFolderInbox).Items
Debug.Print "Application Starup" & Now()
End Sub
Private Sub Items_ItemAdd(ByVal item As Object)
On Error GoTo ErrorHandler
Dim Msg As Outlook.MailItem
' define variable for other use
Dim eDate As String
eDate = Format(Now, "DD/MM/YYYY")
Debug.Print eDate
If TypeName(item) = "MailItem" Then
Set Msg = item
Debug.Print Msg.Subject & " Time:" & Now()
If InStr(1, Msg.Subject, "MS - Execute Services Trigger - " & eDate) Then
run_excel_macro
End If
Set Msg = Nothing
End If
ProgramExit:
Exit Sub
ErrorHandler:
MsgBox Err.Number & " - " & Err.Description
Resume ProgramExit
End Sub
下面是run_excel_macro 的内容
Function run_excel_macro()
' define variable for excel
Dim File$
Dim XlAPP As Object ' Excel.Application
Dim Wb As Object ' Excel.Workbook
Dim Ws As Object ' Excel.Worksheet
Dim Rn As Object ' Excel.Range
File = "C:\Users\esnxsxx\OneDrive - Ericsson AB\NBN DL Step Change\NBN_Execute_Service_Tracker_v3.xlsm" ' Set File Path
'Debug.Print "connecting Excel"
On Error Resume Next
Set XlAPP = GetObject(, "excel.application")
On Error GoTo 0
If XlAPP Is Nothing Then Set XlAPP = New Excel.Application
XlAPP.Visible = True
'Debug.Print "Opening excel file"
Set Wb = XlAPP.Workbooks.Open(File)
Set Ws = Wb.Sheets("Read Me")
Ws.Activate
'Debug.Print "Run Macro"
Wb.Application.Wait (Now + TimeValue("0:00:05"))
Wb.Application.Run "Module1.getDataFromOutlook"
Wb.Save
'XlAPP.Quit
Set XlAPP = Nothing
Set Wb = Nothing
Set Ws = Nothing
End Function
求大神帮忙看一下!
只要是从outlook触发,item.body就只能读251行。 手动直接从excel 就可以读全部。