Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating e-mail in Excel
Hello,
I have an application where I create an e-mail in Excel based on what team members to play in different hockey games. The body in Outlook is created as a HTML-body. To enhance the application I would like to add text to the body. I have a userform with a textbox where I write the message, which works just fine but with one problem though. The linefeed I have in the textbox are lost when it is passed in to the e-mailbody. Is there any way to get keep the format from the textbox in the e-mail? Best regards, Torbjörn |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating e-mail in Excel
Torbjorn
Try replacing Chr(10) with "<BR" to insert line breaks in the HTML. Here's an example: Private Sub CommandButton1_Click() Dim ol As Outlook.Application Dim mi As Outlook.MailItem Dim stHTML As String Set ol = New Outlook.Application Set mi = ol.CreateItem(olMailItem) stHTML = "<HTML<BODY" stHTML = stHTML & Me.TextBox1.Text stHTML = Replace(stHTML, Chr(10), "<BR") mi.HTMLBody = stHTML mi.Display End Sub -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Torbjörn Steijer" wrote in message ... Hello, I have an application where I create an e-mail in Excel based on what team members to play in different hockey games. The body in Outlook is created as a HTML-body. To enhance the application I would like to add text to the body. I have a userform with a textbox where I write the message, which works just fine but with one problem though. The linefeed I have in the textbox are lost when it is passed in to the e-mailbody. Is there any way to get keep the format from the textbox in the e-mail? Best regards, Torbjörn |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating e-mail in Excel
Thanks a lot! It works just the way I want it. One other prblem to solve,
how do I get the font to be Arial when it is passed into the mailbody? In the textbox it is Arial but Times in the mailbody. Minor problem but it doesn't look as good as I wish! Any good ideas? TIA Torbjörn "Dick Kusleika" skrev i meddelandet ... Torbjorn Try replacing Chr(10) with "<BR" to insert line breaks in the HTML. Here's an example: Private Sub CommandButton1_Click() Dim ol As Outlook.Application Dim mi As Outlook.MailItem Dim stHTML As String Set ol = New Outlook.Application Set mi = ol.CreateItem(olMailItem) stHTML = "<HTML<BODY" stHTML = stHTML & Me.TextBox1.Text stHTML = Replace(stHTML, Chr(10), "<BR") mi.HTMLBody = stHTML mi.Display End Sub -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Torbjörn Steijer" wrote in message ... Hello, I have an application where I create an e-mail in Excel based on what team members to play in different hockey games. The body in Outlook is created as a HTML-body. To enhance the application I would like to add text to the body. I have a userform with a textbox where I write the message, which works just fine but with one problem though. The linefeed I have in the textbox are lost when it is passed in to the e-mailbody. Is there any way to get keep the format from the textbox in the e-mail? Best regards, Torbjörn |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating e-mail in Excel
Torbjorn
Try changing this stHTML = "<HTML<BODY" to this stHTML = "<HTML<BODY<FONT FACE=" & Chr(34) & "ARIAL" & Chr(34) & "" -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Torbjörn Steijer" wrote in message ... Thanks a lot! It works just the way I want it. One other prblem to solve, how do I get the font to be Arial when it is passed into the mailbody? In the textbox it is Arial but Times in the mailbody. Minor problem but it doesn't look as good as I wish! Any good ideas? TIA Torbjörn "Dick Kusleika" skrev i meddelandet ... Torbjorn Try replacing Chr(10) with "<BR" to insert line breaks in the HTML. Here's an example: Private Sub CommandButton1_Click() Dim ol As Outlook.Application Dim mi As Outlook.MailItem Dim stHTML As String Set ol = New Outlook.Application Set mi = ol.CreateItem(olMailItem) stHTML = "<HTML<BODY" stHTML = stHTML & Me.TextBox1.Text stHTML = Replace(stHTML, Chr(10), "<BR") mi.HTMLBody = stHTML mi.Display End Sub -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Torbjörn Steijer" wrote in message ... Hello, I have an application where I create an e-mail in Excel based on what team members to play in different hockey games. The body in Outlook is created as a HTML-body. To enhance the application I would like to add text to the body. I have a userform with a textbox where I write the message, which works just fine but with one problem though. The linefeed I have in the textbox are lost when it is passed in to the e-mailbody. Is there any way to get keep the format from the textbox in the e-mail? Best regards, Torbjörn |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating e-mail in Excel
Hi I tried with your suggestion but it resulted in a loss of the message instead. Here's my part of my code after your suggestion (probably I have missed some "simple/obvious" part that I fully don't understand yet.. FrmMeddelande.Show '(Meddelande=Message) Message = FrmMeddelande.TextBox1.Value Dim stHTML As String stHTML = "<html<body<font FACE=" & Chr(34) & "ARIAL" & Chr(34) & "" stHTML = Message stHTML = Replace(stHTML, Chr(10), "<br") The procedure ends with the following code where stHTML is combined with another object (never mind the swedish words...) With olMail .To = SändLista .Subject = "Matchkallelse" .HTMLBody = stHTML & vbCrLf & vbCrLf & vbCrLf & SheetToHTML(ActiveSheet) 'ThisWorkbook.Sheets("2002")) .Attachments.Add "C:\Bilaga.xls", olByValue, _ 1, "Bilaga" .Display End With Set olMail = Nothing Set olApp = Nothing Sheets("Tempmail").Delete Kill "c:\bilaga.xls" End If *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating e-mail in Excel
Thor
Yes, simple, but not always so obvious Dim stHTML As String stHTML = "<html<body<font FACE=" & Chr(34) & "ARIAL" & Chr(34) & "" 'stHTML = Message Replace this line stHTML = stHTML & Message ' with this line stHTML = Replace(stHTML, Chr(10), "<br") -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Thor" wrote in message ... Hi I tried with your suggestion but it resulted in a loss of the message instead. Here's my part of my code after your suggestion (probably I have missed some "simple/obvious" part that I fully don't understand yet.. FrmMeddelande.Show '(Meddelande=Message) Message = FrmMeddelande.TextBox1.Value Dim stHTML As String stHTML = "<html<body<font FACE=" & Chr(34) & "ARIAL" & Chr(34) & "" stHTML = Message stHTML = Replace(stHTML, Chr(10), "<br") The procedure ends with the following code where stHTML is combined with another object (never mind the swedish words...) With olMail .To = SändLista .Subject = "Matchkallelse" .HTMLBody = stHTML & vbCrLf & vbCrLf & vbCrLf & SheetToHTML(ActiveSheet) 'ThisWorkbook.Sheets("2002")) .Attachments.Add "C:\Bilaga.xls", olByValue, _ 1, "Bilaga" .Display End With Set olMail = Nothing Set olApp = Nothing Sheets("Tempmail").Delete Kill "c:\bilaga.xls" End If *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating e-mail in Excel
Yes, simple when I saw it! Thanks a lot! Next issue, how do I change the font size from 12 to 10? Probably also simple when I see it, but I assume it is different in HTML script from VB in Excel. Perhaps something like this: & SIZE=10 & "" BR Thor *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating e-mail in Excel
I did some testing and the following line solved my problem; stHTML = "<html<body<font FACE=" & Chr(34) & "ARIAL" & Chr(34) & "SIZE=2" & "" Thor *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
creating numbered coupons using word and excel mail merge | Excel Worksheet Functions | |||
Creating a mail link | Links and Linking in Excel | |||
creating labels with excel and mail merge | Excel Discussion (Misc queries) | |||
How can I stop Excel from creating an e-mail link for addresses? | Excel Discussion (Misc queries) | |||
Creating "Plain Text" mail message in Outlook from Excel VBA | Excel Programming |