Trying to print specific worksheet from outlook attachment

[J]

Hi everyone-

I'm trying to use VBA to print a specific "(1)ORDER FORM" worksheet
from workbooks that I'm receiving in Outlook. I've constructed the
following, but it keeps erring out when reaches the
Worksheet("(1)ORDER FORM").Printout line. I receive errors ranging
from error 9 to error 462 to error 1004. What am I doing wrong? Thanks
for your help in advance!


Public Sub PrintOrders()
On Error GoTo ErrorHandler

Dim objOutlook As Outlook.Application
Dim objMsg As Object
Dim objAttachments As Outlook.Attachments
Dim objSelectedItems As Outlook.Selection
Dim i As Long
Dim lngCounter As Long
Dim strFile As String
Dim strLocalFileLink As String
Dim strLocalPathLink As String
Dim strUserName As String
Dim strFolder As String
Dim strFolderpath As String



' Set the Attachment folder.

strFolderpath = "C:\Temp"


Set objOutlook = CreateObject("Outlook.Application")
Set objSelectedItems = objOutlook.ActiveExplorer.Selection



For Each objMsg In objSelectedItems
' if the message is a mail message
If objMsg.Class = olMail Then

Set objAttachments = objMsg.Attachments
lngCounter = objAttachments.Count

If lngCounter > 0 Then
Dim strLogger As String
strLogger =
"------------------------------------------------------------"


' create and display a link to the destination folder

strLogger = strLogger & vbCrLf & "This order was
printed on " & Date & vbCrLf

'iterate the attachment object collection
For i = lngCounter To 1 Step -1

strFile = objAttachments.Item(i).FileName

If UCase(Right(strFile, 3)) = "XLS" Then
strSavedFile = strFolderpath & "\" & strFile

' save the attachment file
objAttachments.Item(i).SaveAsFile strSavedFile

PrintAtt (strSavedFile)

Kill strSavedFile
End If
Next i

strLogger = strLogger &
"------------------------------------------------------------" &
vbCrLf

' display log/links in the message body
objMsg.Body = objMsg.Body & vbCrLf & strLogger
objMsg.Save
objMsg.UnRead = False
End If
End If
Next


ExitSub:
Set objAttachments = Nothing
Set objMsg = Nothing
Set objSelectedItems = Nothing
Set objOutlook = Nothing
Exit Sub

ErrorHandler:
MsgBox "Error Code: " & Err.Number & vbCrLf & _
"Description: " & Err.Description

Err.Clear
GoTo ExitSub
End Sub


'###############################################################################
'### print routine
Sub PrintAtt(fFullPath As String)
On Error GoTo ErrorHandler
Dim xlApp As Excel.Application
Dim wb As Excel.Workbook
'in the background, create an instance of xl then open, print,
quit
Set xlApp = New Excel.Application
Set wb = xlApp.Workbooks.Open(fFullPath)

Worksheets("(1)ORDER FORM").PrintOut

ActiveWorkbook.Close savechanges:=False
Set wb = Nothing
xlApp.Quit
Set xlApp = Nothing


ExitSub:
ActiveWorkbook.Close savechanges:=False
Set wb = Nothing
xlApp.Quit
Set xlApp = Nothing

Exit Sub

ErrorHandler:
MsgBox "Error Code: " & Err.Number & vbCrLf & _
"Description: " & Err.Description

Err.Clear
GoTo ExitSub
End Sub

 

[Jim Cone]

You set a reference to the workbook and then didn't use it.
Power point doesn't know what "WorkSheets" is and the
Excel application is not visible when it starts, so there is no ActiveWorkbook.
Also, you have duplicated your exit routine.
Try...

'print routine
Sub PrintAtt(fFullPath As String)
On Error GoTo ErrorHandler
Dim xlApp As Excel.Application
Dim wb As Excel.Workbook

' in the background, create an instance of xl then open, print, quit
Set xlApp = New Excel.Application
Set wb = xlApp.Workbooks.Open(fFullPath)

wb.Worksheets("(1)ORDER FORM").PrintOut

ExitSubHere:
wb.Close savechanges:=False
Set wb = Nothing
xlApp.Quit
Set xlApp = Nothing
Exit Sub

ErrorHandler:
MsgBox "Error Code: " & Err.Number & vbCrLf & _
"Description: " & Err.Description
Err.Clear
GoTo ExitSubHere
End Sub
--
Jim Cone
Portland, Oregon USA
http://www.contextures.com/excel-sort-addin.html
(30+ ways to sort in excel)

..
..
..

"J" <[email protected]>
wrote in message
Hi everyone-

I'm trying to use VBA to print a specific "(1)ORDER FORM" worksheet
from workbooks that I'm receiving in Outlook. I've constructed the
following, but it keeps erring out when reaches the
Worksheet("(1)ORDER FORM").Printout line. I receive errors ranging
from error 9 to error 462 to error 1004. What am I doing wrong? Thanks
for your help in advance!

-snip-

'print routine
Sub PrintAtt(fFullPath As String)
On Error GoTo ErrorHandler
Dim xlApp As Excel.Application
Dim wb As Excel.Workbook
'in the background, create an instance of xl then open, print, quit
Set xlApp = New Excel.Application
Set wb = xlApp.Workbooks.Open(fFullPath)

Worksheets("(1)ORDER FORM").PrintOut

ActiveWorkbook.Close savechanges:=False
Set wb = Nothing
xlApp.Quit
Set xlApp = Nothing


ExitSub:
ActiveWorkbook.Close savechanges:=False
Set wb = Nothing
xlApp.Quit
Set xlApp = Nothing

Exit Sub

ErrorHandler:
MsgBox "Error Code: " & Err.Number & vbCrLf & _
"Description: " & Err.Description

Err.Clear
GoTo ExitSub
End Sub

 

[Jim Cone]

Correction...
"Power point" should read "Outlook"