Create a report PDF then email

[Adzi]

Hi There,
I'm developing a bookings system for work. The outcome is a report of the booking confirmation. I've found software that will "print" to Adobe PDF format however, is there any way to attach the specific confirmation report to a standard email and then send to the customers supplied email address.

The email client being used is Outlook 2003 and the system is currently being developed in Access 2007.

The confirmation reports are generated through the booking reference from a function and public variable GetBookingRef.

Any help is greatly appreciated.

 

[ted.martin]

There are two aspects to this; firstly to create the .pdf then e-mail it.

Just import the two modules called clsCommonDialog and Output Quotation in PDF.

You will then need to adapt the other to suit your requirements. Be careful to declare your variables.

There is a form and report included too.

Good luck.

 

[scouser]

PDF from cmd button

Hi Ted (sorry for hijacking this thread)!!


My brother has requested I do simliar for him. Currently from a sales order form he hits a cmd button that allows him to preview his formatted report. He can then hit send as email attachement then select format to send in (rich text / excel / html). PDF is not an option .Tested sending as html but report loses formatting.He can print to pdf converter, save as pdf file then attach to email from his hard drive (I installed PDF converter software).

Finally I arrive at my question:

How can I use your code to generate the pdf then send as attachment from his cmd button 'Email as PDF' all in one go?

I am not much of a programmer!!

Many Thanks,
Phil.

 

[ted.martin]

I have uploaded a revised version of the zip file as I had omitted the Calling Sub. No need for a PDF writer as the modules sort that out.

You will need to be careful of the variables but essentially once you call the Output_Quotation_in_PDF() code, the rest is fairly automatic.

My code is based upon a Report being output and the name of that report is required in the above.

Hope this works for you and if you get stuck I will try to help.

Ted

 

[scouser]

Thanks

Ted I will have a play and report back.
Regards,
Phil.

 

[bsnapool]

Hi,

Also hijacking this thread. Is there any chance of an earlier version access to do this in? This would be perfect for project im working on????

 

[ted.martin]

What version of Access?

 

[bsnapool]

Access 2000

 

[ted.martin]

Access 2k version attached. Remember to change the TOOLS/REFERENCES to the Object Libraries in your version.

 

[bsnapool]

Thanks Ted, much appreciated.

A

 

[Big Pat]

Yet another hijack!

Ted, you must be getting tired of this but you're providing invaluable help here and I'm sure everyone is really grateful. You sound like you know your stuff on this topic and might be just the guy to help me. I've downloaded your zip file above, but most of it is WAY over my head and I think my problem is probably simpler anyway.

I have a database with several reports, but there are three in particular, which I run all together from a single command button that activates a macro. I need these reports (and only these three) to be "printed" to the users' cutePDF printer, which is installed but is not the default.

Is there some code I can put into the OnOpen event of each report, so that it will set the printer to cutePDF and then go ahead and print the report? This would then bring up the Save As dialog, allowing the user to save it.

And would I need similar code in the OnClose event to reverse things and set the printer back to what it was before?

Ideally, I don't want the users to be presented with any dialogs or choices - they're scared of them - and I'll get pestered by calls.

Alternatively - is there something I can do from within the macro itself? Beware, my programming skills are VERY BASIC!

Grateful for any help you can offer.

 

[ted.martin]

I didn't realise my code which after all really originated elsewhere was that popular. Thanks for the compliment, I wish it were true.

Anyway, I did some work a while ago on selecting printers. The attached module and report does this. There may well/will be better ways as different pc's may well have different printer numbers associated with them in which case, each pc will need its own code version. I guess it wouldn't be rocket science to get the use to select the required printer number but if you went that far, they may as well change the default. Anyway, have a go and see if it helps.

 

[Big Pat]

Ted, I just wanted to come back and say a BIG thank you. It took me a while to understand what the application you sent did, because I thought I needed to build that whole thing into my application. But of course I just need to run it on the users PC to get the "number" of his pdf printer and then set that in the report.

I've done that now and it works a treat. You're a star!

 

[ted.martin]

Glad to have been of help.

 

[capsula4]

Hey Ted / Helpful Friend ! Not sure if you are still on these forums but I gotta say that your module is getting really popular!

I'm a bit newbie into VBA and I imported the modules you created (those 3) and also the sample form.

But I got some doubts.

- Shall I create a field where email body text is stored?
- What variables and in which module do I gotta modify?
- I have different client emails already stored in a tlkpClients, shall I add a sql query in a module (which?) so as to make it automatic?

Thanks a lot!!

 

[ted.martin]

Yes, the email body needs to stored in a table emailbodytext which is a memo field and is amended by the form F-emailbodytext. The variables are those used in the module SendeMailAttachment but where you amend them is up to you and they are all public variables. If you want to cycle through a table or query of e-mail addresses, then you will need to create a loop that includes the statement MyMail.Send where the MyMail.To line refers to your table or query of e-mail addresses.

 

[capsula4]

Thanks for Answering Ted still I find the code a bit hard to understand lol

I don't really find where the "email quotation" VBA calls the "PDF quotation" VBA, are they actually related somewhere?

Another thing, where do I gotta pass the variable values to the different functions?? If anybody has a working sample of this code would be really nice, that's how I tend to understand this things better lol

Excuse me if I'm being pretty silly but I'm a bit newbie into VBA.

Thanks a lot!

 

[ChronicFear]

Hi Ted,

If you don't mind, I'd like to add one last question to your long list here. I've downloaded your demonstrations and built them into my system, but I am stuck on the automatic attachment of the newly created PDF file. I've stepped through the code about 20 times already thinking I must've missed something, but it appears that if I try to assign a new name to the output .pdf file, the system just sort of never creates it, instead of just renaming the .tmp. Is there some other code that I need to add, or do you think I'm just assigning variables incorrectly. I didn't really change any of the underlying code.

Could you please point me in the right direction?

Thanks so much.

 

[harleyskater]

in access 2007 you can create a button to do this using the wizard : )

 

[ChronicFear]

Thanks, Harley. Unfortunately, I'm still working with 2002.