emailing attaching active workbook to it to myself

[alistair]

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.

 

[Bernie Deitrick]

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 = "(e-mail address removed)"
myItem.Subject = "Subject Line"
myItem.Body = myMsg
Set myAtts = myItem.Attachments
myAtts.Add ActiveWorkbook.FullName
myItem.Send

Set ol = Nothing

End Sub

 

[Guest]

Bernie

I done it but if I dont put
ChDir "C:\My Documents"
' ActiveWorkbook.SaveAs FileName:= _
' "C:\My Documents\test.xls"

then I get run time error as system cannot find file
because each time someone click the button it will say the
file exists and they will have to press yes to save over
the existing file is there not a code that could do this.

email me please on (e-mail address removed)

thanks

 

[Bernie Deitrick]

Alistair,

You don't need to save the file to a specific place to email it. You could
just put

Application.DisplayAlerts = False
ActiveWorkbook.Save
Application.DisplayAlerts = True

at the top of the code example that I gave you to ensure that the file is
saved prior to emailing it.

The file that is emailed is the current file, no matter where it is stored:
the line

myAtts.Add ActiveWorkbook.FullName

doesn't care where the file is located.

HTH,
Bernie
MS Excel MVP