Send report to individual recipients

[M Joomun]

I have a difficult problem with a database. We have patients from
multiple GP Practices. We need to send these practices an individual
report with the results for their patients, say on a monthly basis.
There may be up to 80 practices.

I think I understand how to, say, send one email containing one report
to one practice, or one email containing one report to many practices,
but to send an individually tailored report/email to eighty practices
has me stumped.

Would I need to create 80 results reports? How would I loop through the
recordset of 80 practises?

If anyone has any pointers I would be really grateful.

TIA.

[PW]

On Wed, 23 May 2012 09:50:04 +0100, M Joomun <m.a.j...@qmul.ac.uk>
wrote:

I have a form that the user fills in whatever criteria they want and I
write the client's email addresses to a table. Then, I read through
the records and build a string containing all of their email
addresses. I then put it in the BCC of an Outlook email.

Not sure if this is what you want to do. If so, I will post the code.

-paulw

[Bob Barrows]

M Joomun wrote:
> I have a difficult problem with a database. We have patients from
> multiple GP Practices. We need to send these practices an individual
> report with the results for their patients, say on a monthly basis.
> There may be up to 80 practices.
>
> I think I understand how to, say, send one email containing one report
> to one practice,

Are you intending to send it as a snapshot? Or do you intend to export it to
Excel or rich text and email the result?

> or one email containing one report to many practices,
> but to send an individually tailored report/email to eighty practices
> has me stumped.
>
> Would I need to create 80 results reports? How would I loop through
> the recordset of 80 practises?
>
> If anyone has any pointers I would be really grateful.
>
> TIA.

Never had to do it but this would be my plan:
Create a single report that can be filtered to produce each practice's
results.
Assuming you have a table containing the email addresses, add a column or
columns to contain the filter values for the report .
In a button's event procedure, open a recordset on the table containing the
email addresses and filter values.
Assign the report to a Report variable (a variable of type Report).
Loop through the recordset. In each pass through the loop, use the filter
values from the recordset to build a filter string to assign to the report
variable's Filter property. Then run the report and send it to the email
address from the recordset (I don't remember, is there a SendReport method
of a Report object?).

There may be more nuances that depend on the version of Access you are
using, so be sure to provide that information to us. If you are using 2007
or later, I will need to defer to other posters because there may be
features in those later versions to make this task easier.

PS. I just had a thought: instead of storing filter values in multiple
columns in the email table, use a single column to contain an entire filter
string. Then in the loop, assign the filter string directly to the report's
Filter property rather than constructing it in code.

[Tony Toews]

On Wed, 23 May 2012 09:50:04 +0100, M Joomun <m.a.j...@qmul.ac.uk>
wrote:

>Would I need to create 80 results reports? How would I loop through the
>recordset of 80 practises?

Emailing a different report to each recipient
http://www.granite.ab.ca/access/email/reporttomultiplerecipients.htm

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

[M Joomun]

On 23/05/2012 17:33, PW wrote:
>
> I have a form that the user fills in whatever criteria they want and I
> write the client's email addresses to a table. Then, I read through
> the records and build a string containing all of their email
> addresses. I then put it in the BCC of an Outlook email.
>

Thanks very much PW. I think that would help.

[M Joomun]

On 23/05/2012 22:27, Tony Toews wrote:
> On Wed, 23 May 2012 09:50:04 +0100, M Joomun<m.a.j...@qmul.ac.uk>
> wrote:
>
>> Would I need to create 80 results reports? How would I loop through the
>> recordset of 80 practises?
>
> Emailing a different report to each recipient
> http://www.granite.ab.ca/access/email/reporttomultiplerecipients.htm
>
> Tony

Hello Tony,

Thanks for the reply. I don't think some of the solutions will work for
Access 2010 64-bit.

I'm trying to create a series of excel spreadsheets from a recordset
object, which I'll later attach to emails, hopefully. The recordset
contains three records (to test), so I'd like to create three excel
spreadsheets.

The name of the spreadsheet is comprised of the 'practice' and '.xls'.
Effectively, I want the code to loop through the recordset, grab the
first practice, create an excel spreadsheet using the practice and
splatter the data into it, move to the next record in the recordset and
so on.

The code below works as far as one spreadsheet is concerned (i.e. the
'first' record in the recordset), but fails at the 'rst.movenext' with
the 'runtime error '3021''.

If I remark the following lines:

oSheet.Range("A1").CopyFromRecordset rst
oBook.SaveAs ePath
oExcel.Quit

I get no error at all.




<Code>

Dim rst As DAO.Recordset
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
Dim ePath As String
Dim strSQL As String
Dim rcount As Integer

strSQL = "SELECT Practice, EmailAddress, ImmunisationDate FROM tblTempMail"

Set rst = CurrentDb.OpenRecordset(strSQL)
Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets(1)

rst.MoveLast
rcount = rst.RecordCount

rst.MoveFirst

Do Until rst.EOF = True

ePath = rst![practice]
ePath = "C:\EmailFromAccess\" & ePath & ".xls"

oSheet.Range("A1").CopyFromRecordset rst
oBook.SaveAs ePath
oExcel.Quit

rst.MoveNext

Loop

rst.Close
Set rst = Nothing
strSQL = ""

</code>

[Phil]

On 25/05/2012 09:35:34, M Joomun wrote:
> On 23/05/2012 22:27, Tony Toews wrote:
>> On Wed, 23 May 2012 09:50:04 +0100, M Joomun<m.a.j...@qmul.ac.uk>
>> wrote:
>>
>>> Would I need to create 80 results reports? How would I loop through the
>>> recordset of 80 practises?
>>
>> Emailing a different report to each recipient
>> http://www.granite.ab.ca/access/email/reporttomultiplerecipients.htm
>>
>> Tony
>
>
> Hello Tony,
>
> Thanks for the reply. I don't think some of the solutions will work for
> Access 2010 64-bit.

Firstly it is not a good idea using 64 bit Access 2010 as a number of API's
wont work. The reccommendation is unless you are using extreamly large Excel
files you should use Office 32 bit. That aside, I have an extreamly
complicated Db that does just what you want. I can use just about any form,
report, query or table as the source of an email, personalise the email so
that it says Dear Fred you owe me Ł5.00 or Dear Mary you owe me Ł1000 etc.
Attach a number of documents to the email such as external documents like
letters or pictures, MailMerge letters to Fred & Mary or personal internal
reports such as Fred's invoice or Mary's invoice or a static report on say
turnover figures. So in your case there would be a personalised report for
each practice. As I said this is all part of a 45Mb Front end database so it
is not possible to do anything through the newsgroup

Phil

[M Joomun]

>>
>> Thanks for the reply. I don't think some of the solutions will work for
>> Access 2010 64-bit.
>
> Firstly it is not a good idea using 64 bit Access 2010 as a number of API's
> wont work. The reccommendation is unless you are using extreamly large Excel
> files you should use Office 32 bit. That aside, I have an extreamly
> complicated Db that does just what you want. I can use just about any form,
> report, query or table as the source of an email, personalise the email so
> that it says Dear Fred you owe me Ł5.00 or Dear Mary you owe me Ł1000 etc.
> Attach a number of documents to the email such as external documents like

> letters or pictures, MailMerge letters to Fred& Mary or personal internal

> reports such as Fred's invoice or Mary's invoice or a static report on say
> turnover figures. So in your case there would be a personalised report for
> each practice. As I said this is all part of a 45Mb Front end database so it
> is not possible to do anything through the newsgroup
>
> Phil
>

Hello Phil,

Thanks very much for the reply. Am I right in thinking that your front
end Db would not work with Access 64-bit or not the functionality I
want? This is the direction we're moving in at work, so it's not really
my decision and I'm not sure it's something I can change.

If you want to discuss this via email, please contact me here:

m.a.joomun AT qmul.ac.uk

[Patrick Finucane]

On May 25, 5:15 am, "Phil" <p...@stantonfamily.co.uk> wrote:
> On 25/05/2012 09:35:34, M Joomun wrote:
>
>
>
>
>
> > On 23/05/2012 22:27, Tony Toews wrote:

> >> On Wed, 23 May 2012 09:50:04 +0100, M Joomun<m.a.joo...@qmul.ac.uk>

> >> wrote:
>
> >>> Would I need to create 80 results reports? How would I loop through the
> >>> recordset of 80 practises?
>
> >> Emailing a different report to each recipient
> >>http://www.granite.ab.ca/access/email/reporttomultiplerecipients.htm
>
> >> Tony
>
> > Hello Tony,
>
> > Thanks for the reply. I don't think some of the solutions will work for
> > Access 2010 64-bit.
>
> Firstly it is not a good idea using 64 bit Access 2010 as a number of API's
> wont work. The reccommendation is unless you are using extreamly large Excel
> files you should use Office 32 bit. That aside, I have an extreamly
> complicated Db that does just what you want. I can use just about any form,
> report, query or table as the source of an email, personalise the email so

> that it says Dear Fred you owe me £5.00 or Dear Mary you owe me £1000 etc.

> Attach a number of documents to the email such as external documents like
> letters or pictures, MailMerge letters to Fred & Mary or personal internal
> reports such as Fred's invoice or Mary's invoice or a static report on say
> turnover figures. So in your case there would be a personalised report for
> each practice. As I said this is all part of a 45Mb Front end database so it
> is not possible to do anything through the newsgroup
>

> Phil- Hide quoted text -
>
> - Show quoted text -

Phil, looking at his code I didn't see any API. There may be reasons
to not use Access64 but I would think automating Excel and Outlook and
Word would not be one of the reasons to not use it.

To the OP, I might make a multi-select listbox that contains a list of
clients since 80 aren't that much. Then select the clients you want
to send and email to. Then press a command button to start the
process, It enumerates the selected clients and creates the email,
updates Excel, etc.

[Phil]

I guess that my Db will not work with 64 bit. All of the declare statements
need something like Private Declare PtrSafe Function BlockInput Lib
"user32.dll" _ (ByVal fBlockIt As Long) As Long
Most of mine don't use the PtrSafe keyword, and Long in some instances needs
changing. Dont know which APi's wont work and I have references to
MSCOMCTL.OCX which may or may not register or work.

I suggest that you read a number of different posts on the subject of 64 bit
office. As I said, generally frowned upon. Suggest you enquire why you are
using 64 bit Office, I know it sounds "higher tech" than 32 bit, but there
needs to be a better reason than that.

Happy to email or Skype if you want to go further.

Phil

[Phil]

Patrick

Please see posts from various people whos opinion is to be trusted

see

http://office.microsoft.com/en-us/word-help/choose-the-32-bit-or-64-bit-version-of-microsoft-office-HA010369476.aspx

Phil

[M Joomun]

On 25/05/2012 16:39, Phil wrote:
>>>> Thanks for the reply. I don't think some of the solutions will work for
>>>> Access 2010 64-bit.
>>>
>>> Firstly it is not a good idea using 64 bit Access 2010 as a number of API's
>>> wont work. The reccommendation is unless you are using extreamly large Excel
>>> files you should use Office 32 bit. That aside, I have an extreamly
>>> complicated Db that does just what you want. I can use just about any form,
>>> report, query or table as the source of an email, personalise the email so

>>> that it says Dear Fred you owe me �5.00 or Dear Mary you owe me �1000 etc.

>>> Attach a number of documents to the email such as external documents like
>>> letters or pictures, MailMerge letters to Fred& Mary or personal internal
>>> reports such as Fred's invoice or Mary's invoice or a static report on say
>>> turnover figures. So in your case there would be a personalised report for
>>> each practice. As I said this is all part of a 45Mb Front end database so it
>>> is not possible to do anything through the newsgroup
>>>
>>> Phil


Hello Phil,

Thanks for the offer of help on this.

I think I've found a solution that works for me. Not elegant and
probably not efficient.

1) I append records to a temp table based on user-defined date. Records
contain each practice's email addresses and practice id.

These records contain a Practice ID, unique to each individual practice.
Another procedure loops through the temp table, exporting all records to
a different Excel spreadsheet depending on the practice ID.

2) The excel file is saved with the practice id as part of the file name.

3) Another bit of code looks through the temp table, one record at a
time and uses the practice id to find the path to an excel file saved in
a particular directory and then sends an email using the email address
for that practice to send the excel spreadsheet.

It seems to work but needs a bit more testing for robustness. Do you see
any problems I might have with this approach?

Thanks again,

Mo