Solved

Inconsistent and seemingly arbitrary rendering of htmlbody in CDO.Message

Posted on 2008-06-24
20
860 Views
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

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

0
Comment
Question by:Beda01
  • 12
  • 8
20 Comments
 
LVL 2

Expert Comment

by:dbanttari
Comment Utility
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
 

Author Comment

by:Beda01
Comment Utility
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
 

Author Comment

by:Beda01
Comment Utility
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
 
LVL 2

Expert Comment

by:dbanttari
Comment Utility
If you post the headers from a broken e-mail, we can see which header is breaking it.
0
 

Author Comment

by:Beda01
Comment Utility
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
 
LVL 2

Expert Comment

by:dbanttari
Comment Utility
There should be a header like this:

Content-Type: multipart/mixed;
      boundary="------=_NextPart_000_17B3_01C8D6CF.4CE7C090"
0
 

Author Comment

by:Beda01
Comment Utility
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
 
LVL 2

Expert Comment

by:dbanttari
Comment Utility
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
 

Author Comment

by:Beda01
Comment Utility
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
 
LVL 2

Expert Comment

by:dbanttari
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:Beda01
Comment Utility
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
 

Author Comment

by:Beda01
Comment Utility
I attach two screen shots, one of a broken message, one of a message that is ok.
ok.jpg
broken.jpg
0
 

Author Comment

by:Beda01
Comment Utility
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
 
LVL 2

Expert Comment

by:dbanttari
Comment Utility
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
 

Author Comment

by:Beda01
Comment Utility
let me check that, and I'll come back to you
0
 

Author Comment

by:Beda01
Comment Utility
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
 
LVL 2

Expert Comment

by:dbanttari
Comment Utility
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
 

Author Comment

by:Beda01
Comment Utility
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
 
LVL 2

Accepted Solution

by:
dbanttari earned 500 total points
Comment Utility
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
 

Author Comment

by:Beda01
Comment Utility
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now