Is there any VBA coding that will allow me to e-mail a file from within
that file?

I want to be able to put a button in an excel file that will let me
e-mail the file it is in to a list of pre-assigned e-mail addresses.

We are a Lotus based e-mail system here, I don't know if that affects
anything.

Any help would be appreciated.

Private Sub CommandButton1_Click()
'This will temporarily save the Spreadsheet and then
'e-mail the saved file to someone else
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim wbname As String

' Stop screen updating
Application.ScreenUpdating = False
Set wb1 = ActiveWorkbook
'Create the temporary file name
wbname = "C:/" & wb1.Name & "" & Format(Now, "dd-mm-yyyy h-mm-ss") &
".xls"
'Save the temporary file
wb1.SaveCopyAs wbname
'Open the temporary file
Set wb2 = Workbooks.Open(wbname)
'using the temporary file create an email
With wb2
.SendMail ", "Subject"
.ChangeFileAccess xlReadOnly
' delete the temporary file
Kill .FullName
.Close False
End With
' start screen updating
Application.ScreenUpdating = True
End Sub


--
Rui Caetano
IT Manager
Township of East Hanover, NJ

"SmartyPants" wrote in message
oups.com...
Is there any VBA coding that will allow me to e-mail a file from within
that file?

I want to be able to put a button in an excel file that will let me
e-mail the file it is in to a list of pre-assigned e-mail addresses.

We are a Lotus based e-mail system here, I don't know if that affects
anything.

Any help would be appreciated.

Hi SmartyPants

See this site for Lotus code
http://www.excelkb.com/?cNode=1X5M7A

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"SmartyPants" wrote in message oups.com...
Is there any VBA coding that will allow me to e-mail a file from within
that file?

I want to be able to put a button in an excel file that will let me
e-mail the file it is in to a list of pre-assigned e-mail addresses.

We are a Lotus based e-mail system here, I don't know if that affects
anything.

Any help would be appreciated.

See
http://www.rondebruin.nl/mail/prevent.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Freddy" wrote in message ...
Allow me to interject here. I tested successfully the code below. Is there a
way to suppress the dialogue box notifying me that an email is being sent?

"ruic" wrote:

Private Sub CommandButton1_Click()
'This will temporarily save the Spreadsheet and then
'e-mail the saved file to someone else
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim wbname As String

' Stop screen updating
Application.ScreenUpdating = False
Set wb1 = ActiveWorkbook
'Create the temporary file name
wbname = "C:/" & wb1.Name & "" & Format(Now, "dd-mm-yyyy h-mm-ss") &
".xls"
'Save the temporary file
wb1.SaveCopyAs wbname
'Open the temporary file
Set wb2 = Workbooks.Open(wbname)
'using the temporary file create an email
With wb2
.SendMail ", "Subject"
.ChangeFileAccess xlReadOnly
' delete the temporary file
Kill .FullName
.Close False
End With
' start screen updating
Application.ScreenUpdating = True
End Sub


--
Rui Caetano
IT Manager
Township of East Hanover, NJ

"SmartyPants" wrote in message
oups.com...
Is there any VBA coding that will allow me to e-mail a file from within
that file?

I want to be able to put a button in an excel file that will let me
e-mail the file it is in to a list of pre-assigned e-mail addresses.

We are a Lotus based e-mail system here, I don't know if that affects
anything.

Any help would be appreciated.

There is a link to my CDO page there

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Freddy" wrote in message ...
I reviewed the hyperlink and went with Express ClickYes. HOWEVER, is there a
way to do the same thing using VBA code in Excel.

"Ron de Bruin" wrote:

See
http://www.rondebruin.nl/mail/prevent.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Freddy" wrote in message ...
Allow me to interject here. I tested successfully the code below. Is there a
way to suppress the dialogue box notifying me that an email is being sent?

"ruic" wrote:

Private Sub CommandButton1_Click()
'This will temporarily save the Spreadsheet and then
'e-mail the saved file to someone else
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim wbname As String

' Stop screen updating
Application.ScreenUpdating = False
Set wb1 = ActiveWorkbook
'Create the temporary file name
wbname = "C:/" & wb1.Name & "" & Format(Now, "dd-mm-yyyy h-mm-ss") &
".xls"
'Save the temporary file
wb1.SaveCopyAs wbname
'Open the temporary file
Set wb2 = Workbooks.Open(wbname)
'using the temporary file create an email
With wb2
.SendMail ", "Subject"
.ChangeFileAccess xlReadOnly
' delete the temporary file
Kill .FullName
.Close False
End With
' start screen updating
Application.ScreenUpdating = True
End Sub


--
Rui Caetano
IT Manager
Township of East Hanover, NJ

"SmartyPants" wrote in message
oups.com...
Is there any VBA coding that will allow me to e-mail a file from within
that file?

I want to be able to put a button in an excel file that will let me
e-mail the file it is in to a list of pre-assigned e-mail addresses.

We are a Lotus based e-mail system here, I don't know if that affects
anything.

Any help would be appreciated.