How to send email from MySQL 5.1

Question

I'm trying to send an email to several recipients when a new row is inserted into a table. The list of recipients varies. I would like to be able to set this list using a select statement. I also have installed Navicat which allows me to send email notifications but only to a predetermined set of people.

Thanks.

Answer 1

If you have an SMTP service running, you can outfile to the drop directory. If you have high volume, you may result with duplicate file names, but there are ways to avoid that.

Otherwise, you will need to create a UDF.

Here's a sample trigger solution:

CREATE TRIGGER test.autosendfromdrop BEFORE INSERT ON test.emaildrop FOR EACH ROW BEGIN       /* START THE WRITING OF THE EMAIL FILE HERE*/             SELECT  concat("To: ",NEW.To),               concat("From: ",NEW.From),               concat("Subject: ",NEW.Subject),               NEW.Body           INTO OUTFILE                     "C:\\inetpub\\mailroot\\pickup\\mail.txt"                FIELDS TERMINATED by '\r\n' ESCAPED BY '';             END; 

To markup the message body you will need something like this...

CREATE FUNCTION `HTMLBody`(Msg varchar(8192))      RETURNS varchar(17408) CHARSET latin1 DETERMINISTIC BEGIN   declare tmpMsg varchar(17408);   set tmpMsg = cast(concat(       'Date: ',date_format(NOW(),'%e %b %Y %H:%i:%S -0600'),'\r\n',       'MIME-Version: 1.0','\r\n',       'Content-Type: multipart/alternative;','\r\n',       ' boundary=\"----=_NextPart_000_0000_01CA4B3F.8C263EE0\"','\r\n',       'Content-Class: urn:content-classes:message','\r\n',       'Importance: normal','\r\n',       'Priority: normal','\r\n','','\r\n','','\r\n',       'This is a multi-part message in MIME format.','\r\n','','\r\n',       '------=_NextPart_000_0000_01CA4B3F.8C263EE0','\r\n',       'Content-Type: text/plain;','\r\n',       '  charset=\"iso-8859-1\"','\r\n',       'Content-Transfer-Encoding: 7bit','\r\n','','\r\n','','\r\n',       Msg,       '\r\n','','\r\n','','\r\n',       '------=_NextPart_000_0000_01CA4B3F.8C263EE0','\r\n',       'Content-Type: text/html','\r\n',       'Content-Transfer-Encoding: 7bit','\r\n','','\r\n',       Msg,       '\r\n','------=_NextPart_000_0000_01CA4B3F.8C263EE0--'       ) as char);   RETURN tmpMsg; END ;