create HTML from a range

[Martin]

Hello,

I am trying to send a Mail with a HTML body created from a range. Using the
following code:

Sub test()

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

rngesend = Selection

With OutMail

.To = ""
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.HTMLBody =
ActiveWorkbook.PublishObjects.Add(SourceType:=xlSourceRange,
Filename:="C:\tempsht.htm", Sheet:=rngesend.Parent.Name,
Source:=rngesend.Address, HtmlType:=xlHtmlStatic)
.Display
End With

End Sub


I get an error pop-up at the line where I define the HTML body. Can somebody
help on this?

Many Thanks
Martin

 

[Joel]

from
rngesend = Selection
to
set rngesend = activesheet.Selection

 

[Martin]

thak you but this does not work either

from
rngesend = Selection
to
set rngesend = activesheet.Selection

 

[Jacob Skaria]

Try

Dim rngesend As Range
Set rngesend = Selection

You havent mentioned what error? Type mismatch OR what ??

If this post helps click Yes

 

[Martin]

Hi,

yes, also tried that, not working.

excatly, I get the error 13, type mismatch.

tx

 

[Joel]

There were a few things wrong. the published object creates a file but you
need to open the file to add it to the document.

Sub test1()

Const ForReading = 1, ForWriting = 2, ForAppending = 3

FName = "C:\tempsht.htm"

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

Set rngesend = Sheets("Sheet2").Range("A1").CurrentRegion

Set obj = ActiveWorkbook.PublishObjects
With obj.Add(SourceType:=xlSourceRange, _
Filename:=FName, _
Sheet:=rngesend.Parent.Name, _
Source:=rngesend.Address, _
HtmlType:=xlHtmlStatic)

.Publish (True)
End With

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile(Filename:=FName, _
iomode:=ForReading)


With OutMail

.To = ""
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.HTMLBody = f.readall
.Display
End With

f.Close
End Sub

 

[Ron de Bruin]

Hi Martin

See
http://www.rondebruin.nl/mail/folder3/mail4.htm

 

[r]

try ...

....
..HTMLBody = RangeInHtml (Selection)
....

Function RangeInHtml(rng As Range) As String
Dim fso As Object
Dim ts As Object
Dim TempFile As String
Dim TempWB As Workbook
Const ForReading As Long = 1
'Funzione a cui viene passato un range
'restituisce una stringa html che visualizza
'il range
'utilizzabile per creare il testo di messaggi
'di posta elettronica

'Creo un nome di file temporaneo
TempFile = Environ$("temp") & "/" & _
Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

'recupero la cartella del Range passato alla
'funzione
Set TempWB = rng.Parent.Parent

'salvo la cartella come pagina web
With TempWB.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=rng.Parent.Name, _
Source:=rng.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With

'setto FSO
Set fso = CreateObject("Scripting.FileSystemObject")

'recupero il file in lettura
Set ts = fso.GetFile(TempFile). _
OpenAsTextStream(ForReading)

'lo leggo tutto impostando il risultato della funzione
RangeInHtml = ts.ReadAll

'chiudo il file
ts.Close

'di defoult il testo viene disposto al centro
'... meglio a sinistra
RangeInHtml = Replace(RangeInHtml, _
"align=center xublishsource=", _
"align=left xublishsource=")

'cancello la pagina web creata in precedenza
Kill TempFile

End Function

regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html

 

[r]

hello Ron

I wrote my answer without having first read your ... I discover with
pleasure that RangeInHtml has a father ... I had read here:

http://groups.google.it/group/micro...=it&lnk=gst&q=PublishObjects#88c03900ee32733b

and I did not know then who was the author
.... can also just update my article:

http://excelvba.altervista.org/blog...e-XPS-una-alternativa-ai-PDF-Prima-parte.html

RangeInHtml adding to the author's name
Meanwhile I am sorry even if I have acted in good faith.
regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html

 

[Ron de Bruin]

No problem r

Have a nice day

--

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