Print report to PDF and email it 5

[SkyHigh]

Hi Folks

I really need your expertise here, I have an order form which displays the order details of a customer and it also generates an invoice using a command button in access snapshot format, what I need to do is to print the invoice to pdf writer and email it to the customer whose email address is on the form using one command button.

Has anyone done that before, or knows how to do it, I need the VBA code.

Your kind help will be much appreciated !

Rgds
Brenda.

 

[alfalf]

Hello.
I'm afraid that this cannot be done. Simply because in order to create *.pdf file one have to have any Acrobat program capable of making *.pdf files, wich, as you may know, Access cannot do.
Yet, there are other format that Access supports (perhaps you allready know), so why not use one of them? And all that to be under one command, use

DoCmd.OutputTo acReport, stDocName, OutputFormat, OutputFile, , TemplateFile

- where OutputFormat is one of following (use abberviations, check Acc.Help for exact syntax):
HTML (*.html), Microsoft ActiveX Server (*.asp), Microsoft Excel (*.xls), Microsoft IIS (*.htx, *.idc), MS-DOS Text (*.txt), or Rich Text Format (*.rtf)
- OutputFile is name of the file outputed
- TemplateFile is name of your template file if formats are html, asp, htx, idc.

Also, check Access Reports Forum on this site.

 

[LittleSmudge]

The DoCmd.OutPutTo command also support ( but it is not documented clearly ) SnapShots.

Put acSnapShot in the Output Format parameter and try it.

A SnapShot is 'similar' to a .pdf.
The SnapShot viewer comes with Office - but is not always installed by default. So you might need to get your recipients to load it.


'ope-that-'elps.



G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[gavinjb]

you could try the following web site there are quite few links to freeware PDF utils that apparently work with access (haven't tried them myself)

http://www.granite.ab.ca/access/pdffiles.htm

Gavin,

 

[SkyHigh]

Hi

I have the acrobat pdf writter, I just need the VBA code to achieve the above.

Thanks
Brenda

 

[SgtJarrow]

SkyHigh,

See faq703-2533 for information on saving a file as a PDF programatically....as you have Adobe PDF Writer, this will work great for you....and I just modified it some today to make it a little better.

Once you have that working, the mailing of the file can also be accomplished fairly easily. I am almost sure it can be done with the DoCmd.SendObject command, and you can get the file name of the newly created PDF file from the code in my FAQ. If you can't make it work with DoCmd.SendObject, there is automation tied to Outlook, but this is much more intense and I am not going to go into it unless you absolutley need it. there are several good threads here on the Automation processes, so check them out if you like. But I suggest at least trying DoCmd.SendObject first.

If you are stuck on my FAQ code or anything else, please let me know....Good Luck!

****************************
Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: wildmage@tampabay.rr.com

 

[SkyHigh]

Hi Robert

Thanks for your help, just to let you know, I do absolutely need to automate the whole thing and its so urgent that I don't think I can do it myself as I haven't done VBA coding before at all.

Rgds
Brenda

 

[flaviooooo]

Here you go:

To start, you need to read the FAQ Robert mentioned above.
Then add this code to a button to make the PDF and email it:

Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application&quot
Set MailOutLook = appOutLook.CreateItem(olMailItem)
Dim i As Integer


Call SaveReportAsPDF("your reportname", "C:\pdfname.pdf&quot

With MailOutLook
.To = emailadresses
.CC = more emailadresses
.Subject = "subject"
.HTMLBody = ""
.Attachments.Add "C:\pdfname.pdf", olByValue, 1, "\pdfname.pdf"
.Display
End With

 

[flaviooooo]

oops, one more thing.
make sure you have the references to Outlook 98 and Microsoft Word 8.0 checked.

 

[SgtJarrow]

flaviooooo,

thanks for the assist! I am tryignt o finish my workload for the day as I have to leave early....Have a star!

****************************
Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: wildmage@tampabay.rr.com

 

[SkyHigh]

Thanks guys so much for taking your time out to help, I appreciate it so much, I have 2 questions :

1) Since there will be multiple reports, one for each customer, how can I control naming the report using a value from a variable like the reportname+customer_id.

2)How can I make multiple file attachements

Thanks and best regards
Brenda

 

[SeeThru]

For multiple attachemnts you will need to use outlook.

Out of interest, what PDF creator are you using - I need to look at doing this myself!

 

[SkyHigh]

I am using adobe pdf writer.

Can some please help me, I need to rename a file, the new file name should be reportname+customer_id, can you pls correct the following syntax for me the customer_id is in a text box on the form where I am creating the pdf files from :

name "c:\invoices\invoice.pdf" as "c:\invoices\invoice-[Forms]![PrtViewInvoice]![customer_id].pdf"

Thanks much for you assistance.
Brenda

 

[flaviooooo]

This should do the trick:
"c:\invoices\invoice-" & [Forms]![PrtViewInvoice]![customer_id] & ".pdf"

And to make multiple file attachments, just add this line as much as you need:

.Attachments.Add "C:\pdfnamea.pdf", olByValue, 1, "\pdfname.pdf"
.Attachments.Add "C:\pdfnameb.pdf", olByValue, 1, "\pdfname.pdf"
.Attachments.Add "C:\pdfnamec.pdf", olByValue, 1, "\pdfname.pdf"
...

Thanks for the stars

 

[NightZEN]

Hi, Your code(s) are very impressive, and I hope to use it in a project I am working on similar to SkyHigh. I am getting an error perhaps one of you can help me with:

I have created the module, and the Code on a button in a form as you outline in your faq. When I hit the button, I get an:

Acrobat PDFwriter error:
Cannot Print; file already in use or locked.

When I click OK I get This:

Run-time error 2212
Test Database Couldn't Print your Object

Make sure the Default printer is available.
For Information about setting a default printer, Search the Windows Help Index for 'default printer, setting'.
DEBUG CONTINUE END

The Name of my PDF Printer is the same used in the Code. My Install of Adobe Writer is good (works in all other tests - I have sent .doc's to it etc.). It is Version 5.0.

Thanks for an insights you may have,
and the code!

-Keith

 

[NightZEN]

BTW, I am developing in Access 97 on Win2Kpro. I will eventually have to create a Front End for Access 2000 as well. Thanks,

Keith

 

[contessa]

I am having the same problem with the code - the error message that I get is

'Adobe PDFWriter Error'
"Cannot print; file is already in use or locked"

Then
'Microsoft Access Error'
"Database couldn't print your object"

I'm using Acrobat 5.0, Access 2002 and Windows XP

Is this a problem with Acrobat 5.0 in that the code in the module references PDF Writer and I understand I should be using Distiller with Acrobat 5.0 - is it something to do with these registry entries? If, so any idea of the new ones to be using? Any help is greatly appreciated!

 

[bubblez]

hi, i would like to export a report from access to excel with the tables drawn. Is that possible?

i have added the access object library and DAO object library, but i still get the error message 'user defined type not defined" for the following line of code: "Dim objXL As Excel.Application"

the following is some code i found on the net :
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim rst As Recordset
Dim iRow As Integer

Set objXL = New Excel.Application
objXL.Visible = True
Set objWkb = objXL.Workbooks.Open("c:\excel_templates\template_A.xls&quot
one "sheet 1" by default)
Set objSht = objWkb.Worksheets("output&quot

iRow = 10

Set rst = CurrentDb.OpenRecordset("SELECT * FROM myTable " & _
"WHERE ((somefield)= 'somevalue') " & _
"order by field1, field2;&quot
rst.MoveFirst

Do While Not rst.EOF
objSht.Cells(iRow, 1).Value = rst!field1
objSht.Cells(iRow, 2).Value = rst!field2
objSht.Cells(iRow, 3).Value = rst!field3
iRow = iRow + 1
rst.MoveNext
Loop

rst.Close
objSht.Range("A1&quot.Select

objSht.Cells(3, 1).Value = "Sale Date"
objSht.Cells(3, 2).Value = "ID Num"
objSht.Cells(3, 3).Value = "Sales Person"
objSht.Cells(3, 4).Value = "Sale Amount"
objSht.Cells(3, 5).Value = "Accumulation"

objSht.Range("A3:E3&quot.Font.Bold = True
objSht.Range("A3:E3&quot.Font.Name = "Arial"
objSht.Range("A3:E3&quot.Font.Size = 10

objSht.Cells(1, 1).Value = "Weekly Sales for Week Ending " & Me.strWeekEnding

objSht.Range("A1:A1&quot.Font.Bold = True
objSht.Range("A1:A1&quot.Font.Name = "Arial"
objSht.Range("A1:A1&quot.Font.Size = 14

objSht.Range("A1:E1&quot.HorizontalAlignment = xlCenter
objSht.Range("A1:E1&quot.Merge

objSht.Range("A1&quot.Select

thanks

 

[DoppyNL]

I want to send an email with an pdf file made from a report as a attachment.
I checked the FAQ to wich was linked.

Only difference is, I don't have Adobe PDF write, but Fineprint PDF Factory.
How would I make the PDF file with that?

 

[marksnow]

DoppyNL have a look at the website for Fineprint PDF and see if they have any support. Most have developers support and sample code etc.

I used Win2PDF and their website contained some very helpfull information on creating PDF from VBA and about attaching them to an email automatically aswell.