Carriage Return and Splitting Lines to Lotus Notes Msg through VBA

I have 5-6 rows of data that I want to capture from Excel and email
them through Lotus Notes. I'm getting the email functionality to work
but am having trouble getting the body of the email to look like it
does in Excel. Say I want the body to be:

June 06, 2005

Re: XYZ Mutual Funds, Class B

NAV = 11.43

Volume = 23,010,245

Mark Johnson
555-1212

The above is what's populated in 6 rows in Excel.


I'm using an array to capture what I want from excel
BodyText(1) = range("a1")
BodyText(2) = range("a3")
BodyText(3) = range("a5")
BodyText(4) = range("a7")
BodyText(5) = range("a8")

MailDoc.Body = BodyText

But I'm unable to figure out how to separate these into rows as they
look in Excel.

Any help?

How about just using a single string, not an array, and insert carriage
return / line feeds:

Dim BodyText As String

BodyText = Range("a1")
BodyText = BodyText & vbCrLf & Range("a3")
BodyText = BodyText & vbCrLf & Range("a5")
BodyText = BodyText & vbCrLf & Range("a7")
BodyText = BodyText & vbCrLf & Range("a8")


"skiloa" wrote:

> I have 5-6 rows of data that I want to capture from Excel and email
> them through Lotus Notes. I'm getting the email functionality to work
> but am having trouble getting the body of the email to look like it
> does in Excel. Say I want the body to be:
>
> June 06, 2005
>
> Re: XYZ Mutual Funds, Class B
>
> NAV = 11.43
>
> Volume = 23,010,245
>
> Mark Johnson
> 555-1212
>
> The above is what's populated in 6 rows in Excel.
>
>
> I'm using an array to capture what I want from excel
> BodyText(1) = range("a1")
> BodyText(2) = range("a3")
> BodyText(3) = range("a5")
> BodyText(4) = range("a7")
> BodyText(5) = range("a8")
>
> MailDoc.Body = BodyText
>
> But I'm unable to figure out how to separate these into rows as they
> look in Excel.
>
> Any help?
>
>

Thanks - that worked!

Any idea on how to get percnet format come across as such. In Excel I
have 2.8912% and it's coming across on the email as .028912...

In addition, is it possible to get this formatting done in a
concatenate formula? e.g.,

="The rates below reference"&A2&"."

Where A2 = 4/12/05, this resolves as "The rates below reference 38505"
(or whatever the Excel date is) instead of "The rates below reference
4/12/05"

Format(Range("A1"), "0.0000%")
Format(Range("A2"), "mm/dd/yy")

"skiloa" wrote:

> Thanks - that worked!
>
> Any idea on how to get percnet format come across as such. In Excel I
> have 2.8912% and it's coming across on the email as .028912...
>
> In addition, is it possible to get this formatting done in a
> concatenate formula? e.g.,
>
> ="The rates below reference"&A2&"."
>
> Where A2 = 4/12/05, this resolves as "The rates below reference 38505"
> (or whatever the Excel date is) instead of "The rates below reference
> 4/12/05"
>
>