Macro button - save as and email

[jay-excel]

Hello,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I am trying to create a button that when clicked, it will save the file in a new location then send the file as an attachment in an email. I have been playing around with the macro posted below, but have not had very much success (being new at macros, my knowledge is rather limited). <o></o>
<o></o>
My goal for the macro is:<o></o>
<o> </o>
1. click button<o></o>
2. the file name is taken from a cell within the workbook <o></o>
3. file saves to a specified directory<o></o>
4. after the file is saved, it is sent as an attachment in an email<o></o>
5. the subject in the email will be the same as the file name<o></o>
<o></o>
<o></o>
So far I have pieced together the following formula:<o></o>
<o></o>
---------<o></o>
Private Sub Sendorderbutton_Click()
Application.Dialogs(xlDialogSendMail).Show arg1:="email@email.com", _
arg2:=ActiveSheet.Range("Y2")<o></o>

fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")<o></o>
'If user specified file name, perform Save and display msgbox
If fileSaveName <> False Then
ActiveWorkbook.SaveAs Filename:=ActiveSheet.Range("Y2"), FileFormat:=xlNormal<o></o>
MsgBox "Save as " & fileSaveName
End If
End Sub<o></o>
<o></o>
------------<o></o>
<o></o>
The problems (among many) that I am having are:<o></o>
<o></o>
1. Setting the directory location for the file to be saved.<o></o>
2. I cannot get the file name to be taken from a cell in the sheet<o></o>
3. I want the file to saved and named before the email is sent.<o></o>
<o></o>
I am aware that the code for saving the file comes after the code for emailing the file. However, given my limited knowledge I was unable to successfully change the order of the two.<o></o>
<o></o>
Any help or suggestions would be greatly appreciated. Or if anyone could point me to a similar post or macro that is performing the same function I would be very grateful.<o></o>
<o> </o>
Thanks in advance for helping me advance my VBA knowledge.<o></o>
<o> </o>
- Jay<o></o>

 

[jim may]

Check out:

http://www.rondebruin.nl/sendmail.htm

 

[jay-excel]

Hi,

Thanks for the link. Unfortunately however I was unable to locate an example that contained an emailing and saving option. The closest one saved the file in a temp directory then the file was deleted.

Cheers

 

[jay-excel]

Hello,

I have come up with the following which seems to work pretty well.

Private Sub Sendorderbutton_Click()
ThisWorkbook.SaveAs Range("M53") & Range("J53")
Application.Dialogs(xlDialogSendMail).Show arg1:="mail@mail.com", _
arg2:=ActiveSheet.Range("Y2")
End Sub



I am experiencing a problem is the file name exists and the user chooses not to replace it.

Thanks.

Jason