GJavor
11-09-2012, 09:54 AM
Hello Everyone!
I have the following problem and hope that maybe someone can help me resolve it.
I have created a macro in outlook that is triggered to run by a rule for when i receive an email into my inbox from a specific sender with certain words in the subject.
The outlook macro once activated looks through my inbox items again and determines based on subject, sender and attachment if its something it should work with (need this because in the end the macro will run in a shared inbox).
Once all criterias are met it should take the attached zip file and save it to a specific folder location with a specific name. Once that is done it should open a specific excel file and run a macro within that excel.
Now without going into too much detail the excel macro among other things opens the previously saved zip file and extracts the excel file found within it to certain locations and starts to work with that file.
And now comes the actual problem. If i have the outlook rule turned off and the specific email is in my inbox and i run the mentioned macro manually from outlook everything works 100% all the time as it should however if i turn the rule on and i get the same email sent to me the rule starts the macro however strangely 9 out of 10 times the macro hangs allways at the same place which is already inside the excel macro part where the zip extraction should happen.
And 1 out of 10 times maybe with the same setup the macro with the rule setting on works out of the box.
My guess is that the outlook macro jumps to soon into the excel part and looks for the zip file to extract which is not yet saved to the location because the email has not even registered in my inbox as arrived. Once i hit the end button on the error message the email actually shows up in my inbox.
Anybody have an idea what is causing this and how i could find a workaround to this? I need to have the rule activate the macro as a manual triggering is not an option. Below is the outlook macro code i use.
Sub OCPNM(MyMail As MailItem)
Dim ns As NameSpace
Dim Inbox As MAPIFolder
Dim Item As Object
Dim Atmt As Attachment
Dim FileName As String
Dim i As Integer
Set ns = GetNamespace("MAPI")
Set Inbox = ns.GetDefaultFolder(olFolderInbox)
'========== If there are messages in the inbox they will be examined for attachments, Sender, Subject ===========
For Each Item In Inbox.Items 'outer loop start. Looks at the emails one by one
For Each Atmt In Item.Attachments 'inner loop start. Looks at the attachments, subject and sender. if no match goes back to outer loop
If (Right(Atmt.FileName, 3) = "zip") And (Item.SenderName = "John Doe") And (InStr(1, Item.Subject, "Testing", vbTextCompare)) > 0 Then
FileName = "C:\Testing\" & "Test File.zip"
Atmt.SaveAsFile FileName 'inner loop saves attachment to above folder with specified name
Item.UnRead = False 'Marks the email unread
Item.Delete 'deletes the email
End If
Next Atmt 'end of inner loop
Next Item 'end of outer loop
'======= Clear Memmory========
OCPNM_exit:
Set Atmt = Nothing
Set Item = Nothing
Set ns = Nothing
' Open excel part ==========================
Dim XLApp As Object ' Excel.Application
Dim XlWK As Object ' Excel.Workbook
Set XLApp = CreateObject("Excel.Application")
XLApp.Workbooks.Open FileName:="C:\Test2\Change.xlsm"
XLApp.Run ("ExtractRawAndSave")
End Sub
I have the following problem and hope that maybe someone can help me resolve it.
I have created a macro in outlook that is triggered to run by a rule for when i receive an email into my inbox from a specific sender with certain words in the subject.
The outlook macro once activated looks through my inbox items again and determines based on subject, sender and attachment if its something it should work with (need this because in the end the macro will run in a shared inbox).
Once all criterias are met it should take the attached zip file and save it to a specific folder location with a specific name. Once that is done it should open a specific excel file and run a macro within that excel.
Now without going into too much detail the excel macro among other things opens the previously saved zip file and extracts the excel file found within it to certain locations and starts to work with that file.
And now comes the actual problem. If i have the outlook rule turned off and the specific email is in my inbox and i run the mentioned macro manually from outlook everything works 100% all the time as it should however if i turn the rule on and i get the same email sent to me the rule starts the macro however strangely 9 out of 10 times the macro hangs allways at the same place which is already inside the excel macro part where the zip extraction should happen.
And 1 out of 10 times maybe with the same setup the macro with the rule setting on works out of the box.
My guess is that the outlook macro jumps to soon into the excel part and looks for the zip file to extract which is not yet saved to the location because the email has not even registered in my inbox as arrived. Once i hit the end button on the error message the email actually shows up in my inbox.
Anybody have an idea what is causing this and how i could find a workaround to this? I need to have the rule activate the macro as a manual triggering is not an option. Below is the outlook macro code i use.
Sub OCPNM(MyMail As MailItem)
Dim ns As NameSpace
Dim Inbox As MAPIFolder
Dim Item As Object
Dim Atmt As Attachment
Dim FileName As String
Dim i As Integer
Set ns = GetNamespace("MAPI")
Set Inbox = ns.GetDefaultFolder(olFolderInbox)
'========== If there are messages in the inbox they will be examined for attachments, Sender, Subject ===========
For Each Item In Inbox.Items 'outer loop start. Looks at the emails one by one
For Each Atmt In Item.Attachments 'inner loop start. Looks at the attachments, subject and sender. if no match goes back to outer loop
If (Right(Atmt.FileName, 3) = "zip") And (Item.SenderName = "John Doe") And (InStr(1, Item.Subject, "Testing", vbTextCompare)) > 0 Then
FileName = "C:\Testing\" & "Test File.zip"
Atmt.SaveAsFile FileName 'inner loop saves attachment to above folder with specified name
Item.UnRead = False 'Marks the email unread
Item.Delete 'deletes the email
End If
Next Atmt 'end of inner loop
Next Item 'end of outer loop
'======= Clear Memmory========
OCPNM_exit:
Set Atmt = Nothing
Set Item = Nothing
Set ns = Nothing
' Open excel part ==========================
Dim XLApp As Object ' Excel.Application
Dim XlWK As Object ' Excel.Workbook
Set XLApp = CreateObject("Excel.Application")
XLApp.Workbooks.Open FileName:="C:\Test2\Change.xlsm"
XLApp.Run ("ExtractRawAndSave")
End Sub