Export table to Excel for emailing

[nc]

The database which I have produced for a charity produces (via a query) a
quarterly report for other charities which receive money through ours. The
reports show the names & addresses of the donors, the amount they have
donated and the date they paid the money to us. The printed reports are fine.
However we have been asked if we could email the contents of the report as
an Excel worksheet. Is this possible? Do I need some elaborate VB coding?
(I am a novice in this area - I can cut & paste!). We have the appropriate
email addresses in the database. Is it possible to export the necessary
information from either an Access query or table to an Excel workbook (say
'April payments') and have individual worksheets which will bear the name of
each charity? If so is there some way to automatically pick up the email
address, open a new message in Outlook Express and attach the relevant sheet
from the workbook?
Not sure that this is very clear - but maybe someone can start me down the
right road?

 

[Daniel Pineault]

nc,

No elaborate coding required! Look at the TransferSpreadsheet Method to
transfer the info to Excel Workbooks. Then you'd need to use Outlook
automation to create the e-mail with the attachment and send it off. You can
get the required Outlook Function at
http://www.devhut.net/index.php?lang=en&id=0000000013#AutoOlk

Look this over and post back if you need further help.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[nc]

Thanks for reminding me about TransferSpreadsheet. But this will put all the
contents of my table (or query) into a single worksheet within the workbook.
How can I get it to group on 'Charity' and put the relevant entries into
seperate worksheets each with the name of the charity?

I will have a look at your suggestion regarding automating within Outlook
Express. Thanks for you help.

 

[Ken Snell MVP]

Create a Query and Export multiple "filtered" versions of a Query (based on
data in another table) to separate Worksheets within one EXCEL file via
TransferSpreadsheet
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#FilterExportSameFile

 

[nc]

Thanks Ken. I've just seen your post this morning. This looks promising. I
will now try and make all the appropriate changes to table, query and field
names so that it works for me. I will let you know how I get on.

 

[nc]

I've hit a snag! I think I have converted all the variables to suit my
database but am getting 'run-time error 3012 - Object 'zExportQuery' already
exists'. This is at the 'set qdf = .....' line.
When I hover over 'strSQL' in that line I discover that this is showing the
name of a Table which is not relevant to this particular function! The whole
block of code which I copied from your link is in a Private Sub Command on
Click. What do I need to do to resolve this problem?

 

[Ken Snell MVP]

Did you run the code a first time and it didn't complete because of an
error? If yes, then the deletion of that temporary query wasn't done by the
code; the code does that near the end.

Manually delete the zExportQuery query from your database, and then try
again.

 

[nc]

Yes. When I delete the temporary query 'zExportQuery' and run the code I get
a 'Run-time error 3075 Syntax Error (missing operator) in Query expression
'[Name of Mission] = Latin Link'
The following code is highlighted as the error - strMgr = DLookup("[Name of
Mission]", "[April List]", _
"[Name of Mission] = " & rstMgr![Name of Mission].Value)
My source table/query is called 'April List' and the field for grouping is
'Name of Mission' - this is the field where 'Latin Link' is picked up.
Does this help you to work out what is going wrong?

 

[Ken Snell MVP]

You need to delimit string values with ' characters in the expression. Try
this:

strMgr = DLookup("[Name of Mission]", "[April List]", _
"[Name of Mission] = '" & rstMgr![Name of Mission].Value & "'")

But I'm puzzled by why you're doing a DLookup on a field where you already
know the value? In the above expression, you are looking up the record for
the value that is already in your rstMgr recordset's field. You could
replace the above by this expression for this situation:

strMgr = rstMgr![Name of Mission].Value

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Yes. When I delete the temporary query 'zExportQuery' and run the code I
get
a 'Run-time error 3075 Syntax Error (missing operator) in Query expression
'[Name of Mission] = Latin Link'
The following code is highlighted as the error - strMgr = DLookup("[Name
of
Mission]", "[April List]", _
"[Name of Mission] = " & rstMgr![Name of Mission].Value)
My source table/query is called 'April List' and the field for grouping is
'Name of Mission' - this is the field where 'Latin Link' is picked up.
Does this help you to work out what is going wrong?

Did you run the code a first time and it didn't complete because of an
error? If yes, then the deletion of that temporary query wasn't done by
the
code; the code does that near the end.

Manually delete the zExportQuery query from your database, and then try
again.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/