[DB Tip]SP로메일보내기

출처 : http://www.cuteguy.pe.kr
대상 : ASP, MS-SQL7.0(2000?)

--실행하기
exec pSendMail '보내는 사람 멜 주소', '받을사람멜주소', '제목','내용'

--저장 프로시져 생성
create procedure pSendMail(@From varchar(50), @To varchar(50), @Subject varchar(255), @Body varchar(8000)) as
begin
declare @object int
declare @hr int
declare @src varchar(255)
declare @desc varchar(255)
exec @hr = sp_OACreate 'CDONTS.NewMail', @object OUT
if @hr <> 0
begin
exec sp_OAGetErrorInfo @object, @src OUT, @desc OUT
select hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
return @hr
end
exec @hr = sp_OASetProperty @object, 'From', @From
if @hr <> 0
begin
exec sp_OAGetErrorInfo @object, @src OUT, @desc OUT
select hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
return @hr
end

exec @hr = sp_OASetProperty @object, 'To', @To
if @hr <> 0
begin
exec sp_OAGetErrorInfo @object, @src OUT, @desc OUT
select hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
return @hr
end

exec @hr = sp_OASetProperty @object, 'Subject', @Subject
if @hr <> 0
begin
exec sp_OAGetErrorInfo @object, @src OUT, @desc OUT
select hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
return @hr
end
exec @hr = sp_OASetProperty @object, 'Body', @Body
if @hr <> 0
begin
exec sp_OAGetErrorInfo @object, @src OUT, @desc OUT
select hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
return @hr
end
exec @hr = sp_OASetProperty @object, 'BodyFormat', 1
if @hr <> 0
begin
exec sp_OAGetErrorInfo @object, @src OUT, @desc OUT
select hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
return @hr
end
exec @hr = sp_OASetProperty @object, 'MailFormat', 1
if @hr <> 0
begin
exec sp_OAGetErrorInfo @object, @src OUT, @desc OUT
select hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
return @hr
end
exec @hr = sp_OAMethod @object, 'Send'
if @hr <> 0
begin
exec sp_OAGetErrorInfo @object, @src OUT, @desc OUT
select hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
return @hr
end
exec @hr = sp_OADestroy @object
if @hr <> 0
begin
exec sp_OAGetErrorInfo @object, @src OUT, @desc OUT
select hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
return @hr
end
print 'Message Sent.'
return 0
end

참고로 아직.. 저는 테스트를 못해봤네요. ㅎㅎㅎ