Change Printer to PDF in Access with VBA

I have the following code, and I need to know how to incorporate code to have an Access Report output to a PDF file, and then after the output is completed, change Printer back to the original Printer.

I originally was using this same code with Windows XP and Office/Access 2003; since upgrading to Windows 7 and Office/Access 2010, it is taking 20+ minutes to output the report to PDF and email, where before the upgrades, it was taking just a few seconds to complete

Since upgrading my Windows and Office Professional, I installed Adobe Acrobat XI Pro.  I thought that maybe if I had VBA Code incorporated into my current code to actually change the printer to PDF, that it may speed things up, unless someone has some other insight as to how to resolve the latency issues I've encountered since upgrading my Windows and Office Professional.

Thanks,
gdunn59

Public Function SendEmailEmpCompletedRptNoErrorsNoAction(ByVal varEmpEmail, varAddlEmail As String)
On Error GoTo Err_handler
Dim objOutlook As Object
Dim objOutlookMsg As Object
Dim objOutlookRecip As Object
Dim objOutlookAttach As Object
Dim AttachmentPath As String
Dim DisplayMsg As String
Dim strAttach1 As String
Dim strAttachTemp As String
Dim strOutputToTemp As String
Dim strAttachCopy As String
Dim strPTR As Printer

DoCmd.Echo False
DoCmd.SetWarnings False

' check to see if folder c:\users exists on user's hard drive, if not, create
    If Len(Dir("C:\Users", vbDirectory)) = 0 Then
        MkDir "C:\Users"
    End If
    
strOutputToTemp = "C:\Users\Audit_Completed" & "_" & InquiryNum & ".PDF"

'''Output to Temp Area (to C:\Users)
DoCmd.OutputTo acOutputReport, "rptAudit_Emails_EMP_NoErrorsNoAction", acFormatPDF, strOutputToTemp, False

'Set Attachments (from Temp Dir on C Drive)
strAttachTemp = strOutputToTemp

' Copy to Network
'strAttachCopy = "\\Wiw2pwpfle001\data\QA Database\Employee Audit Scorecard System\Audit_Completed\Audit_Completed" & "_" & InquiryNum & ".PDF" 'OLD PATH -- NOT WORKING as of 7-15-2014
strAttachCopy = "\\w2pwpfp001\data\QA Database\Employee Audit Scorecard System\Audit_Completed\Audit_Completed" & "_" & InquiryNum & ".PDF"

' Variable/Path (to copy from C: Drive to Network Drive Folder for Completed Audits
FileCopy strAttachTemp, strAttachCopy

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

'Create the email message and send
'Set objMessage = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
    .To = varEmpEmail & "; " & "seniorqaebrteam@wellpoint.com"
'    .CC = varAddlEmail
    ' Set the Subject, Body, and Importance of the message.
    .Subject = "Audit Completed With No Errors -- No Action Required as of " & Now() & ""
    .HTMLBody = "Attached you will find a copy of your Quality Audit Scorecard.   If you would like to challenge your audit, your response must be " & _
             "received within 2 business days of the receipt of this message. Challenges received after 2 business days will not be accepted." & _
             "<BR><BR>" & "All challenges must be completed using the proper challenge form found within the QA Audit Challenge Process (QLA02); challenges on the wrong form will not be accepted." & _
             "<BR><BR>" & "Please see your OE for assistance should you have questions on the challenge process.   Do not contact your auditor by phone to challenge an audit." & _
             "<BR><BR>" & "Remember that this audit is a way for us to help you achieve the goals and objectives set by management." & _
             "<BR><BR>" & "Sincerely," & "<BR><BR>" & "Your Medicare Programs Quality Audit Team"

    ' Add attachments to the message.
    If Dir(strAttachTemp) <> "" Then
       .Attachments.Add (strAttachTemp)
    End If

    ' Resolve each Recipient's name.
    For Each objOutlookRecip In .Recipients
        objOutlookRecip.Resolve
    Next

'        .Save
        .Send

End With

Set objOutlook = Nothing

DoCmd.SetWarnings True
DoCmd.Echo True

''Remove attachments from C:\Users
Kill strAttachTemp
''Kill strAttach1
''Kill strAttach2
''Kill strAttach3
''Kill strAttach4
''Kill strAttach5

Exit_Handler:
    Exit Function

Err_handler:
    DoCmd.CancelEvent
    MsgBox Err.Description
    Resume Exit_Handler
End Function

Open in new window

LVL 1
gdunn59Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
In A2010 ... you know you can directly specify PDF in the DoCmd.OutputTo command .... specifying acFormatPDF
One line of code does it ... since A2010 directly supports PDF ... no Adobe Acrobat required.

mx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gdunn59Author Commented:
mx,

Yes, that is how my code is written, but as I mentioned since upgrading to Windows 7/Access 2010, it is taking extremely long to process the report to PDF, whereas before the upgrades, it was only taking a few seconds (using the same code that I posted).

Any other suggestions?

Thanks,
gdunn59
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Sorry ,,, some how I completely missed that line of code. For some reason, I thought you were still switching printers to do PDF, sorry.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

gdunn59Author Commented:
mx:

No problem.  Do you have any other suggestions?

Thanks,
gdunn59
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
I really don't, sorry.
 Here is why I was confused:

"and I need to know how to incorporate code to have an Access Report output to a PDF file, and then after the output is completed, change Printer back to the original Printer."

IRONICALLY, my code is also in a QA database. What are the chances ???

And prior to switching to A2010, I had to installed virtual PDF printer (required full Acrobat) and configure it a certain way ... then, temporarily change printers to that printer, print, then restore the default printer.
Created a PDF in a Temp folder, then copied up to a specific on our share drive, then deleted the temp PDF.  That typically took 10-15 seconds, and was prone to hanging up !

Now ... with the OutputTo and PDF option, it's 2-3 seconds and never hangs up.
0
gdunn59Author Commented:
mx:

So what did you do . . . "Now ... with the OutputTo and PDF option, it's 2-3 seconds and never hangs up . . . "?

Where you said it worked and only took 2-3 seconds and never hangs up, and you're using 2010 also?

Thanks,

gdunn59
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
I'm just using

DoCmd.OutputTo command .... specifying acFormatPDF

That replaced a  LOAD of code to change printers, etc.  

Are you saying that line of code is taking a long time to execute ?
0
gdunn59Author Commented:
Yes.  That line is where its taking 20+ minutes now.  Before my upgrades to Windows 7 and Office/Access 2010 it was only taking a couple of seconds.
0
gdunn59Author Commented:
In my variable for the output:  
strOutputToTemp = "C:\Users\Audit_Completed" & "_" & InquiryNum & ".PDF"

Should I remove the:
        & ".PDF"
at the end of the variable/string since I have it outputting to PDF, or does that matter?
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Let me check my code at work tomorrow ...
Pretty sure I am including .PDF also ...

Very odd ...
0
IT Project MgrVBA/SQL developerCommented:
Here is some generic code to switch the default printer - but as mentioned above, you should not need to do this.  Is the variable "InquiryNum" a public variable that is defined somewhere else?  I cannot see where it would know this value otherwise...

Dim prt As Printer

'current default printer
prt = Application.Printer

'set the default printer to the one you want - whatever your pdf printer is called.
Application.Printer = Application.Printers("Adobe PDF")  

'print something
DoCmd.PrintOut

'change it back to the original default printer
Application.Printer = prt
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
>>  Application.Printers("Adobe PDF")    <<

The problem here will be that you will get unwanted prompts using the standard Adobe PDF Printer.
That's why I had to create a 2nd version of the PDF Printer, then set properties such that the prompts did not occur.

mx
0
gdunn59Author Commented:
mx,

For some reason the code/reports/queries that I was originally using when I had Windows XP and Office/Access 2003 just does not want to process quickly with Windows 7 and Office/Access 2010; so I went through the reports that I was having latency issues with and changed the record source from a query to a table.  I took the query that it was using as the data source before the upgrades, and changed it to a "Make Table" Query, and then used the table created from this query, as the data source.  Now it only takes a couple of seconds to process, same as before I upgraded.  

Still baffled as to why it wasn't an issue with the older versions, but anyway, it's working fine now with the Docmd OutputTo . . . code.

I would like to still award you the points since you were the first to reach out to me.

Thanks much,

gdunn59
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
wow ... that ... is very strange, and I have no explanation.
But the good news is you got it working :-)

mx
0
gdunn59Author Commented:
mx,

Yes very strange, but very happy I found another alternative to the issue.

Thanks again for all your assistance/time.

Have a blessed weekend and holiday!!!!!

gdunn59
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.