Hi Corey,

Try something like:

Sub Mail_Selection_Outlook_Body()
' You must add a reference to the Microsoft outlook Library
' Don't forget to copy the function RangetoHTML in the module.
' Is not working in Office 97
Dim source As Range
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem

Set source = Nothing
On Error Resume Next
Set source = ThisWorkbook.Sheets("Sheet1").Range("A1:D20")
On Error GoTo 0
If source Is Nothing Then
MsgBox "The selection is not a range or the sheet is protect" & _
vbNewLine & "please correct and try again.", vbOKOnly
Exit Sub
End If

If ActiveWindow.SelectedSheets.Count 1 Or _
source.Cells.Count = 1 Or _
source.Areas.Count 1 Then
MsgBox "An Error occurred :" & vbNewLine & vbNewLine & _
"You have more than one sheet selected." & vbNewLine & _
"You only selected one cell." & vbNewLine & _
"You selected more than one area." & vbNewLine & vbNewLine &
_
"Please correct and try again.", vbOKOnly
Exit Sub
End If

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = ThisWorkbook.Sheets("Sheet1").Range("B53").Value
.CC = ThisWorkbook.Sheets("Sheet1").Range("E53").Value
.BCC = ""
.Subject = ThisWorkbook.Sheets("Sheet1").Range("B55").Value
.HTMLBody = RangetoHTML(source)
.Send 'or use .Display
End With

Set OutMail = Nothing
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub


Public Function RangetoHTML(source As Range)
' You can't use this function in Excel 97
Dim fso As Object
Dim ts As Object
Dim TempFile As String
TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") &
".htm"
With ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=ActiveSheet.Name, _
source:=source.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
Set ts = Nothing
Set fso = Nothing
Kill TempFile
End Function


---
Regards,
Norman



"Corey" wrote in message
...
Thanks Ron.
I put this in the code, but still get the whole sheet in the email body.
Do i drop off somehting here ?

.HTMLBody = SheetToHTML(ActiveSheet) <-----


Corey....
"Ron de Bruin" wrote in message
...
Hi Corey

Look at this link
http://www.rondebruin.nl/mail/folder3/mail4.htm

You see this line in the code
Set source = Selection

Change that to

Set source = ThisWorkbook.Sheets("Sheet1").Range("B45:I107")

Note that I use the Function RangetoHTML in this example


--
Regards Ron De Bruin
http://www.rondebruin.nl



"Corey" wrote in message
...
Thnaks again Norman, but i cannot get ONLY a range of cells to email
instead of the whole activesheet.

Code current below:

Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = ThisWorkbook.Sheets("Sheet1").Range("B53").Value ' address
in sheet
.CC = ThisWorkbook.Sheets("Sheet1").Range("E53").Value ' cc
address in sheet
.BCC = ""
.Subject = ThisWorkbook.Sheets("Sheet1").Range("B55").Value '
subject line info in sheet
' .Body = bodyStr.("Sheet1").Range("B45:I107") <-----------------
Tried this to no avail also
.HTMLBody = SheetToHTML(ActiveSheet) ' <----------------- WANT
TO SET THIS TO SEND IN BODY AS HTML ONLY RANGE("B45:I107") NOT WHOLE
SHEET
' .Attachments.Add () Add a file address here to add an attachment
later
.Display '.send to auto send without prompting
End With
Application.ScreenUpdating = True
Set OutMail = Nothing
Set OutApp = Nothing
End Sub


I looked at the 'Set source = Selection' but i could not get it to work
either.

Any idea's ?


Corey....
"Norman Jones" wrote in message
...
Hi Corey,

I can see the code there, but cannot still find the code to Select
ONLY cells say (B45:I107)

Is it there some where, as i cannot see any reference to cell ranges.

The suggested code includes the line:

Set source = Selection

Try changing Selection to your required range.


---
Regards,
Norman


"Corey" wrote in message
...
Thanks.
I can see the code there, but cannot still find the code to Select
ONLY cells say (B45:I107)

Is it there some where, as i cannot see any reference to cell ranges.

Corey....

"Norman Jones" wrote in message
...
Hi Corey,

See Ron de Bruin's example code at:

http://www.rondebruin.nl/mail/folder3/mail4.htm


---
Regards,
Norman


"Corey" wrote in message
...

I want to adapt this:
.HTMLBody = SheetToHTML(ActiveSheet)

How can i only have a selected range of cells, or a selected page
sent in the body of an email instaed of the entire sheet as it
currently does?
Any idea's ??

I want to send a range of ("A45:I107") or
Page 1


Corey....