Thread: 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:

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 = " no-reply@myemail.com "
objMsg.To = " myemail@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 (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

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:

ActiveWorkbook.SaveAs ("c:\temp\") & strNewFolderName & "\" & Folder_Name & ".xls"

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

objMsg.AddAttachment

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