Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Sending ONLY Range or Page x, BUT not entire Activesheet, How ?
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default Sending ONLY Range or Page x, BUT not entire Activesheet, How ?
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....













Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 172
Default Sending ONLY Range or Page x, BUT not entire Activesheet, How ?
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....















Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default Sending ONLY Range or Page x, BUT not entire Activesheet, How ?
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


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 172
Default Sending ONLY Range or Page x, BUT not entire Activesheet, How ?
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



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,123
Default Sending ONLY Range or Page x, BUT not entire Activesheet, How ?
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





Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 276
Default Sending ONLY Range or Page x, BUT not entire Activesheet, How ?


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


Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default Sending ONLY Range or Page x, BUT not entire Activesheet, How ?
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


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to delete entire page 2 so document shows as 1 page only Elinita New Users to Excel 1 November 22nd 09 06:35 PM
Sending an entire worksheet [email protected] Excel Discussion (Misc queries) 3 November 23rd 06 03:01 PM
Resetting the activesheet used range Mike Fogleman Excel Programming 0 September 8th 04 01:02 PM
Resetting the activesheet used range Norman Jones Excel Programming 1 September 8th 04 11:57 AM
ActiveSheet.Printout or Page Setup? Michelle Excel Programming 0 April 23rd 04 03:55 PM


All times are GMT +1. The time now is 05:16 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement