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

Mister H

Well-known Member
Joined
Mar 6, 2002
Messages
1,507
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.
Code:
'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??? :rolleyes: 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 . :rolleyes: ???

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

THANKS,
Mark :biggrin:
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

blazonQC

Board Regular
Joined
Jun 6, 2007
Messages
92
Code:
    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:

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

Derek Brown

Well-known Member
Joined
Dec 26, 2005
Messages
2,390
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

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

Well-known Member
Joined
Mar 6, 2002
Messages
1,507
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 :biggrin: 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 :biggrin:
 

Derek Brown

Well-known Member
Joined
Dec 26, 2005
Messages
2,390
It looks as though you didn't remove the use of FileExtStr.
The following code works for me:

Code:
'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

Well-known Member
Joined
Mar 6, 2002
Messages
1,507
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
 

Forum statistics

Threads
1,181,404
Messages
5,929,750
Members
436,687
Latest member
Glass of Gin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top