RangeToHTML as plain text in Outlook

S

Sabosis

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
 
R

Ron de Bruin

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
 
J

justas

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

Ron de Bruin

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
 
R

Ron de Bruin

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

Richard Fantozzi

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel 2010 Sendmail .Display works, but not .Send --- Please help 2
Used to work now it doesnt 5
Macro sending sheet with not working hyperlink 0
Send mail from excel - Copy/paste unformatted values and error mes 6
CDO and Exchange Server 2007 3
Send email from excel with image 2
Send mail from excel - Copy/paste unformatted values and error 5
email code 1

Top