VBA Save As Prompt Help

[Emoncada]

I have the following code

If Dir(dirstr & FileName) <> "" Then

MsgBox "File already exists!"

ChDir dirstr
sFName = Application.GetSaveAsFilename(dirstr & FileName)
If sFName = False Then
MsgBox "You Pressed Cancel! You Must Save File Or You Will Lose It!"

ChDir dirstr
sFName = Application.GetSaveAsFilename(dirstr & FileName)
If sFName = False Then
MsgBox "Kiss All this Data Goodbye!"

WB.Close SaveChanges:=False
Exit Sub

Else

WB.Close SaveChanges:=True

End If
Else

WB.Close SaveChanges:=True

End If

WB.Close SaveChanges:=True

Else
WB.SaveAs FileName:=dirstr & FileName
End If

What I want is when the file exists in that directory for it to prompt the user the SAVE As Prompt with a Specific Directory. Which this script is doing, Then if they click Cancel instead of save It will prompt one last warning.

The problem I am having is when the user clicks save after renaming the file I get an error Type Mismatch on

If sFName = False Then

Any Help would be great.

[Kenneth Hobs]

Dim sFName as Variant, not a String.

[Emoncada]

Thanks Kenneth I made that fix, but have another problem now.
I modified my code

Sub SaveNow()
'Variable declaration
Dim oApp As Object, _
oMail As Object, _
WB As Workbook, _
FileName As String
Dim dirstr As String
Dim sFName As Variant

'Turn off screen updating
Application.ScreenUpdating = False

ActiveSheet.Copy
Set WB = ActiveWorkbook
FileName = Environ("Username") & " - Imaging Report " & Format(Now, "mm.dd.yy") & ".xlsx"
ActiveSheet.Name = "Imaging Report"

dirstr = "H:\Imaging Reports\" & Environ("Username") & "\"

If Not DirectoryExist(dirstr) Then
MkDir dirstr
End If

If Dir(dirstr & FileName) <> "" Then

MsgBox "File already exists." & vbNewLine & vbNewLine & _
"You Will Lose all your Data if you don't Rename & Save", vbExclamation

ChDir dirstr
sFName = Application.GetSaveAsFilename(dirstr & FileName)
If sFName = "False" Then Exit Sub

Else

WB.SaveAs FileName:=dirstr & FileName
End If

'Create and show the outlook mail item
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
'Uncomment the line below to hard code a recipient
.To = "johndoe@yahoo.com"
'.Cc = ""
'Uncomment the line below to hard code a subject
.Subject = Environ("Username") & " - Imaging Report " & Format(Now, "mm.dd.yy")
.Body = "Attached Is " & Environ("Username") & " Imaging Report " & Format(Now, "mm.dd.yy")
.Attachments.Add WB.FullName
.Send 'or .Display
End With

'Delete the temporary file
' WB.ChangeFileAccess Mode:=xlReadOnly
' Kill WB.FullName
WB.Close SaveChanges:=True

'Restore screen updating and release Outlook
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing

The problem is when file exists and user enters new name and saves, I get a "Cannot find this file. Verify the path and file name are correct"
I think it has to do with the file not getting the "FileName" value and instead i need to get the user's save as value.
I get the error here
.Attachments.Add WB.FullName

Any help would be great.
Thanks

[Emoncada]

I tried
If sFName = True Then

Set WB = ActiveWorkbook


but still get same error.

[Kenneth Hobs]

Why would sFName ever be True or "False". Some use the quoted version of False which can be sort of equal but I prefer to be literal and just use the boolean False. It is either the fullname of the file that was chosen or False, no choice was made.

Of course you are not performing the SaveAs. You are just getting a filename or not with Application.GetSaveAsFilename().

[Emoncada]

Thanks Kenneth for the reply, so what would be the best way to accomplish my task.

I want it to prompt the user the save as dialog within a directory, then when saved to grab that new name over to finish off the rest of the script. Also if clicked "Cancel" then to exit the entire Sub

[Kenneth Hobs]

I don't think that you understand. After you get sFName you can use it to do whatever you want like saving the activeworkbook to that name. Be careful though as the activeworkbook might be the one with your macro and you are saving to a non-macro workbook with .xlsx.

Look at:
activeworkbook.Save fFName