VBA - Help modify email code

[unit213]

I use the following code to save a copy of my active workbook and add the current date to the name of the file. Here's and example of a file name the code generates: Daily Schedule For 2004-09-14 Tuesday.xls.

Sub CopyForProduction()

Call DeleteShapes

 'Sheets("Attainment").Select
 '   ActiveWindow.SelectedSheets.Delete

Dim da, mot, daypa, newfor As String
    Dim sp As Integer
    da = Date
    da = Trim$(da)
    sp = InStr(da, "/")
    mot = Right$("0" & Left$(da, sp - 1), 2)
    daypa = Mid$(da, sp + 1)
    sp = InStr(daypa, "/")
    daypa = Right$("0" & Left$(daypa, sp - 1), 2)
    'IF len(da
    newfor = Right$(da, 4) & "-" & mot & "-" & daypa & " " & WeekdayName(Weekday(Date))
    
  
    
    ChDir "c:\sched"
    ActiveWorkbook.SaveAs FileName:= _
        "c:\sched\Daily Schedule For " & newfor & ".xls", FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
        False, CreateBackup:=False
    
    'Call savexpad
    
    Range("B5").Select

End Sub

Here's my problem. This procedure is run daily so there are 100's of files in the same folder. I want to email the file containing today's date in the name. How do I modify the following code to do that? I bolded the part that I think needs to be modified.

Sub sendmail()

'Open a new blank message in Outlook
Set myol = New Outlook.Application
Set myitem = myol.CreateItem(olMailItem)
myitem.To = Variable_to
myitem.CC = Variable_cc
'myitem.BCC = ""
myitem.Subject = "Daily Production Schedule" 'Variable_subject
myitem.Body = "The most current schedule is attached."
Set myAttachments = myitem.Attachments

myAttachments.Add "C:/Sched/filename.xls", olByValue, , "Daily Schedule"
        
myitem.Display ':- if you want outlook to display the mail message for you to check/add to before you send
'or use
'myitem.send :-if you just want your application to send email automatically.

End Sub

Help is appreciated as always. Thanks in advance to all of you VBA gurus.

 

[Damon Ostrander]

Hi unit213,

Here's what I would suggest. Replace

myAttachments.Add "C:/Sched/filename.xls", olByValue, , "Daily Schedule"

with the following:

Dim fName As String

'copy code here that forms newfor from the current date

'find file pathname containing current date in required format
fName = Dir("C:/Sched/*" & newfor & "*")

myAttachments.Add fName, olByValue, ,"Daily Schedule"


You will need to copy and paste the code that forms the filename (newfor) where indicated above.

 

[bat17]

You did not say what format for the date but it is easy to change

myAttachments.Add "C:/Sched/filename" & Format(Date, "dd-mm-yy")
& ".xls", olByValue, , "Daily Schedule"

HTH

Peter

 

[unit213]

Thanks for the replies guys. I tried both methods and I can't get them to work. Here's the code:

Sub sendmail()

'Call DeleteShapes

'Open a new blank message in Outlook
Set myol = New Outlook.Application
Set myitem = myol.CreateItem(olMailItem)
myitem.To = Variable_to
myitem.CC = Variable_cc
'myitem.BCC = ""
myitem.Subject = "Daily Production Schedule" 'Variable_subject
myitem.Body = "The most current schedule is attached."
Set myAttachments = myitem.Attachments

'myAttachments.Add "C:/SCHED/Daily Schedule For" & Format(Date, "yyyy-mm-dd"), olByValue, , "Daily Production Schedule"

Dim fName As String

newfor = Right$(da, 4) & "-" & mot & "-" & daypa & " " & WeekdayName(Weekday(Date))


'find file pathname containing current date in required format
fName = Dir("C:/SCHED/Daily Schedule For" & newfor)

myAttachments.Add fName, olByValue, , "Daily Schedule"




myitem.Display ':- if you want outlook to display the mail message for you to check/add to before you send
'or use
'myitem.send :-if you just want your application to send email automatically.

End Sub

Do you see anything wrong?

 

[unit213]

I'm getting an error...can't find this file. The formatting must be wrong. Here's the file name:

Daily Schedule For 2004-09-14

 

[Damon Ostrander]

Hi again unit213,

I think the only thing you are really missing is the ".xls" and the space after the "For" (the asterisks I used in my posting were intended to allow it to tolerate differences in prefixes and suffixes--like the ".xls"--in the filename). In addition, since you really know the complete file name and path, it shouldn't be necessary to use the Dir() function to search for the file using wildcards as I suggested before. So I think you can just replace

fName = Dir("C:/SCHED/Daily Schedule For" & newfor)

with

fName = "c:\sched\Daily Schedule For " & newfor & ".xls"

Damon

 

[unit213]

Damon - I replaced

fName = Dir("C:/SCHED/Daily Schedule For" & newfor)

with

fName = "c:\sched\Daily Schedule For " & newfor & ".xls"

and it's still blowing up. Excel can't find the file. It says to check to make sure the path and filename are correct. I've checked both and they seem right on.

 

[bat17]

if after the line
fName = "c:\sched\Daily Schedule For " & newfor & ".xls"

you add
Debug.print fname
this will print the value of fname to the debug window (ctr-G to open) when you run the code.

This will let you see if you are getting the expected value. you could even try copying it from there and pasting it in the Windows Run box to see if it will open the file that way as a check of the path/name

HTH

Peter

 

[unit213]

Thanks Peter. The Debug.print fname helped. It's looking for:

C:\SCHED\Daily Schedule For-- Wednesday.xls

instead of:

Daily Schedule For 2004-09-15 Wednesday.xls

So the code is wrong somwhere.

 

[unit213]

I got it guys. I forgot to Dim some variables.

Peter. Your Debug.Print command did it for me. Thanks again everyone.

Dan