Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Inconsistent and seemingly arbitrary rendering of htmlbody in CDO.Message

Posted on 2008-06-24
20
Medium Priority
?
876 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
[X]
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
20 Comments
 
LVL 2

Expert Comment

by:dbanttari
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 "xxx.xxx.xxx.xxx")
0
 

Author Comment

by:Beda01
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 = '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
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

0
Build and deliver software with DevOps

A digital transformation requires faster time to market, shorter software development lifecycles, and the ability to adapt rapidly to changing customer demands. DevOps provides the solution.

 
LVL 2

Expert Comment

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

Author Comment

by:Beda01
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.

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
ID: 21869784
There should be a header like this:

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

Author Comment

by:Beda01
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?

0
 
LVL 2

Expert Comment

by:dbanttari
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?
0
 

Author Comment

by:Beda01
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?
0
 
LVL 2

Expert Comment

by:dbanttari
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.
0
 

Author Comment

by:Beda01
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?
0
 

Author Comment

by:Beda01
ID: 21870757
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
ID: 21873857
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
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:
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
ID: 21877366
let me check that, and I'll come back to you
0
 

Author Comment

by:Beda01
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.

0
 
LVL 2

Expert Comment

by:dbanttari
ID: 21885472
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
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.
0
 
LVL 2

Accepted Solution

by:
dbanttari earned 2000 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?
0
 

Author Comment

by:Beda01
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.
0

Featured Post

The top UI technologies you need to be aware of

An important part of the job as a front-end developer is to stay up to date and in contact with new tools, trends and workflows. That’s why you cannot miss this upcoming webinar to explore the latest trends in UI technologies!

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Six Sigma Control Plans

705 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