Outlook.mailitem - How to assign to body a memo field with 255 characters and more

[informer]

Outlook.mailitem - How to assign to body a memo field with 255 characters & more

Hi

In a VBA function, I assign to the body of Outlook object a memo field with more than 255 characters

.Body = recSet!eMailComment

But the memo field content is cut off after 255 characters.

How to circumvent this restriction?

Thanks by advance for your help

 

[Minty]

Try storing the body into a string variable first.

strBody = recSet!eMailComment
With Outlook.mailitem
.Body = strBody

If that doesn't work is your recordset using any grouping? - see http://allenbrowne.com/ser-63.html for more info.

 

[informer]

Hy Minty

Thanks for your help but still doesn't work and even after declaring my variable as below

Dim sBody As String * 1024

I used the function below to check sBody content and same result, only 255 charaters were saved in SQL.txt

Function writeInFile(sSQL As String)
    sPath = "D:\SQL.txt"
    NumFic = FreeFile
    Open sPath For Output As #NumFic
    Print #NumFic, sSQL
    Close #NumFic
End Function

 

[Minty]

So is the recordset using any aggregation at all?
What is the recordset SQL?
That is the most common cause for truncation.

 

[informer]

Hi Minty

In my vba code I wrote

 sSQL = "SELECT * FROM qryPayeurPourEdition"
 Set recSet = CurrentDb.OpenRecordset(sSQL)

And qryPayeurPourEdition SQL is

SELECT DISTINCT tblPayeur.Afficher, tblPayeur.eMailComment, tblPayeur.eMail, tblPayeur.PaiementImediat, UCase([tblPayeur].[civilite] & " " & [tblPayeur].[Prenom] & " " & [tblPayeur].[Nom]) AS PayeurLong, UCase([tblPayeur].[zip] & " " & [tblPayeur].[ville]) AS PayeurLocalite, tblEleve.idPayeur, tblPayeur.civilite AS PayeurCivilite, tblPayeur.Prenom AS PayeurPrenom, tblPayeur.Nom AS PayeurNom, [tblPayeur].[Prenom] & " " & [tblPayeur].[Nom] AS Payeur, UCase([tblPayeur].[Adresse]) AS PayeurAdresse, tblPayeur.ville AS PayeurVille, tblPayeur.zip AS PayeurZip
FROM (tblEleve INNER JOIN tblPayeur ON tblEleve.idPayeur = tblPayeur.idPayeur) INNER JOIN tblAEditer ON tblEleve.idEleve = tblAEditer.idELeve
WHERE (((tblEleve.aEditer)=True));

 

[Minty]

Remove the DISTINCT.
See the explanation in Allen Browne's list on Uniqueness.

 

[arnelgp]

To get all the text from memo, make the memo field the first column in your query.
This is also mr.browne's suggestion and mostly work.

 

[informer]

Thanks a lot for your help and advices. I’ll implement your solution today.

 

[informer]

Hi All

It works great now

I applied recommendations provided by Allen’s Browne :

1. Move on first position the memo field
2. remove DISTINCT SQL command and change my query to select only one record by using SELECT memoField,.... FROM tblX WHERE tblX.id IN ( SELECT DISTINCT tblX.id FROM....).

Thanks a lot again

PS: what does it happen if there is more than one memo field?

 

[Minty]

There won't (shouldn't) be as your Sub query is selecting a Distinct record.

 

[informer]

Hi Minty,

Using DISTINCT SQL command in subquery doesn’t cause truncation of memo field if it is selected in the principal request. For my case, it works perfectly

 

[Minty]

What I meant was you using Distinct in the sub query will only return one record. Which won't affect your memo as it isn't in the Sub Query.

 

[informer]

Thanks a lot again for your help.

I precise :
A payer (tblPayeur) can pay for several students (tblEleve).
Even if I trangress rules for modelling a db scheme, I store in an table (tblAEditer), students who have courses for a period MM/YYYY entered dynamically in a form which displays the list of these students.
Then, I flag (aEditer=true) in this form, students for which an invoice will be printed (report) and sent by email.

So my final request is :

SELECT tblPayeur.eMailComment AS MemoField, tblPayeur.eMail, tblPayeur.PaiementImediat, UCase([tblPayeur].[civilite] & " " & [tblPayeur].[Prenom] & " " & [tblPayeur].[Nom]) AS PayeurLong, UCase([tblPayeur].[zip] & " " & [tblPayeur].[ville]) AS PayeurLocalite, tblPayeur.civilite AS PayeurCivilite, tblPayeur.Prenom AS PayeurPrenom, tblPayeur.Nom AS PayeurNom, [tblPayeur].[Prenom] & " " & [tblPayeur].[Nom] AS Payeur, UCase([tblPayeur].[Adresse]) AS PayeurAdresse, tblPayeur.ville AS PayeurVille, tblPayeur.zip AS PayeurZip
FROM tblPayeur WHERE idPayeur
IN (SELECT DISTINCT tblPayeur.idPayeur
FROM (tblEleve INNER JOIN tblPayeur ON tblEleve.idPayeur = tblPayeur.idPayeur) INNER JOIN tblAEditer ON tblEleve.idEleve = tblAEditer.idELeve
WHERE tblEleve.aEditer=True)