Creating PDF from specific report

[MikeGreen]

Hi all
I last touched Access about 9 years ago when I was quite good and I wrote a number of successful databases. As I no longer us it I have just discovered the level of skill fade that I have acquired when a friend asked me to update a database that I wrote for him (that's how I know it was 9 years ago!).
He has requested the following update:
1. Create a PDF from a specific report automatically. I'm stuck!
2. Attach it to an email. The email needs to auto send the email in the back ground (through Outlook is fine). I think that this is OK
3. save a copy of the PDF locally. I can do this
4. Update the database that the email is sent. This is easy!

As I have the database working fine and creating a print preview of the required report (I have set link criteria to get the correct report which can have a single or a number of record sets) I am stuck on how I can get VB to create the PDF from the report?
My research has revelled using DoCmd.OutputTo acOutputReport but I am stuck on how to get it to PDF the current report with the correct record set?
I can post my current attempts if required? I current get a message "The current OutputTo action was cancelled"
The lines that I think are causing the problem (all based on snippets that I have researched) are
-----------------------------------------------------------------------------
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
DoCmd.OutputTo acOutputReport, "", acFormatPDF, MyPath & MyFilename, False
------------------------------------------------------------------------------
I have all of the strings declared earlier in the code.
Any advice and guidance from the experts will be appreciated. How do I PDF the current report please?

 

[James Deckert]

Putting an empty string ("") in the second argument looks suspicious to me. This is saying ton not give it a name. It's an optional argument, but if you don't want to specify it leave out the "".
The other thing to try is to stop the code in debug and look at MyPath & MyFilename to make sure it's correct. Look for the slash between the path and filename to make sure it's there also.

 

[MikeGreen]

James
Thanks for the quick reply. I also was suspicious of the "". I have tried omitting the "" but it returns the same error "The current OutputTo action was cancelled" result. I have run through Debug and everything looks fine with no errors. The file path etc are like this-
-----------------------------------------------------------------
Dim stDocName As String
Dim stLinkCriteria As String
Dim MyPath As String
Dim MyFilename As String

stDocName = "RptInvoice"
stLinkCriteria = "[InvoiceID]=" & Me![InvoiceID]
MyFilename = Me.Invoice
MyPath = "C:\My Documents\InvoiceArchive\" & MyFilename & ".pdf"

Me.InvoicePrintedDate = Date
Me.InvoiceAmount = Me.Text43
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
-----------------------------------------------------------------------
Thanks again.

 

[Gasman]

Your 'MyPath' has the filename already in it from your last post, and you are adding it again in the first post?

 

[Minty]

The lines

DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
DoCmd.OutputTo acOutputReport, "", acFormatPDF, MyPath & MyFilename, False

Should have the report name also in red;

DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
DoCmd.OutputTo acOutputReport, [COLOR="Red"]stDocName[/COLOR], acFormatPDF, MyPath & MyFilename, False

Depending on the version of access you are using the switch acExportQualityPrint will improve the quality of the pdf

 

[MikeGreen]

Minty, thank you for the feedback. I have tried this and I get the same message "The current OutputTo action was cancelled". and nothing happens regarding the creation of the PDF report. I am suspicious of this solution as there is no setting of the required link criteria. The report needs the record set reference as just using the report name will dump all of the report pages (hundreds of them) as it has no specific report identified.
Thanks anyway.
I am really scratching my head as to why I am getting the message "The current OutputTo action was cancelled"? Unless it's because it does not know which specific report to create the PDF from? The Print preview appears perfectly though from the DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria command.

 

[MikeGreen]

Just to let you all know that I have managed to sort out the vba that I required. Just for completeness here's the final code:-
_______________________________________________________________________
Dim stDocName As String
Dim MyPath As String
Dim MyFilename As String
Dim oApp As New Outlook.Application
Dim oEmail As Outlook.MailItem
Dim Mtext As String

Mtext = "This is to allow multiple lines in the email message"
stDocName = "RptInvoice1"
MyFilename = Me.Invoice
MyPath = "C:\InvoiceArchive\" & MyFilename & ".pdf"
'Don't use spaces in the file path!

If IsNull(Me.FrmCustomerSmall.Form.EmailAddress) Then
MsgBox "Cannot send email as there is no email address.", _
vbOKOnly + vbCritical, _
"Attention! Attention!! Attention!!!"
DoCmd.CancelEvent
'Makes sure that there is an email address to send to
Else
Me.FdInvoiceEmailSentDate = Date
Me.InvoiceAmount = Me.Text43
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
'Save the record

DoCmd.OpenReport stDocName, acPreview
'This is just to show the user that something is happening

DoCmd.OutputTo acOutputReport, stDocName, acFormatPDF, MyPath, False
'Print and save. Once you see it works, you can change True to False so that the file created is not opened after completion.
'Close our previewed report
If oApp Is Nothing Then
'Check if Outlook is already open
Set oApp = New Outlook.Application
'If it is not open then open it in the background
End If

Set oEmail = oApp.CreateItem(olMailItem)

oEmail.To = Me.FrmCustomerSmall.Form.EmailAddress
oEmail.CC = ""
oEmail.Subject = "Customer Invoice No: " & MyFilename
oEmail.Body = Mtext
oEmail.Attachments.Add MyPath

With oEmail
If Not IsNull(.To) Then
.Send
MsgBox "Email has been succesfully sent.", vbInformation
Me.FdInvoiceEmailSent = True
'Checking that there is an address to send to and telling the user what is happening
Else
MsgBox "We have a problem!", _
vbOKOnly + vbExclamation, _
"Attention! Attention!! Attention!!!"
'If there is something wrong
End If

End With
DoCmd.Close acReport, stDocName
'Just cleaning up
End If
End Sub
_____________________________________________________________________

If anybody has any tips or recommendations on this code please let know.
Thanks again.

 

[zpy2]

vba scripts look fine.
can you upload your accdb file for debug？

 

[Gasman]

So it helped by taking out MyFile out of that line of code?, as well as adding the stdocname variable?

Just to let you all know that I have managed to sort out the vba that I required. Just for completeness here's the final code:-
_______________________________________________________________________
Dim stDocName As String
Dim MyPath As String
Dim MyFilename As String
Dim oApp As New Outlook.Application
Dim oEmail As Outlook.MailItem
Dim Mtext As String

Mtext = "This is to allow multiple lines in the email message"
stDocName = "RptInvoice1"
MyFilename = Me.Invoice
MyPath = "C:\InvoiceArchive\" & MyFilename & ".pdf"
'Don't use spaces in the file path!

If IsNull(Me.FrmCustomerSmall.Form.EmailAddress) Then
MsgBox "Cannot send email as there is no email address.", _
vbOKOnly + vbCritical, _
"Attention! Attention!! Attention!!!"
DoCmd.CancelEvent
'Makes sure that there is an email address to send to
Else
Me.FdInvoiceEmailSentDate = Date
Me.InvoiceAmount = Me.Text43
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
'Save the record

DoCmd.OpenReport stDocName, acPreview
'This is just to show the user that something is happening

DoCmd.OutputTo acOutputReport, stDocName, acFormatPDF, MyPath, False
'Print and save. Once you see it works, you can change True to False so that the file created is not opened after completion.
'Close our previewed report
If oApp Is Nothing Then
'Check if Outlook is already open
Set oApp = New Outlook.Application
'If it is not open then open it in the background
End If

Set oEmail = oApp.CreateItem(olMailItem)

oEmail.To = Me.FrmCustomerSmall.Form.EmailAddress
oEmail.CC = ""
oEmail.Subject = "Customer Invoice No: " & MyFilename
oEmail.Body = Mtext
oEmail.Attachments.Add MyPath

With oEmail
If Not IsNull(.To) Then
.Send
MsgBox "Email has been succesfully sent.", vbInformation
Me.FdInvoiceEmailSent = True
'Checking that there is an address to send to and telling the user what is happening
Else
MsgBox "We have a problem!", _
vbOKOnly + vbExclamation, _
"Attention! Attention!! Attention!!!"
'If there is something wrong
End If

End With
DoCmd.Close acReport, stDocName
'Just cleaning up
End If
End Sub
_____________________________________________________________________

If anybody has any tips or recommendations on this code please let know.
Thanks again.