[[email protected]]

I need someones urgent help. Below is what i am trying to accomplish.
I want to send one of the sheets in the excel to a specific email
address. The sheet name should be constant. So I was saving the sheet
first as xyz.xls and then sending this excel. I want to give option to
the user to select the filepath.

I tried using GetSaveAsFilename but in this i want to hide the cancel
button and not let the user edit the filename. May I request someone
to help.

Thank you.

[Bernie Deitrick]

Uthra,

Try this code, below.

HTH,
Bernie
MS Excel MVP


Option Explicit

Sub PickFolder()
Dim FolderChoice As Variant
Dim Chosen As Boolean
Dim FileName As String
FileName = "This is the file to email"

Chosen = False
While Not Chosen
FolderChoice = BrowseForFolder
If VarType(FolderChoice) < vbBoolean Then Chosen = True
Wend

'Then use SaveAs like this
FileName = FolderChoice & "\" & FileName & ".xls"
Msgbox "I will now save " & FileName & "!!!!"
ActiveWorkbookSaveAs FileName

End Sub

Function BrowseForFolder(Optional OpenAt As Variant) As Variant
'Function purpose: To Browser for a user selected folder.
'If the "OpenAt" path is provided, open the browser at that directory
'NOTE: If invalid, it will open at the Desktop level

Dim ShellApp As Object

'Create a file browser window at the default folder
Set ShellApp = CreateObject("Shell.Application"). _
BrowseForFolder(0, "Please choose a folder", 0, OpenAt)

'Set the folder to that selected. (On error in case cancelled)
On Error Resume Next
BrowseForFolder = ShellApp.self.Path
On Error GoTo 0

'Destroy the Shell Application
Set ShellApp = Nothing

'Check for invalid or non-entries and send to the Invalid error
'handler if found
'Valid selections can begin L: (where L is a letter) or
'\\ (as in \\servername\sharename. All others are invalid
Select Case Mid(BrowseForFolder, 2, 1)
Case Is = ":"
If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
Case Is = "\"
If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
Case Else
GoTo Invalid
End Select

Exit Function

Invalid:
'If it was determined that the selection was invalid, set to False
BrowseForFolder = False

End Function




wrote in message
...
I need someones urgent help. Below is what i am trying to accomplish.
I want to send one of the sheets in the excel to a specific email
address. The sheet name should be constant. So I was saving the sheet
first as xyz.xls and then sending this excel. I want to give option to
the user to select the filepath.

I tried using GetSaveAsFilename but in this i want to hide the cancel
button and not let the user edit the filename. May I request someone
to help.

Thank you.

[Jacob Skaria]

Dear Uthra

Another way is to use Application.FileDialog() option..The below code allows
to select a folder and you can validate the selection. Alternatively if you
are looking for Save as use Application.FileDialog(msoFileDialogSaveAs)

Dim strFolderPath As String
' Open the file dialog
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
If .SelectedItems.Count = 0 Then MsgBox "Please select a folder": Exit Sub
strFolderPath = .SelectedItems(1)
End With


If this post helps click Yes
---------------
Jacob Skaria


" wrote:

I need someones urgent help. Below is what i am trying to accomplish.
I want to send one of the sheets in the excel to a specific email
address. The sheet name should be constant. So I was saving the sheet
first as xyz.xls and then sending this excel. I want to give option to
the user to select the filepath.

I tried using GetSaveAsFilename but in this i want to hide the cancel
button and not let the user edit the filename. May I request someone
to help.

Thank you.

[[email protected]]

Thanks a million.... This really helped and the code worked :)