To add onto Luis's suggestion:

WITH SampleData AS (

SELECT test = 'my email is Test@email.com.au how do i capture just the email' UNION ALL

SELECT 'Some invalid@email addresses can also be removed like this' UNION ALL

SELECT 'Some invalid @email addresses can also be removed like this' UNION ALL

SELECT 'Some invalid email@. addresses can also be removed like this' UNION ALL

SELECT 'Some invalid email@@address.can can also be removed like this' UNION ALL

SELECT 'No email address in this string'

)

SELECT test, item

FROM SampleData a

CROSS APPLY dbo.DelimitedSplit8K(test, ' ') b

WHERE CHARINDEX('@', item) < CHARINDEX('.', item) AND CHARINDEX('@', item) > 1 AND

CHARINDEX('.', item) < LEN(item) AND LEN(item) - 1 = LEN(REPLACE(item, '@', ''));

Etc.