Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
RangeToHTML as plain text in Outlook
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sending mail from Outlook as plain text with VBA | Excel Programming | |||
Hyperlinks in plain text? | Excel Discussion (Misc queries) | |||
Copying Data from Excel to MS Outlook in Plain Text Mode | Excel Worksheet Functions | |||
Hyperlink from plain text. | Excel Discussion (Misc queries) | |||
Creating "Plain Text" mail message in Outlook from Excel VBA | Excel Programming |