I've previously posted code for opening any email attachment. See Open Any Email Attachment From Outlook. Note that this technique works for any file and in any VBA environment; they don't have to be email attachments for you to open them.
But you can open OR print any file using the ShellExecute API, and it also works anywhere. The following function can do this.
' adapted from Access 2003 VBA Programmer's Reference
Public Enum actionType
openfile
printfile
End Enum
Public Const SW_SHOWNORMAL As Long = 1
Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
(ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, _
ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
Function ExecuteFile(fileName As String, action As actionType)
' action can be either "Openfile" or "Printfile".
Dim sAction As String
Select Case action
Case 0 ' openfile
sAction = "Open"
Case 1 ' printfile
sAction = "Print"
End Select
ShellExecute 0, sAction, fileName, vbNullString, "", SW_SHOWNORMAL
End Function
The Enum section delimits how you can call the function, so the API will always be called correctly.
Sample usage
To call the ExecuteFile function, just pass in the filename and the action you want to perform (either open or print). If using this in Outlook, you need to save the file first before opening it. And there's no way to customize the output; you'll need to use the object model for the given file for that. This is just a quick way to open or print any file.
Sub TestPrint() ' open a file ExecuteFile "C:\MyFile.xls", openfile ' print another ExecuteFile "C:\MyPDF.pdf", printfile End Sub
Now the challenge becomes: how to close the file? 
About JP
10 Comments:
Very clever JP. I like this one.
Did you ever figure out how to close the file? That is really making my life suck right now, if I could close it all would be well with the world.
No deal, but I'm sure you could use some Windows API to grab the window and close it that way. Exactly how, escapes me.
Nice work, thanks for sharing!
How do I print with the default printer without prompting for any user interaction?
That's what the above method should do.
What a timesaver! Added that code to a regular routine! Now the whole process is lightening fast. On a different note JP, as a Purchaser, I receive attachment with Quotations. I have figured a way how to save the attachments to a particular folder, but incase the Sender has some fancy pictures included in his Email Signature or Email Body, then this gets stored as an attachment too. Is there anywhere where I can have only the Selected file to be stored in the folder?.
Currently I have a Routine that Grabs all the files from this Folder containing my Quotations. This piece of code allows me print out the Quotations without opening these files Manual individually and then manually printing them. Thanks for sharing the code!
Sorry, I should add that I use MS Outlook 2010, as my email client!
Hi JP,
I'm new to VBA and I need to do something like what you've posted in my macro. I'm wondering whether it is possible for me to specify my printer name in this code, since it is a network printer?
Basically, what I need to do is to open a freshly created PDF file from a folder, then print this pdf file to my network printer. Can you pls pls help me? THANKEW!!
Is it the default printer? If so, all you need to do is use the code above.
Hej,
This works great but if i print multiple files faster than adobe can send them to the printer they come out in random order from the printer.
I have read all pdf-files from a folder structure into excel and then put them in the order i want them. I wold not like to sort 100+documents after print.
I added a delay betwen prints but it is not a very nice sulution and takes lots of time. Can i somehow check if adobe is ready to recive the next job?