Alistair,

You can modify the macro below.

HTH,
Bernie
MS Excel MVP

Sub EmailActiveWorkBookNow()
Dim ol As Object
Dim myItem As Outlook.MailItem
Dim myMsg As String
Dim myAtts As Outlook.Attachments

Set ol = CreateObject("outlook.application")

myMsg = "Alistair," & Chr(10)
myMsg = myMsg & "Here's that stupid file." & Chr(10)
myMsg = myMsg & Application.UserName

Set myItem = ol.CreateItem(olMailItem)
myItem.to = "
myItem.Subject = "Subject Line"
myItem.Body = myMsg
Set myAtts = myItem.Attachments
myAtts.Add ActiveWorkbook.FullName
myItem.Send

Set ol = Nothing

End Sub


"alistair" wrote in message
...
Hi

I have this problem I am trying to get users to click on a
button and send it to me.

Sub email()
' ChDir "C:\My Documents"
' ActiveWorkbook.SaveAs FileName:= _
' "C:\My Documents\stats9.xls"

'x = MsgBox("Your Stats 9 has now been saved to c:/My
documents and is about to email it to Alistair Straight
away. Would you like this to continue to email Alistair or
email him later.", vbYesNo)

'If x = vbYes Then
ActiveWorkbook.Save

Beep
Beep

Dim ol As Outlook.Application
Dim ns As Outlook.NameSpace
Dim newm As Outlook.MailItem
Set ol = New Outlook.Application
Set ns = ol.GetNamespace("MAPI")
Set newm = ol.CreateItem(olMailItem)
With newm
.To = "bob the builder" ' email address to send to
.Subject = "material" ' subject of the email
.Body = "Hello" ' message in the email
With .Attachments ' attachment
.Add ("ActiveWorkbook") ' add the file i am
using at present
.DisplayName = "boo!!"
End With
.Send
End With
Set ol = Nothing
Set ns = Nothing
Set newm = Nothing

End sub

when i run this all i get is a Run-time error '-1006174187
(c4070015)': device is not ready.

error message and then goes to the part in the macro that
says .add ("activeworkbook") part. what I am trying to do
is save this file onto the users machine but i have no
idea where they are going to put it so i stated a location
but ultimately i work liek to send the activework book
with all the information back to me.