How to send email with a report as an attachment

[TravelingCat]

I have a report. I have a button. What i want is: when user clicks the button, the report gets sent to some recipient. I searched all over the net, including this forum, and this site
http://www.granite.ab.ca/access/emai...ttachments.htm,
but i still have no idea how to do this and whether it can be done at all!..
I saw that everyone recommends to turn the report into a pdf file. But the command SendObject can't send a pdf file, can it? So even if i would create a pdf, how would i then send it?

P.S: Even creating pdf doesn't work, I'm running this line:
DoCmd.OutputTo acOutputReport, "rptEventProtoc ole", acFormatPDF, , False(or True, doesn't change anything)
access prompts me for an output format, and PDF is not one of the options in the list.

[Jim Doherty]

Hi,
I have a report. I have a button. What i want is: when user clicks the button, the report gets sent to some recipient. I searched all over the net, including this forum, and this site
http://www.granite.ab.ca/access/emai...ttachments.htm,
but i still have no idea how to do this and whether it can be done at all!..
I saw that everyone recommends to turn the report into a pdf file. But the command SendObject can't send a pdf file, can it? So even if i would create a pdf, how would i then send it?
P.s. even creating pdf doesn't work, i'm running this line:
DoCmd.OutputTo acOutputReport, "rptEventProtoc ole", acFormatPDF, , False(or True, doesn't change anything)
access prompts me for an output format, and PDF is not one of the options in the list.
Hope someone can help me..

Hi

If you are using Access 2000 then my advice to you is to have a look at Stephen Lebans site at this page

http://www.lebans.com/reporttopdf.htm

Stephen has officially retired from Access but his wonderful efforts over the years to the Access community has helped many people. I for one know his contribution to output a report to PDF was a great contribution relying on a couple of DLLs he created that are dumped in the same folder as your mdb file. Don't expect any support on this. given his retirement, but if it is to tide you over until you upgrade then it is an easy method to employ.

Get your head around creating the PDFs from report based objects first, then the email thing can be dealt with when you understand how the PDF creation aspect works

[TravelingCat]

send email with a report as an attachment

Thanks, but i already downloaded the mdb from this site, but there's so much code which i don't even understand... i wonder if i really need all this. And i'm using access 2003. I don't understand, why for doing the same thing - creating a pdf - one site has a whole mdb, and the other one line of code?..
DoCmd.OutputTo acOutputReport, "Name of Report in MDB", acFormatPDF, "Path/Name of report on hard drive", False
And why doesn't this line work for me?
And i would still like to know whether sending pdf via access is possible
I'm pretty frustrated by now, will appreciate any help..

[Jim Doherty]

Thanks, but i already downloaded the mdb from this site, but there's so much code which i don't even understand... i wonder if i really need all this. And i'm using access 2003. I don't understand, why for doing the same thing - creating a pdf - one site has a whole mdb, and the other one line of code?..
DoCmd.OutputTo acOutputReport, "Name of Report in MDB", acFormatPDF, "Path/Name of report on hard drive", False
And why doesn't this line work for me?
And i would still like to know whether sending pdf via access is possible
I'm pretty frustrated by now, will appreciate any help..

Where did you get your reference to acFormatPDF? I did say Access 2000 when I referred to creating a PDF I summized this when you said PDF format was not an available option to you, in your output formats.

Both Access 2000 and Access 2003 do not send in these formats natively only Access 2007 does that. The workaround in the earlier versions of Access was to either buy a piece of third party software to do the job or pick up on Stephens free offering which I already said works and does exactly what it says on the tin...so to speak.

Stephen Lebans method converts an access report saved to disk as a snapshot file and converts it to PDF as an external file created on the fly as part of that process. It is this path of the newly created PDF file that you must pick up on and attach to any email message .

Like I said before I am afraid to say it is chicken and egg. If you want to achieve your aim the steps are necessary to first know how to implement Stephens method......... the grabbing of the external file to attach to an email message is the easy bit comparatively and well documented, with plenty of references of how to do that on here using office automation.

I am not saying for one minute that understanding this process is an easy task but it is necessary in order to achieve your ends I am afraid, as frustrating as that may be :)

[patjones]

Hi TravelingCat,

If I may just pick up on something in your first post...you mentioned that everyone recommends converting the report to PDF format. However, for your purposes is it necessary to have it in PDF format or are any of the other formats presented in the list alright? If other formats are acceptable to you then this process could become easier.

Pat

[DataAnalyzer]

I use Total Access Emailer. It'll let you send emails to everyone in your list, reference fields from your table and attach the pages of the report for each contact. It filters the report based on a field in your email list. The 2007 version support the reports as PDFs.

Runs as an add-in wizard so no programming is required. There's a VBA interface if you need that.

Good luck

[TravelingCat]

Jim, the reference is from this site, http://www.granite.ab.ca/access/emai...ttachments.htm. I did change the "Snapshot Format" to acFormatPDF, just because there was such option in the vba, but it didn't work. Thank you for detailed answer, i will look deeper in the way you suggested.
Zepphead80, other formats are not acceptable unfortunately, there's not much to choose from, and rtf is just horrible, it distorts the whole report. But pdf document can't be modified, and in addition to be able to send a report by mail, my user wants to be able to design the report before printing it - meaning change font, size, etc., and then i also should save it back to mdb including the changes he made.. i have no idea so far as to how it will be implemented.. I will have to ask for help from my colleague, otherwise.... it won't be pretty:)
DataAnalyzer, i understand that this program is not free, so that is not a option for me.
Anyway, thank you for all your replies, i'll continue working on it

[TravelingCat]

Me again.. Jim, since you recommended Stephens program so highly, i have 2 questions to you:)
I've put his code into my program, but how do i add a condition to the report that is being saved? When i had a "view report" button, it had this condition:
DoCmd.OpenRepor t "rptEventProtoc ole", acViewDesign, , "eventNum = " & evNum, it presented the event that was currently on the screen.
And number two, the code doesn't support hebrew language.. it comes out with question marks. Is there a way to fix it?
Oh, and of course, now that i have the pdf file, how do i send it via outlook?
Thanks for your help

[Jim Doherty]

Me again.. Jim, since you recommended Stephens program so highly, i have 2 questions to you:)
I've put his code into my program, but how do i add a condition to the report that is being saved? When i had a "view report" button, it had this condition:
DoCmd.OpenRepor t "rptEventProtoc ole", acViewDesign, , "eventNum = " & evNum, it presented the event that was currently on the screen.
And number two, the code doesn't support hebrew language.. it comes out with question marks. Is there a way to fix it?
Oh, and of course, now that i have the pdf file, how do i send it via outlook?
Thanks for your help

I had no idea from your post that internationalis ation would be eluded to I am afraid. I cannot answer every eventually resolving issues related to Hebrew time, date, and numerical formats with Stephens offering it was always a developmental idea, free, and for the most part works.

It may well be that your only option is to buy a third party software or upgrade to a later version of Access I am afraid.

In relation to your report criteria. It is generally better if you can pass some criteria to your report at runtime from some other object in your database ie: a form based control ie a textbox that can pass the value to the report as it opens. I see that you are opening your report in design mode (but why I do not know) You can open the report acViewNormal and still pass criteria to the report

The following block of code will open a report based on some string criteria (note: not numeric datatype) that is passed from a Form control textbox. The report is opened in design mode and the criteria is passed to the filter property of the report. You are then prompted to ave the changes (amend to suit)

Dim stDocName As String
    Dim mycriteria As String
    stDocName = "rptEventProtocole"
    mycriteria = "[LN]='" & Me!txt1 & "'"
    MsgBox "The criteria for your report is " & mycriteria
    'DoCmd.OpenReport stDocName, acPreview, , mycriteria
    DoCmd.OpenReport stDocName, acDesign, , mycriteria
    Reports!rptEventProtocole.Filter = mycriteria
    DoCmd.Close acReport, "rptEventProtocole", acSavePrompt

The next block of code will open that same report based on the same form control but this time just in preview mode passing the criteria straight to the report as part of the where clause argument to the Docmd.openRepor t command

Dim stDocName As String
    Dim mycriteria As String
    stDocName = "rptEventProtocole"
    mycriteria = "[LN]='" & Me!txt1 & "'"
    MsgBox "The criteria for your report is " & mycriteria
    DoCmd.OpenReport stDocName, acPreview, , mycriteria

At lastly, if the PDF hebrew conversion issue persists to be a problem (and I am not in a position to debug that) is there much point in me giving you help on attaching an external PDF filename to an email message and sending via an outlook email message?

[TravelingCat]

Hi, thanks for your quick reply.
Well i guess not, no point... (in giving me advice about email).
Anyway thanks a lot for your input on this subject, i will try to figure out some other way.

[TravelingCat]

I found how to change the language:) So if it isn't too much trouble, i would really like to hear how to send the file

[TravelingCat]

And also, about the thing with criterias. When i used to just open the report for preview there was no problem, it opens like i need it to. But i was wondering where do i put this criteria in Stephen's code.
Inside 'Public Function ConvertReportTo PDF' there is a 'DoCmd.OutputTo ....', but it has no 'where' clause, so i don't know how to condition it

[patjones]

Hi,

The following subroutine, which I use in one of my Access projects, takes one or more email addresses (strSendTo), a subject (strSubject), a message for the body of the email (strBody) and a file path (strAttachFile) as arguments and sends the email:

Public Sub SendEmail(strSendTo As String, strSubject As String, strBody As String, Optional strAttachFile As String)

Dim olApp As New Outlook.Application, olMail As Outlook.MailItem
Set olMail = olApp.CreateItem(olMailItem)

With olMail

    .To = strSendTo
    .Subject = strSubject
    .ReadReceiptRequested = False
    .Body = strBody
    
    'Attachment is optional...
    If Not IsNull(strAttachFile) Then .Attachments.Add strAttachFile

    .Send
    
End With
    
Set olApp = Nothing
Set olMail = Nothing
    
End Sub

Bear in mind that it sends a file. So you would previously have had to output the report to some location, and then specify the path to that location in strAttachFile above. I'm not certain whether you can send the report directly without outputting it first.

Pat

[TravelingCat]

zepphead, the following line:
Dim olApp As New Outlook.Applica tion
gives me an error: User-defined type not defined

[patjones]

Yes, I forgot to tell you to set the reference to the Outlook library. Go Tools > References... and in the long list of available references scroll down and check off "Microsoft Outlook 12.0 Object Library". It should compile after that.

Pat