Creating a Lotus Notes Email in MS Access and VBA

This may seem like an MS access question, but it is really a Lotus
Notes email question.

I have an Access database that checks records and creates a Lotus
Notes email based on what it finds in the records. It works great.

Except that sometimes the email I create has too many characters for
Lotus Notes variables. In other words, I create a variable in my
Access routine that holds the "message". But I can not seem to string
two variable together.

For example, if I want to write in my email:

-----
Dear store manager:

The following items are out of stock in your store:

1 product A
2 product B
etc.

Thank you
-----

Here is the type of code I am using:

'Set up the new mail document
Set MailDoc = Maildb.CREATEDOCUMENT
MailDoc.Form = "Memo"
MailDoc.sendto = Recipient
MailDoc.Subject = Subject
MailDoc.Body = BodyText
MailDoc.SAVEMESSAGEONSEND = SaveIt

I created a routine that uses a Variable, let's call it BodyText, and
as I process the records for one store, I keep appending to the
BodyText, i.e. BodyText = BodyTexte + what ever new line I want to
add. Access will let me keep adding more and more, until I hit about
250 characters.

And here is where the problem arises. I can not get Lotus Notes to
let me make longer BodyTexts.

Does anyone know how to do this?

 

250 characters does not seem very much to me ;-)

Where exactly does the problem arise? When appending data to a string or
when you want to write the string to the body field?

What is the exact error message?

Depending on the type of the error, a solution could be to create the
body field as a rich text item, to which you can append text using the
"append" method. Unlike normal text fields, rich text items don't have a
length restriction, so maybe this will already solve your problem.

Regards

 

On Sun, 20 Dec 2009 16:48:10 +0100, Joerg Mertens

(snip)

Thanks for the quick reply. Everything is on my computer at work, so
I writing this from memory.

There is no real error message, I just get a message that is about 250
characters. Everything after that is left off. It is almost as if
Notes only accepts the first 250.

I did not use rich text because I want to have some data columns, as
in this examplt:

department category sub category product id desc
Produce Fruit Bananas 1234 Bananas, Yellow

Ridch text did not hanlde well the alignment of the columns. So I
sent the emails as html, using tables and row html codes. It aligned
very nicely.

This worked well when a store has a few products on the email. But
when a store had a lot of rows, the extra character needed to put in
the html codes caused the character counts to go well above 250.

 

Neither strings nor text fields have such a restriction. Text fields can
be about 64kB, IIRC, strings can be very large. Maybe you put some
characters into the string, which notes cannot handle?

In this case I would rather create a MIME entity in the document and set
the appropriate headers. You can then use it to store your HTML
code. Look at the examples in designer help. This won't help you with
your actual problem, though, I guess.

Regards
Jörg