Report to Pdf to Desktop

[steve21nj]

Quick question about my code. Two days ago I was able to save my report to my desktop, yesterday the pdf file began saving to C: My Documents. I do not remember making any changes to the code to change the file save location, any suggestions on how I can re-route to the desktop again?

Private Sub Command33_Click()
On Error GoTo Err_Command33_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
    
    stDocName = "NFTSrpt"
    stLinkCriteria = "[NFTSID]=" & Me![NFTSID]
    
    DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria, acHidden
        
    DoCmd.OutputTo acOutputReport, stDocName, acFormatPDF, stLinkCriteria & ".pdf", , True
    
    DoCmd.Close acReport, "NFTSrpt"
    
    MsgBox "Report has been saved to Desktop"
    
Exit_Command33_Click:
    Exit Sub
Err_Command33_Click:
    MsgBox Err.Description
    Resume Exit_Command33_Click
End Sub

Thank you!

 

[boblarson]

Right now you are using stLinkCriteria as the file name and it is probably confusing the hell out of Access. Why in the world would you do that?

You need to generate the CORRECT file name (including PATH) so that it will go where you want. So, what do you want the report named when it comes out?

 

[steve21nj]

Right now you are using stLinkCriteria as the file name and it is probably confusing the hell out of Access. Why in the world would you do that?

I was reading different links on how to save to pdf and pieced it together! Plus I enjoy your responses as you are yelling at me lol!

So, what do you want the report named when it comes out?

The file name isn’t extremely important but if we can go specific!

I’d like the report name to be [NFTS]_[Report]-[NFTSID]-[Short Date] = NFTS Report-14-10/24/2012

The report current saves as [NFTSID]=14

I’d like to save right to desktop so it can later be attached to an email with another internal report that is produced, non access related, and then later deleted. This mini database will be used by about 600 users (not at the same time) so having a specific folder or path other than the desktop will not be needed.

 

[boblarson]

Private Sub Command33_Click()
On Error GoTo Err_Command33_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
[COLOR="Red"]    Dim stReportPathName As String[/COLOR]

    stDocName = "NFTSrpt"
    stLinkCriteria = "[NFTSID]=" & Me![NFTSID]

[B][COLOR="red"]    stReportPathName = "C:\Users\" & Environ("UserName") & "\Desktop\NFTS Report-" & Me!NFTSID & "- Format(Date, "mm-dd-yyyy") & ".pdf"[/COLOR][/B]

    DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria, acHidden
        
    DoCmd.OutputTo acOutputReport, stDocName, acFormatPDF, [B][COLOR="red"]stReportPathName [/COLOR][/B], , True
    
    DoCmd.Close acReport, "NFTSrpt", [B][COLOR="red"]acSaveNo[/COLOR][/B]
    
    MsgBox "Report has been saved to Desktop"
    
Exit_Command33_Click:
    Exit Sub
Err_Command33_Click:
    MsgBox Err.Description
    Resume Exit_Command33_Click
End Sub

The path C:\Users\..etc. will work if on Windows Vista or Windows 7. If on Windows XP or earlier you would need to change it to:

stReportPathName = "C:\Documents and Settings\" & Environ("UserName") & "\Desktop\NFTS Report-" & Me!NFTSID & "- Format(Date, "mm-dd-yyyy") & ".pdf"

 

[steve21nj]

Thank you for the help. When I added the updated info, I generated a Compile error: Syntax error on the line. This represents ReportCapture.png. I then hit enter after the end of the line ....& ".pdf" and it generated expected: end of statement, represented in 2ReportCapture.png, any suggestions?

Private Sub Command33_Click()
On Error GoTo Err_Command33_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim stReportPathName As String
    stDocName = "NFTSrpt"
    stLinkCriteria = "[NFTSID]=" & Me![NFTSID]
 
    [COLOR=red]stReportPathName = "C:\Users\" & Environ("UserName") & "\Desktop\NFTS Report-" & Me!NFTSID & "- Format(Date, "mm-dd-yyyy") & ".pdf"[/COLOR]
 
    DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria, acHidden
 
    DoCmd.OutputTo acOutputReport, stDocName, acFormatPDF, stReportPathName, , True
 
    DoCmd.Close acReport, "NFTSrpt", acSaveNo
 
    MsgBox "Report has been saved to Desktop"
 
Exit_Command33_Click:
    Exit Sub
Err_Command33_Click:
    MsgBox Err.Description
    Resume Exit_Command33_Click
End Sub

 

[boblarson]

Sorry, I missed a double quote and ampersand in there.

stReportPathName = "C:\Users\" & Environ("UserName") & "\Desktop\NFTS Report-" & Me!NFTSID & "-" & Format(Date, "mm-dd-yyyy") & ".pdf"

 

[steve21nj]

Thank you!

I was wondering, can I take the existing code and use it somehow for a different command for an email making the file name appear correctly in an email? If so where would I edit that in my code? We have the stReportPathName, could we use something similar in the naming for the sendobject?

Right now the email attaches fine, just with a different name. But I noticed that if I cancel the action, then reclick the Command45 to email, it will open the report (non hidden) along with the email. Is there a way around that?

Private Sub Command45_Click()
 
On Error GoTo Err_Command45_Click
    Dim stReportPathName As String
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stReportPathName = "C:\Users\" & Environ("UserName") & "\Desktop\NFTS Report-" & Me!NFTSID & Format(Date, "-MMDDYYYY") & ".pdf"
    stDocName = "NFTSrpt"
    stLinkCriteria = "[NFTSID]=" & Me![NFTSID]
 
    DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria, acHidden
    DoCmd.SendObject acSendReport, "NFTSrpt", acFormatPDF, , , , NFTS_Report, "Attached is the NFTS Report for our shift", True
 
Exit_Command45_Click:
    Exit Sub
Err_Command45_Click:
    MsgBox "Your email was not sent, please close and try again"
    Resume Exit_Command45_Click
 
End Sub

 

[boblarson]

Using SendObject is very limited. What email system are you on? You can use code to send email and not use SendObject which can be more flexible.

 

[steve21nj]

We use Outlook 2010

 

[boblarson]

We use Outlook 2010

Cool. That is easy to automate.

Do a search for "send email to outlook" in the search box on the forum. Lots of threads about it.

 

[steve21nj]

Email Report to Pdf

Bob,

I did some searching and came up with the below. The first time I ran it, it worked perfect. Now when I run it, it pulls every record instead of the current. Any suggestions?

Private Sub My216Email_Click()
 
    Dim appOutLook As Object
    Dim MailOutLook As Object
 
 'creates the reports temporarily
    DoCmd.OutputTo acOutputReport, "NFTSrpt", acFormatPDF, "C:\Users\" & Environ("UserName") & "\Desktop\NFTS Report-" & Me!NFTSID & Format(Date, "-MMDDYYYY") & ".pdf", False
 
'assign our object references
    Set appOutLook = CreateObject("Outlook.Application")
    Set MailOutLook = appOutLook.CreateItem(olMailItem)
 
With MailOutLook
 
        'set the subject
        .Subject = "NFTS Shift Report"
 
        'set the body text
        .body = "Attached is the NFTS Report in pdf format"
 
        'add the reports we created
        .attachments.Add "C:\Users\" & Environ("UserName") & "\Desktop\NFTS Report-" & Me!NFTSID & Format(Date, "-MMDDYYYY") & ".pdf"
        'send the email
        .Display
 
    'get rid of our object references
    Set appOutLook = Nothing
    Set MailOutLook = Nothing
    'delete our temporary files
    Kill "C:\Users\" & Environ("UserName") & "\Desktop\NFTS Report-" & Me!NFTSID & Format(Date, "-MMDDYYYY") & ".pdf"
    End With
End Sub

 

[boblarson]

You would need to open the report first (hidden of course) using the Where Condition in the Open code (remember to use acViewPreview and not acViewNormal).

 

[steve21nj]

The code works! But still brings me back to post 7.

When I search, I open my form [FRM_SearchMulti], double click the record and open to [ReviewI216]. I click the command and run the private sub MY216Email. If I cancel that action, save and close the form, double click another record from [FRM_SearchMulti], click private sub My216Email, the report opens in access (not hidden this time), and attaches the previous record to the email.

I tried adding a setfocus and requery on the close event of ReviewI216 with no luck.

Private Sub Form_Close()
Forms!FRM_SearchMulti.SearchResults.SetFocus
Forms!FRM_SearchMulti.SearchResults.Requery
End Sub

Final Code For Converting Report to PDF to Email

Private Sub My216Email_Click()
 
    Dim appOutLook As Object
    Dim MailOutLook As Object
 
    stDocName = "NFTSrpt"
    stLinkCriteria = "[NFTSID]=" & Me![NFTSID]
 
    DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria, acHidden
 
 'creates the reports temporarily
    DoCmd.OutputTo acOutputReport, "NFTSrpt", acFormatPDF, "C:\Users\" & Environ("UserName") & "\Desktop\NFTS Report-" & Me!NFTSID & Format(Date, "-MMDDYYYY") & ".pdf", False
'assign our object references
    Set appOutLook = CreateObject("Outlook.Application")
    Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
 
        'set the subject
        .Subject = "NFTS Shift Report"
 
        'set the body text
        .body = "Attached is the NFTS Report in pdf format"
 
        'add the reports we created
        .attachments.Add "C:\Users\" & Environ("UserName") & "\Desktop\NFTS Report-" & Me!NFTSID & Format(Date, "-MMDDYYYY") & ".pdf"
        'send the email
        .Display
 
        'tidy up..
 
    'get rid of our object references
    Set appOutLook = Nothing
    Set MailOutLook = Nothing
    'delete our temporary files
    Kill "C:\Users\" & Environ("UserName") & "\Desktop\NFTS Report-" & Me!NFTSID & Format(Date, "-MMDDYYYY") & ".pdf"
    End With
End Sub

 

[boblarson]

1. I don't think you should be setting the object references to nothing before the mail is sent.

2. Remember to add code to close the report after the DoCmd.OutputTo code.

3. You can release the Outlook stuff once you have the .Display part there by using

MailOutlook.UserControl = True

That might help. But I think the main problem is that you never close the hidden report.