Can you email an Access Report?

[JohnPapa]

gemma,

I use A2003. I will look into the Lebans code? Any ideas about renaming the attachment file?

 

[JohnPapa]

gemma,

Had a look at the Lebans code and there may be two problems:
1) The .dll that need to be added
2) Potentially it may not work with an mde (it works with an mdb)

 

[JohnPapa]

You set the Caption in the FORMAT event of the section it is in. So if in the Report Header you set it in the On Format event of the Header section.

Bob, the report has a Report header, a Report footer and a Detail section. I tried setting the caption on the OnFormat event in all cases, but have been unsuccessful in renaming the .snp attachment. Any ideas?

 

[boblarson]

Yep, it would appear that you can't change the snp file name that way. I think we'll need to go down another path for this. I don't have time to dig into it right now, but in a few hours I might. We'll need to output the report, then change the file name, and then use the Outlook common object model (if you are using Outlook) to do the mailing.

So, are you using Outlook?

 

[sgtSortor]

I send pdf'd versions of reports out automatically all the time. You don't have to have Adobe or any other PDF program to do it either.

DoCmd.OutputTo acOutputReport, ReportName, acFormatPDF, "Location Where Report Will Be Saved\NameYouWanttoGiveReport"& "" & ".pdf"
Call EmailReport

I have my email code set up as a separate function.

Public Function EmailReport()
Dim strAttach, MessageBody

strAttach = "Location Where Report Will Be Saved\NameYouWanttoGiveReport"& "" & ".pdf"

        Dim objOL As New Outlook.Application
        Dim objMail As MailItem
        Set objOL = New Outlook.Application
        Set objMail = objOL.CreateItem(olMailItem)
        
MessageBody = ""
MessageBody = MessageBody & " " & "Blah, Blah, Blah, whatever you want the message body of your email to say" & vbCr & vbCr & vbCr & vbCr

        With objMail
        .To = "email Address"
        '.CC = ""
        .Subject = "Sending Report"
        .Body = MessageBody
        .Attachments.Add strAttach
        '.Display (If you want to see the email before sending it, use this
        .Send  '(if you don't want to see the email before sending it, use this
        End With
        
        Set objMail = Nothing
        Set objOL = Nothing

 

[boblarson]

You don't have to have Adobe or any other PDF program to do it either.

ONLY if you have Access 2007 SP2 or higher.


And thanks for the Outlook code.

 

[gemma-the-husky]

no, it works with an mde ok

i don't understand the issue about renaming files. The lebans code saves the pdf to a temporary folder. once there you can rename with the NAME command

 

[JohnPapa]

Bob, I am using Outlook, but my clients may not. So there may be a problem there.

As Bob has already mentioned, you need A2007 to be able to send Pdfs.

Gemma, the main reason why the Lebans code is not appealing, is the inclusion of the 2 dlls. My application www.VisualDentist.com has been around since 1997 and to this day I have not used any addins, dlls etc. We want to rename the attached .snp file (basically the report file) to something meaningful, by including perhaps the date, patient number, tooth number (since this report is for Root Canal treatment ouch!)

 

[PNGBill]

We use Access 2000, mde and Outlook Express with Stephen Lebans Code.

Don't be scared of the dll's. just two very small files you copy and past in the same folder as your Front End. 5 sec task

Lebans Code allows you to name the saved .pdf document eg the button we click to produce a customers Statement saves the .pdf file in the special folder we use for all attachments to our emails and the file name includes the Customer full name - all part of the main code where we used sql and rst to get the customer name into a variable and then use the variable in the file name.

Each Command Button in our database has it's own unique File Name and place to save the file.

We now have all reports properly saved with the correct name and location.

The code also has a message box that asks if you want to email the report and if you click Yes, outlook express opens up a new email with the customers email address, subject line and email body including the Operators Name in the sign off.
We just click on the email "attach" button and attach the just saved file, click send and it is gone plus a message is added to the customer file to say "doc xxx has been emailed by operator yyyy."

Only part not automated is the actual Attachment of the document to the email.

All of this happens by One Procedure which includes 3 or 4 lines of Lebans Code plus of course his main code in a General Module.

 

[John.Woody]

Bob, I am using Outlook, but my clients may not. So there may be a problem there.

As Bob has already mentioned, you need A2007 to be able to send Pdfs.

Gemma, the main reason why the Lebans code is not appealing, is the inclusion of the 2 dlls. My application www.VisualDentist.com has been around since 1997 and to this day I have not used any addins, dlls etc. We want to rename the attached .snp file (basically the report file) to something meaningful, by including perhaps the date, patient number, tooth number (since this report is for Root Canal treatment ouch!)

It might be worth looking at the OutputTo command

Create the report in your db, then

expression.OutputTo(ObjectType, ObjectName, OutputFormat, OutputFile, AutoStart, TemplateFile, Encoding)

Output format could be acFormatSNP ands the outputFile specified by you, then you could attach that file and send it using CDO giving your customers a config form to config the email account info.