MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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??

Posted by Ivan Moala on May 14, 2001 6:04 AM

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

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
End With


Set objMail = Nothing
Set objOL = Nothing
Set sRg = Nothing
End Sub


Posted by Richard S on May 14, 2001 3:35 PM

Not Quite

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.

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