Your query just needed aliases. 
A fixed version, along with an alternative which may perform better (you should test, of course)

SELECT * FROM #tblContacts c
JOIN #tblEmails e ON c.ContactID = e.ContactID
WHERE e.EmailAddress NOT IN (SELECT EmailAddress FROM #tblNoEmail)

SELECT * FROM #tblContacts c
JOIN #tblEmails e ON c.ContactID = e.ContactID
WHERE NOT EXISTS (SELECT 1 FROM #tblNoEmail ne WHERE ne.EmailAddress = e.EmailAddress)