Inconsistent and seemingly arbitrary rendering of htmlbody in CDO.Message

I am using CDO.Message objects and its htmlbody property from within a stored procedure in SQL server. The problem is that sometimes the htmlbody is rendered correctly in the email client, and sometimes it just displays plain text starting with "This is a multi-part message in MIME format....." followed by a text version of the htmlbody and the htmlcode.  

I have used different mail clients, to no avail.
I have written a test procedure that sends out exactly the same mail 10 times and some of the 10 will show correctly (say #1, 3 and 4) and others will produce the erroneous display. Then I execute the same procedure again and another subset of the 10 (say #5 and 7) will render correctly.

The stored procedure that sends the mails is attached.


CREATE PROCEDURE dbo.sp_send_cdoHtmlMail
@From varchar(255),
@To varchar(255),
@Subject varchar(255),
@Body varchar(8000),
@CC varchar(255) = null,
@BCC varchar(255) = null
AS
Declare @MailID int
Declare @hr int
EXEC @hr = sp_OACreate 'CDO.Message', @MailID OUT
EXEC @hr = sp_OASetProperty @MailID, 'From',@From
EXEC @hr = sp_OASetProperty @MailID, 'HTMLBody', @Body
EXEC @hr = sp_OASetProperty @MailID, 'BCC',@BCC
EXEC @hr = sp_OASetProperty @MailID, 'CC', @CC
EXEC @hr = sp_OASetProperty @MailID, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @MailID, 'To', @To
EXEC @hr = sp_OAMethod @MailID, 'Send', NULL
EXEC @hr = sp_OADestroy @MailID
 
Return

Open in new window

Beda01Asked:
Who is Participating?
 
dbanttariConnect With a Mentor Commented:
Can you disable outbound e-mail scanning in your antivirus?  There's a small chance it's fouling things up.

But my best guess is that there's a bug in your version of CDO, and you'll need to find a way to update it.  Perhaps there's an updater for your specific generation of CDO?
0
 
dbanttariCommented:
This is usually caused by carriage returns sneaking into the headers.  Is there any possibility that there's a carriage return in the Subject, From, To, or CC fields?  Can you post the "source" of one such e-mail, including the SMTP headers?  (Feel free to replace any IP addresses or hostnames in the headers with "xxx.xxx.xxx.xxx")
0
 
Beda01Author Commented:
Thanks for the suggestion. I doesn't seem to me that the code would introduce some carriage returns into the header fields. The variables are prepared in a different stored procedure as shown in the code snippet below. I'll post the HtmlBody in the attachment of the next comment.
SET @Subj = 'Info contable de Unisem para ' + (Select top 1 RazonSocial from Pers where IdPers = @Idpers)  
 
SET @Remitente = 'contabilidad@xxx.com'
Set @To = (select top 1 Email from #t)
SET @CC = (Select top 1 RVEmail from #t) + '; ' + (select top 1 GVEmail from #t) + '; ' + @CCAlways
SET @BCC = @BCCAlways
 
EXEC sp_send_cdoHtmlmail @Remitente, @To, @Subj, @BT, @CC, @BCC

Open in new window

0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Beda01Author Commented:
Html code that is passed to the property HtmlBody.
<html><body><font face="calibri, trebuchet MS, Arial"><h3>YYYYY</h3><div>XXXX le informa sus saldos. Gracias por su atención.</div><h4>Saldos</h4><table border="1" cellpadding="2"><tbody><font face="calibri, trebuchet MS, Arial"><tr><th align="left">Documento</th><th align="left">Vence</th><th align="left">Moneda</th><th align="right">Saldo</th><th align="left">Situación</th></tr><tr><td>e 765</td><td>15/05/2008</td><td>MXN</td><td align="right">3247.58</td><td bgcolor="#FF6A6A">vencido</td></tr><tr><td>e 770</td><td>01/06/2008</td><td>USD</td><td align="right">32364.00</td><td bgcolor="#FF6A6A">vencido</td></tr><tr><td>e 780</td><td>01/06/2008</td><td>USD</td><td align="right">19362.00</td><td bgcolor="#FF6A6A">vencido</td></tr><tr><td>e 787</td><td>09/06/2008</td><td>USD</td><td align="right">1320.00</td><td bgcolor="#FF6A6A">vencido</td></tr><tr><td>e 791</td><td>22/06/2008</td><td>USD</td><td align="right">19440.00</td><td bgcolor="#FF6A6A">vencido</td></tr><tr><td>e 828</td><td>10/07/2008</td><td>USD</td><td align="right">20472.00</td><td bgcolor="#4DBD33">vigente</td></tr><tr><td>e 967</td><td>21/09/2008</td><td>USD</td><td align="right">918.00</td><td bgcolor="#4DBD33">vigente</td></tr><tr><td><b>Total</b></td><td>&nbsp;</td><td><b>MXN</b></td><td align="right"><b>3247.58</b></td><td>&nbsp;</td></tr><tr><td><b>Total</b></td><td>&nbsp;</td><td><b>USD</b></td><td align="right"><b>93876.00</b></td><td>&nbsp;</td></tr></font></tbody></table><small>Saldos positivos = por pagar, negativos = a su favor.</small><h4>Últimos documentos</h4><table border="1" cellpadding="2"><tbody><font face="calibri, trebuchet MS, Arial"><tr><th align="left">Fecha</th><th align="left">Tipo</th><th align="left">Num</th><th align="left">Nota</th><th align="left">Moneda</th><th align="right">Importe</th></tr><tr><td>23/06/2008</td><td>CFD</td><td>&nbsp;967</td><td>xxxxxxxxxxxxxxxxxxxxxxx...</td><td>USD</td><td align="right">918.00</td></tr><tr><td>18/06/2008</td><td>Cont</td><td>&nbsp;&nbsp;</td><td>Su pago, gracias.</td><td>MXN</td><td align="right">-14602.42</td></tr><tr><td>18/06/2008</td><td>Cont</td><td>&nbsp;&nbsp;</td><td>Su pago, gracias.</td><td>USD</td><td align="right">-8587.76</td></tr><tr><td>11/06/2008</td><td>Cont</td><td>&nbsp;&nbsp;</td><td>Su pago, gracias.</td><td>USD</td><td align="right">-10000.00</td></tr><tr><td>28/05/2008</td><td>Cont</td><td>&nbsp;&nbsp;</td><td>Su pago, gracias.</td><td>USD</td><td align="right">-10000.00</td></tr></font></tbody></table><h4>Notas</h4><ul><li><div>Agradecemos ....</div><li><div>Favor de programar pagos de saldos vencidos o por vencer.</div><li><div>Favor de siempre enviar ....</div><li><div>La información de este mail es para referencia únicamente y carece de valor comprobatorio.</div><li><div>Su clave y número de cliente son "YYYYY" y nnnn.</div><li><div>Si recibió este mail por error, le pedimos el favor de avisarnos.</div></li></ul></font></body></html>

Open in new window

0
 
dbanttariCommented:
If you post the headers from a broken e-mail, we can see which header is breaking it.
0
 
Beda01Author Commented:
There you go.

However, are we sure we understand each other? This is exactly the same header, as far as I can see, as in 7 or 8 or 9 other test mails that were generated by the same procedure (see test routine below). 1 or 2 or 3 display incorrectly, the other 7 or 8 or 9 display allright.

De: XXXXX
Enviado el: miércoles, 25 de junio de 2008 14:25
Para: yyyyy@hotmail.com
CC: MMMMMMM; MMMMMMM
Asunto: Info contable de XXXXXX para YYYYYYY

This is a multi-part message in MIME format.

------=_NextPart_000_17B3_01C8D6CF.4CE7C090
Content-Type: text/plain;
      charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

ALTER PROCEDURE spMailTest
AS
SET NOCOUNT ON 
 
Declare @i int
 
SET @i = 0
 
While @i < 10
begin
	exec spMailEdoCtaCliente @IdPers = 1475, @Sendto = 0
	Set @i = @i + 1
 
end
RETURN 

Open in new window

0
 
dbanttariCommented:
There should be a header like this:

Content-Type: multipart/mixed;
      boundary="------=_NextPart_000_17B3_01C8D6CF.4CE7C090"
0
 
Beda01Author Commented:
There are several content type headers in the broken mails.

Unfortunately, I don't know how to display those headers in the good mails and compare them with the ones in the broken mails.

Still, that would not explain why some identical mails render ok and others don't, would it?

0
 
dbanttariCommented:
Multiple content-type headers does explain why things are breaking.

The question then becomes: why are you getting multiple content-type headers?  Incorrect processing of attachments?
0
 
Beda01Author Commented:
i don't use attachments, just htmlbody as explained above.

there is one contenttype header for the plain text and one for the html, i don't see anything unusual in this.

any suggestions?
0
 
dbanttariCommented:
I don't think you understand what I mean by "message headers".  They're the lines of text above "This is a multi-part message in MIME format."  The body of a multipart message may have several message parts, each with a few of their own headers, but you have yet to show a complete set of actual message headers, which is, at this point, rather frustrating.

If there's more than one content-type header in the main message headers, then there's a problem.

When you have a multipart html/plaintext message, then the message is technically an attachment that's displayed inline.

But the most likely culprit remains an extra carriage return sneaking into the headers.  Is there some reason you can't show a complete set of headers from one of the "broken" messages?  I can't help if you won't show them.
0
 
Beda01Author Commented:
I am sorry if I stress your patience.

I don't see anything else. The text "This is a multi-part message in MIME format" is at the top of the (text window of the) message. Above this, well there is the subject line and recipient etc. but I already gave you these.

Is it possible that there is something missing or hidden?
0
 
Beda01Author Commented:
I attach two screen shots, one of a broken message, one of a message that is ok.
ok.jpg
broken.jpg
0
 
Beda01Author Commented:
I found what you probably mean.

Microsoft Mail Internet Headers Version 2.0
Received: from mail pickup service by xxx.com with Microsoft SMTPSVC;
       Wed, 25 Jun 2008 14:25:23 -0500
Thread-Topic: Info contable de MMMMM
thread-index: AcjW+TbSIEacZF50Sp63bGTz8kkxEA==
From: <xxxxx@xxxxx.com>
To: <yyyyy@yyyyy.com>
Cc: <xxxxx@xxxxx.com>,
      <xxxxx@xxxxx.com>
Subject: Info contable de MMMMM
Date: Wed, 25 Jun 2008 14:25:23 -0500
Message-ID: <AC8FBBBF03B541F7A0685DC3940DDA5E@unisem.local>
X-Mailer: Microsoft CDO for Exchange 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.4133
X-OriginalArrivalTime: 25 Jun 2008 19:25:23.0611 (UTC) FILETIME=[36E30EB0:01C8D6F9]
0
 
dbanttariCommented:
Ah, that's what I wanted to see.

Interestingly, there's no content-type header in there indicating that it's a multipart message.  Or for that matter, indicating what sort of message it might be...

In the absence of a content-type header, the default is:
Content-type: text/plain; charset=us-ascii
...according to RFC 2045:
http://tools.ietf.org/html/rfc2045#section-5.2

So, it appears that there's a bug in your version of CDO where it sometimes doesn't include the content-type header that indicates the message is multipart, and so you see the plain text.

Do you have any available updates for CDO applied?
0
 
Beda01Author Commented:
let me check that, and I'll come back to you
0
 
Beda01Author Commented:
The version of my Exchange Server is 2003, SP2, build 7638.2

Thus the header "X-Mailer: Microsoft CDO for Exchange 2000" looks a little strange.

0
 
dbanttariCommented:
Looks like the latest version of CDO is 1.2.1:
http://www.microsoft.com/downloads/details.aspx?familyid=2714320D-C997-4DE1-986F-24F081725D36&displaylang=en

An X-Mailer header is very normal.  Is it the "2000" part that's looking wrong?
0
 
Beda01Author Commented:
Thank you for bearing still with me.

I tried to install the above download, but that's for Office 2007 which I don't use on the server.

Yes it was the "2000" part that looked a little strange to me, given that my Exchange is 2003, but then, I don't know whether the dll for the extended stored procedures came with SQL Server (which is version 2000) or with Exchange.

In the meantime a figured out that the dll containing the sp_OA... extended stored procedures is odsole70.dll. So I replaced it with a restore from a backup from january, but I get the same result, about 1 broken mail in 50.

Interestingly the percentage of broken mails seems to depend on the content. These are automated mails to customers. As it happens, the majority of customers seem never to get a broken mail, while for certain customers the percentage of broken mails seems to be between 10 and 30%. I tried to figue out whether it has to do with the length of the mails or the amount of query results, but there seems to be no clear pattern.

I did not have this problem a few weeks ago. But then, I accidentially deleted a stored procedure and rewrote it. I also installed a new antivirus on the server about at that time.
0
 
Beda01Author Commented:
Thank you for your suggestion. You were on target. I started to play around with the various configurations of the antivirus (AVG Internet Security SBS edition 8.0.131) and as it turned out, the problem disappeared as soon as I turned off a feature called "resident shield". Disabling email scanning and spam filter did not have any effect.

Thanks again, for the solution but also for your patience.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.