Validate email address will be accepted by sp_send_dbmail?

How do you validate that a text string is a valid email address which will be accepted by the sp_send_dbmail function?

I've looked through other questions like this one, which yes works great, until a user copies their email address from outlook and comes through like Jane Doe <DoeJ@xdomain.com>, which fails to send via the system proc.

I also want users to be able to supply multiple emails in a single string separated by semicolons, which are accepted by sp_send_dbmail. Thanks!

You can try this (there are other ways),

--PARAM:START
DECLARE @EmailList varchar(max);

SET @EmailList = 'Jane Doe <DoeJ@xdomain.com>;info.support@mssqltips.com;.info@mssqltips.com;
info..@mssqltips.com;info@mssqltips.c;info@support@mssqltips.com;info.support@mssql_tips.com;
+info@mssqltips.com;info Support@mssqltips.com;info@mssql tips.com;NULL;22@mssqltips.com;@mssqltips.com';
--PARAM:END

--VALIDATION:START
DECLARE @EmailTable TABLE
(
    Email varchar(max),
    IsValid bit
);

INSERT INTO @EmailTable(Email, IsValid)
SELECT LTRIM(RTRIM(value))
,CASE WHEN LTRIM(RTRIM(value)) = '' THEN 0
        WHEN LTRIM(RTRIM(value)) LIKE '% %' THEN 0
        WHEN LTRIM(RTRIM(value)) LIKE ('%["(),:;<>\]%') THEN 0
        WHEN SUBSTRING(LTRIM(RTRIM(value)),CHARINDEX('@',LTRIM(RTRIM(value))),LEN(LTRIM(RTRIM(value)))) LIKE ('%[!#$%&*+/=?^`_{|]%') THEN 0
        WHEN (LEFT(LTRIM(RTRIM(value)),1) LIKE ('[-_.+]') OR RIGHT(LTRIM(RTRIM(value)),1) LIKE ('[-_.+]')) THEN 0                                                                                    
        WHEN (LTRIM(RTRIM(value)) LIKE '%[%' or LTRIM(RTRIM(value)) LIKE '%]%') THEN 0
        WHEN LTRIM(RTRIM(value)) LIKE '%@%@%' THEN 0
        WHEN LTRIM(RTRIM(value)) NOT LIKE '_%@_%._%' THEN 0
        ELSE 1 
    END
FROM STRING_SPLIT(@EmailList, ';');
--VALIDATION:END

--GET VALID EMAIL
DECLARE @ValidEmailList varchar(max);

SELECT @ValidEmailList = COALESCE(@ValidEmailList + ', ', '') + Email
FROM @EmailTable
WHERE IsValid = 1;

--DO SENDING OF EMAIL USING ValidEmailList

Source: Valid Email Address Check with TSQL