excel vba form

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Excel VBA form - Save as

Excel VBA form - Save as

resolvedResolved · High Priority · Version 2003

Anna has attended:
Excel VBA Advanced course

Excel VBA form - Save as

I need to create a button on a user form that will launch the 'Save as' window and point to the user's desktop to allow them to save the workbook. I also need it to automatically send the workbook in an email.
I tried using the following code which managed to send the email, but did not seem to save the file:

Dim TheFile As Variant
TheFile = Application.GetSaveAsFilename("Desktop:OutreachDataSubmit.xls", _
"Workbook (*.xls), *.xls", , "Your choice:")

If TheFile = False Then
MsgBox "You cancelled - please ensure you save the file"

End If


ActiveWorkbook.SendMail _
Recipients:="anna@bbbbbbbc.co.uk", _
Subject:="Outreach Data " & Format(Date, "dd/mmm/yy")

RE: Excel VBA form - Save as

I think I've actually solved this now using the following code, however, if you know of a better solution to this then please let me know, thanks:

MsgBox "Please select location to save file"

ActiveWorkbook.SaveAs _
Application.GetSaveAsFilename("OutreachDataSubmit", _
"Microsoft Office Excel Workbook (*.xls), *.xls")

MsgBox "An email will now launch to " & vbCr & "send the data, " & vbcr & "Please accept"

ActiveWorkbook.SendMail _
Recipients:="anna@bbbbbbbc.co.uk", _
Subject:="Outreach Data " & Format(Date, "dd/mmm/yy")

ActiveWorkbook.Close

 

Training courses

 

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Highlighting only Text cells

To select onlt text value cells in a spreadsheet, click on Edit-Go to(F5)


In the Go To dialog box, click Special.
In the Go To Special dialog box, select Constants.
Click OK.

View all Excel hints and tips


Server loaded in 0.09 secs.