Email using CDO method and network days/weeks in VBA

[Challenged]

HiI 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 orDaily 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:

Sub test()     Dim vbMonth As Long    vbMonth = Format(Date, "mm")    Dim Today    Dim vbWeekday    myWeekday = Weekday(Date)    If myWeekday = 2 Then        Today = Format(Date - 3, "dddd mmmm dd yyyy")    Else        Today = Format(Date - 1, "dddd mmmm dd yyyy")    End If     Dim vbWeeknum As Long    vbWeeknum = Format(Date, "ww", 2)    myWeekno = vbWeeknum    If myWeeknum = 2 Then        Weeknum = Format(vbWeeknum - 3, "dd")    Else        Weeknum = Format(vbWeeknum - 1, "dd")    End If      Folder_Month = (vbMonth)    Folder_Name = ("Daily Claims Stats -" & " Week " & vbWeeknum & " - " & Today)    Dim strNewFolderName As String    strNewFolderName = MonthName(Format(Date, "mm")) & " " & Format(Date, "yy")    If Len(Dir("c:\temp\" & strNewFolderName, vbDirectory)) = 0 Then       MkDir ("c:\temp\" & strNewFolderName)    End If   ActiveWorkbook.SaveAs ("c:\temp\") & strNewFolderName & "\" & Folder_Name & ".xls" *************The above code works well and saves the file in the following path on the c:temp folderC:\temp\August 08\Daily Claims Stats - Week 33 - Tuesday August 12 2008 Its the email section below that is providing the challenge.. *************  Dim objMsg As ObjectSet objMsg = CreateObject("CDO.Message")objMsg.Subject = "Sample CDO Message"objMsg.From = " me@myemail.com "objMsg.To = " me@myemail.com "'objMsg.Cc = " email@removed "'objMsg.Bcc = " email@removed "objMsg.TextBody = "This sample message rocks! See Attachment..."'objMsg.AddAttachment "d:\temp\"'objMsg.AddAttachment "D:/webs/123/j2005_demo/temp/data.csv"'objMsg.AddAttachment (ThisWorkbook.Path & "/" & Replace(Replace(shtData.Range("AC2").Value, ".", ""), "/", "") & " " & Format(Date, "mm-dd-yyyy") & ".xls")'objMsg.AddAttachment ("c:\temp\") & strNewFolderName & "\" & Folder_Name & ".xls"objMsg.attachfile "ThisWorkbook.Path & strNewFolderName & " \ " & Folder_Name & .xls"objMsg.SendSet objMsg = Nothing End Sub 

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??

'objMsg.AddAttachment (ThisWorkbook.Path & "/" & Replace(Replace(shtData.Range("AC2").Value, ".", ""), "/", "") & " " & Format(Date, "mm-dd-yyyy") & ".xls")'objMsg.AddAttachment (ThisWorkbook.Path & "/" & ".xls")

I have tried playing with this code:

objMsg.attachfile "ThisWorkbook.Path & strNewFolderName & " \ " & Folder_Name & .xls"

topass the file name and path C:\temp\August 08\Daily Claims Stats - Week 33 - Tuesday August 12 2008 to the attach file method to no avail..Incidently the Outlook.application method below, does not work for me, but does include

.Attachments.Add ActiveWorkbook.FullName

- the ability to attach files directly thru the VBA

Dim OutApp As Outlook.Application Dim OutMail As Outlook.MailItem Set OutMail = OutApp.CreateItem(olMailItem)

Any ideas, would be greatly appreciated??thanks Gareth

[justsomeguy]

Correct me if I'm wrong, but if this is a macro in Excel (which I assume it is from the references to ActiveWorkbook and things like that), and the Excel file is open so that you can run the macro, or the macro opens the file, and you try to save the file with a new name and then attach that file, it's not going to attach the file because it's open. Excel files get locked when they get opened, so if the Excel file is open so that you are running the macro it won't be able to attach itself to an email because it's still open. Am I reading that correctly?

[Challenged]

Correct me if I'm wrong, but if this is a macro in Excel (which I assume it is from the references to ActiveWorkbook and things like that), and the Excel file is open so that you can run the macro, or the macro opens the file, and you try to save the file with a new name and then attach that file, it's not going to attach the file because it's open. Excel files get locked when they get opened, so if the Excel file is open so that you are running the macro it won't be able to attach itself to an email because it's still open. Am I reading that correctly?

You were correct - I had to revert to the first saved file, then i could email and kill the second file thus it sends out as an attachmentThanks for the response, sorry for the delay in my thanking you - this project was placed on the back burner for a while..thanks again