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

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

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

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

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!

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!

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!

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!