How to set message priority using CDO?

• Don Staten

  SSC Eights!

  Points: 957

  More actions

  November 3, 2005 at 1:10 pm

  #166129

  I've been able to successfully send emails via CDO through a stored proc (Reference: http://support.microsoft.com/kb/312839/) using SQL Server 2000. However, I have been unable to set the message priority. This topic has been discussed briefly in previous forum posts, but no one has been able to provide an answer.

  Here's what I know:

  • I can successfully send an email (with HIGH priority) through our SMTP server using CDO via a VBS file (Executed with CScript) on the same SQL server.
  • I can successfully send an email using the stored proc using CDO.
  • I cannot send an email with HIGH priority using the same stored proc (but the email is still sent with normal priority).

  I have tried various methods of setting the Priority property in the stored proc, but none have worked. Here are some examples of what I have tried (NOTE: the iMsg object is the created CDO.Message object):

  EXEC @hr = sp_OASetProperty @iMsg, 'Fields("urn:schemas:mailheader:X-Priority").Value',2

  EXEC @hr = sp_OASetProperty @iMsg, 'Fields("urn:schemas:mailheader:priority").Value','High'

  EXEC @hr = sp_OASetProperty @iMsg, 'Fields("urn:schemas:mailheader:priority").Value',1

  What is interesting is that I can call the sp_OAGetProperty to check the priority property and it reports the value I set it to:

  EXEC @hr = sp_OAGetProperty @iMsg, 'Fields("urn:schemas:mailheader:priority").Value'

  Has anyone been able to successfully set the message priority using a store proc? If so, how did you accomplish it?

  Thanks in advance - Don

• Don Staten

  SSC Eights!

  Points: 957

  More actions

  November 3, 2005 at 1:52 pm

  #602234

  Wow. I can't believe I took so long to realize my error. I am now able to send an email with HIGH priority through our SMTP server. Here's the scoop:

  First, set the priority property:

  EXEC @hr = sp_OASetProperty @iMsg, 'Fields("urn:schemas:mailheader:priority").Value','High'

  Then the field must be updated before the Send method is executed:

  EXEC @hr = sp_OAMethod @iMsg, 'Fields.Update', null

  I was missing the Update method to ensure the field was update prior to sending the message.

  Here is a very condensed version of the code used to send the email:

  EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
  EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("<A href="http://schemas.microsoft.com/cdo/configuration/sendusing&quot.Value','2'">http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
  EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("<A href="http://schemas.microsoft.com/cdo/configuration/smtpserver&quot.Value'">http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @SMTPServer
  EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

  EXEC @hr = sp_OASetProperty @iMsg, 'Fields("urn:schemas:mailheader:priority").Value','High'
  EXEC @hr = sp_OAMethod @iMsg, 'Fields.Update', null

  EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
  EXEC @hr = sp_OASetProperty @iMsg, 'From',@From
  EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
  EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @TextBody

  EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
  EXEC @hr=sp_OADestroy @iMsg