SQL Server Stored Procedure Example

---

This code can be executed in a SQL Server stored procedure to send mail through VIM/MAPI/VINES/MHS/Active Messaging:

create procedure sp_quote_mail_send
as
begin

declare @hr int
declare @object int
declare @recipient char(35)

exec @hr=sp_OACreate 'IDSMailInterface.Server', @object OUTPUT
exec @hr=sp_OASetProperty @object,'ObjectKey','ABC123'
exec @hr=sp_OAMethod @object,'NewMessage'
exec @hr=sp_OASetProperty @object,'Subject','Sql Server E-mail Test'
exec @hr=sp_OASetProperty @object,'Message','Here id the test message.'
select @recipient='Jim Smith'
exec @hr=sp_OAMethod @object,'AddRecipientTo',NULL, @recipient
exec @hr=sp_OAMethod @object,'Send'

end

Note that we didn't need to specify the type of E-mail system we wished to use. IDSMail automatically determined the type of E-mail present on the workstation, and sent the message accordingly.

Here is a similar example for SMTP (Internet mail):

create procedure sp_quote_mail_send
as
begin

declare @hr int
declare @object int
declare @recipient char(35)

exec @hr=sp_OACreate 'IDSMailInterface.Server', @object OUTPUT
exec @hr=sp_OASetProperty @object,'ObjectKey','ABC123'
exec @hr=sp_OASetProperty @object,'SMTPServer','mySMTPServer.com'
exec @hr=sp_OASetProperty @object,'MailSystem',10
exec @hr=sp_OAMethod @object,'NewMessage'
exec @hr=sp_OASetProperty @object,'From','me@mycompany.com'
exec @hr=sp_OASetProperty @object,'Subject','Sql Server E-mail Test'
exec @hr=sp_OASetProperty @object,'Message','Here id the test message.'
select @recipient='jsmith@acme.com'
exec @hr=sp_OAMethod @object,'AddRecipientTo',NULL, @recipient
exec @hr=sp_OAMethod @object,'Send'

end

If you wish, download our Programming Guide (140k)