Run Time Error 1004: Cannot save this workbook with same name as another open workbook or add-in.

[tahjb]

Hello -

Anyone able to assist a newbie on VBA coding, please.

I have learned some great stuff reading comments and watching online videos, etc.

But, I have patched together some code from various excel experts to:
1. save the file and then email as an excel attachment (.xlsx)

The issue currently running into thus far, is if i have already saved the file, then re-open to make adjustments, and then click my macro button to finally email the file. I get a run time error 1004: You cannot save this workbook with the same name as another open workbook or add-in. Choose a different name, or close the other workbook or add-in before closing.

The Debug checker points to this line of code as error - ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook

Is there away to skip or ignore the run time error and still have excel re-save or overwrite the existing with same file name?

Here is my full code:

Sub Mail_workbook_Outlook_2()
'Working in Excel 2000-2016
'Mail a copy of the ActiveWorkbook with another file name
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
    Dim wb1 As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim OutApp As Object
    Dim OutMail As Object
    Dim NewFN As Variant


    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With


    Set wb1 = ActiveWorkbook
    
    'Copy req to a new workbook
    Sheets.Copy
    NewFN = "C:\Users\tbomar\Documents\Requisitions\Submitted Reqs\Material Req" & Range("H4").Value & Range("H2").Value & Range("B3").Value & ".xlsx"
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
    Application.DisplayAlerts = True
    ActiveWorkbook.Close


    'Make a copy of the file/Open it/Mail it
    'If you want to change the file name then change only TempFileName
    TempFilePath = Environ$("temp") & ""
    TempFileName = "Material Req." & Range("H4").Value & Range("H2").Value & Range("B3").Value & ".xlsx"
    FileExtStr = ".xlsx" & LCase(Right(wb1.Name, Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))


    wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
    
    Range("H4").Value = Left(Range("H4").Value, 3) & Mid(Range("H4").Value, 4, 4) + 1
    Range("A7:A31").ClearContents
    Range("A33,A36").ClearContents
    Range("D7:H30").ClearContents
    Range("H31").ClearContents
    Range("I7:P30").ClearContents
    Range("E3").ClearContents
    Range("H2").ClearContents
    ActiveWorkbook.Save


    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)


    On Error Resume Next
    With OutMail
        .to = "reqs@gandhtowing.com"
        .CC = ""
        .BCC = ""
        .Subject = ""
        .Body = "Please see attached material requisition"
        .Attachments.Add TempFilePath & TempFileName & FileExtStr
        .Display   'or use .send
    End With
    On Error GoTo 0




    Set OutMail = Nothing
    Set OutApp = Nothing


    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    
End Sub

 

[JLGWhiz]

Whixh one is giving you the problem, NewFN or TempFileName?

It looks like the NewFn would be a different name each time and should save withouth problem so long as different data is entered in the two cell references. The options are to either just save the file instead of SaveAs, or to add a suffix to the file name that automatically changes with the date or time.

 

[tahjb]

The NewFN line of code.

Excel doesn’t like me saving the file again with same name. But, I don’t want to copies of same file in same folder. Just would like to overwrite existing file with any changes or non changes.

 

[Paul Ked]

don't use

ActiveWorkbook[COLOR=#333333].SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook[/COLOR]

just use

ActiveWorkbook.Save

 

[JLGWhiz]

I think you are trying to use code designed for one purpose to do something with a different purpose and that is not going to give you the results you want. If you are not creating a new file as the code in the OP indicates, then the 'SaveAs' method should not be used. But if you just change the 'SaveAs' statement to a 'Save' statement, then your code would no longer be valid for creating a new file. The solution is probably going to be to create a new macro to just save the workbook with changes and then email it.

From:

Sheets.Copy
NewFN = "C:\Users\tbomar\Documents\Requisitions\Submitted Reqs\Material Req" & Range("H4").Value & Range("H2").Value & Range("B3").Value & ".xlsx"
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook

To:

Sheets.Copy
Application.DisplayAlerts = False
ActiveWorkbook.Save FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = True
ActiveWorkbook.Close

Application.DisplayAlerts = True
ActiveWorkbook.Close[/CODE]

 

[tahjb]

GLGWhiz - I'll give your suggestion a try as well.

so i got past the error message with the below code updates. BUT, now when I go to use my other macro to email the file from excel, it doesn't attach the correct file/activeworkbook.


scenario:
X User re-opens their saved file to continue adding items
X User saves file then clicks the email macro (code listed below) button to email form/file.
X The outlook email window opens up and attaches a file – BUT, the file attached is not the workbook/requisition that user opened to add items and submit/email = BAD RESULT
-The incorrect file attached is in the correct file format .xlsx = GOOD RESULT
-The incorrect file attached when opened is blank and no data = BAD RESULT
-The incorrect file attached doesn’t have the same requisition # (it increases +1) = BAD RESULT


Any suggestions?

Sub Mail_workbook_Outlook_2()
'Working in Excel 2000-2016
'Mail a copy of the ActiveWorkbook with another file name
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm


'***Added the ws and wbSave variables
Dim ws As Worksheet
Dim wb1, wbSave As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim OutApp As Object
Dim OutMail As Object
'***Added the '.DisplayAlerts'
With Application
.ScreenUpdating = False
.EnableEvents = False
.DisplayAlerts = False
End With


Set wb1 = ActiveWorkbook
'***Setting the wbSave variable to the a new workbook
Set wbSave = Workbooks.Add
'***Copying each sheet in this workbook into this new workbook
For Each ws In ThisWorkbook.Worksheets
ws.Copy after:=wbSave.Sheets(wbSave.Sheets.Count)
Next ws
'***Deleting any extra sheets in wbSave
For Each ws In wbSave.Worksheets
If ws.Name Like "Sheet*" Then ws.Delete
Next ws
Application.GoTo wbSave.Sheets(1).Range("A1"), True
'***Added the '.xlsx' extension to the end of the file name
NewFN = "C:\Users\tbomar\Documents\Requisitions\Submitted Reqs\Material Req" & Range("H4").Value & Range("H2").Value & Range("B3").Value & ".xlsx"
'***Commented out the error suppression here
'On Error Resume Next
wbSave.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
'***Moved this from below to save the temp copy somewhere
'***Make a copy of the file to email it
'If you want to change the file name then change only TempFileName
TempFilePath = Environ$("temp") & ""
TempFileName = "Material Req" & Range("H4").Value & Range("H2").Value & Range("B3").Value
'***Fixed
FileExtStr = ".xlsx": FileFormatNum = 51 & LCase(Right(wb1.Name, Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))


wbSave.SaveAs TempFilePath & TempFileName & FileExtStr
wbSave.Close
'NextReq
'Range("H4").Value = Left(Range("H4").Value, 3) & Mid(Range("H4").Value, 4, 4) + 1
'Range("A7:A31").ClearContents
'Range("A33,A36").ClearContents
'Range("D7:H30").ClearContents
'Range("H31").ClearContents
'Range("I7:p30").ClearContents
'Range("E3").ClearContents
'Range("H2").ClearContents
'ActiveWorkbook.Save


Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)


On Error Resume Next
With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = ""
.Body = "Please see attached material requisition"
.Attachments.Add TempFilePath & TempFileName & FileExtStr
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Display 'or use .Display
End With
'Delete the file
Kill TempFilePath & TempFileName & FileExtStr
'***Moved this below the kill file code above so that it suppresses that error if one occurs
On Error GoTo 0


Set OutMail = Nothing
Set OutApp = Nothing
With Application
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
End With
End Sub

 

[JLGWhiz]

Try doing your Attachment add with this line.

Attachments.Add TempFilePath & TempFileName & ".xlsx"

I suspect it is the FileFormat data in the variable that is causing the miscue.