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, "rptEventProtocole", 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]

@TravelingCat
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]

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]

@TravelingCat
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.OpenReport "rptEventProtocole", 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]

@TravelingCat
I had no idea from your post that internationalisation 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.openReport 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 ConvertReportToPDF' 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.Application
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

[Jim Doherty]

@TravelingCat
OK I see Zepphead has dealt with the email aspect :) so I have no need to enlarge on that.

In relation to opening the report:- You do not need to alter Stephens code for that purpose you simply pass the full name of the report and the function opens the report and outputs it as expected.

In relation to passing a string criteria for the where clause you can do this in different ways. The report itself can look for something external to itself to set its criteria/filter property when the report itself opens.

One way of doing this is to simply create a Global variable of the String type. A global variable is available throughout your application. In other words the value contained in it is not tied to anything in particular. You place this global variable in a module and set the value of this global variable from your calling code.

Create a new module and paste the following into it immediately underneath the 'Option Compare Database' line. Save the module with a meaningful name for instance mod_Globals

Global MY_CRITERIA As String
 
Public Function GetMyCriteria()
GetMyCriteria = MY_CRITERIA
End Function

Now go to your report in design mode and in the 'On Open' event of the report
paste into it the following code so it looks like this

Private Sub Report_Open(Cancel As Integer)
On Error Resume Next
Me.Filter = GetMyCriteria()
Me.FilterOn = True
End Sub

Now imagine we have a command button on a form (or something similar) and we want to open the report passing some filtration criteria to the report as it opens. We achieve this by first setting the value of the global variable (which is automatically picked up by the report which in turn applies this criteria to itself) Get the idea?

Below is an example of opening that report from a command button mounted on a form as mentioned above. The syntax required to do so is as follows:.

Private Sub Command1_Click()
On Error GoTo Err_Command1_Click
    Dim stDocName As String
    Dim mycriteria As String
    stDocName = "rptEventProtocole"
    MY_CRITERIA = "[LN]='" & Me!txt1 & "'"
    DoCmd.OpenReport stDocName, acPreview
Exit_Command1_Click:
    Exit Sub
 
Err_Command1_Click:
    MsgBox Err.Description
    Resume Exit_Command1_Click
 
End Sub

Notice in the code block above, there is NO criteria argument on the DoCmd.Openreport command line. In this instance there is no need to express this because the report is synchronising any underlying dataset by reference to its filter property and the value contained in the global variable when the report itself opens.

This is just one of many different techniques that exist it is by no means the only one.

Hope this helps!

[TravelingCat]

Hi, i got back from the weekend.
Thanks a lot for your time and help, and sorry for this neverending post, but..
zepphead, i have "Microsoft Outlook 11.0 Object Library", and when i try to check it off it says "can't remove control or reference; in use"... any ideas?
Jim, thanks a lot for the help, i managed to make it open what i need

[patjones]

Hi,

I think I worded my post the wrong way...I apologize. I mean that it should be checked. You want to have that reference turned on.

Pat

[TravelingCat]

Well then why doesn't it compile, do you know?

[patjones]

Can you post the code, the error your getting and note the line the compiler is complaining about? Thanks.

Pat

[TravelingCat]

Hi, here's the code (in bold is the line where compiler gets stuck, it's right after the first Dim)

Private Sub btnSendProtocole_Click()
Dim olApp As New Outlook.Application
Dim olMail As Outlook.MailItem
Set olMail = olApp.CreateItem(olMailItem)
 

Here's the message:
"Compile error: User-defined type not defined"

[patjones]

Right, and this is because the reference to the Outlook library is not set. If that reference is checked, then this code should compile.

Pat

[TravelingCat]

Wow zepphead i'm so sorry... i didn't have it checked, the whole time i was looking at this Microsoft Access Object Library, i was reading Access but thinking Outlook.. i should've doublechecked.
Thank you very much for your time spent on this:)

[patjones]

It's no problem at all. That's what the forum is for. And now for the real purpose of your post...does the report get sent along with the email?

Pat

[TravelingCat]

I have to leave now, I will check it on Sunday and will let you know about my progress

[TravelingCat]

After a really quick check i see that he can't find the file, i'm passing a path, which is correct. I'll look into it later

[TravelingCat]

Hi again, i forgot to pass the file extension.. .so now it works perfectly and sends the file, thank you!
You said earlier that you're not certain whether i can send the report directly without outputting it first, so just out of curiosity, how do you use this code in your program? Do you first save the file and then send it?

[patjones]

The report that I send goes out to several supervisors within my division on a weekly basis, however I like to keep a copy in a designated spot on my local drive for reference later. So I output it to that location then email it as an attachment.

I could very well just output the report to a spot on the network drive that everyone has access to, and point them to where it is - but I think people like to be able to just click on it when it arrives as an email attachment. Not to mention that Outlook has the ability to preview PDF's right in the message window.

Pat

[TravelingCat]

Got it
Well, thank you very much for all your help, i definitely achieved something thanks to you and Jim:) These forums are great