Automate Sending Access Report

Dear Experts

I have a series of reports which I need to be emailed automatically.  The reports are group into 3 batches.  The details of each report are stored in a table “Tbl_Reports_List” which amongst other data, contains the following fields:

Sendout1 (Yes/No)
Sendout2 (Yes/No)
Sendout3 (Yes/No)
Rpt_Name (Text field)

Each of the reports already has the recipient’s email addresses contained in the [.tag] field in the report.

Does anybody have a piece of code to scroll through the table and if [Sendout1] = Yes, then email the report [Rpt_Name] in rtf format to .tag

Many thanks
correlateAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
open the table using recordset

dim rs as dao.recordset
set rs=currentdb.openrecordset("tbl_Reports_List")

rs.movefirst
do until rs.eof
     if rs("sendout1")=true then

        'send report here
     end if
rs.movenext
loop
0
Rey Obrero (Capricorn1)Commented:
open the table using recordset

dim rs as dao.recordset, rptName as string
set rs=currentdb.openrecordset("tbl_Reports_List")

rs.movefirst
do until rs.eof
     if rs("sendout1")=true then
        rptName=rs("Rpt_Name")

        'send report here
     end if
rs.movenext
loop
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
correlateAuthor Commented:
HI Capricorn1

Many thanks for this - I'm running to problems using the .tag as the recipient I am getting this error message ....Compile error, invalid qualifier (the code is attached). I have tried a few options around this & certainly the rest of the code seems to work ok
Private Sub Run_1st_Cut_Click()
Dim rs As dao.Recordset, rptName As String
Set rs = CurrentDb.OpenRecordset("tbl_Reports_List")

rs.MoveFirst
Do Until rs.EOF
     If rs("sendout1") = True Then
        rptName = rs("Rpt_Name")
        'send report here
        DoCmd.SendObject acSendReport, rptName, "RichTextFormat(*.rtf)", rptName.Tag, "", "", "Subject Test", "Body Test", False, ""
        
     End If
rs.MoveNext
Loop
End Sub

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Rey Obrero (Capricorn1)Commented:
how many recipients do you have in the tag?
0
correlateAuthor Commented:
it will vary, never more than 4 or 5 (in the test I did there was only one - the recipients in the tag are formatted thus jo.bloggs@hotmail.com; dave.bloggs@hotmail.com;
0
Rey Obrero (Capricorn1)Commented:
easiest way is to include the recipients as a field in the table.. is this possible for your application?
0
Rey Obrero (Capricorn1)Commented:
or you have to open the report in design view (using VBA codes) get the value form the Tag property, close report.. send report
0
correlateAuthor Commented:
Yep I have added the recipients to a field in the tabel & it works beautifully - Also I remember from when I last used the Tag Property you did have to open the report
0
correlateAuthor Commented:
Brilliant, Thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.