Inconsistent and seemingly arbitrary rendering of htmlbody in CDO.Message

Posted on 2008-06-24
Last Modified: 2011-10-19
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
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

Open in new window

Question by:Beda01
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 8

Expert Comment

ID: 21865715
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 "")

Author Comment

ID: 21868921
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 = ''
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


Author Comment

ID: 21868953
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.


Expert Comment

ID: 21868992
If you post the headers from a broken e-mail, we can see which header is breaking it.

Author Comment

ID: 21869152
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.

Enviado el: miércoles, 25 de junio de 2008 14:25
Asunto: Info contable de XXXXXX para YYYYYYY

This is a multi-part message in MIME format.

Content-Type: text/plain;
Content-Transfer-Encoding: quoted-printable

Declare @i int
SET @i = 0
While @i < 10
	exec spMailEdoCtaCliente @IdPers = 1475, @Sendto = 0
	Set @i = @i + 1

Open in new window


Expert Comment

ID: 21869784
There should be a header like this:

Content-Type: multipart/mixed;

Author Comment

ID: 21869974
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?


Expert Comment

ID: 21870098
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?

Author Comment

ID: 21870150
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?

Expert Comment

ID: 21870409
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.

Author Comment

ID: 21870650
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?

Author Comment

ID: 21870757
I attach two screen shots, one of a broken message, one of a message that is ok.

Author Comment

ID: 21873857
I found what you probably mean.

Microsoft Mail Internet Headers Version 2.0
Received: from mail pickup service by with Microsoft SMTPSVC;
       Wed, 25 Jun 2008 14:25:23 -0500
Thread-Topic: Info contable de MMMMM
thread-index: AcjW+TbSIEacZF50Sp63bGTz8kkxEA==
From: <>
To: <>
Cc: <>,
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]

Expert Comment

ID: 21876630
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:

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?

Author Comment

ID: 21877366
let me check that, and I'll come back to you

Author Comment

ID: 21879881
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.


Expert Comment

ID: 21885472
Looks like the latest version of CDO is 1.2.1:

An X-Mailer header is very normal.  Is it the "2000" part that's looking wrong?

Author Comment

ID: 21886680
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.

Accepted Solution

dbanttari earned 500 total points
ID: 21891088
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?

Author Comment

ID: 21891461
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.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to shrink a transaction log file down to a reasonable size.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question