I have no idea which lines are causing the problem but if stepping the code
fixes it then throw in one or more DoEvents calls at that point and see if
that helps.

--
Ken Slovak
[MVP - Outlook]
http://www.slovaktech.com
Author: Professional Programming Outlook 2007.
Reminder Manager, Extended Reminders, Attachment Options.
http://www.slovaktech.com/products.htm


"JingleRock" wrote in message
...
The Subject title succintly describes what I am doing.
I am using an OL Rule to grab a Mail Item that satisfies Rule; I am
then saving the Attachment so that I can open it and start "massaging"
the data w/ my XCL VBA Code. After massaging, I save the
modified .xls file so that I can attach it to a forwarded Mail Item.

The Code below does this:

Public Sub BIG_TICKETS(RuleSelectedMI As MailItem)

On Error GoTo PROBLEM_ERROR

Dim strID As String
Dim myPathTemp As String
Dim NewFilePathName As String

'Declare variables
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Dim olNs As Outlook.NameSpace
Dim Fldr As MAPIFolder
Dim olAtt As Attachment
Dim olMi As Outlook.MailItem
Dim MyForward As Outlook.MailItem

'************************* PATH NAME
************************************
'ORIGINAL ATTACHMENT SAVED (SO IT CAN BE OPENED) HERE
'ALSO, MODIFIED ATTACHMENT SAVED (SO IT CAN BE ATTACHED TO
MailItem) HERE
myPathTemp = "C:\Documents and Settings\userID\Local Settings\Temp
\"

strID = RuleSelectedMI.EntryID

'Set variables
Set xlApp = CreateObject("Excel.Application")
Set olNs = Application.GetNamespace("MAPI")
Set Fldr = olNs.GetDefaultFolder(olFolderInbox)
Set olMi = olNs.GetItemFromID(strID)

Set olAtt = olMi.Attachments(1)

'SAVE ORIGINAL ATTACHMENT IN THE SPECIFIED FOLDER USING
SAME FILENAME
olAtt.SaveAsFile (myPathTemp & olAtt.FileName)

Set xlBook = xlApp.Workbooks.Open(myPathTemp &
olAtt.FileName)
xlApp.Visible = True
Set xlSheet = xlBook.Sheets(1) ' IS THIS NEEDED?

'+++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++
+
'INSERT EXCEL VBA CODE THAT WILL "MASSAGE" DATA IN ORIGINAL ATTACHMENT
'+++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++
+

'+++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++
+
'END OF EXCEL VBA CODE THAT "MASSAGED" DATA IN ORIGINAL ATTACHMENT
'+++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++
+

'SAVE ATTACHMENT (NOW, A MODIFIED FILE) IN THE SPECIFIED FOLDER
USING NEW FILENAME;
'THEN CLOSE WB
NewFilePathName = myPathTemp & "SUMMARY.xlS"

xlApp.ActiveWorkbook.SaveAs NewFilePathName
xlApp.ActiveWorkbook.Close

'DELETE ORIGINAL (UNMODIFIED) ATTACHMENT FILE
'Kill (myPathTemp & olAtt.FileName) '==== DO NOT KILL
DURING CODE TEST

olMi.Attachments.Remove 1
olMi.Attachments.Add NewFilePathName

olMi.Recipients.Remove 1

Set MyForward = olMi.Forward
MyForward.Recipients.Add "THE WORLD"

If MyForward.Recipients.ResolveAll Then

MyForward.Subject = "Weekly Wholesaler Report:
SUMMARY"
MyForward.Body = ""
MyForward.Send
olMi.Delete

Else
MsgBox "PROB w/ Address Book Name"
End If

'DELETE MODIFIED ATTACHMENT FILE
'Kill NewFilePathName '============ DO NOT
KILL DURING CODE TEST

BIG_TICKETS_EXIT:
Set MyForward = Nothing
Set olMi = Nothing
Set olAtt = Nothing
Set Fldr = Nothing
Set olNs = Nothing

Set xlSheet = Nothing
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing

Set RuleSelectedMI = Nothing

Exit Sub

PROBLEM_ERROR:
MsgBox "An unexpected error has occurred." _
& vbCrLf & "Please note and report the following information." _
& vbCrLf & "Macro Name: BIG_TICKETS" _
& vbCrLf & "Error Number: " & Err.Number _
& vbCrLf & "Error Description: " & Err.Description, vbCritical,
"Error AGAIN!"
Resume BIG_TICKETS_EXIT

End Sub
'+++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++
+

The above Code satisfies all of the objectives stated above, except:
about 30 seconds after the Code finishes processing, including
shutting down XCL, the second saved file (the Modified Attachment)
pops open; also, I get Error #1004 and Err.Description of "Method
'Range' of object'_Global' failed".

When I step thru the Code, there is no .xls file pop-up and there is
no error message.

I would like to have the Code delete the two saved files, but I am not
there yet.

Please help.