login about faq

Hello, I'm using the 64bit ActiveX control (version 9.5.0.59) to send emails to an Office365 SMTP server from SQL Server 2012. It all works well except that the To address is not cleared out after each Send(). I'm creating a single instance of the MailMan and Email objects and just re-populating the From, To, Subject and Message, see the sample code below.

    DECLARE @lQuit bit
SET @lQuit = 0

DECLARE @hr int
DECLARE @sErrorMsg nvarchar(4000)

--  The mailman object is used for sending and receiving email.
DECLARE @mailman int
EXEC @hr = sp_OACreate 'Chilkat_9_5_0.MailMan', @mailman OUT
IF @hr <> 0
BEGIN
   RAISERROR('Unable To create mail control.',16,1)
   RETURN
END

-- unlock the control
DECLARE @success int
EXEC sp_OAMethod @mailman, 'UnlockComponent', @success OUT, 'xxxxxxxxx'
IF STR(@success) <> 1
  BEGIN
    EXEC sp_OAGetProperty @mailman, 'LastErrorText', @sErrorMsg OUT
    RAISERROR(@sErrorMsg,16,2)
    EXEC @hr = sp_OADestroy @mailman
    RETURN
  END

-- set SMTP settings
EXEC sp_OASetProperty @mailman, 'SmtpHost', 'smtp.office365.com'
EXEC sp_OASetProperty @mailman, 'SmtpPort', 587
EXEC sp_OASetProperty @mailman, 'StartTLS', 1

--  Set the SMTP login/password
EXEC sp_OASetProperty @mailman, 'SmtpUsername', 'user_name'
EXEC sp_OASetProperty @mailman, 'SmtpPassword', 'password'

--  Create an email object
DECLARE @email int
EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Email', @email OUT
IF @hr <> 0
BEGIN
    RAISERROR('Unable To create the email object.',16,3)
    EXEC @hr = sp_OADestroy @mailman
    RETURN
END

-- send the first email
EXEC sp_OAMethod @email, 'ClearTo', @success OUT
EXEC sp_OAMethod @email, 'RemoveHtmlAlternative', @success OUT
EXEC sp_OAMethod @email, 'RemovePlainTextAlternative', @success OUT

-- code to send out the email
EXEC sp_OASetProperty @email, 'Subject', 'This is the first email'
EXEC sp_OAMethod @email, 'SetHtmlBody', NULL, '<html><body>1. this is the HTML body</body></html>'
EXEC sp_OAMethod @email, 'AddPlainTextAlternativeBody', @success OUT, 'this is the plain text version of the body'
EXEC sp_OASetProperty @email, 'From', 'from_email'
EXEC sp_OAMethod @email, 'AddTo', @success OUT, 'support', 'email_address1'

EXEC sp_OAMethod @mailman, 'SendEmail', @success OUT, @email

-- send the second email
EXEC sp_OAMethod @email, 'ClearTo', @success OUT
EXEC sp_OAMethod @email, 'RemoveHtmlAlternative', @success OUT
EXEC sp_OAMethod @email, 'RemovePlainTextAlternative', @success OUT

-- code to send out the email
EXEC sp_OASetProperty @email, 'Subject', 'This is the second email'
EXEC sp_OAMethod @email, 'SetHtmlBody', NULL, '<html><body>2. this is the HTML body</body></html>'
EXEC sp_OAMethod @email, 'AddPlainTextAlternativeBody', @success OUT, 'this is the plain text version of the body'
EXEC sp_OASetProperty @email, 'From', 'from_email'
EXEC sp_OAMethod @email, 'AddTo', @success OUT, 'support', 'email_address2'

EXEC sp_OAMethod @mailman, 'SendEmail', @success OUT, @email

-- send the third email
EXEC sp_OAMethod @email, 'ClearTo', @success OUT
EXEC sp_OAMethod @email, 'RemoveHtmlAlternative', @success OUT
EXEC sp_OAMethod @email, 'RemovePlainTextAlternative', @success OUT

-- code to send out the email
EXEC sp_OASetProperty @email, 'Subject', 'This is the third email'
EXEC sp_OAMethod @email, 'SetHtmlBody', NULL, '<html><body>3. this is the HTML body</body></html>'
EXEC sp_OAMethod @email, 'AddPlainTextAlternativeBody', @success OUT, 'this is the plain text version of the body'
EXEC sp_OASetProperty @email, 'From', 'from_email'
EXEC sp_OAMethod @email, 'AddTo', @success OUT, 'support', 'email_address3'

EXEC sp_OAMethod @mailman, 'SendEmail', @success OUT, @email

IF STR(@success) <> 1
  BEGIN
    -- got an error
    EXEC sp_OAGetProperty @mailman, 'LastErrorText', @sErrorMsg OUT
    SET @sErrorMsg = 'Unable to send the email';
    RAISERROR(@sErrorMsg,16,4)
    SET @lQuit = 1
  END
EXEC @hr = sp_OADestroy @mailman
EXEC @hr = sp_OADestroy @email

When i execute the code from SSMS, it sends out 3 emails, however i see from Outlook that the first email has one To address, the second email has two To addresses, and the third email has all three To addresses. So it appears that the ClearTo() is not working. No errors are returned by the LastErrorText() method when placed right after the ClearTo() method.

Thanks for developing a great product! it generally works flawlessly!!

asked Sep 22 at 20:38

jetch's gravatar image

jetch
1


That's very strange. It's tested in C++ in the QA suite (run prior to release) by this:

bool EmailTesting::qa_clearTo(void)
    {
    const char *testName = "qa_clearTo";

CkEmail email;
email.put_Subject("test");
email.put_Body("test");

email.AddTo("Recip1","r1@something.com");
email.ClearTo();
email.AddTo("Recip2","r2@something.com");
email.ClearTo();
email.AddTo("Recip3","r3@something.com");

int n = email.get_NumTo();
if (n != 1) return failed(testName,email);

printf("Num TO recipients = %d\n",n);
printf("%s\n",email.getMime());

return succeeded(testName);
}

The number of recipients is correct, and the MIME is also correct.. The ActiveX uses the same underlying C++ code.

I'll investigate more though... and will respond back tomorrow when I've had a chance to duplicate this exact code using the ActiveX..

link

answered Sep 22 at 20:59

chilkat's gravatar image

chilkat ♦♦
11.8k316358420

I suspect this is the problem: The ClearTo method is not a function that returns anything. It's a procedure (or subroutine). For example, in C++ it would look like this:

void ClearTo(void);
Whereas AddTo is a function that returns success/failure. Therefore, the call to ClearTo should have no "@success OUT". I suspect the error in calling ClearTo is getting suppressed and it's not actually getting called at all. Try this:
EXEC sp_OAMethod @email, 'ClearTo', NULL

link

answered Sep 22 at 22:36

chilkat's gravatar image

chilkat ♦♦
11.8k316358420

Holy Cow, that's it! replacing the @success with NULL fixed this issue!

Thanks for the great support and the awesome software!

Jean-Claude

link

answered Sep 23 at 03:06

jetch's gravatar image

jetch
1

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or __italic__
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×47

Asked: Sep 22 at 20:38

Seen: 149 times

Last updated: Sep 23 at 03:06

powered by OSQA