Auto Attach Access generated .xls file to email

[Guest]

Hi Everyone,

I have two questions relating to the same Access 03 Database macro problem:

[1] - I use a macro to produce an .xls file from a Query. I want to use
SendObject to automate the emailing of that file to a specific recipient.
Unfortunately, the name of the file produced each time is that of the Query
from which it is drawn. I need for the filename to be different but cannot
work out how to do so. The file will be used as an upload file to an FMIS
and so the filename needs to be unique each time the file is created. I can
achieve this with the OutputTo function but not the SendObject function. Any
suggestions?

[2] - If the above cannot be done, then the alternative would be to produce
the file with the correct filename using the OutputTo function and then pick
up that file as an email attachment. I would like to use the SendObject
function to do this and have a macro that would automatically pick up the
correct file from the relevant directory. I cannot seem to get this to work.
As the filename changes each time (the directory remains constant) I cannot
figure out how to setup a macro that allows for such a scenario. The
filename contains a date reference (DD MM YY) and it is this part on which
the macro seems to fall apart.

Any advice/suggestions would be greatly appreciated . . . . I am relatively
new to Access and whilst willing to learn, this problem beating me at the
moment !!

Thanks - ShaunJ

 

[Steve Schapel]

Shaun,

I can only think of one way to do this wilth a macro.

Start the macro off with a CopyObject action, so that you make a copy of
the Query with the name of the Excel file name you want. So, let's say
your base query in named MyQuery, use CopyObject to make a query
MyQuery090207 and then, in the Object Name argument of the SendObject
action in your macro, you can put like this:
="MyQuery" & Format(Date(),"ddmmyy")
This should result in your email going with a file attached as
MyQuery090207.xls