Nate or Anyone who knows about Lotus Notes

[brettvba]

I need to copy and paste a worksheet into lotus notes I can do this manually but has anyone got any extra code to do this I have code for sending attachments so I just need the extra code to put it into the message body.

[NateO]

When you say a worsheet do you mean every cell? Could be huge...

Have you tried using a range? I haven't, the following will work, but you'd hate to write it 16,777,216 times (I wouldn't like that very much):

BodyText = Range("a6") & Chr(13) & Range("a7") & Chr(13) & Range("a8")
MailDoc.Body = BodyText

[brettvba]

Yeah I want to specify a range
a1:k59 any ideas

[Ivan F Moala]

I don't have Lotus notes @ home....will
need to search for the CoMS syntax to get
a sheet Obj embedded in Notes....

Let me have a look....

[brettvba]

Thanks Ivan

Nice pic of the sky tower!>

[Ivan F Moala]

On 2002-05-20 19:42, brettvba wrote:
Thanks Ivan

Nice pic of the sky tower!>

Hi Brett, hey you should have a Pic of
Jade stadium...when the Crusaders take out
the brumbies....then Next year when the
All Blacks (Crusaders ) take out the
World Cup....

[brettvba]

yeah if I had a current pic of Jade that would be cool im gearing up for this weekends final will be an excellent game!

_________________


[ This Message was edited by: brettvba on 2002-05-20 20:30 ]

[NateO]

Alright better late never (yes-no?). Need an array to pull this off. Add the following declarations:

Dim myAr() As String, cell As Range, cnt As Integer, bodytext As String

Now add/replace the following code:

    ReDim myAr(1 To Sheets("Sheet1").[a1:k19].Count)

    For Each cell In Sheets("Sheet1").[a1:k19]

       cnt = cnt + 1

       myAr(cnt) = cell.Value

    Next cell

    For f = 1 To UBound(myAr)

        bodytext = bodytext & " " & myAr(f)

    Next f

    MailDoc.Body = bodytext

Change the sheet name in the array stack. The 2nd loop is to stack a string with the individual array items so that your message doesn't have a bunch of ";"s throughout.

Also, with the code your using make the following adjustment:

MailDoc.SAVEMESSAGEONSEND = True

If you want to save the outgoing mail.

You can array and string multiple recipients, cc's and bcc's the same way. Just use "," instead of " " when you're stacking the string.

Hope this helps.

_________________
Cheers, Nate Oliver


[ This Message was edited by: NateO on 2002-07-09 13:09 ]

[brettvba]

cHEERS Nate I knew you would come through eventually......I think

[NateO]

Damn, you saw this too quickly!

You actually don't need to array, you can just stack the string:

like this:

For Each cell In Sheets("Sheet1").[a1:k19]
bodytext = bodytext & " " & cell.Value
Next cell

The nice thing is that it stacks left to right, top to bottom.

So get rid of myar business (I was originally looking at this quite differently)

There is the issue of borders and commonly sized strings (formatting). Haven't solved that one yet. Give me another couple of months