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
