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

I not forget you
I hope I see him this weekend so we can test a few things

--

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


"Ron de Bruin" wrote in message ...
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.
.

You know I had this same problem, and I needed to do it quick and dirty. So I re-used one of Ron's other solutions to save the current worksheet as a text file and attach it to the email. Instead of attaching the file I simply read it out through a file streem and then returned that as the body of the email. Like I said I was looking for a quick solution so this is probably not the best but does work very well. I an still trying to figure out how to set the MIME type so I can include the html and text version of the email, but as far as I can figure out so far you cannot set the an alternative view when creating an email outlook. You can only set the body type (html, rtf or text).

Anyway here is the code, looking forward to Ron's crack at this.
in the Mail_Sheet_Outlook_Body code simply change:
..HTMLBody = RangetoHTML(rng) to
..Body = RangeToText()

Function RangeToText()
'Working in 97-2010
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim I As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set Sourcewb = ActiveWorkbook

'Copy the sheet to a new workbook
ActiveSheet.Copy
Set Destwb = ActiveWorkbook

'Determine the Excel version and file extension/format
With Destwb
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007-2010, we exit the sub when your answer is
'NO in the security dialog that you only see when you copy
'an sheet from a xlsm file with macro's disabled.
If Sourcewb.Name = .Name Then
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
MsgBox "Your answer is NO in the security dialog"
Exit Function
Else
'Select Case Sourcewb.FileFormat
'Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
'Case 52:
' If .HasVBProject Then
' FileExtStr = ".xlsm": FileFormatNum = 52
' Else
' FileExtStr = ".xlsx": FileFormatNum = 51
' End If
'Case 56: FileExtStr = ".xls": FileFormatNum = 56
'Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
'End Select
FileExtStr = ".txt": FileFormatNum = -4158
End If
End If
End With

' 'Change all cells in the worksheet to values if you want
' With Destwb.Sheets(1).UsedRange
' .Cells.Copy
' .Cells.PasteSpecial xlPasteValues
' .Cells(1).Select
' End With
' Application.CutCopyMode = False

'Save the new workbook/Mail it/Delete it
TempFilePath = Environ$("temp") & "\"
TempFileName = "Part of " & Sourcewb.Name & " " _
& Format(Now, "dd-mmm-yy h-mm-ss")

With Destwb
.SaveAs TempFilePath & TempFileName & FileExtStr, _
FileFormat:=FileFormatNum
On Error Resume Next
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFilePath & TempFileName & FileExtStr).OpenAsTextStream(1, -2)
RangeToText = ts.ReadAll
ts.Close
On Error GoTo 0
.Close SaveChanges:=False
End With

'Delete the file you have send
Kill TempFilePath & TempFileName & FileExtStr

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Function




On Monday, December 14, 2009 10:51 PM Sabosis wrote:

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 Tuesday, December 15, 2009 11: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 Bruin
http://www.rondebruin.nl/tips.htm


On Wednesday, December 16, 2009 1:43 AM Sabosis wrote:

...
-
ishsource=3D")

Ron-

It is a small amount, roughly A1:D8

Thanks for getting back to me.


On Thursday, December 17, 2009 3:39 PM Ron de Bruin wrote:

I reply to you private

--

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



Ron-


On Friday, January 29, 2010 9:15 AM justas wrote:

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:


On Friday, January 29, 2010 4:48 PM Ron de Bruin wrote:

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


On Wednesday, February 03, 2010 12:45 PM Ron de Bruin wrote:

I not forget you
I hope I see him this weekend so we can test a few things

--

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


On Wednesday, June 09, 2010 10:41 AM Lefty Jim wrote:

Hey Ron... Thanks for your awesome work. Did you ever figure this out. I have the same problem with the blackberry's.


Submitted via EggHeadCafe
WCF Generic DataContract object Serializer
http://www.eggheadcafe.com/tutorials...erializer.aspx