Creating e-mail in Excel

  • Thread starter Thread starter Torbjörn Steijer
  • Start date Start date
T

Torbjörn Steijer

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
 
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
 
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
 
Torbjorn

Try changing this

stHTML = "<HTML><BODY>"

to this

stHTML = "<HTML><BODY><FONT FACE=" & Chr(34) & "ARIAL" & Chr(34) & ">"
 
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
 
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>")
 
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
 
I did some testing and the following line solved my problem;

stHTML = "<html><body><font FACE=" & Chr(34) & "ARIAL" & Chr(34) &
"SIZE=2" & ">"

Thor
 
Back
Top