We help IT Professionals succeed at work.

Text over two lines in VBA for email

Seamus2626
Seamus2626 asked
on
Hi,

I have some code that is creating a lotus notes email. i want the text in body of the email to split over two lines

"Following the communication of 24th February, please find attached the January 2015 version of your personal Horis portfolio Sales Manager report._"
 & "If you have any questions around the content of this report, please contact your regional SPM team in the first instance.

I cant get the syntax right! Can someone amend please?!

Thanks
Comment
Watch Question

Phillip BurtonDirector, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014

Commented:
At the end of the first line, try one of the following
 
& chr(13)
& chr(10)
& chr(13) & chr(10)

Author

Commented:
"Following the communication of 24th February, please find attached the January 2015 version of your personal Horis portfolio Sales Manager report" & Chr(13)
"If you have any questions around the content of this report, please contact your regional SPM team in the first instance."


"If you have any questions around the content of this report, please contact your regional SPM team in the first instance."

This is highlighted red now?

Thanks Phillip
Phillip BurtonDirector, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014

Commented:
You are missing an & after "& chr(13)". You had that at the start of line 2 in your example.

Author

Commented:
MailDoc.Body = _
"Following the communication of 24th February, please find attached the January 2015 version of your personal Horis portfolio Sales Manager report._Chr(13)" &
  & "If you have any questions around the content of this report, please contact your regional SPM team in the first instance"

That is still coming up red Phillip
Phillip BurtonDirector, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014
Commented:
And a _

MailDoc.Body = _
"Following the communication of 24th February, please find attached the January 2015 version of your personal Horis portfolio Sales Manager report._Chr(13)" & _
  & "If you have any questions around the content of this report, please contact your regional SPM team in the first instance"
Commented:
if you have a keyed enter while writing the lines (so your code is having two line numbers in editor)  then you have to use underscore at the end of first line _

Author

Commented:
MailDoc.Body = _
 "Following the communication of 24th February, please find attached the January 2015 version of your personal Horis portfolio Sales Manager report._Chr(13)" & _
   & "If you have any questions around the content of this report, please contact your regional SPM team in the first instance"


That is returning all red Phillip

Commented:
please use code tag in comment editor and  post the code again

Author

Commented:
Thanks

MailDoc.Body = _
 "Following the communication of 24th February, please find attached the January 2015 version of your personal Horis portfolio Sales Manager report._Chr(13)" & _
   & "If you have any questions around the content of this report, please contact your regional SPM team in the first instance"

Open in new window

Commented:
there is 2 & at the end of third line and at the forth line , remove one of them

Commented:
remove &  in third line

Commented:
MailDoc.Body = _
 "Following the communication of 24th February, please find attached the January 2015 version of your personal Horis portfolio Sales Manager report._Chr(13)" & _
    "If you have any questions around the content of this report, please contact your regional SPM team in the first instance"

Open in new window

why not use .HTMLBody instead?

For x = CInt(StartRow) To CInt(EndRow)
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
    .To = Worksheets(Name).Cells(x, 1) 'Row x, Column A
    .CC = ""
    .BCC = ""
    .Subject = Worksheets(Name).Cells(x, 3) 'Row x, Column C
    ' greeting using nickname
    .HTMLBody = "Dear " & Worksheets(Name).Cells(x, 18) & "," 'Row x, Column R
    ' first paragraph of letter
    .HTMLBody = .HTMLBody & Worksheets(Name).Cells(x, 8).Value 'Row x, Column H
    ' attach the previous years survey or a blank
    .Attachments.Add Worksheets(Name).Cells(x, 2).Value 'Row x, Column B
    ' attach this years facility usage table or a blank
    .Attachments.Add Worksheets(Name).Cells(x, 17).Value 'Row x, Column Q
    .display
    Application.Wait (Now + TimeValue("0:00:02"))
    Application.SendKeys "%s"
    '.Send
    End With
    Set OutMail = Nothing
Next x

Open in new window


Granted this is code to generate an outlook email, but it can be easily modified.
Sjef BosmanGroupware Consultant

Commented:
What HTMLBody? In a Notes mail? No can do.
Sjef BosmanGroupware Consultant

Commented:
You can use NotesMimeEntity objects, but then it's a completely different ballgame...
Didn't know that Notes mail didn't use .HTMLBody.  Time to switch to outlook :-)

Commented:
for me I don't know Lotus Notes at all :)
but very strange that it has VBA!, I thought IBM will never think to do that
Sjef BosmanGroupware Consultant

Commented:
Nope, notes has LotusScript, which is similar to VB. In this case, the application is an Excel application that communicates with the Notes client via the COM interface.

@FarWest: time to learn something about Notes :-)

Commented:
Thanks a lot for the info
But I'm now over 50 and last thing I know about Lotus was 123. 25 years ago
:)
Sjef BosmanGroupware Consultant

Commented:
Too old to learn? Well, so am I, maybe... By the way: what's outlook?

Author

Commented:
Lotus notes is cruel lads, but its what i have to work with!!

So when i put in the below line, it runs over two lines in the editor but still comes out as one line in the body of the email (pic attached)

So i need it to split over two lines in the email so it appears as

Following the communication of 24th February, please find attached the January 2015 version of your personal Horis portfolio Sales Manager report.

If you have any questions around the content of this report, please contact your regional SPM team in the first instance

Thanks!

MailDoc.Body = _
 "Following the communication of 24th February, please find attached the January 2015 version of your personal Horis portfolio Sales Manager report._Chr(13)" & _
    "If you have any questions around the content of this report, please contact your regional SPM team in the first instance"

Open in new window

Capture.PNG
Sjef BosmanGroupware Consultant

Commented:
Try with _Chr(13)_Chr(10)

Author

Commented:
Sorry Sief, i have no idea where too put _Chr(13)_Chr(10)

I tried

 "Following the communication of 24th February, please find attached the January 2015 version of your personal Horis portfolio Sales Manager report._Chr(13)_Chr(10) " & _
    "If you have any questions around the content of this report, please contact your regional SPM team in the first instance"

That's not working though

Commented:
not inside the string

MailDoc.Body = _
 "Following the communication of 24th February, please find attached the January 2015 version of your personal Horis portfolio Sales Manager report " & vbCRLf &  _
    "If you have any questions around the content of this report, please contact your regional SPM team in the first instance"

Open in new window

Sjef BosmanGroupware Consultant
Commented:
That would have been the idea, indeed. Well, I suppose that Notes doesn't support control characters in the string, so you'll have to revert to using a real rich-text item for Body. Don't forget to remove the current line with .Body ...

I don't have the exact code here, but it's something in the line of this:
body= MailDoc.CreateRichTextItem("Body")
body.AppendText "line1"
body.addNewline 1 
body.AppendText "line2"
body.addNewline 3 
body.AppendText "Yours truly,"

Open in new window

etc.

Author

Commented:
Thanks guys!