Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
create HTML from a range
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:=xlSo urceRange, 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
create HTML from a range
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:=xlSo urceRange, 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
create HTML from a range
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:=xlSo urceRange, 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
create HTML from a range
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:=xlSo urceRange, 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
create HTML from a range
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:=xlSo urceRange, 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
create HTML from a range
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:=xlSo urceRange, 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
create HTML from a range
Hi Martin
See http://www.rondebruin.nl/mail/folder3/mail4.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Martin" wrote in message ... 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:=xlSo urceRange, 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
create HTML from a range
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 x:publishsource=", _ "align=left x:publishsource=") '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:=xlSo urceRange, 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
create HTML from a range
hello Ron I wrote my answer without having first read your ... I discover with pleasure that RangeInHtml has a father ... I had read he 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" wrote in message ... 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:=xlSo urceRange, 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
create HTML from a range
No problem r Have a nice day -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "r" wrote in message ... hello Ron I wrote my answer without having first read your ... I discover with pleasure that RangeInHtml has a father ... I had read he 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" wrote in message ... 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:=xlSo urceRange, 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Programmatically create html as input to Excel | Excel Programming | |||
if i cut and paste from html into excel, i cannot create formulas | Excel Discussion (Misc queries) | |||
How do I create a HTML text for my web page? | New Users to Excel | |||
Macro To Create Hyperlink from html text | Excel Programming | |||
Create Excel sheet in HTML with comments | Excel Programming |