e-mail range and controlling font size and style

[LaRana!]

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

 

[Ron de Bruin]

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

 

[LaRana!]

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 xublishsource=", _
"align=left xublishsource=")

'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]

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

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 xublishsource=", _
"align=left xublishsource=")

'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

 

[LaRana!]

Thanks Ron. I will test a little more. Nevertheless, your vba code is
perfect for what I need.