Code SEEMED to be working Yesterday. Help PLEASE!!!

[Mister H]

Hi All:

I am confused. I was working with the partial code below yesterday. I made alterations to it (THANKS Alan) and I thought it was working properly but now soemthing has gone wrong??? It no longer functions as it should and I have no idea what I need to do to get it working again. Here is the little piece of code that is supose to save a Worksheet as a NEW Workbook giving it a preset name with the option to edit that filename. It is also supose to go to the directory specified.

'Save the new workbook AND Mail it
    FilePath = "C:\New\"
    
    TempFileName = Format(ActiveSheet.Range("C1"), "yyyy mm dd") & " " & "MOHLTCK REV"
    
    NewName = Application.GetSaveAsFilename(InitialFileName:=TempFileName)
    
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)
  
    With Destwb
        .SaveAs NewName & FileExtStr, FileFormat:=FileFormatNum
        On Error Resume Next
        With OutMail
            .To = ""
            .CC = ""
            .BCC = ""
            .Subject = "MOHLTC Revenue Listing Attached"
            .Body = "Hi:  Please see attached MOHLTC Revenue Listing for Upload to your system."
            .Attachments.Add Destwb.FullName
            
            'You can add other files also like this
            '.Attachments.Add ("C:\test.txt")
            
            .Display   'can use .Send or .Display
            
        End With
        
        On Error GoTo 0
        .Close savechanges:=False
    End With
 
'IF you want to Delete the file you have sent then Activate the line below
    'Kill FilePath & FileName & FileExtStr
 
    Set OutMail = Nothing
    Set OutApp = Nothing
 
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

Here is what I can't figure out:

A)
No matter what I set the Path to it is bringing up the intial path as:
C:|Documents and Settings\HUGGINM\Desktop in the example above it should be going to C:\New??? How can I get the correct Path to come up?

B)
The name of the file that is being created should be (in this case)
18 Jun 2007 MOHLTCK REV.xls For some reason it is being saved as ..xls (extra decimal?) eg. 18 Jun 2007..xls??? How can I get rid of the EXTRA . ???

ANY assistance would be GREATLY appreciated. I have played around since the morning but my lack of VBA skills has reulted in me accomplishing nothing?

THANKS,
Mark

 

[blazonQC]

    With Destwb 
        .SaveAs NewName & FileExtStr, FileFormat:=FileFormatNum

The only place you seem to specify the var FilePath is in the 'Kill' command. Not when you're saving. I believe it think your documents folder is default for some reason. Try this:

    With Destwb 
        .SaveAs FilePath & NewName & FileExtStr, FileFormat:=FileFormatNum

 

[Derek Brown]

Two changes will resolve your problem.
You need to add the filepath to TempFileName and you need to set the file extension(s) for the file SAVEAS dialog box (see the VBA Help for a description of how to add more than one extension).
Remove the use of the fileExtStr variable

    filepath = "C:\test\"
   
    TempFileName = filepath & Format(ActiveSheet.Range("C1"), "yyyy mm dd") & " " & "MOHLTCK REV"
   
    NewName = Application.GetSaveAsFilename(InitialFileName:=TempFileName, _
            fileFilter:="Excel Files (*.xls), *.xls")

[/u]

 

[Mister H]

Almost there...

THANKS to both ogf you for your replies. I played around with both suggestions.

blazonQC's code was not changing the path however it seemed to remove the extra decimal. When i ran it by clicking the E-Mail button that I created I wound up getting an error:

Run-time error 1004
Method 'SaveAs' of object'_Workbook'failed

I then replied to you but then saw a 2nd response so I deleted my other Post. I have now tried d3p2j5b suggestion and it seems to be getting me closer. THANKS.

The path is now correct but the Automatic naming convention is no longer working properly.

The name should be:

18 Jun 2007 MOHLTCK REV.xls but is now saving as
MOHLTC DEPOSIT FILES2007 06 18 MOHLTCK REV.xls.xls

Any suggestions?

THANKS Again,
Mark

 

[Derek Brown]

It looks as though you didn't remove the use of FileExtStr.
The following code works for me:

'Save the new workbook AND Mail it
    filepath = "C:\test\"
   
    TempFileName = filepath & Format(ActiveSheet.Range("C1"), "dd mmm yyyy") & " " & "MOHLTCK REV"
   
    NewName = Application.GetSaveAsFilename(InitialFileName:=TempFileName, _
        fileFilter:="Excel Files (*.xls), *.xls")
   
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)
 
    With Destwb
        .SaveAs Filename:=NewName
        On Error Resume Next

You also need to change the VBA formatting of the date to "dd mmm yyyy"

 

[Mister H]

THANKS d3p2j5b I will check that out when I return to the office on Monday. I appreciate the follow up.

Have a GREAT weekend,
Mark