Sending ONLY Range or Page x, BUT not entire Activesheet, How ?

[Corey]

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 Jones]

Hi Corey,

See Ron de Bruin's example code at:

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


---
Regards,
Norman



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

 

[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]

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.

 

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

 

[Ron de Bruin]

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

 

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

 

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

 

[Ron de Bruin]

Please read

 

[Norman Jones]

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("A120")
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

 

[Ron de Bruin]

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

 

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

 

[Corey]

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

 

[Norman Jones]

Hi Corey,

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

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

 

[Corey]

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

 

[Ron de Bruin]

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

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

 

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

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

 

[Norman Jones]

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.

 

[Corey]

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

 

[Corey]

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

Corey,,,,