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:

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"



' This part of the code works well except for the objMsg.AddAttachment object.....

Dim objMsg As Object
Set objMsg = CreateObject("CDO.Message")

objMsg.Subject = "Sample CDO Message"
objMsg.From = " [email protected] "
objMsg.To = " [email protected] "
'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 (ThisWorkbook.Path & "/" & ".xls")
objMsg.Send

Set 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")
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