The app I'm working on needs to email a report to users preferably in PDF format as not all users/customers have Word
Does anyone have any ideas on creating pdf's automagically? I cannot set the printer default to a pdf print driver as the user needs to print reports - the user will be a warehouse worker with NO computer knowledge so I need to keep this as simple as possible. The best scenario is to email the pdf at the same time he generates his copy of the report without him having to name files or enter email addresses, etc. - just push a button .... KISS principle
A less desirable option is to do the same in Word and email to someone who knows how to convert but that sort of negates some of the productivity gains we're trying to achieve.
What level of expertise are your VBA skills. I believe this one will take some expert level skills. It will be too time consuming if you don't have expert skills. If you do have expert skills, then I will try to help you through this. Otherwise, it will take way too long to try to help.
Lets just say I'm on the uphill slope of of a pretty steep learning curve. I've been away from vba for 5+ years and when I was using it I had access to a REAL VB expert so I can't say I was ever proficient in vba. I am, however, very persistent and stubborn and intend to get this project completed as planned, even if it involves more than a few sleepless nights. My first Access app was a help desk for a local manufacturer - I sold it for cheap $ figuring I could modify a sample contact manager app - wrong - in the end I had at least 200 hours in the project giving me less than minimum wages - the bright spot was it opened the door to more opportunities with them. The app I'm doing now is similar in that I'm using it to open a few doors with a VERY large company that has an unmet need for services in an industry that is not normally computerized. The vendor of theirs that I'm doing this for is currently using an array of Excel spreadsheets to track info and reports, etc. The parts I've delivered have already demonstrated significant savings in turnaround and manpower, not to mention accuracy, enough that the LARGE company is pursuing my client for larger contracts.
In short - I am good at tweaking other folk's code.
You could email the report in a snapshot format, I use this format to email reports. The report is sent as an attachement with a file extension .snp. Most users should have the file SNAPVIEW.EXE already installed on their computer to view files in the snapshot format.
I may have to use snap files for now because I'm under time constraints to get things done yesterday (or maybe its the day before?) The big problem is sending snap files to clients - I know for sure that one larger client does not support snap files and according to junior IT, any additional user software has to be approved by senior management. I've been involved in that type of scenario before so it looks like pdf's will be the final solution.
In the meantime I dl'd a sample from lebans that may do the trick. Does anyone have any experience with Leban's Report to PDF code?
I had a quick look at the code, the report to PDF works if the report is simple that is no parameters are used e.g. selected date range using a form etc.
I have modified his code when using a dropdown on a form to generate a report ,I put the dll files in the c:\windows\system32 folder or they could be in same directory as the database.
In the attached sample, use frm_rpt_selected_staff and look at the code behind the PDF format button.
This line of code generates the report in a snapshot format to c:\ drive with a file name staff_rpt.snp, you will need to change this location for your use. DoCmd.OutputTo acReport, "rpt_selected_staff", "SnapshotFormat(*.snp)", "c:\staff_rpt.snp", False, ""
The next line is the location of the snapshot to be converted to pdf format. sName = "c:\staff_rpt.snp"
The conversion continues as per the original code.
My first attempt was to use PDF995 - it did the conversion to PDF OK, and the email worked flawlessly in the background. I did have problems switching print drivers but the biggest problem was saving to unique filenames. The option to do so just wouldn't work (pdf995 is supposed to number sequentially starting with a preconfigured number) programmatically renaming the file seemed a royal pain and the print driver problem seemed like it could cause user problems long term.
I then tried Stephen Lebans' PDFtoReport - it was a little tricky at first but it really works - I was able to programmatically name the files the way I wanted and not have to mess around with print drivers. I reconfigured the email portion of pdf995 to do the background emailing but it started getting a bit messy - it also has a problem with the fact that your settings are held in an ini file - to use it differently within the app, or with a different app you would have to change ini settings, something I have no clue about - I found a free SMTP COM library from http://www.ostrosoft.com/ (license is $19 for each commercial application) that works really well - it does everything pdf995's email does and then some, all without the use of an ini file. I was able to control subject and message via VBA. I'm now working on generating the recipient lists with VBA - I will have several email configurations that send different reports to different people - I plan on using a MailConfig Table and Recipient Table for each variation which will allow the app administrator to change things rather than hard code the settings.
Does anyone have experience with Ostrosoft? I'm having a problem integrating it into my form - it works if I call up a mail form from the report form but I would like to do it all from a single button - I keep getting undeclared variables and function errors, although everything appears to be the same as the separate mail form???
How to generate report in PDF format
Reply With Quote
Report in PDF format
PDF progress
