Email using CDO method and network days/weeks in VBA

Hi

I have a script that saves a file using yesterdays date onto a network drive, using the VBA equivalent of network days and weeks to save the file in the following format: Daily Claims Stats - Week 33 - Monday August 11 2008 or
Daily Claims Stats - Week 32 - Friday August 08 2008. - this part works fine, but when i try to use the CDO mail method I can only manage to send the email without the attachment.

The idea is that this all takes place with one button click, the file is saved and emailed to an email group.

My script is as follows:

Please Login or Register  to view this content.

I have seen the sendmail option, but i don't want the pop up and have read Ron de Bruin but cannot seem to work out the sending of a file into an email without opening outlook and/or the file again and sending it out in that manner.

I was wondering if this code was the key?? to use a virtual workbook path??

Please Login or Register  to view this content.

Incidently the Outlook.application method below, does not work for me, but does include

Please Login or Register  to view this content.

- the ability to attach files directly thru the VBA

Please Login or Register  to view this content.

Any ideas, would be greatly appreciated??

thanks Gareth

Bump - any ideas please??

You should put Option Explicit at the top of the module, fix the things that keep it from compiling, and then step though your code watching the variable formation. You have undeclared variables in use, variables declared and unused, variables set and then never used for anything, and way more variables than you need. You alse have a leading space in a directory name, which is not valid.

Thanks for the response...Picked up the extra space - thx

the problem is not with the save as script, and it works so i am comforatable with the unassigned variables.

the problem is transfering the file name stored by this code:

Please Login or Register  to view this content.

for example =
Daily Claims Stats - Week 33 - Monday August 11 2008 or
Daily Claims Stats - Week 32 - Friday August 08 2008 or (today's)
Daily Claims Stats - Week 33 - Tuesday August 12 2008

into the email path of

Please Login or Register  to view this content.

How do i do this?? ie, attach the file directly?

thanks gareth

I don't do any Outlook automation, so I can't help with that part.

Are you dead certain that the file path to the attachment is correct?

Absolutely positve..just insert the code into a module and run it, it will save the file like - Daily Claims Stats - Week 33 - Tuesday August 12 2008.xls
See the below screenshot as proof

thanks for your help tho, anyone with outlook automation skills that can help???

thanks Gareth