Question Send Report through Email (1 Viewer)

[majid.pervaiz]

Dear All,

First of all, many thanks to the expert users of this forum those who always helped me to develop access application for my department.

Part of continuous improvement , I need your support again and below is my question

I have a query / report which will show the record when date in my data matches with today. Ideally, I want to send this report only when there is data in the report only.

let me explain more in details now, we have to review documents at a certain date in future which is calculated in the query and it is showing record. But I want this query data / report to be send when there is data.

I manage to generate and attach the report through email but I don't want this report to generate when there is no data.

If you have a better solution to handle such situations I would really appreciate if you can share.

 

[Minty]

Please show the code you have so far, normally all you would need to do is count the records that meet today's criteria and if >0 run your report otherwise don't.

 

[majid.pervaiz]

Dim Subject As String, Body As String, EmailAddress As String
EmailAddress = "XXXXXXXX@gmail.com"
Subject = "Action Required"
Body = "Policy Due for Review"
DoCmd.SendObject acSendReport, "Policies Due for Periodic Review", acFormatPDF, "XXXXXXXX@gmail.com", , , test, , , False
I just want report to be send once there is data

many thanks for help

 

[Minty]

Is your report based on a saved query? Assuming it is then

IF Dcount("AnyFieldFromYourReportQuery","YourReportQuery") > 0 Then

    EmailAddress = "XXXXXXXX@gmail.com"
    Subject = "Action Required"
    Body = "Policy Due for Review"
    DoCmd.SendObject acSendReport, "Policies Due for Periodic Review",     acFormatPDF, "XXXXXXXX@gmail.com", , , test, , , False

End If

 

[majid.pervaiz]

Yes my report is based on a query.

 

[Minty]

So - have you tried it ?

 

[majid.pervaiz]

Hello Minty,

Accept my sincere apologies for yesterday, actually I left for the day from office.

I have tried it's attaching PDF only when there are records else it's not. that's really nice.
would it be possible that the report will be sent automatically, as of now whoever opened access application and if the criteria matches it will generate this email and someone will have to click in order to send the email.

 

[majid.pervaiz]

Hello - I checked this, the second last parameter for edit. I put no and the email is going automatically.

it is asking for permission allow or deny only... will check and show you screenshot.

once again thank you very much my expert friend Minty

 

[majid.pervaiz]

One more question:
I am calculating a future date in query and sending report on that future date, if this date falls on weekend and access application is close it will not send the report.
example today (11/08/16) future date (11/08/2018) which is Saturday, surely PCs are shutdown / log-off so the report will not be generated and send. When we return on next working day and open the application the date is already passed and will not send the report.
how to deal with such situations

 

[Minty]

That's a long way off...
I would look at having a version of your database that is set up to just send these reports. You can program it so that an autoexec runs code on start up that sends your report then closes the database. Use the windows scheduled task to open this database every day and put it on a machine you know will be left on.

The alternative would be to have a flag on the records that indicated the report had been run and collate all those records into one report run during the week.

 

[majid.pervaiz]

Hi Minty - I believe windows schedule task option is better, will have a look into this.
will have to read a bit on how to use this windows functionality. Hopefully will get through

many thanks buddy

 

[majid.pervaiz]

On thing Minty - even scheduler open access, how the event to send automatic report will occur?

 

[Minty]

You will need to use outlook automation or CDO to send the mail. Search on here for some guidance.
CDO - http://access.mvps.org/access/modules/mdl0019.htm
Access / Outlook example : http://www.access-programmers.co.uk/forums/showthread.php?t=283494

 

[majid.pervaiz]

how to run this CDO .. I believe I don't need to amend anything in the code... and I cannot even since I am not that expert .. to be very honest.

 

[Minty]

Outlook automation is probably simpler in fairness.

 

[majid.pervaiz]

Since you are expert user may be for you surely... but I am at learning curve..

 

[Minty]

Have a look at post#9 in the link I posted for here. That code is as straight forward to implement and use as possible to achieve what you want.