Grabbing OL Mail Attachment, Using XCL VBA Code, Replacing .xlsAttachment, & Mailing

[JingleRock]

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.

 

[Ken Slovak - [MVP - Outlook]]

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.

 

[JingleRock]

Thanks for your post, Ken.
I have a partial solution.
The problem has to do with creating two instances of Excel. Remember,
I am grabbing a mail item, saving the Excel attachment to my local
hard drive using the same filename, opening it and using Excel VBA
code to modify it, then saving the modified file, using a new name, to
the same folder used in saving the original attachment.
By keeping Task Manager open, I am able to monitor the Excel.exe
process while the macro runs.
I inserted an extra 'xlApp.Quit' in my Code (therefore, a total of
two) (and I am still using the stmt 'xlApp.ActiveWorkbook.Close' in my
Code). The result is that I no longer get an error message and I no
longer get the pop-up modified file spreadsheet. Also, I am able to
have my Code 'Kill" each of the two saved files. The only drawback is
that Excel.exe remains open in the background until I close Outlook,
and then Excel.exe disappears from Task Manager immediately.
Any thoughts?

 

[Ken Slovak - [MVP - Outlook]]

After you quit Excel are you releasing all references to it by setting them
to Nothing?

 

[JingleRock]

Yes, see all 'Nothing's above.
By the way, I deleted the 3 stmts involving 'xlSheet'.

 

[Ken Slovak - [MVP - Outlook]]

Well, something's holding it open and that's usually the cause.

 

[JingleRock]

Also interesting, that two 'xlApp.Quit's are required to get to the
point where I am.

 

[JingleRock]

Also interesting, that two 'xlApp.Quit's are required to get to the
point where I am.

I have been experimentling -- now, I am able to duplicate my earlier
results, but with only one occurrence of 'xlApp.Quit', and zero
occurrences of 'xlSheet'. When I close Outlook, then Excel.exe
immediately disappears from the Task Manager. So, what is the best
Outlook VBA way for my Code to shut down Outlook?

 

[Ken Slovak - [MVP - Outlook]]

You want to shut down Outlook from within Outlook VBA code? Not really a
best practice, but Application.Quit would do it.

Also interesting, that two 'xlApp.Quit's are required to get to the
point where I am.

I have been experimentling -- now, I am able to duplicate my earlier
results, but with only one occurrence of 'xlApp.Quit', and zero
occurrences of 'xlSheet'. When I close Outlook, then Excel.exe
immediately disappears from the Task Manager. So, what is the best
Outlook VBA way for my Code to shut down Outlook?

 

[JingleRock]

I inserted the following stmts immediately prior to 'Exit Sub':

Outlook.Application.Quit
Set Outlook.Application = Nothing

This works great: both Excel and Outlook disappear from the Task
Manager.

I am much more familiar with Excel VBA: in all of my Project coding,
I shut down Excel from within my Code.