Auto save password protect Email - need to check file name

[columbo1977]

Hi All

Hope you can help :

I have the following code and although it works first time it will not work after that as it cannot overwrite the file that is already in the temp folder, so I need to be able to let excel choose the filenames or check to see if there is a file in the temp folder with the same name first.

Sub Send1Sheet_ActiveWorkbook()

    Workbook.Copy
    
    Dim sTemp As String
    sTemp = Environ("TEMP")

    ActiveWorkbook.SaveAs Filename:=sTemp & "\test.xls", FileFormat:= _
    xlNormal, Password:="1234", WriteResPassword:="x", ReadOnlyRecommended:= _
    True, CreateBackup:=False

    With ActiveWorkbook
         .SendMail Recipients:="xxx@cc.co.uk", _
          Subject:="Test of Protect"
         .Close SaveChanges:=False
    End With
    
End Sub

Thansk for looking

Graham

 

[CautionMP]

columbo1977,

If Dir$(sTemp & "\test.xls") <> "" then
  'The file exists already
End If

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[columbo1977]

Where do I put this?

 

[columbo1977]

Any ideas where I put this code to get it to work for me?

Cheers

 

[CautionMP]

Sub Send1Sheet_ActiveWorkbook()

    Workbook.Copy
    
    Dim sTemp As String
    sTemp = Environ("TEMP")

    [b]If Dir$(sTemp & "\test.xls") <> "" then
      Kill sTemp & "\test.xls"
    End If[/b]

    ActiveWorkbook.SaveAs Filename:=sTemp & "\test.xls", FileFormat:= _
    xlNormal, Password:="1234", WriteResPassword:="x", ReadOnlyRecommended:= _
    True, CreateBackup:=False

    With ActiveWorkbook
         .SendMail Recipients:="xxx@cc.co.uk", _
          Subject:="Test of Protect"
         .Close SaveChanges:=False
    End With
    
End Sub

 

[columbo1977]

That is excellent, work like a dream, Could you tell me what I would need to add into the code so that the new copied workbook breaks the links to the old one?

Ta