Bill said:What part do you need help with? Autoexec macro?
General module? Where does the recipient list and
date information come from to feed the automation?
It works very well to have the general module
ALWAYS receive control, whether it be for normal
human interaction or automated tasks. A simple
parameter passed by the Autoexec macro would
signal to the general module what code path to take.
Bill
dbl said:Bill thanks for the response, you wouldn't have any sample code to get me
started.
All of our reports are run manually by entering the start date and finish
date into a form and then clicking the command button appropriate to the
report to be produced using the code below.
Dim stDocName As String
Dim strWhere As String
msgbox "This report goes to ???.???@????co.uk and to
?????.???@????.co.uk"
strWhere = "[Date] between #" & Format(Me.txtStartDate,
"dd-mmm-yy") & "# AND #" & Format(Me.txtEndDate, "dd-mmm-yy") & "#"
stDocName = "rptMaximumDaily"
DoCmd.OpenReport stDocName, acPreview, , strWhere
We then manually send the report to Outlook and select the email address.
The process is very long winded.
Any further help would be very much appreciated
Bob
Bill said:Create a general module that gets run
by the Autoexec macro of your primary
database with the appropriate code that
creates the reports and sends them to
an established list of recipients. Then,
create a Windows Scheduled Task that
runs your database.
Bill
Is it possible to get Access 2003 to send out daily and weekly reports
by email to clients at a certain time of the day automatically?
If so how do you go about this? Or is it possible to get a programe
that will work with Access to do this?
Thanks Bob
Bill said:Bob,
Your post subject implies to me that you'd like to
distribute a report each day showing the DB's data
status of the previous day's end, likewise on say
Mondays for the previous week. If that's the case
then that suggests a fixed set of recipients and their
corresponding EMA's coupled with their respective
data interests, namely a specific query of the DB
each day and, again say, on Monday showing the
previous week's end.
Do I have the correct impression of what you're
after?
Bill
Bill said:Bob,
First, I take it that you want to run these reports
when there's a user in attendance, rather than having
them fire off at some designated time when the user
is off-line? Second, you might want to think about
a new table that is normalized (link without any
duplication of data) to the reports that lists who
and where reports should be sent. You would
necessarily have another form that allows you to
edit the recipients and their EMA's. (Obviously,
if this information changes with each session, it
wouldn't be practical to use this approach.) If
possible, this new table might also contain the
frequency and period bracketing that would add
to the possibility of TOTALLY automating the
sending of reports.
I'm in California USA, so we have a time gap
that will tend to slow down our communications,
but I'll do what I can to keep up with you as time
allows.
Bill
dbl said:Bill by the sound of it I need a lot of help. Ok can we start with the
general module
All of the command buttons for the daily/weekly reports are on a form
called frmReportsPrinter
On this form we have 2 text boxes txtStartDate and txtEndDate in these
fields we enter the report start date and end date which would be
yesterdays date unless it is Monday then it would be
Friday, Saturday and Sundays dates. We also run weekly reports that run
from the previous Monday to Sunday these are run on a Monday
So using the report rptMaximumDaily how do I get started? How do we
build the code to run the report and email it without user input? Other
than opening the db.
Do we use code to enter the required dates in the 2 text boxes then run
each command button? but how does it know where to email it to?
As I said I think I need a lot of help to get started.
Thanks Bob
What part do you need help with? Autoexec macro?
General module? Where does the recipient list and
date information come from to feed the automation?
It works very well to have the general module
ALWAYS receive control, whether it be for normal
human interaction or automated tasks. A simple
parameter passed by the Autoexec macro would
signal to the general module what code path to take.
Bill
Bill thanks for the response, you wouldn't have any sample code to get
me started.
All of our reports are run manually by entering the start date and
finish date into a form and then clicking the command button
appropriate to the report to be produced using the code below.
Dim stDocName As String
Dim strWhere As String
msgbox "This report goes to ???.???@????co.uk and to
?????.???@????.co.uk"
strWhere = "[Date] between #" & Format(Me.txtStartDate,
"dd-mmm-yy") & "# AND #" & Format(Me.txtEndDate, "dd-mmm-yy") & "#"
stDocName = "rptMaximumDaily"
DoCmd.OpenReport stDocName, acPreview, , strWhere
We then manually send the report to Outlook and select the email
address. The process is very long winded.
Any further help would be very much appreciated
Bob
Create a general module that gets run
by the Autoexec macro of your primary
database with the appropriate code that
creates the reports and sends them to
an established list of recipients. Then,
create a Windows Scheduled Task that
runs your database.
Bill
Is it possible to get Access 2003 to send out daily and weekly
reports by email to clients at a certain time of the day
automatically?
If so how do you go about this? Or is it possible to get a programe
that will work with Access to do this?
Thanks Bob
Bill said:I would suggest that you begin with 3 tables:
REPORT TABLE: (report name appears only once)
ReportID Autonumber (primary key)
ReportName text
DISTRIBUTION TABLE: (who gets what report with what frequency)
DistID Autonumber (primary key)
ReportID text
RecipWkly text (Of the form: RecipientID1,
RecipientID2,..........IDn)
RecipDly text (Of the form: RecipientID1,
RecipientID2,..........IDn)
RECIPIENTS TABLE: (Name and EMA only)
RecipientID Autonumber (primary key)
RecipientName text
RecipientEMA text
When you're in attendance and ready to send out
a report, your command button can call a general
module that will run the report, save the snapshot
to disk (OutputTo) and then run an IMAP
complient e-mail client "quietly" to actually send
the Snapshot files to whoever is to receive them.
The "report name" is the only piece of information
needed to search the three tables to find out who
and how often they're to receive them.
You need to define what weekly means, e.g.,
Monday 12AM to Sunday midnight??
Notice that there's no duplication of data amongst
the three tables. Also note that one only has to make
changes to one field to effect the whole scheme of
things for any one recipient. Like someone's EMA
or the name of the report.
The Distribution table needs to have an edit/entry
form to maintain the list of recipients. I'll send you
a screenshot of how one of my applications attends
to like tasks as folks are added or deleted from
a group.
Bill
dbl said:Hi Bill yes you do have the general idea. Each report that has data is
sent to the same email address or addresses. Some reports go to 1-2 or 3
people max. Some people require daily reports some only require their
reports weekly, but the email addresses are all static they very rarely
change. A weekly report would not be sent to someone who has daily
reports and daily reports would not be sent to someone who had weekly
reports. Each report is numbered automatically when it opens (if it has
data to report) otherwise it give a message the "report has no data" and
the report doesn't open.
I would only want the reports to go when someone is in attendance.
Because we have to get the info on to the system from the previous night
or from the week-end.
With regard to the new table some of the reports go to people who are not
linked to any part of the db so how do I go about this, do I need the
following fields
EmailAddress
EmailAddress1
EmailAddress2
ReportName
ReportFrequency
The only part that would link the table to the report would be the
"ReportName"
"ReportFrequency" what type of data would we store hear text i.e. Weekly
or numbers But how do you over come the Monday daily reports that
covers
Fri-Sunday.
So how would the new table link to the reports? I can see why we need
the
table, but I cannot see how we link the report to the table or can we
search for the report name in the table to find the required EMA's and
report frequency.
Regards Bob
Bill said:Bob,
Your post subject implies to me that you'd like to
distribute a report each day showing the DB's data
status of the previous day's end, likewise on say
Mondays for the previous week. If that's the case
then that suggests a fixed set of recipients and their
corresponding EMA's coupled with their respective
data interests, namely a specific query of the DB
each day and, again say, on Monday showing the
previous week's end.
Do I have the correct impression of what you're
after?
Bill
Bob,
First, I take it that you want to run these reports
when there's a user in attendance, rather than having
them fire off at some designated time when the user
is off-line? Second, you might want to think about
a new table that is normalized (link without any
duplication of data) to the reports that lists who
and where reports should be sent. You would
necessarily have another form that allows you to
edit the recipients and their EMA's. (Obviously,
if this information changes with each session, it
wouldn't be practical to use this approach.) If
possible, this new table might also contain the
frequency and period bracketing that would add
to the possibility of TOTALLY automating the
sending of reports.
I'm in California USA, so we have a time gap
that will tend to slow down our communications,
but I'll do what I can to keep up with you as time
allows.
Bill
Bill by the sound of it I need a lot of help. Ok can we start with
the
general module
All of the command buttons for the daily/weekly reports are on a form
called frmReportsPrinter
On this form we have 2 text boxes txtStartDate and txtEndDate in these
fields we enter the report start date and end date which would be
yesterdays date unless it is Monday then it would be
Friday, Saturday and Sundays dates. We also run weekly reports that
run from the previous Monday to Sunday these are run on a Monday
So using the report rptMaximumDaily how do I get started? How do we
build the code to run the report and email it without user input?
Other
than opening the db.
Do we use code to enter the required dates in the 2 text boxes then
run
each command button? but how does it know where to email it to?
As I said I think I need a lot of help to get started.
Thanks Bob
What part do you need help with? Autoexec macro?
General module? Where does the recipient list and
date information come from to feed the automation?
It works very well to have the general module
ALWAYS receive control, whether it be for normal
human interaction or automated tasks. A simple
parameter passed by the Autoexec macro would
signal to the general module what code path to take.
Bill
Bill thanks for the response, you wouldn't have any sample code to
get me started.
All of our reports are run manually by entering the start date and
finish date into a form and then clicking the command button
appropriate to the report to be produced using the code below.
Dim stDocName As String
Dim strWhere As String
msgbox "This report goes to ???.???@????co.uk and to
?????.???@????.co.uk"
strWhere = "[Date] between #" & Format(Me.txtStartDate,
"dd-mmm-yy") & "# AND #" & Format(Me.txtEndDate, "dd-mmm-yy") & "#"
stDocName = "rptMaximumDaily"
DoCmd.OpenReport stDocName, acPreview, , strWhere
We then manually send the report to Outlook and select the email
address. The process is very long winded.
Any further help would be very much appreciated
Bob
Create a general module that gets run
by the Autoexec macro of your primary
database with the appropriate code that
creates the reports and sends them to
an established list of recipients. Then,
create a Windows Scheduled Task that
runs your database.
Bill
Is it possible to get Access 2003 to send out daily and weekly
reports by email to clients at a certain time of the day
automatically?
If so how do you go about this? Or is it possible to get a
programe that will work with Access to do this?
Thanks Bob
Bill said:(PS) You might want to think about a naming
convention for the snapshot files.
E.g.,
c:\MyApplication\Weekly\reportname.snp
c:\MyApplication\dayoftheweek\reportname.snp
The life of any file is one week old.
Bill
Bill said:Bob,
It might be easier for you to build the 3rd table
this way:
DISTRIBUTION:
ReportID number
Frequency number (1/2 daily/weekly)
RecipientID number
Where table REPORTS.REPORTID is a one-to-many
relationship to the DISTRIBUTION.REPORTID and
RECIPIENTS.RECIPIENTID is a one-to-many
relationship to DISTRIBUTION.RECIPIENTID.
Thus, a query of reports can be made that will yield
everything needed to carry out the task of saving
and sending of the report. The query builder will
create the INNER JOINs for you.
Bill
dbl said:Hi Bill
Ok I have built 2 of the tables tblReports & tblRecipients I am stuck on
the 3rd table I dont follow what you mean with regard to these fields
RecipWkly text (Of the form: RecipientID1, What does this
mean? Of the form: RecipientID1
RecipientID2,..........IDn) I don't follow this at all plus 2 fields
with the same name?
RecipDly text (Of the form: RecipientID1,
RecipientID2,..........IDn)
With regard to weekly, yes from Monday 0.01am to Sunday 12 midnight
And the Monday daily reports are from Friday 0.01am to Sunday 12
midnight
Do you want to email the screenshot?
(e-mail address removed) the email address is correct if you replace the nospam
with 07790
Thanks for help
Bob
I would suggest that you begin with 3 tables:
REPORT TABLE: (report name appears only once)
ReportID Autonumber (primary key)
ReportName text
DISTRIBUTION TABLE: (who gets what report with what frequency)
DistID Autonumber (primary key)
ReportID text
RecipWkly text (Of the form: RecipientID1,
RecipientID2,..........IDn)
RecipDly text (Of the form: RecipientID1,
RecipientID2,..........IDn)
RECIPIENTS TABLE: (Name and EMA only)
RecipientID Autonumber (primary key)
RecipientName text
RecipientEMA text
When you're in attendance and ready to send out
a report, your command button can call a general
module that will run the report, save the snapshot
to disk (OutputTo) and then run an IMAP
complient e-mail client "quietly" to actually send
the Snapshot files to whoever is to receive them.
The "report name" is the only piece of information
needed to search the three tables to find out who
and how often they're to receive them.
You need to define what weekly means, e.g.,
Monday 12AM to Sunday midnight??
Notice that there's no duplication of data amongst
the three tables. Also note that one only has to make
changes to one field to effect the whole scheme of
things for any one recipient. Like someone's EMA
or the name of the report.
The Distribution table needs to have an edit/entry
form to maintain the list of recipients. I'll send you
a screenshot of how one of my applications attends
to like tasks as folks are added or deleted from
a group.
Bill
Hi Bill yes you do have the general idea. Each report that has data
is
sent to the same email address or addresses. Some reports go to 1-2 or
3
people max. Some people require daily reports some only require their
reports weekly, but the email addresses are all static they very
rarely
change. A weekly report would not be sent to someone who has daily
reports and daily reports would not be sent to someone who had weekly
reports. Each report is numbered automatically when it opens (if it
has
data to report) otherwise it give a message the "report has no data"
and
the report doesn't open.
I would only want the reports to go when someone is in attendance.
Because we have to get the info on to the system from the previous
night
or from the week-end.
With regard to the new table some of the reports go to people who are
not
linked to any part of the db so how do I go about this, do I need the
following fields
EmailAddress
EmailAddress1
EmailAddress2
ReportName
ReportFrequency
The only part that would link the table to the report would be the
"ReportName"
"ReportFrequency" what type of data would we store hear text i.e.
Weekly
or numbers But how do you over come the Monday daily reports that
covers
Fri-Sunday.
So how would the new table link to the reports? I can see why we need
the
table, but I cannot see how we link the report to the table or can we
search for the report name in the table to find the required EMA's and
report frequency.
Regards Bob
Bob,
Your post subject implies to me that you'd like to
distribute a report each day showing the DB's data
status of the previous day's end, likewise on say
Mondays for the previous week. If that's the case
then that suggests a fixed set of recipients and their
corresponding EMA's coupled with their respective
data interests, namely a specific query of the DB
each day and, again say, on Monday showing the
previous week's end.
Do I have the correct impression of what you're
after?
Bill
Bob,
First, I take it that you want to run these reports
when there's a user in attendance, rather than having
them fire off at some designated time when the user
is off-line? Second, you might want to think about
a new table that is normalized (link without any
duplication of data) to the reports that lists who
and where reports should be sent. You would
necessarily have another form that allows you to
edit the recipients and their EMA's. (Obviously,
if this information changes with each session, it
wouldn't be practical to use this approach.) If
possible, this new table might also contain the
frequency and period bracketing that would add
to the possibility of TOTALLY automating the
sending of reports.
I'm in California USA, so we have a time gap
that will tend to slow down our communications,
but I'll do what I can to keep up with you as time
allows.
Bill
Bill by the sound of it I need a lot of help. Ok can we start with
the
general module
All of the command buttons for the daily/weekly reports are on a
form
called frmReportsPrinter
On this form we have 2 text boxes txtStartDate and txtEndDate in
these
fields we enter the report start date and end date which would be
yesterdays date unless it is Monday then it would be
Friday, Saturday and Sundays dates. We also run weekly reports
that
run from the previous Monday to Sunday these are run on a Monday
So using the report rptMaximumDaily how do I get started? How do we
build the code to run the report and email it without user input?
Other
than opening the db.
Do we use code to enter the required dates in the 2 text boxes then
run
each command button? but how does it know where to email it to?
As I said I think I need a lot of help to get started.
Thanks Bob
What part do you need help with? Autoexec macro?
General module? Where does the recipient list and
date information come from to feed the automation?
It works very well to have the general module
ALWAYS receive control, whether it be for normal
human interaction or automated tasks. A simple
parameter passed by the Autoexec macro would
signal to the general module what code path to take.
Bill
Bill thanks for the response, you wouldn't have any sample code
to
get me started.
All of our reports are run manually by entering the start date
and
finish date into a form and then clicking the command button
appropriate to the report to be produced using the code below.
Dim stDocName As String
Dim strWhere As String
msgbox "This report goes to ???.???@????co.uk and to
?????.???@????.co.uk"
strWhere = "[Date] between #" & Format(Me.txtStartDate,
"dd-mmm-yy") & "# AND #" & Format(Me.txtEndDate, "dd-mmm-yy") &
"#"
stDocName = "rptMaximumDaily"
DoCmd.OpenReport stDocName, acPreview, , strWhere
We then manually send the report to Outlook and select the email
address. The process is very long winded.
Any further help would be very much appreciated
Bob
Create a general module that gets run
by the Autoexec macro of your primary
database with the appropriate code that
creates the reports and sends them to
an established list of recipients. Then,
create a Windows Scheduled Task that
runs your database.
Bill
Is it possible to get Access 2003 to send out daily and weekly
reports by email to clients at a certain time of the day
automatically?
If so how do you go about this? Or is it possible to get a
programe that will work with Access to do this?
Thanks Bob
dbl said:Bill
I now the tables, query and form in place as listed below:
tblDistribution
tblReports
tblRecipients
qryReports
frmReports
I take it the next step is to build the module, how do we do this?
Thanks again for your help.
Bob
Bill said:(PS) You might want to think about a naming
convention for the snapshot files.
E.g.,
c:\MyApplication\Weekly\reportname.snp
c:\MyApplication\dayoftheweek\reportname.snp
The life of any file is one week old.
Bill
Bill said:Bob,
It might be easier for you to build the 3rd table
this way:
DISTRIBUTION:
ReportID number
Frequency number (1/2 daily/weekly)
RecipientID number
Where table REPORTS.REPORTID is a one-to-many
relationship to the DISTRIBUTION.REPORTID and
RECIPIENTS.RECIPIENTID is a one-to-many
relationship to DISTRIBUTION.RECIPIENTID.
Thus, a query of reports can be made that will yield
everything needed to carry out the task of saving
and sending of the report. The query builder will
create the INNER JOINs for you.
Bill
Hi Bill
Ok I have built 2 of the tables tblReports & tblRecipients I am stuck
on the 3rd table I dont follow what you mean with regard to these
fields
RecipWkly text (Of the form: RecipientID1, What does this
mean? Of the form: RecipientID1
RecipientID2,..........IDn) I don't follow this at all plus 2 fields
with the same name?
RecipDly text (Of the form: RecipientID1,
RecipientID2,..........IDn)
With regard to weekly, yes from Monday 0.01am to Sunday 12 midnight
And the Monday daily reports are from Friday 0.01am to Sunday 12
midnight
Do you want to email the screenshot?
(e-mail address removed) the email address is correct if you replace the
nospam with 07790
Thanks for help
Bob
I would suggest that you begin with 3 tables:
REPORT TABLE: (report name appears only once)
ReportID Autonumber (primary key)
ReportName text
DISTRIBUTION TABLE: (who gets what report with what frequency)
DistID Autonumber (primary key)
ReportID text
RecipWkly text (Of the form: RecipientID1,
RecipientID2,..........IDn)
RecipDly text (Of the form: RecipientID1,
RecipientID2,..........IDn)
RECIPIENTS TABLE: (Name and EMA only)
RecipientID Autonumber (primary key)
RecipientName text
RecipientEMA text
When you're in attendance and ready to send out
a report, your command button can call a general
module that will run the report, save the snapshot
to disk (OutputTo) and then run an IMAP
complient e-mail client "quietly" to actually send
the Snapshot files to whoever is to receive them.
The "report name" is the only piece of information
needed to search the three tables to find out who
and how often they're to receive them.
You need to define what weekly means, e.g.,
Monday 12AM to Sunday midnight??
Notice that there's no duplication of data amongst
the three tables. Also note that one only has to make
changes to one field to effect the whole scheme of
things for any one recipient. Like someone's EMA
or the name of the report.
The Distribution table needs to have an edit/entry
form to maintain the list of recipients. I'll send you
a screenshot of how one of my applications attends
to like tasks as folks are added or deleted from
a group.
Bill
Hi Bill yes you do have the general idea. Each report that has data
is
sent to the same email address or addresses. Some reports go to 1-2
or 3
people max. Some people require daily reports some only require
their
reports weekly, but the email addresses are all static they very
rarely
change. A weekly report would not be sent to someone who has daily
reports and daily reports would not be sent to someone who had weekly
reports. Each report is numbered automatically when it opens (if it
has
data to report) otherwise it give a message the "report has no data"
and
the report doesn't open.
I would only want the reports to go when someone is in attendance.
Because we have to get the info on to the system from the previous
night
or from the week-end.
With regard to the new table some of the reports go to people who are
not
linked to any part of the db so how do I go about this, do I need the
following fields
EmailAddress
EmailAddress1
EmailAddress2
ReportName
ReportFrequency
The only part that would link the table to the report would be the
"ReportName"
"ReportFrequency" what type of data would we store hear text i.e.
Weekly
or numbers But how do you over come the Monday daily reports that
covers
Fri-Sunday.
So how would the new table link to the reports? I can see why we
need the
table, but I cannot see how we link the report to the table or can we
search for the report name in the table to find the required EMA's
and
report frequency.
Regards Bob
Bob,
Your post subject implies to me that you'd like to
distribute a report each day showing the DB's data
status of the previous day's end, likewise on say
Mondays for the previous week. If that's the case
then that suggests a fixed set of recipients and their
corresponding EMA's coupled with their respective
data interests, namely a specific query of the DB
each day and, again say, on Monday showing the
previous week's end.
Do I have the correct impression of what you're
after?
Bill
Bob,
First, I take it that you want to run these reports
when there's a user in attendance, rather than having
them fire off at some designated time when the user
is off-line? Second, you might want to think about
a new table that is normalized (link without any
duplication of data) to the reports that lists who
and where reports should be sent. You would
necessarily have another form that allows you to
edit the recipients and their EMA's. (Obviously,
if this information changes with each session, it
wouldn't be practical to use this approach.) If
possible, this new table might also contain the
frequency and period bracketing that would add
to the possibility of TOTALLY automating the
sending of reports.
I'm in California USA, so we have a time gap
that will tend to slow down our communications,
but I'll do what I can to keep up with you as time
allows.
Bill
Bill by the sound of it I need a lot of help. Ok can we start
with the
general module
All of the command buttons for the daily/weekly reports are on a
form
called frmReportsPrinter
On this form we have 2 text boxes txtStartDate and txtEndDate in
these
fields we enter the report start date and end date which would be
yesterdays date unless it is Monday then it would be
Friday, Saturday and Sundays dates. We also run weekly reports
that
run from the previous Monday to Sunday these are run on a Monday
So using the report rptMaximumDaily how do I get started? How do
we
build the code to run the report and email it without user input?
Other
than opening the db.
Do we use code to enter the required dates in the 2 text boxes
then run
each command button? but how does it know where to email it to?
As I said I think I need a lot of help to get started.
Thanks Bob
What part do you need help with? Autoexec macro?
General module? Where does the recipient list and
date information come from to feed the automation?
It works very well to have the general module
ALWAYS receive control, whether it be for normal
human interaction or automated tasks. A simple
parameter passed by the Autoexec macro would
signal to the general module what code path to take.
Bill
Bill thanks for the response, you wouldn't have any sample code
to
get me started.
All of our reports are run manually by entering the start date
and
finish date into a form and then clicking the command button
appropriate to the report to be produced using the code below.
Dim stDocName As String
Dim strWhere As String
msgbox "This report goes to ???.???@????co.uk and to
?????.???@????.co.uk"
strWhere = "[Date] between #" & Format(Me.txtStartDate,
"dd-mmm-yy") & "# AND #" & Format(Me.txtEndDate, "dd-mmm-yy") &
"#"
stDocName = "rptMaximumDaily"
DoCmd.OpenReport stDocName, acPreview, , strWhere
We then manually send the report to Outlook and select the email
address. The process is very long winded.
Any further help would be very much appreciated
Bob
Create a general module that gets run
by the Autoexec macro of your primary
database with the appropriate code that
creates the reports and sends them to
an established list of recipients. Then,
create a Windows Scheduled Task that
runs your database.
Bill
Is it possible to get Access 2003 to send out daily and weekly
reports by email to clients at a certain time of the day
automatically?
If so how do you go about this? Or is it possible to get a
programe that will work with Access to do this?
Thanks Bob