Ivan Moala re email from Macro
Posted by Richard S on May 14, 2001 12:41 AM
Hi Ivan,
Earlier this month you answered a question regarding generating an email from within Excel. I want to do something similar, but I have absolutely no idea what I'm doing. Instead of the Body being the contents of just one cell, I want to send a range say A4:J31, or even the entire sheet. If I use .Body = Range("A4:J31").Select, the message just says true. If I use .Text, there is a runtime error. As I said, no idea what I'm doing. Can you help??
Richard
Posted by Ivan Moala on May 14, 2001 6:04 AM
Richard
This may help you.
Sub Notify()
Dim objOL As New Outlook.Application
Dim objMail As MailItem
Dim sRg As Range 'Your range to send
Dim sbody As String 'The actual string to send
Dim c As Range
Set sRg = Range("A4:J31")
For Each c In sRg
sbody = sbody & c.Text
Next
Set objOL = New Outlook.Application
Set objMail = objOL.CreateItem(olMailItem)
With objMail
.To = Range("A1").Text 'Or just place your add here
.Subject = "Test"
.Body = sbody
.Display
End With
objMail.Send
Set objMail = Nothing
Set objOL = Nothing
Set sRg = Nothing
End Sub
Ivan
Posted by Richard S on May 14, 2001 3:35 PM
Not Quite
Ivan,
The Macro you wrote seems to grab the data from each cell and put it in the body of the email message. The formatting is all gone. What I want to emulate is the commands in Excel "File | Send To | Mail Recipient" and choose send this sheet. If I do that from Excel, the formatting is retained and it looks exactly the same as a printed version. Is this possible?
Thanks for your efforts.
Richard
Posted by Ivan Moala on May 14, 2001 10:05 PM
Re: Not Quite Ah ha thats a little diff
See Daves answer 16596.html
Ivan
