SaveAs Unique Name

[jaclynresendez]

Hello All,

I'm trying to get my Command Button to save the workbook as a unique name each time. Here I have it saving with a specific filename and, if it already exists it's SUPPOSED to add a number to the name. Like if WorkbookA1 already exists then it's to save as WorkbookA2, THEN sends it in an email. The problem is it'll save the first book as filename1 but will not go on to save the next as filename2 if filename1 already exists. This is the code I already have:

Dim objFSO As Object
Dim i As Integer
Dim strFileNamePrefix As String
Dim strPath As String
Dim strUniqueFilename As String
strPath = "\\Snnp-oa-001\snnopertns\AutoRun\NGE_"
strFileNamePrefix = Range("B25")
Set objFSO = CreateObject("Scripting.FileSystemObject")
i = 1
While objFSO.FileExists(strPath & strFileNamePrefix & Pad(i, 3))
i = i + 1
Wend
Set objFSO = Nothing
strUniqueFilename = strPath & strFileNamePrefix & Pad(i, 3) & ".xls"
ActiveWorkbook.SaveAs strUniqueFilename
ActiveWorkbook.SendMail Recipients:=myemail@company.com

Any help or a better way to do this would be greatly appreciated. Was trying to get this done today, but may not. Hopefully it'll be ready to go tomorrow???

 

[gauntletxg]

Upon first glance, you don't appear to have a file extension in your loop.

 

[jaclynresendez]

I have no idea what I'm doing. Any suggestions? Where would I indicate the extension?