Error Printing Excel Attachment

Status
Not open for further replies.
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

On Jan 22, 1:28 pm, Cathy Landry

<CathyLan...> wrote:
> 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)
>
 
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

"JP" wrote:


> 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

> On Jan 22, 1:28 pm, Cathy Landry
> <CathyLan...> wrote:
> > 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)
> >
> .
>
 
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

On Jan 22, 5:40 pm, Cathy Landry

<CathyLan...> wrote:
> 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

> "JP" wrote:
> > 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
>
> > On Jan 22, 1:28 pm, Cathy Landry
> > <CathyLan...> wrote:
> > > 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)
>
> > .-

 
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

On Jan 22, 5:40 pm, Cathy Landry

<CathyLan...> wrote:
> 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

> "JP" wrote:
> > 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
>
> > On Jan 22, 1:28 pm, Cathy Landry
> > <CathyLan...> wrote:
> > > 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)
>
> > .-

 
Status
Not open for further replies.
< Attaching Files to Outlook | Tab Order Out of Sequence After Attaching Document >
Similar threads
Thread starter Title Forum Replies Date
L Error when exporting Sent Mail to Excel Outlook VBA and Custom Forms 6
G Outlook 365 My iCloud Outlook doesn’t work after reinstalling Microsoft365 on Windows 10 PC – now I get error message on contacts and calendar Using Outlook 1
V Outlook Error The Attempted operation Failed. An Object Could Not be found Outlook VBA and Custom Forms 0
S macro error 4605 Outlook VBA and Custom Forms 0
humility36 Cannot move emails to archive - 440 error Outlook VBA and Custom Forms 1
D.Moore Strange VBA error Outlook VBA and Custom Forms 4
T Event Error on non existent Event. Using Outlook 2
P now on office 365 but getting error messages about missing Outlook 2013 cache folders Using Outlook 2
W Outlook 365 I am getting the "Either there is no default mail client" error when I try to send an email on excel Office 365 Using Outlook 1
A Links in email getting error message about group policy Using Outlook 4
Aussie Outlook 365 Rule runs manually but returns the error code "an unexpected error has occurred" when incoming mail arrives Using Outlook 1
Cathy Rhone Mail merge error message Using Outlook 1
U Outlook 2019 VBA run-time error 424 Outlook VBA and Custom Forms 2
V Outlook error 500 Using Outlook 2
O Comma Separated Values.ADR and A file error has occurred in the translator Using Outlook 6
D We're sorry but outlook has run into an error Using Outlook 6
D Outlook 2016 Outlook Error Msg "The operation cannot be performed ..." How to Stop it Using Outlook 4
P Outlook 2013 All imported Mail Rules in error when imported into new profile Using Outlook 5
H Outlook 2019 Certificate error Using Outlook 2
V Date and/or time error in Outlook Form Outlook VBA and Custom Forms 0
A Run-time error '430' on certain emails when trying to set "Outlook.mailitem" as "ActiveExplorer.Selection.Item" Outlook VBA and Custom Forms 2
E Complite error on SaveAsFile method Outlook VBA and Custom Forms 2
I Error saving screenshots in a custom form in outlook 2016, outlook 365 - ok in outlook 2013, outlook 2010 Outlook VBA and Custom Forms 5
D Outlook VBA error extracting property data from GetRules collection Outlook VBA and Custom Forms 10
A Unable to save recurring Meeting to Documents folder due to error Using Outlook 2
M Compile error: User-defined type not defined Outlook VBA and Custom Forms 0
R Error when trying to forward current email item Outlook VBA and Custom Forms 7
M ERROR: None of your email accounts could send to this recipient Using Outlook 2
J OLADD.FAE Error When Exporting Contacts Using Outlook 6
C Send/receive error 80040119 Using Outlook 2
W error with the permission for the file Outlook VBA and Custom Forms 0
L Outlook 2019 MAC sync error after working for 4 hours Using Outlook 1
A Run time error 424. object required in outlook 2013 Outlook VBA and Custom Forms 10
M error code 0x8DE00006 Using Outlook 1
M Desktop Version Of Outlook Generating Error Using Outlook 4
M Send/Receive error 0x800CCC0F Using Outlook 0
T Outlook 2016 CSV Translator Import Error Using Outlook 6
ManaarZakaria I'm afraid of this issue, cause of strange error Exchange Server Administration 2
P Suppress dialog box on email check error? Using Outlook 5
vodkasoda Object could not be found Error in Outlook 2007 Outlook VBA and Custom Forms 5
S VBA Macro - Run-time error '424': object required - Help Please Outlook VBA and Custom Forms 3
avant-guvnor Outlook.Application now produces error Outlook VBA and Custom Forms 5
P Run Time Error 91 when linking contact to task in VBA Outlook VBA and Custom Forms 1
N Error 0x80090326 when trying to setup IMAP account on Outlook.com Using Outlook.com accounts in Outlook 1
N Saving And Deleting Outlook Attachments with Unknown Error Message Outlook VBA and Custom Forms 1
Dennis Gaudenzi Your setup couldn't be started because of an unexpected error (mapi 0x80040604) Using Outlook 14
M Outlook 2013 fails to start -- missing WindowsCodecs.dll error Using Outlook 3
Steshelter ICloud Unexpected Error Using Outlook 5
Rupert Dragwater "there was an error reading this theme" Using Outlook 3
A Error: The name cannot be matched to a name in the address list Using Outlook.com accounts in Outlook 0

Similar threads

Back
Top