Solved Line break in email (1 Viewer)

[allen675]

Hello fellow Forum'ers

I have been trying for hours now pulling my hair out trying to understand why my code will not put a line break in my email. In basic terms I have a table which contains a field with the body for an email which can be amended by the end user through a form. It enables the end user to personalise the bulk of their messages rather than me programming a set message. With a click of a button on a form an email is sent to that customer. I have DLOOKUP successfully pulling through the email body, HOWEVER I cant get the bugger to put a line break between "Dear X" and "I have tried......"

This is my code, please help!

Private Sub Command16_Click()

Dim MsgBody As String
Dim Msg As String

MsgBody = DLookup("AutomatedMessageEmail", "AutomatedMessageT", "AutomatedMessageID=1")

Msg = "Dear " & Client_FN & ", <p>" & MsgBody


Dim O As Outlook.Application
Dim M As Outlook.MailItem


Set O = New Outlook.Application
Set M = O.CreateItem(olMailItem)

With M
    .BodyFormat = olFormatHTML
    .To = Email_Address
    .BCC = "test@test.com"
    .Subject = "Mortgage Enquiry"
    .Display
    .HTMLBody = Msg & .HTMLBody
End With

Set M = Nothing
Set O = Nothing

End Sub

 

[strive4peace]

hi @allen675

to add a line BReak using HTML, use

<BR>

or you can also use

<BR />

to indicate there is no closing tag.

Use 2 <BR /> if you want 2 blank lines. Case doesn't matter, so you can also use <br /><br /> or <br><br>

<p> could do it too -- but the paragraph tag is like parentheses -- need beginning and ending tag. For instance:

Msg = "<p>Dear " & Client_FN & ", </p>" & MsgBody

 

[strive4peace]

ps, @allen675, welcome to AWF!

 

[strive4peace]

pps, @allen675

assign HTMLBody before Display
EDIT: maybe not ... if you wait, adds signature!

and if you're creating a new email, would there already be an HTMLBody ?? I'm assuming not ... but perhaps there's a template kicking in?

 

[allen675]

hi @allen675

to add a line BReak using HTML, use

<BR>

or you can also use

<BR />

to indicate there is no closing tag.

Use 2 <BR /> if you want 2 blank lines. Case doesn't matter, so you can also use <br /><br /> or <br><br>

<p> could do it too -- but the paragraph tag is like parentheses -- need closing and ending tag. For instance:

Msg = "<p>Dear " & Client_FN & ", </p>" & MsgBody

striver peace,

You are a diamond, thank you, worked a treat.

Bit of a learning curve, I've not used VBA for years throw in a bit of HTML just for good measure and it had me stumped

Anyhow one again thank you for your help and quick response

I may very well be back!

 

[allen675]

pps, @allen675

assign HTMLBody before Display

and if you're creating a new email, would there already be an HTMLBody ?? I'm assuming not ... but perhaps there's a template kicking in?

Do you mean this

.HTMLBody = Msg & .HTMLBody

I had a signature that wasn't being picked up so I added the .HTMLBody on the end.

Would moving this piece of code ahead of display and removing . HTMLBody from the end pick up the signature?

 

[strive4peace]

you're welcome, @allen675 ~ happy to help

actually, this required HTML, not VBA knowledge -- lucky to know both

... and, by the way, Access can link to local Outlook mailboxes ... much better way to find things in your email (smile)

 

[strive4peace]

hi @allen675

> Would moving this piece of code ahead of display and removing . HTMLBody from the end pick up the signature?

good question -- I actually don't know ... I would think that siggy would be a different object. Question: Does it WORK to assign the body after the display?

 

[allen675]

you're welcome, @allen675 ~ happy to help

actually, this required HTML, not VBA knowledge -- lucky to know both

... and, by the way, Access can link to local Outlook mailboxes ... much better way to find things in your email (smil

hi @allen675

> Would moving this piece of code ahead of display and removing . HTMLBody from the end pick up the signature?

good question -- I actually don't know ... I would think that siggy would be a different object. Does it WORK to assign the body after the display?

Not tried it before . Display so can't really comment. What I can say is that previously I had it as .HTMLBody = Msg and no signature was being added!?

 

[strive4peace]

hi @allen675

let me know when you test ... could be different code is needed to add the signature ... good idea though!

 

[allen675]

hi @allen675

let me know when you test ... could be different code is needed to add the signature ... good idea though!

Just tested it, moved ahead of display and didn't pick up signature!?

 

[strive4peace]

Just tested it, moved ahead of display and didn't pick up signature!?

thanks, @allen675

does it pick up signature if it is after Display?

 

[allen675]

thanks, @allen675

does it pick up signature if it is after Display?

Yes it does

 

[strive4peace]

oh, interesting and good to know! thanks, @allen675

 

[allen675]

Yes it does

PS only when it is

.HTMLBody = Msg & .HTMLBody

But NOT when it is

.HTMLBody = Msg

I only found this out by trial and error so I can't really take credit for it!

 

[strive4peace]

I wouldn't have thought to even try it that way, thanks for testing @allen675 (is your name "Allen"?)

 

[allen675]

I wouldn't have thought to even try it that way, thanks for testing @allen675 (is your name "Allen"?)

No that's my surname, my first name is David

 

[strive4peace]

welcome, David, look forward to more posts from you, and wishing you great success with Access

 

[strive4peace]

ps, David, this statement:
Set O = New Outlook.Application

requires early binding, which means the correct version of the Outlook library must be referenced. If you're writing code to use in any version and want to use late binding instead, you can use

dim O as Object   'Outlook.Application
Set O = CreateObject("Outlook.Application")

Outlook can't have more than one instance running, so CreateObject actually does a GetObject if it's already open

you would also change:
Dim M As Outlook.MailItem
to

Dim M As object   'Outlook.MailItem

and
Set M = O.CreateItem(olMailItem)
to

Set M = O.CreateItem(0)  '0=olMailItem

and then, finally the olFormatHTML constant that is used in code would also need to be defined:

 .BodyFormat = 2  'olFormatHTML

 

[allen675]

ps, David, this statement:
Set O = New Outlook.Application

requires early binding, which means the correct version of the Outlook library must be referenced. If you're writing code to use in any version and want to use late binding instead, you can use

dim O as Object   'Outlook.Application
Set O = CreateObject("Outlook.Application")

Outlook can't have more than one instance running, so CreateObject actually does a GetObject if it's already open

you would also change:
Dim M As Outlook.MailItem
to

Dim M As object   'Outlook.MailItem

and
Set M = O.CreateItem(olMailItem)
to

Set M = O.CreateItem(0)  '0=olMailItem

and then, finally the olFormatHTML constant that is used in code would also need to be defined:

 .BodyFormat = 2  'olFormatHTML

Updated my code with your suggestions, thank you