Export to Excel and email off

[Ian Baker]

I am not sure of the best way to do this but using Office 2003 I basically I
have a table called Territory with:
TerritoryCode
TerritoryMgrName
EmailAddress

and a query called qryTerritoryExport that is:
SELECT [SAP Raw Data].TerritoryCode, [SAP Raw Data].ProfitCentre, [SAP Raw
Data].AccountNo, [SAP Raw Data].AccountName, [SAP Raw Data].ProductNo, [SAP
Raw Data].OldMaterialNo, [SAP Raw Data].ProductDescription, [SAP Raw
Data].StockQty, Null AS StockCount, Null AS Diff, Null AS CountDate, [SAP
Raw Data].Batch, Null AS BatchChange, [SAP Raw Data].BatchExpiryDate,
[Master Data].Category, "" AS Comments, Date() AS SheetSent
FROM [SAP Raw Data] INNER JOIN [Master Data] ON [SAP Raw Data].ProductNo =
[Master Data].ProductNo
ORDER BY [SAP Raw Data].AccountNo
WITH OWNERACCESS OPTION;

I need to create an Excel spreadsheet for each TerritoryCode (common field
in both the table and the query), name the spreadsheet with TerritoryCode
and the current date in x folder and then email it to the EmailAddress. This
has to be done daily and for each TerritoryCode using Outlook 2003. The
query has fields that are set as null to create the columns required in the
spreadsheet. Two fields in the spreadsheet need to have a formula:
1. query field Diff needs the formula of =Nz([StockCount])-[StockQty]
relating to each row in the spreadsheet
2. query field CountDate needs to be set to the current date when an entry
goes in StockCount relating to each row in the spreadsheet

Not only am I unsure how to do this but also I am worried about jamming up
Outlook if it is slower processing the email with the spreadsheet as an
attachment then exporting and creating each spreadsheet.

Your help is so greatly appreciated