KilianD
02-22-2013, 06:20 AM
Hello,
firstly, to avoid XY problem, hereīs the task at hand: automated email with attached txt file is delivered. I need to save the file and call excel macro to process it.
Initially, I used rule to trigger outlook macro, but it was failing when batch of emails was delivered (typically at stratup). I was advised to use NewMailEx event, which I did, but thereīs a problem:
The NewMailEx event (placed in ThisOutlookSession) evaluates incoming email ok, but fails to call the procedure which saves the attachment (placed in normal module). By failing I mean that the second procedure isnīt run (determined by debug.print at its beginning), there is no error message. I am able to call other procedures residing in normal modules, so I am guessing the problems is some inconsistency in the way I am defining objects.
NOTE: I could embed the piece of code doing the saving into NewMailEx procedure (and it actually works this way), but I would like to keep this procedure as brief and flexible for future use as possible. Also, I would like to understand what the problem is, because separately both procedures are working fine, I just cannot make them work together.
These are the codes:
'code residing in ThisOutlookSession
Private Sub Application_NewMailEx(ByVal EntryIDCollection As String)
Dim arr() As String
Dim i As Integer
Dim ns As Outlook.NameSpace
Dim itm As MailItem
On Error Resume Next
Set ns = Application.Session
arr = Split(EntryIDCollection, ",")
For i = 0 To UBound(arr)
Set itm = ns.GetItemFromID(arr(i))
If itm.Class = olMail Then
If itm.Subject = "Job report" Then
saveAttachtoDisk (itm)
End If
End If
Next
Set ns = Nothing
Set itm = Nothing
End Sub
'code placed in normal module
Public Sub saveAttachtoDisk(itm As MailItem)
Dim objAtt As Outlook.Attachment
Dim saveFolder As String
saveFolder = "C:\Users\XXX\Desktop\"
For Each objAtt In itm.Attachments
objAtt.SaveAsFile saveFolder & "\" & objAtt.DisplayName
Set objAtt = Nothing
Next
CallExcelMacro
End Sub
Any help will be greatly appreciated.
firstly, to avoid XY problem, hereīs the task at hand: automated email with attached txt file is delivered. I need to save the file and call excel macro to process it.
Initially, I used rule to trigger outlook macro, but it was failing when batch of emails was delivered (typically at stratup). I was advised to use NewMailEx event, which I did, but thereīs a problem:
The NewMailEx event (placed in ThisOutlookSession) evaluates incoming email ok, but fails to call the procedure which saves the attachment (placed in normal module). By failing I mean that the second procedure isnīt run (determined by debug.print at its beginning), there is no error message. I am able to call other procedures residing in normal modules, so I am guessing the problems is some inconsistency in the way I am defining objects.
NOTE: I could embed the piece of code doing the saving into NewMailEx procedure (and it actually works this way), but I would like to keep this procedure as brief and flexible for future use as possible. Also, I would like to understand what the problem is, because separately both procedures are working fine, I just cannot make them work together.
These are the codes:
'code residing in ThisOutlookSession
Private Sub Application_NewMailEx(ByVal EntryIDCollection As String)
Dim arr() As String
Dim i As Integer
Dim ns As Outlook.NameSpace
Dim itm As MailItem
On Error Resume Next
Set ns = Application.Session
arr = Split(EntryIDCollection, ",")
For i = 0 To UBound(arr)
Set itm = ns.GetItemFromID(arr(i))
If itm.Class = olMail Then
If itm.Subject = "Job report" Then
saveAttachtoDisk (itm)
End If
End If
Next
Set ns = Nothing
Set itm = Nothing
End Sub
'code placed in normal module
Public Sub saveAttachtoDisk(itm As MailItem)
Dim objAtt As Outlook.Attachment
Dim saveFolder As String
saveFolder = "C:\Users\XXX\Desktop\"
For Each objAtt In itm.Attachments
objAtt.SaveAsFile saveFolder & "\" & objAtt.DisplayName
Set objAtt = Nothing
Next
CallExcelMacro
End Sub
Any help will be greatly appreciated.