Is there any way to put email body using Sendmail? 1

[pangerankodok]

Hi All,
I tried to send excel workbook automatically using Macro, and here is the syntax:

Sub email()
ActiveWorkbook.SendMail "someone@yahoo.com", Range("subject&quot.Value
End Sub

what I want is that user click the button, and the workbook automatically sent to someone@yahoo.com with subject and message body taken from the workbook.
Is there any way to include message body using this methode? (SendMail)

I also tried some API Code from this forum to send email, but it won't attached the excel workbook and send it automatically.

Appreciated your helps,
TIA,

 

[Nikita6003]

Hi pangerankodok

Nope, SendMail will just send the sheet as attachment - you can only set Recipients & Subject & ReturnReceipt
Which email client are you using? If it's Outlook, you can set up an Outlook object quite easily. This'll allow you to create e nem email in Excel using the Outlook object & set every option/outlook field available to you
If it's Lotus Notes/GroupWise, you can do the same, but using the Notes/GroupWise object (different code for each object type, though - that's why I'm not adding any to this post)

Failing that, you *could* try the RoutingSlip option that Excel offers - has the advantage that it allows you to set the message body, has the *dis*advantage that the mail is sent as a "routing slip" - i.e. to be routed through several recipients. 1 person finishes work on the spreadsheet & then routs the mail onwards

Just a few ideas - let us know if you need/want more info

Cheers
Nikki

 

[pangerankodok]

Thanks Nikita6003,
I am using Outlook, and after surfing around, I found this code
--------------
Sub Button1_Click()
Dim objOL As Outlook.Application
Dim objMail As MailItem
Set objOL = New Outlook.Application
Set objMail = objOL.CreateItem(olMailItem)
With objMail
.To = "someone@yahoo.com"
.Attachments = ActiveWorkbook
.CC = "metoo@yahoo.com"
.Subject = Range("subject&quot.Value
.Body = Range("body&quot.Value
.Send
End With
Set objMail = Nothing
Set objOL = Nothing
End Sub
----------------
It gave me error message, "Compile Error, User-defined type not defined".
Fortunately, I found the solution in

http://www.slipstick.com/dev/tips/gethelp.htm

"If you're in Word or Excel VBA, choose Tools | References, and add the Microsoft Outlook library to the project."
Finally it works.

Now is the question:

I still had error message during the line .Attachments = ActiveWorkbook.
It gave me error message: "Runtime Error : Property is Read Only"
Any clue?
----
PS: Sorry for the long message. I just want to share my experience related with this problem. Hopefully it will be useful to another novice out there
----

 

[RobBroekhuis]

try
.Attachments.Add ActiveWorkbook.FullName

Rob

 

[sdraper]

Wouldn't you want
olmail.Attachments.Add ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
in order to attach the workbook?
sdraper

 

[sdraper]

OOOOOPSSSS!
I didn't try FullName
to quote the great Emily Littela of SNL fame
nevermind...
sdraper

 

[pangerankodok]

Thanks RobBroekhuis, I tried the code, and the final code is this:
------
Sub emailme()

Dim objOL As Outlook.Application
Dim objMail As MailItem
Set objOL = New Outlook.Application
Set objMail = objOL.CreateItem(olMailItem)
With objMail
.To = "me@yahoo.com"
.Subject = Range("subject&quot.Value
.Attachments.Add ActiveWorkbook.FullName
.Body = Range("body&quot.Value
.Send
End With
Set objMail = Nothing
Set objOL = Nothing

End Sub
------
As my previous question, there's an error message, "Compile Error, User-defined type not defined".
And the solution is:
"If you're in Word or Excel VBA, choose Tools | References, and add the Microsoft Outlook library to the project."
Finally it works ... for me.

My end users got the same error message when they tried to run this procedure. I don't want them to play with Excel VBA setting (and neither do them) to solve the problem.

Is there any way to overcome solve this problem from the code?

Thanks ...

 

[RobBroekhuis]

The references are part of the VBA project (or so I believe), and should be part of the saved workbook. If your users are having problems, check if they have the same version of Office/Outlook installed. It may be a case of a different version of the library.
If all else fails, you can use late binding to prevent the compile errors, but it's a lot less pleasant while you're programming, and more difficult to read the code, too. If you think that's the way you want to go and need some help with that, check back and we'll help.
Rob

 

[pangerankodok]

Some of use are using Outlook XP, and the others Outlook 97.
And it gave the same error message.
Yes, I am interested in late binding that you mentioned. I suppose that will make life easier for usser, right?
And do you have an URL that discuss this methode?

Thks, a lot..