Archived Forum Post
Index of archived forum posts
Question:
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!!
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..
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
Holy Cow, that's it! replacing the @success with NULL fixed this issue!
Thanks for the great support and the awesome software!
Jean-Claude