Hello-

I have used Ron De Bruins code for some time to automate the
distribution of my department statistics. The problem I face is that I
need to have the range pasted into Outlook as plain text instead of
HTML because several people receive the updates on Blackberries.
Although the Blackberries are set to receive HTML images in the
options menus, the updates still open with a blank screen and you have
to select a menu option on the Blackberry called "Get Plain Text" in
order to view the update.

Is there a way to modify The RangetoHTML code so that it pastes
inttothe Outlook message as plain "unformatted" text before sending
the email? Any help with this would be greatly appreciated.

Ron's Code:

Function RangetoHTML(rng As Range)
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2007
Dim fso As Object
Dim ts As Object
Dim TempFile As String
Dim TempWB As Workbook

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

'Copy the range and create a new workbook to past the data in
rng.Copy
Set TempWB = Workbooks.Add(1)
With TempWB.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Cells(1).PasteSpecial xlPasteFormats, , False, False
.Cells(1).Select
Application.CutCopyMode = False
On Error Resume Next
.DrawingObjects.Visible = True
.DrawingObjects.Delete
On Error GoTo 0
End With

'Publish the sheet to a htm file
With TempWB.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=TempWB.Sheets(1).Name, _
Source:=TempWB.Sheets(1).UsedRange.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With

'Read all data from the htm file into RangetoHTML
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
RangetoHTML = Replace(RangetoHTML, "align=center
x:publishsource=", _
"align=left x:publishsource=")

'Close TempWB
TempWB.Close SaveChanges:=False

'Delete the htm file we used in this function
Kill TempFile

Set ts = Nothing
Set fso = Nothing
Set TempWB = Nothing
End Function

Hi Sabosis

This function create html and use htmlbody

You can create a body message
How many cells do you want in the body of the mail

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Sabosis" wrote in message ...
Hello-

I have used Ron De Bruins code for some time to automate the
distribution of my department statistics. The problem I face is that I
need to have the range pasted into Outlook as plain text instead of
HTML because several people receive the updates on Blackberries.
Although the Blackberries are set to receive HTML images in the
options menus, the updates still open with a blank screen and you have
to select a menu option on the Blackberry called "Get Plain Text" in
order to view the update.

Is there a way to modify The RangetoHTML code so that it pastes
inttothe Outlook message as plain "unformatted" text before sending
the email? Any help with this would be greatly appreciated.

Ron's Code:

Function RangetoHTML(rng As Range)
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2007
Dim fso As Object
Dim ts As Object
Dim TempFile As String
Dim TempWB As Workbook

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

'Copy the range and create a new workbook to past the data in
rng.Copy
Set TempWB = Workbooks.Add(1)
With TempWB.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Cells(1).PasteSpecial xlPasteFormats, , False, False
.Cells(1).Select
Application.CutCopyMode = False
On Error Resume Next
.DrawingObjects.Visible = True
.DrawingObjects.Delete
On Error GoTo 0
End With

'Publish the sheet to a htm file
With TempWB.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=TempWB.Sheets(1).Name, _
Source:=TempWB.Sheets(1).UsedRange.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With

'Read all data from the htm file into RangetoHTML
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
RangetoHTML = Replace(RangetoHTML, "align=center
x:publishsource=", _
"align=left x:publishsource=")

'Close TempWB
TempWB.Close SaveChanges:=False

'Delete the htm file we used in this function
Kill TempFile

Set ts = Nothing
Set fso = Nothing
Set TempWB = Nothing
End Function

On Dec 15, 8:52*am, "Ron de Bruin" wrote:
Hi Sabosis

This function create html and use htmlbody

You can create a body message
How many cells do you want in the body of the mail

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm



"Sabosis" wrote in ...
Hello-

I have used Ron De Bruins code for some time to automate the
distribution of my department statistics. The problem I face is that I
need to have the range pasted into Outlook as plain text instead of
HTML because several people receive the updates on Blackberries.
Although the Blackberries are set to receive HTML images in the
options menus, the updates still open with a blank screen and you have
to select a menu option on the Blackberry called "Get Plain Text" in
order to view the update.

Is there a way to modify The RangetoHTML code so that it pastes
inttothe Outlook message as plain "unformatted" text before sending
the email? Any help with this would be greatly appreciated.

Ron's Code:

Function RangetoHTML(rng As Range)
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2007
* *Dim fso As Object
* *Dim ts As Object
* *Dim TempFile As String
* *Dim TempWB As Workbook

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

* *'Copy the range and create a new workbook to past the data in
* *rng.Copy
* *Set TempWB = Workbooks.Add(1)
* *With TempWB.Sheets(1)
* * * *.Cells(1).PasteSpecial Paste:=8
* * * *.Cells(1).PasteSpecial xlPasteValues, , False, False
* * * *.Cells(1).PasteSpecial xlPasteFormats, , False, False
* * * *.Cells(1).Select
* * * *Application.CutCopyMode = False
* * * *On Error Resume Next
* * * *.DrawingObjects.Visible = True
* * * *.DrawingObjects.Delete
* * * *On Error GoTo 0
* *End With

* *'Publish the sheet to a htm file
* *With TempWB.PublishObjects.Add( _
* * * * SourceType:=xlSourceRange, _
* * * * Filename:=TempFile, _
* * * * Sheet:=TempWB.Sheets(1).Name, _
* * * * Source:=TempWB.Sheets(1).UsedRange.Address, _
* * * * HtmlType:=xlHtmlStatic)
* * * *.Publish (True)
* *End With

* *'Read all data from the htm file into RangetoHTML
* *Set fso = CreateObject("Scripting.FileSystemObject")
* *Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
* *RangetoHTML = ts.ReadAll
* *ts.Close
* *RangetoHTML = Replace(RangetoHTML, "align=center
x:publishsource=", _
* * * * * * * * * * * * *"align=left x:publishsource=")

* *'Close TempWB
* *TempWB.Close SaveChanges:=False

* *'Delete the htm file we used in this function
* *Kill TempFile

* *Set ts = Nothing
* *Set fso = Nothing
* *Set TempWB = Nothing
End Function- Hide quoted text -

- Show quoted text -

Ron-

It is a small amount, roughly A1:D8

Thanks for getting back to me.

I reply to you private

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Sabosis" wrote in message ...
On Dec 15, 8:52 am, "Ron de Bruin" wrote:
Hi Sabosis

This function create html and use htmlbody

You can create a body message
How many cells do you want in the body of the mail

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm



"Sabosis" wrote in ...
Hello-

I have used Ron De Bruins code for some time to automate the
distribution of my department statistics. The problem I face is that I
need to have the range pasted into Outlook as plain text instead of
HTML because several people receive the updates on Blackberries.
Although the Blackberries are set to receive HTML images in the
options menus, the updates still open with a blank screen and you have
to select a menu option on the Blackberry called "Get Plain Text" in
order to view the update.

Is there a way to modify The RangetoHTML code so that it pastes
inttothe Outlook message as plain "unformatted" text before sending
the email? Any help with this would be greatly appreciated.

Ron's Code:

Function RangetoHTML(rng As Range)
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2007
Dim fso As Object
Dim ts As Object
Dim TempFile As String
Dim TempWB As Workbook

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

'Copy the range and create a new workbook to past the data in
rng.Copy
Set TempWB = Workbooks.Add(1)
With TempWB.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Cells(1).PasteSpecial xlPasteFormats, , False, False
.Cells(1).Select
Application.CutCopyMode = False
On Error Resume Next
.DrawingObjects.Visible = True
.DrawingObjects.Delete
On Error GoTo 0
End With

'Publish the sheet to a htm file
With TempWB.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=TempWB.Sheets(1).Name, _
Source:=TempWB.Sheets(1).UsedRange.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With

'Read all data from the htm file into RangetoHTML
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
RangetoHTML = Replace(RangetoHTML, "align=center
x:publishsource=", _
"align=left x:publishsource=")

'Close TempWB
TempWB.Close SaveChanges:=False

'Delete the htm file we used in this function
Kill TempFile

Set ts = Nothing
Set fso = Nothing
Set TempWB = Nothing
End Function- Hide quoted text -

- Show quoted text -

Ron-

It is a small amount, roughly A1:D8

Thanks for getting back to me.

Ron - how come u replied in private ;) I wanted to know the answer too.
The problem i am running into is viewing the HTML in Blackberry. I have a
Blackberry Bold 9000, HTML is enabled. I see other HTML files just fine, but
whatever i send using RangetoHTML shows up as blank page on blackberry.
Ideally, i would want to be able to view this in HTML, but that might be a
question for blackberry speacialists - i will take this up with my company
techies. As a workaround i would like to send the range as plain text, or
rich text. Is there a way to do that? Appreciate your help.

"Ron de Bruin" wrote:

I reply to you private

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Sabosis" wrote in message ...
On Dec 15, 8:52 am, "Ron de Bruin" wrote:
Hi Sabosis

This function create html and use htmlbody

You can create a body message
How many cells do you want in the body of the mail

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm



"Sabosis" wrote in ...
Hello-

I have used Ron De Bruins code for some time to automate the
distribution of my department statistics. The problem I face is that I
need to have the range pasted into Outlook as plain text instead of
HTML because several people receive the updates on Blackberries.
Although the Blackberries are set to receive HTML images in the
options menus, the updates still open with a blank screen and you have
to select a menu option on the Blackberry called "Get Plain Text" in
order to view the update.

Is there a way to modify The RangetoHTML code so that it pastes
inttothe Outlook message as plain "unformatted" text before sending
the email? Any help with this would be greatly appreciated.

Ron's Code:

Function RangetoHTML(rng As Range)
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2007
Dim fso As Object
Dim ts As Object
Dim TempFile As String
Dim TempWB As Workbook

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

'Copy the range and create a new workbook to past the data in
rng.Copy
Set TempWB = Workbooks.Add(1)
With TempWB.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Cells(1).PasteSpecial xlPasteFormats, , False, False
.Cells(1).Select
Application.CutCopyMode = False
On Error Resume Next
.DrawingObjects.Visible = True
.DrawingObjects.Delete
On Error GoTo 0
End With

'Publish the sheet to a htm file
With TempWB.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=TempWB.Sheets(1).Name, _
Source:=TempWB.Sheets(1).UsedRange.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With

'Read all data from the htm file into RangetoHTML
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
RangetoHTML = Replace(RangetoHTML, "align=center
x:publishsource=", _
"align=left x:publishsource=")

'Close TempWB
TempWB.Close SaveChanges:=False

'Delete the htm file we used in this function
Kill TempFile

Set ts = Nothing
Set fso = Nothing
Set TempWB = Nothing
End Function- Hide quoted text -

- Show quoted text -

Ron-

It is a small amount, roughly A1:D8

Thanks for getting back to me.
.

My brother in law have also a Blackberry so I put it on my list of things to do and
test it on his phone. I reply when I have test it

You can set the format also in the code but I not know if this wil work


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"justas" wrote in message ...
Ron - how come u replied in private ;) I wanted to know the answer too.
The problem i am running into is viewing the HTML in Blackberry. I have a
Blackberry Bold 9000, HTML is enabled. I see other HTML files just fine, but
whatever i send using RangetoHTML shows up as blank page on blackberry.
Ideally, i would want to be able to view this in HTML, but that might be a
question for blackberry speacialists - i will take this up with my company
techies. As a workaround i would like to send the range as plain text, or
rich text. Is there a way to do that? Appreciate your help.

"Ron de Bruin" wrote:

I reply to you private

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Sabosis" wrote in message ...
On Dec 15, 8:52 am, "Ron de Bruin" wrote:
Hi Sabosis

This function create html and use htmlbody

You can create a body message
How many cells do you want in the body of the mail

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm



"Sabosis" wrote in
...
Hello-

I have used Ron De Bruins code for some time to automate the
distribution of my department statistics. The problem I face is that I
need to have the range pasted into Outlook as plain text instead of
HTML because several people receive the updates on Blackberries.
Although the Blackberries are set to receive HTML images in the
options menus, the updates still open with a blank screen and you have
to select a menu option on the Blackberry called "Get Plain Text" in
order to view the update.

Is there a way to modify The RangetoHTML code so that it pastes
inttothe Outlook message as plain "unformatted" text before sending
the email? Any help with this would be greatly appreciated.

Ron's Code:

Function RangetoHTML(rng As Range)
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2007
Dim fso As Object
Dim ts As Object
Dim TempFile As String
Dim TempWB As Workbook

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

'Copy the range and create a new workbook to past the data in
rng.Copy
Set TempWB = Workbooks.Add(1)
With TempWB.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Cells(1).PasteSpecial xlPasteFormats, , False, False
.Cells(1).Select
Application.CutCopyMode = False
On Error Resume Next
.DrawingObjects.Visible = True
.DrawingObjects.Delete
On Error GoTo 0
End With

'Publish the sheet to a htm file
With TempWB.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=TempWB.Sheets(1).Name, _
Source:=TempWB.Sheets(1).UsedRange.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With

'Read all data from the htm file into RangetoHTML
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
RangetoHTML = Replace(RangetoHTML, "align=center
x:publishsource=", _
"align=left x:publishsource=")

'Close TempWB
TempWB.Close SaveChanges:=False

'Delete the htm file we used in this function
Kill TempFile

Set ts = Nothing
Set fso = Nothing
Set TempWB = Nothing
End Function- Hide quoted text -

- Show quoted text -

Ron-

It is a small amount, roughly A1:D8

Thanks for getting back to me.
.