VBA References - Microsoft Outlook 14.0 Object Library - load with macro

I've had a look around at some other threads. In essence, this is my setup:

Microsoft Access 2010, Excel 2010, Outlook 2010.

On Access close:
Export query to: C:\file.xls
Open excel object and import module
call module
close excel
kill file.xls

Basically, when the database closes I want it to automatically do some stuff I'm fine with all this, works well. The issue comes here:

Included in running the macro on the xls file is to generate and send an email. Easily done, just tick the appropriate reference for microsoft outlook 14.0 object library. Works a treat.

This issue is that the minute I close excel and try to run the macro, this reference is unticked. No idea why it doesnt save. I've tried some code to load this reference but it hasnt worked.

Any suggestions welcome.

Thanks

I "mess around with it" to make it a nicer format before attaching the file to the email and sending it.

I'm not as familar with access, I dont doubt it could be done. I can make it so the workbook is an existing workbook and access just calls the macro within that. My only issue then is how to export the query to that workbook, without replacing it.

I had been using the Access Macro rather than access VBA for that.

Originally Posted by JosephP

you could just set up an external data query in the excel workbook and run everything from there

Originally Posted by Norie

How about doing everything in Excel?

*facepalm* You know when the answer is staring you in the face, but you need a fresh pair of eyes to point it out.

Thanks both, I'll do a link into excel.