MS Access Report To PDF

[Suaveboy]

Hello all, I am trying to accomplish the following; I have a MS Access Form ( MS Access 2002) which has a botton used to send an email based on the current record on the form and I want to do two things at the same time if possible,

I want that when the user click on the "SEND EMAIL" botton, the system generates the email with the report as a PDF format. Right now I can generate the email using the following code;

Private Sub Email_Report_of_Current_Record__s__Click()
On Error GoTo Err_Email_Report_of_Current_Record__s__Click

Dim strReportName As String
Dim strCriteria As String
Dim stDocName As String

strReportName = "rpt_My_Report"
strCriteria = "[BusOwner]='" & Me![BusOwner] & "'"
stDocName = "rpt_My_Report"

DoCmd.OpenReport strReportName, acViewPreview, , strCriteria
DoCmd.SendObject acReport, stDocName, , strCriteria, , , "EMAIL SUBJECT", "EMAIL MSG"

Exit_Email_Report_of_Current_Record__s__:
Exit Sub

Err_Email_Report_of_Current_Record__s__Click:
MsgBox Err.Description
Resume Exit_Email_Report_of_Current_Record__s__

End Sub

However, I cannot seem to be able to

1.) send the report as a pdf file
2.) include another attachment within the same code so that everything happens at once. (like an instruction's document)

I know how to print to a PDF printer and how to attach a file in an email, but the purpose of automating is to have as few keystroke as possible and program the system to do what you what to do specially when you need to repeat the process over 100 times.

ANY ONE CARE TO ADVENTURE ON AN ANSWER?

P.S. I have seen some suggestions, but some do not work with Access 2002, others want you to buy Acrobat Wrtier and some have to be done through a module rather than embeding the code within a FORM botton.

My email is Chiqolate@hotmail.com if you care to discuss any further.

 

[greaseman]

I've attacheda link to a thread in another Access forum (I know, I know....) that might help you. Check it out and good luck. Here's the link

http://www.utteraccess.com/forums/s...pe=&olderval=&oldertype=#Post306684&bodyprev=

 

[mazza]

A quick fix would be to print the report as a print peview report. Use Control P
to open the print dialog and select the PDF printer. Once PFD file is created use file send.

Pfd writer (Adobe or any other pdf creating software is required on each PC).

 

[ghudson]

You can call the print dialog if you want to allow your users to select the printer of their choice. This does not solve your problem but it gives your users the ability to print [save] the report in the format they want and to use the printer of their choice. The Adobe "printer" option will allow them to choose where the file will be saved to and the file name to be saved.

DoCmd.RunCommand acCmdPrint

Ensure that you trap for error # 2501 [if the user clicks the Cancel button].

You could open the report in the print preview mode and then call the print dialog or give the user a toolbar button to call it when needed.

 

[greaseman]

Did anyone take a look at the link I posted yesterday? With a small bit of effort, the info provided there should do the trick.

 

[mazza]

How would I trap the error code?

I would use it in a runtime application and as soon as you get an error message it will close down the application.

 

[greaseman]

Also from GHudson, here is an example of trapping for error codes. You can also do a search on this forum for "Error Codes", which is how I found this good example. Keep in mind this is only an example!


Code:
Private Sub bDeleteRecord_Click()
On Error GoTo bDeleteRecord_Click_Err

Beep
If MsgBox("Delete current record?", vbQuestion + vbYesNo) = vbYes Then
DoCmd.RunCommand acCmdDeleteRecord
End If

bDeleteRecord_Click_Exit:
Exit Sub

bDeleteRecord_Click_Err:
If Err = 2046 Then 'The command or action 'DeleteRecord' isn't available now
Exit Sub
ElseIf Err = 2501 Then 'The RunCommand action was canceled
Exit Sub
Else
MsgBox Err.Number & " - " & Err.Description
Resume bDeleteRecord_Click_Exit
End If

End Sub

 

[TMN]

I've attacheda link to a thread in another Access forum (I know, I know....) that might help you. Check it out and good luck. Here's the link

http://www.utteraccess.com/forums/s...pe=&olderval=&oldertype=#Post306684&bodyprev=

Hi, trying to acces your link, but getting the message:"Forum not found."

Something missing?

BR
TMN

 

[sha7jpm]

this is what we use

i took this and added bits in with a lot of help from guys here on the forum

you may be able to take some useful bits out of it...

it takes a list of recipients (in my case schools (estab))

filters through them and prints a report to pdf dumping it in my documents.

the key is to make your pdf converter (you can download free versions from the net) the default printer and to remove all prompting options so to allow it to run through automatically

you could then add in your code which picks up the file and attaches it to the email.

in your report you need to ensure that your title is empty to allow the code to populate a report name of your choice eg report9_1234.pdf (where 1234 is your recipient.

ps. this codes means you have to create a dummy blank query in the code below it is - pdf_school_profile

good luck!

john

************

Private Sub schprofile_Click()
Dim repQuery As QueryDef
Dim dBase As Database
Dim rsRep As DAO.Recordset
Dim strrep As String
Dim data1 As String
Dim data2 As String


Set dBase = CurrentDb()
Set repQuery = dBase.QueryDefs("pdf_school_profile")
Set rsRep = CurrentDb.OpenRecordset("school_list_for_pdf_export")

Do While Not rsRep.EOF
data1 = rsRep.Fields("SLD_PK").Value
data2 = rsRep.Fields("ESTAB").Value

repQuery.sql = "SELECT * FROM school_profile WHERE ((([school_profile].SLD_PK)= " & data1 & "));"
repQuery.Close

DoCmd.Rename "school_profile_" & data2, acReport, "school_profile"
DoCmd.OpenReport "school_profile_" & data2, acViewNormal
DoCmd.Rename "school_profile", acReport, "school_profile_" & data2
DoCmd.Close acReport, "school_profile"
rsRep.MoveNext
Loop

Set rsRep = Nothing

Set rsRep = Nothing

End Sub

 

[greaseman]

Hi, TMN.....

Try this link.....

http://www.utteraccess.com/forums/s...d=allposts&Main=245541&Search=true#Post245541

Just copy and paste it into your search screen and give it a go.

Please be aware it may not be the most recent version of this concept, but at least it'll get you into the neighborhood, so to speak.

Let me know if you have problems, or questions.