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

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

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

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

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

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

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

Code i am now using in module3:

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 24/06/2006 by Corey


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
.CC = ThisWorkbook.Sheets("Sheet1").Range("E53").Value
.BCC = ""
.Subject = ThisWorkbook.Sheets("Sheet1").Range("B55").Value
Set source = ThisWorkbook.Sheets("Sheet1").Range("B45:I107") '
<-------------------------
.Body = bodyStr
.HTMLBody = RangetoHTML2 '
<-------------------------
' .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
Public Function RangetoHTML2() '
<----------------------------- ONWARDS
' 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:=ActiveSheet.UsedRange.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML2 = ts.ReadAll
ts.Close
Set ts = Nothing
Set fso = Nothing
Kill TempFile
End Function



Corey....
"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....

Please read

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

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

Hi

My advise is not correct

Add this line
Range("B45:I107").Select
before
Set source = Nothing

I update the site soon with range example


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



"Ron de Bruin" wrote in message ...
Please read

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

Ron,
Tried the below, but I STILL get the entire worksheet being emailed and not
the only the range selected.

Code is below:

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 24/06/2006 by Corey


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
.CC = ThisWorkbook.Sheets("Sheet1").Range("E53").Value
.BCC = ""
.Subject = ThisWorkbook.Sheets("Sheet1").Range("B55").Value
Range("B45:I107").Select '
<---------------------
Set source = Nothing '
<-----------------------
.Body = bodyStr
.HTMLBody = RangetoHTML2
' .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
Public Function RangetoHTML2()
' 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:=ActiveSheet.UsedRange.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML2 = ts.ReadAll
ts.Close
Set ts = Nothing
Set fso = Nothing
Kill TempFile
End Function


??
Corey....



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

My advise is not correct

Add this line
Range("B45:I107").Select
before
Set source = Nothing

I update the site soon with range example


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



"Ron de Bruin" wrote in message
...
Please read

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

Norman,
I tried the code you posted below but get an error as below:




"Norman Jones" wrote in message
...
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 & _ '
<---------------- I GET A SYNTAX ERROR HERE....... ?
"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
<------------------ TO HERE.....
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....

Hi Corey,

I tried the code you posted below but get an error as below:

<---------------- I GET A SYNTAX ERROR HERE....... ?
"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
<------------------ TO HERE


The suggsted code works for me.

Your problem is merely one of line breaeks: the problem section was intended
as a single line of code separated by the underscore line break character.

Replace the problem lines by copymg and pasting the following:

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


---
Regards,
Norman

Thanks You Norman and Ron.
Your last post corrected the syntax error i was getting.

Macro worked exactly as required.

Thanks for your help, appreciate it very much.

I can now move another post i need to fix.

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

I tried the code you posted below but get an error as below:

<---------------- I GET A SYNTAX ERROR HERE....... ?
"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
<------------------ TO HERE


The suggsted code works for me.

Your problem is merely one of line breaeks: the problem section was
intended as a single line of code separated by the underscore line break
character.

Replace the problem lines by copymg and pasting the following:

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


---
Regards,
Norman

I update the site
http://www.rondebruin.nl/mail/folder3/mail4.htm

Hi Corey

I changed the function and macro example



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



"Corey" wrote in message ...
Thanks You Norman and Ron.
Your last post corrected the syntax error i was getting.

Macro worked exactly as required.

Thanks for your help, appreciate it very much.

I can now move another post i need to fix.

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

I tried the code you posted below but get an error as below:

<---------------- I GET A SYNTAX ERROR HERE....... ?
"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 <------------------ TO HERE


The suggsted code works for me.

Your problem is merely one of line breaeks: the problem section was intended as a single line of code separated by the underscore
line break character.

Replace the problem lines by copymg and pasting the following:

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


---
Regards,
Norman

Ron,
Just found i am getting an error now due to the Sheet name.

What i need to reference is NOT BY Sheet NAME but by Active Sheet then Range
in that Sheet, SEE Comments Below with Arrows.

Sub Macro3()
' 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("B45:J107")
<=============== Want to Replace ("Sheet1") with ActiveSheet +
Range("B45:J107")
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 <=====
ActiveSheet + Range instead of ("Sheet1")
.CC = ThisWorkbook.Sheets("Sheet1").Range("E53").Value <====
ActiveSheet + Range instead of ("Sheet1")
.BCC = ""
.Subject = ThisWorkbook.Sheets("Sheet1").Range("B55").Value
<====ActiveSheet + Range instead of ("Sheet1")
.HTMLBody = RangetoHTML(source)
.Display 'or use .Send
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

Corey....

Hi Corey,

Ron,
Just found i am getting an error now due to the Sheet name.

What i need to reference is NOT BY Sheet NAME but by Active Sheet then
Range in that Sheet,
SEE Comments Below with Arrows.

If you follow Ron's link you will see that he has responded to your need to
mail a specified range in the body of an outlook email.

More specifically, Ron has today posted a revised procedure and an updated,
more flexible RangetoHTML function.

If you plug your specific data into Ron's new code, you will obtain the
following:

'=============
Public Sub Mail_Selection_Outlook_Body()
Dim sh As Worksheet
Dim rng As Range
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem

Set sh = ActiveSheet
Set rng = sh.Range("B45:J107")

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = sh.Range("B53").Value
.CC = sh.Range("E53").Value
.BCC = ""
.Subject = sh.Range("B55").Value
.HTMLBody = RangetoHTML(sh, rng)
.Display 'or use .Send
End With
Set OutMail = Nothing
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub

'---------------------

Public Function RangetoHTML(sh As Worksheet, rng As Range)
'Changed by Ron de Bruin 25-June-2006
' You can't use this function in Excel 97
Dim TempFile As String
Dim Nwb As Workbook
Dim fso As Object
Dim ts As Object

sh.Copy
Set Nwb = ActiveWorkbook

With Nwb.Sheets(1)
On Error Resume Next
.DrawingObjects.Visible = True
.DrawingObjects.Delete
On Error GoTo 0
End With

TempFile = Environ$("temp") & "/" & _
Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

With Nwb.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=sh.Name, _
source:=rng.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With

Nwb.Close False

Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close

Set ts = Nothing
Set fso = Nothing
Set Nwb = Nothing
Kill TempFile
End Function
'<<=============


Note that the above code should replace your problematic code.

Clearly, if the code works for you, you should thank Ron.


---
Regards,
Norman

Thanks Norman for the feedback and Ron for the updated code.
It works precisely as required now.
Although as i perfect the ultimate goal i have, i keep finding problems.

When i create an new sheet and click on the email button, i get the sheet
and the relative data in the body of the email, but the LOGO on the sheet
does not show.

Is there a way to include a jpg logo ?

Corey....




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

Ron,
Just found i am getting an error now due to the Sheet name.

What i need to reference is NOT BY Sheet NAME but by Active Sheet then
Range in that Sheet,
SEE Comments Below with Arrows.

If you follow Ron's link you will see that he has responded to your need
to mail a specified range in the body of an outlook email.

More specifically, Ron has today posted a revised procedure and an
updated, more flexible RangetoHTML function.

If you plug your specific data into Ron's new code, you will obtain the
following:

'=============
Public Sub Mail_Selection_Outlook_Body()
Dim sh As Worksheet
Dim rng As Range
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem

Set sh = ActiveSheet
Set rng = sh.Range("B45:J107")

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = sh.Range("B53").Value
.CC = sh.Range("E53").Value
.BCC = ""
.Subject = sh.Range("B55").Value
.HTMLBody = RangetoHTML(sh, rng)
.Display 'or use .Send
End With
Set OutMail = Nothing
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub

'---------------------

Public Function RangetoHTML(sh As Worksheet, rng As Range)
'Changed by Ron de Bruin 25-June-2006
' You can't use this function in Excel 97
Dim TempFile As String
Dim Nwb As Workbook
Dim fso As Object
Dim ts As Object

sh.Copy
Set Nwb = ActiveWorkbook

With Nwb.Sheets(1)
On Error Resume Next
.DrawingObjects.Visible = True
.DrawingObjects.Delete
On Error GoTo 0
End With

TempFile = Environ$("temp") & "/" & _
Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

With Nwb.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=sh.Name, _
source:=rng.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With

Nwb.Close False

Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close

Set ts = Nothing
Set fso = Nothing
Set Nwb = Nothing
Kill TempFile
End Function
'<<=============


Note that the above code should replace your problematic code.

Clearly, if the code works for you, you should thank Ron.


---
Regards,
Norman

Thought i would mention, the LOGO (jpg) is actually in the Customer Header.

Corey,,,,


"Corey" wrote in message
...
Thanks Norman for the feedback and Ron for the updated code.
It works precisely as required now.
Although as i perfect the ultimate goal i have, i keep finding problems.

When i create an new sheet and click on the email button, i get the sheet
and the relative data in the body of the email, but the LOGO on the sheet
does not show.

Is there a way to include a jpg logo ?

Corey....




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

Ron,
Just found i am getting an error now due to the Sheet name.

What i need to reference is NOT BY Sheet NAME but by Active Sheet then
Range in that Sheet,
SEE Comments Below with Arrows.

If you follow Ron's link you will see that he has responded to your need
to mail a specified range in the body of an outlook email.

More specifically, Ron has today posted a revised procedure and an
updated, more flexible RangetoHTML function.

If you plug your specific data into Ron's new code, you will obtain the
following:

'=============
Public Sub Mail_Selection_Outlook_Body()
Dim sh As Worksheet
Dim rng As Range
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem

Set sh = ActiveSheet
Set rng = sh.Range("B45:J107")

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = sh.Range("B53").Value
.CC = sh.Range("E53").Value
.BCC = ""
.Subject = sh.Range("B55").Value
.HTMLBody = RangetoHTML(sh, rng)
.Display 'or use .Send
End With
Set OutMail = Nothing
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub

'---------------------

Public Function RangetoHTML(sh As Worksheet, rng As Range)
'Changed by Ron de Bruin 25-June-2006
' You can't use this function in Excel 97
Dim TempFile As String
Dim Nwb As Workbook
Dim fso As Object
Dim ts As Object

sh.Copy
Set Nwb = ActiveWorkbook

With Nwb.Sheets(1)
On Error Resume Next
.DrawingObjects.Visible = True
.DrawingObjects.Delete
On Error GoTo 0
End With

TempFile = Environ$("temp") & "/" & _
Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

With Nwb.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=sh.Name, _
source:=rng.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With

Nwb.Close False

Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close

Set ts = Nothing
Set fso = Nothing
Set Nwb = Nothing
Kill TempFile
End Function
'<<=============


Note that the above code should replace your problematic code.

Clearly, if the code works for you, you should thank Ron.


---
Regards,
Norman

The code delete all shapes before it make html Corey.
It is not possible to send shapes in the body with my code.

Look at the KB on top if my webpage
If you use 2002/2003 you can use the code in the KB


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



"Corey" wrote in message ...
Thought i would mention, the LOGO (jpg) is actually in the Customer Header.

Corey,,,,


"Corey" wrote in message ...
Thanks Norman for the feedback and Ron for the updated code.
It works precisely as required now.
Although as i perfect the ultimate goal i have, i keep finding problems.

When i create an new sheet and click on the email button, i get the sheet and the relative data in the body of the email, but the
LOGO on the sheet does not show.

Is there a way to include a jpg logo ?

Corey....




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

Ron,
Just found i am getting an error now due to the Sheet name.

What i need to reference is NOT BY Sheet NAME but by Active Sheet then Range in that Sheet,
SEE Comments Below with Arrows.

If you follow Ron's link you will see that he has responded to your need to mail a specified range in the body of an outlook
email.

More specifically, Ron has today posted a revised procedure and an updated, more flexible RangetoHTML function.

If you plug your specific data into Ron's new code, you will obtain the following:

'=============
Public Sub Mail_Selection_Outlook_Body()
Dim sh As Worksheet
Dim rng As Range
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem

Set sh = ActiveSheet
Set rng = sh.Range("B45:J107")

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = sh.Range("B53").Value
.CC = sh.Range("E53").Value
.BCC = ""
.Subject = sh.Range("B55").Value
.HTMLBody = RangetoHTML(sh, rng)
.Display 'or use .Send
End With
Set OutMail = Nothing
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub

'---------------------

Public Function RangetoHTML(sh As Worksheet, rng As Range)
'Changed by Ron de Bruin 25-June-2006
' You can't use this function in Excel 97
Dim TempFile As String
Dim Nwb As Workbook
Dim fso As Object
Dim ts As Object

sh.Copy
Set Nwb = ActiveWorkbook

With Nwb.Sheets(1)
On Error Resume Next
.DrawingObjects.Visible = True
.DrawingObjects.Delete
On Error GoTo 0
End With

TempFile = Environ$("temp") & "/" & _
Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

With Nwb.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=sh.Name, _
source:=rng.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With

Nwb.Close False

Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close

Set ts = Nothing
Set fso = Nothing
Set Nwb = Nothing
Kill TempFile
End Function
'<<=============


Note that the above code should replace your problematic code.

Clearly, if the code works for you, you should thank Ron.


---
Regards,
Norman

Ron,
Thanks for the reply, AGAIN.
Sorry to be persistantly questioning.

I am running Office 2002, and the MS code did the trick to include my Logo.

It somewhat operates differently to your code as it diplays ALL the active
sheet in the display email prompt, BUT ONLY emails the selected RANGE.

Where your code ONLY displayed the actual emailed RANGES.
I prefered your code but it wont include the logo.

It is a little scary sending the email with the MS code as the 1st page of
the active sheet has COSTING information that is not for the recipients
eyes, but they ONLY recieve the page 2 Quote page, as BOTH pages are viewed
before sending, but ONLY the page 2 Quote is sent.

Thanks for your asistance with this task.

Cheers

Corey....

Hi Corey

has COSTING information
Copy this on a diferent sheet.

You can send the whole sheet then in the body or as a attachment
http://www.rondebruin.nl/mail/folder2/mail2.htm


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



"Corey" wrote in message ...
Ron,
Thanks for the reply, AGAIN.
Sorry to be persistantly questioning.

I am running Office 2002, and the MS code did the trick to include my Logo.

It somewhat operates differently to your code as it diplays ALL the active sheet in the display email prompt, BUT ONLY emails the
selected RANGE.

Where your code ONLY displayed the actual emailed RANGES.
I prefered your code but it wont include the logo.

It is a little scary sending the email with the MS code as the 1st page of the active sheet has COSTING information that is not
for the recipients eyes, but they ONLY recieve the page 2 Quote page, as BOTH pages are viewed before sending, but ONLY the page 2
Quote is sent.

Thanks for your asistance with this task.

Cheers

Corey....