Index: openacs-4/packages/acs-mail/sql/postgresql/acs-mail-nt-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-mail/sql/postgresql/acs-mail-nt-create.sql,v
diff -u -r1.2 -r1.3
--- openacs-4/packages/acs-mail/sql/postgresql/acs-mail-nt-create.sql	30 Aug 2001 19:30:31 -0000	1.2
+++ openacs-4/packages/acs-mail/sql/postgresql/acs-mail-nt-create.sql	6 Sep 2001 19:26:27 -0000	1.3
@@ -43,11 +43,34 @@
 	v_revision_id		cr_revisions.revision_id%TYPE;
 	v_message_id		acs_mail_queue_messages.message_id%TYPE;
 	v_header_to_rec		record;
+	v_creation_user		acs_objects.creation_user%TYPE;
 begin
 	if p_max_retries <> 0 then
 	   raise EXCEPTION '' -20000: max_retries parameter not implemented.'';
 	end if;
 
+	-- get the sender email address
+	select max(email) into v_header_from from parties where party_id = p_party_from;
+
+	-- if sender address is null, then use site default OutgoingSender
+	if v_header_from is null then
+	   	select apm__get_value(package_id, ''OutgoingSender'') into v_header_from
+		from apm_packages where package_key=''acs-kernel'';
+	end if;
+
+	-- make sure that this party is in users table. If not, let creation_user
+	-- be null to prevent integrity constraint violations on acs_objects
+	select max(user_id) into v_creation_user 
+      from users where user_id = p_party_from;
+
+	-- get the recipient email address
+	select max(email) into v_header_to from parties where party_id = p_party_to;
+
+	-- do not let any of these addresses be null
+	if v_header_from is null or v_header_to is null then
+	   raise EXCEPTION '' -20000: acs_mail_nt: cannot sent email to blank address or from blank address.'';
+	end if;
+
 	-- create a mail body with empty content
 
 	select acs_mail_body__new (
@@ -63,7 +86,7 @@
 		null,				   -- p_content_item_id
 		''acs_mail_body'',	   -- p_object_type
 		now(),				   -- p_creation_date
-		null,				   -- p_creation_user
+		v_creation_user,	   -- p_creation_user
 		null,				   -- p_creation_ip
 		null				   -- p_context_id
 	) into v_body_id;
@@ -94,17 +117,11 @@
 		v_body_id,				-- p_body_id
 		null,					-- p_context_id
 		now(),					-- p_creation_date
-		null,					-- p_creation_user
+		v_creation_user,		-- p_creation_user
 		null,					-- p_creation_ip
 		''acs_mail_link''		-- p_object_type
 	) into v_message_id;
 
-	-- get the sender email address
-	select email into v_header_from from parties where party_id = p_party_from;
-
-	-- get the recipient email address
-	select email into v_header_to from parties where party_id = p_party_to;
-
 	-- now put the message into the outgoing queue
 	-- i know this seems redundant, but that''s the way it was built
 	-- the idea is that you put a generic message into the main queue
@@ -129,7 +146,7 @@
 
 			insert into acs_mail_queue_outgoing
 			( message_id, envelope_from, envelope_to )
-			valuues
+			values
 			( v_message_id, v_header_from, v_header_to_rec.email );
 		end loop;