Error Printing Excel Attachment

  • Thread starter Thread starter Cathy Landry
  • Start date Start date
C

Cathy Landry

Hello,

I'm running the following code to print Excel attachments in emails. 2
things are happening. I either get the "File in Use PERSONAL.XLS is locked
for editing" or when I run this on another computer the code completes but
nothing prints. I have the same code printing .tiff attachments ( changed
the Shell program)

Any help would be greatly appreciated!



Public Sub PrintExcelAttachments()
Dim Inbox As MAPIFolder
Dim Item As MailItem
Dim Atmt As Attachment
Dim FileName As String
Dim i As Integer

Set Inbox =
GetNamespace("MAPI").GetDefaultFolder(olFolderInbox).Parent.Folders.Item("Batch Prints")

For Each Item In Inbox.Items
For Each Atmt In Item.Attachments
' all attachments are first saved in the temp folder C:\Temp.
Be sure to create this folder.
FileName = "C:\Temp\" & Atmt.FileName
Atmt.SaveAsFile FileName
' please change the program folder accordingly if MSExcel is not
installed on drive C:
Shell """C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE ""
/h /p """ + FileName + """", vbHide


Next

'Item.Delete
Next

Set Inbox = Nothing
End Sub

Thank you!
Cathy
 
An alternative would be to open the workbook and call the
Workbook.Printout Method.

Public Sub PrintExcelAttachments()
Dim Inbox As MAPIFolder
Dim Item As MailItem
Dim Atmt As Attachment
Dim FileName As String
Dim i As Integer

Set Inbox =
GetNamespace("MAPI").GetDefaultFolder
(olFolderInbox).Parent.Folders.Item("B­atch Prints")

For Each Item In Inbox.Items
For Each Atmt In Item.Attachments
' all attachments are first saved in the temp folder C:
\Temp.
Be sure to create this folder.
FileName = "C:\Temp\" & Atmt.FileName
Atmt.SaveAsFile FileName
Call PrintWorkbook(FileName)
Next


'Item.Delete
Next


Set Inbox = Nothing
End Sub

Function PrintWorkbook(workbookName As String)
Dim xl As Object
Set xl = CreateObject("Excel.Application")

xl.Workbooks.Open workbookName
xl.Workbooks(1).PrintOut

Set xl = Nothing
End Function

--JP
 
Hi JP,

Thank you for your input and quick response!

I'm getting a debug error after the first email with attachment prints that
says "cannot save file" looks like it keeps looping and re-printing the same
attachment. Is there a way to just open/print/delete without saving?

Cathy
 
Sadly, no. You have to save the item before you can open or print it.
At least, that's how it works with the existing object models. You can
always delete the file after saving it.

--JP
 
I failed to mention that you'll need to edit "C:\Temp" to point to an
actual folder on your computer, or create a folder for this purpose
and update the string accordingly.

--JP
 
Back
Top