| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
e-mail range and controlling font size and style
I am using Ron's code to send several sections of my Wksheet. Works well,
except that I would like to control the font size in which each section is embedded in the body of the e-mail. Is there a way to control this? http://www.rondebruin.nl/mail/folder2/mail4.htm -- EddieV |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
e-mail range and controlling font size and style
Hi LaRana
If you want to send it in the body see http://www.rondebruin.nl/mail/folder3/mail2.htm http://www.rondebruin.nl/mail/folder3/mail4.htm Or http://www.rondebruin.nl/mail/folder3/mailenvelope.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "LaRana!" wrote in message ... I am using Ron's code to send several sections of my Wksheet. Works well, except that I would like to control the font size in which each section is embedded in the body of the e-mail. Is there a way to control this? http://www.rondebruin.nl/mail/folder2/mail4.htm -- EddieV |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
e-mail range and controlling font size and style
Thanks, Ron. The code works when attempting to send my Wksheet range in the
body of the e-mail but I was wondering if there was any other way to control the font size and really the entire new "drawing or object" size been created and pasted onto the body of the email. I changed the font size within my worksheet and that helped, but the row and column size remain a bit too wide... I guess the function that has be modified would be the RANGEtoHTML function... 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 EddieV "Ron de Bruin" wrote: Hi LaRana If you want to send it in the body see http://www.rondebruin.nl/mail/folder3/mail2.htm http://www.rondebruin.nl/mail/folder3/mail4.htm Or http://www.rondebruin.nl/mail/folder3/mailenvelope.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "LaRana!" wrote in message ... I am using Ron's code to send several sections of my Wksheet. Works well, except that I would like to control the font size in which each section is embedded in the body of the e-mail. Is there a way to control this? http://www.rondebruin.nl/mail/folder2/mail4.htm -- EddieV |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
e-mail range and controlling font size and style
Hi LaRana
In the function you have no option to change things I am not sure what differents you see Try this example and see if this work better for you http://www.rondebruin.nl/mail/folder3/mailenvelope.htm If not mail me your workbook private and tell me what you expect to see -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "LaRana!" wrote in message ... Thanks, Ron. The code works when attempting to send my Wksheet range in the body of the e-mail but I was wondering if there was any other way to control the font size and really the entire new "drawing or object" size been created and pasted onto the body of the email. I changed the font size within my worksheet and that helped, but the row and column size remain a bit too wide... I guess the function that has be modified would be the RANGEtoHTML function... 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 EddieV "Ron de Bruin" wrote: Hi LaRana If you want to send it in the body see http://www.rondebruin.nl/mail/folder3/mail2.htm http://www.rondebruin.nl/mail/folder3/mail4.htm Or http://www.rondebruin.nl/mail/folder3/mailenvelope.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "LaRana!" wrote in message ... I am using Ron's code to send several sections of my Wksheet. Works well, except that I would like to control the font size in which each section is embedded in the body of the e-mail. Is there a way to control this? http://www.rondebruin.nl/mail/folder2/mail4.htm -- EddieV |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
e-mail range and controlling font size and style
Thanks Ron. I will test a little more. Nevertheless, your vba code is
perfect for what I need. -- EddieV "Ron de Bruin" wrote: Hi LaRana In the function you have no option to change things I am not sure what differents you see Try this example and see if this work better for you http://www.rondebruin.nl/mail/folder3/mailenvelope.htm If not mail me your workbook private and tell me what you expect to see -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "LaRana!" wrote in message ... Thanks, Ron. The code works when attempting to send my Wksheet range in the body of the e-mail but I was wondering if there was any other way to control the font size and really the entire new "drawing or object" size been created and pasted onto the body of the email. I changed the font size within my worksheet and that helped, but the row and column size remain a bit too wide... I guess the function that has be modified would be the RANGEtoHTML function... 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 EddieV "Ron de Bruin" wrote: Hi LaRana If you want to send it in the body see http://www.rondebruin.nl/mail/folder3/mail2.htm http://www.rondebruin.nl/mail/folder3/mail4.htm Or http://www.rondebruin.nl/mail/folder3/mailenvelope.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "LaRana!" wrote in message ... I am using Ron's code to send several sections of my Wksheet. Works well, except that I would like to control the font size in which each section is embedded in the body of the e-mail. Is there a way to control this? http://www.rondebruin.nl/mail/folder2/mail4.htm -- EddieV |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Changing font style/size/color/etc for multiple series in a chart? | Charts and Charting in Excel | |||
| Form CheckBox font size / style | Excel Discussion (Misc queries) | |||
| HOW DO I CHANGE THE FONT SIZE FOR A RANGE NAME? | Excel Discussion (Misc queries) | |||
| How to write vba to evaluate the text font and size on an active cell range? | Excel Programming | |||
| It auto changes font size on e-mail entries, WHY? | Excel Discussion (Misc queries) | |||
Linear Mode
