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?
 
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
 
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
 
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.