ALTER PROCEDURE usp_sql2email (
	@mx			NVARCHAR(255) = 'mailserver',
	@from		NVARCHAR(320) = 'example@example.com',
	@from_name	NVARCHAR(320) = 'Company Name',
	@to			NVARCHAR(320),
	@to_name	NVARCHAR(320),
	@cc			NVARCHAR(320) = NULL,
	@cc_name	NVARCHAR(320) = NULL,
	@bcc		NVARCHAR(320) = NULL,
	@bcc_name	NVARCHAR(320) = NULL,
	@subject	NVARCHAR(255),
	@body		NVARCHAR(4000),
	@file_name	NVARCHAR(255) = NULL,
	@file_cont	NVARCHAR(4000) = NULL
)
AS
	DECLARE @int_result	INT
	DECLARE @int_object	INT
	DECLARE @str_source NVARCHAR(255)
	DECLARE @str_desc	NVARCHAR(500)

	-- Create Object
	EXECUTE @int_result = sp_OACreate 'Persits.MailSender', @int_object OUT
	IF @int_result <> 0 GOTO CLEANUP
	-- Set Propertiest & Methods
	EXECUTE @int_result = sp_OASetProperty @int_object, 'host', @mx
	IF @int_result <> 0 GOTO CLEANUP
	EXECUTE @int_result = sp_OASetProperty @int_object, 'from', @from
	IF @int_result <> 0 GOTO CLEANUP
	EXECUTE @int_result = sp_OASetProperty @int_object, 'fromName', @from_name
	IF @int_result <> 0 GOTO CLEANUP
	EXECUTE @int_result = sp_OAMethod @int_object, 'addAddress', NULL, @to, @to_name
	IF @int_result <> 0 GOTO CLEANUP
	IF @cc IS NOT NULL 
	BEGIN
		IF @cc_name IS NOT NULL
		BEGIN
			EXECUTE @int_result = sp_OAMethod @int_object, 'addCC', NULL, @cc, @cc_name
			IF @int_result <> 0 GOTO CLEANUP
		END
		ELSE
		BEGIN
			EXECUTE @int_result = sp_OAMethod @int_object, 'addCC', NULL, @cc
			IF @int_result <> 0 GOTO CLEANUP
		END
	END
	IF @bcc IS NOT NULL 
	BEGIN
		IF @bcc_name IS NOT NULL
		BEGIN
			EXECUTE @int_result = sp_OAMethod @int_object, 'addBCC', NULL, @bcc, @bcc_name
			IF @int_result <> 0 GOTO CLEANUP
		END
		ELSE
		BEGIN
			EXECUTE @int_result = sp_OAMethod @int_object, 'addBCC', NULL, @bcc
			IF @int_result <> 0 GOTO CLEANUP
		END
	END
	EXECUTE @int_result = sp_OASetProperty @int_object, 'subject', @subject
	EXECUTE @int_result = sp_OASetProperty @int_object, 'body', @body
	IF @file_name IS NOT NULL
	BEGIN
		IF @file_cont IS NOT NULL
		BEGIN
			EXECUTE @int_result = sp_OAMethod @int_object, 'AddAttachment', NULL, @file_name
			IF @int_result <> 0 GOTO CLEANUP
		END
		ELSE
		BEGIN
			EXECUTE @int_result = sp_OAMethod @int_object, 'AddMenAttachment', NULL, @file_name, @file_cont
			IF @int_result <> 0 GOTO CLEANUP
		END
	END
	-- Send Email (returns a recordset)
	EXECUTE @int_result = sp_OAMethod @int_object, 'send', NULL
	GOTO CLEANUP

CLEANUP:
	-- Check whether an error occurred.
	IF @int_result <> 0
	BEGIN
		-- Report the error.
		EXEC sp_OAGetErrorInfo @int_object, @str_source OUT, @str_desc OUT 
		SELECT int_result=convert(varbinary(4),@int_result), Source=@str_source, Description=@str_desc
	END

	-- Destroy the object.
	EXEC @int_result = sp_OADestroy @int_object
	-- Check if an error occurred.
	IF @int_result <> 0 
	BEGIN
		-- Report the error.
		EXEC sp_OAGetErrorInfo @int_object, @str_source OUT, @str_desc OUT 
		SELECT int_result=convert(varbinary(4),@int_result), Source=@str_source, Description=@str_desc
	END
	RETURN

quote:

---

Originally posted by Kristen

You probably need a SET NOCOUNT ON immediately after the "AS"?

---

quote:

---

Originally posted by vsrajan76

try 'SendToQueue'.. instead of 'send' in this line...
sp_OAMethod @int_object, 'send', NULL

---

quote:

---

Originally posted by derrickleggett

What blew it out of the water?

---

quote:

---

Originally posted by derrickleggett

Well, geeee, that's a problem. Tell them you're going to use someone else if they don't grant you EXEC on the sp_OAmethod procs. They'll come around. There's too much competition out there.

---