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

from
rngesend = Selection
to
set rngesend = activesheet.Selection

"Martin" wrote:

> 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

thak you but this does not work either

"Joel" wrote:

> from
> rngesend = Selection
> to
> set rngesend = activesheet.Selection
>
> "Martin" wrote:
>
> > 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

Try

Dim rngesend As Range
Set rngesend = Selection

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

If this post helps click Yes
---------------
Jacob Skaria


"Martin" wrote:

> 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

Hi,

yes, also tried that, not working.

excatly, I get the error 13, type mismatch.

tx

"Jacob Skaria" wrote:

> Try
>
> Dim rngesend As Range
> Set rngesend = Selection
>
> You havent mentioned what error? Type mismatch OR what ??
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "Martin" wrote:
>
> > 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

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

"Martin" wrote:

> thak you but this does not work either
>
> "Joel" wrote:
>
> > from
> > rngesend = Selection
> > to
> > set rngesend = activesheet.Selection
> >
> > "Martin" wrote:
> >
> > > 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

Hi Martin

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

--

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




"Martin" <(E-Mail Removed)> wrote in message news:598D0F3A-08BB-4B09-973D-(E-Mail Removed)...
> 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

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/...ternative.html


"Martin" wrote:

> 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

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/micros...c03900ee32733b

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

http://excelvba.altervista.org/blog/...ima-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/...ternative.html


"Ron de Bruin" wrote:

> Hi Martin
>
> See
> http://www.rondebruin.nl/mail/folder3/mail4.htm
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
>
>
> "Martin" <(E-Mail Removed)> wrote in message news:598D0F3A-08BB-4B09-973D-(E-Mail Removed)...
> > 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
>

No problem r

Have a nice day

--

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




"r" <(E-Mail Removed)> wrote in message news:9C87115A-7BE8-43A6-B8DE-(E-Mail Removed)...
> 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/micros...c03900ee32733b
>
> and I did not know then who was the author
> ... can also just update my article:
>
> http://excelvba.altervista.org/blog/...ima-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/...ternative.html
>
>
> "Ron de Bruin" wrote:
>
>> Hi Martin
>>
>> See
>> http://www.rondebruin.nl/mail/folder3/mail4.htm
>>
>> --
>>
>> Regards Ron de Bruin
>> http://www.rondebruin.nl/tips.htm
>>
>>
>>
>>
>> "Martin" <(E-Mail Removed)> wrote in message news:598D0F3A-08BB-4B09-973D-(E-Mail Removed)...
>> > 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
>>