我有一个MS Outlook VBA脚本,用于侦听收到的具有特定主题行的电子邮件 . 收到后,它使用 Shell
函数运行Python脚本 . Python脚本将主题行字符串的一部分作为参数,并将其工作结果保存到.html文件中,该文件最终被拾取并附加到VBA层中的回复消息 .
目前,附件一直是陈旧的 . 我的意思是,在我的最终输出消息中出现的任何内容都不是来自我最近的查询,而是来自之前的查询 . 就好像VBA在附加我的.html文件之前不允许目录更新自身 . 我目前正在编写电子邮件,在附加任何文件之前等待50秒(比我的Python脚本运行时间长10倍),因此这不是时间问题 .
思考?下面的VBA代码
这会设置我的代码,以便在收到任何电子邮件时进行收听和操作 .
Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Private WithEvents myOlItems 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")
Set myOlItems = objNS.GetDefaultFolder(olFolderInbox).Items
End Sub
这将检查电子邮件是否具有特定主题行,并在满足条件时通过 Shell
命令运行Python脚本 .
Private Sub myOlItems_ItemAdd(ByVal Item As Object)
Debug.Print "received"
On Error GoTo ErrorHandler
Dim Msg As Outlook.MailItem
If TypeName(Item) = "MailItem" Then
Set Msg = Item
Dim cnt As Integer
cnt = InStr(Msg.Subject, "**2step")
If cnt > 0 Then
Dim WrdArr() As String
WrdArr() = Split(Msg.Subject)
Debug.Print WrdArr(1)
Debug.Print WrdArr(2)
Shell ("python C:\Users\fileloc\pythonscript.py " & Chr(34) & WrdArr(1) & " " & WrdArr(2) & Chr(34))
createEmail Msg.sender, WrdArr(1) & " " & WrdArr(2)
Debug.Print "Woo made an email"
Else
GoTo ProgramExit
End If
End If
ProgramExit:
Exit Sub
ErrorHandler:
MsgBox Err.Number & " - " & Err.Description
Resume ProgramExit
End Sub
这会在Python脚本创建文件后写入电子邮件 .
Sub createEmail(sender As String, subj As String)
Debug.Print "OK"
Sleep 50000
' images in stationery need to include full path
strFile = "C:\Users\fileloc\output.html"
' You need to set a reference to the scripting object
Dim objMail As Outlook.MailItem
Dim fso As Scripting.FileSystemObject
Dim tsTextIn As Scripting.TextStream
Dim strTextIn As String
Set fso = New Scripting.FileSystemObject
'read html
Set tsTextIn = fso.OpenTextFile(strFile)
strTextIn = tsTextIn.ReadAll
'Create e-mail item
Set objMail = Application.CreateItem(olMailItem)
With objMail
.To = sender
.BodyFormat = olFormatHTML
' use .body when inserting .txt file
.HTMLBody = strTextIn
.Subject = subj
.Display
End With
End Sub
我没有包含Python脚本 . 我很有信心这不是问题 . 它创建/覆盖的输出文件是正确的,该文件最终不会附加到最终的电子邮件 .