SQL Job Fails - Why? This is my log - if I run the procedure locally it will still perform as it should and generate the message, but when it is run as a SQL Job it fails.  (For security I have changed the server name and database names)

12/07/2015 14:30:00,Send DBMail,Error,1,Hellfire,Send DBMail,Run Stored Procedure,,Executed as user: NT AUTHORITY\SYSTEM. 
Mail queued. [SQLSTATE 01000] (Message 0)  
Mail queued. [SQLSTATE 01000] (Message 0)  
Mail queued. [SQLSTATE 01000] (Message 0)  
Mail queued. [SQLSTATE 01000] (Message 0)  
1234 [SQLSTATE 01000] (Message 0)  
5678 [SQLSTATE 01000] (Message 0)  
11111 [SQLSTATE 01000] (Message 0)  
erqe [SQLSTATE 01000] (Message 0)  
ssssss [SQLSTATE 01000] (Message 0)  
ffff [SQLSTATE 01000] (Message 0)  
Subquery returned more than 1 value. This is not permitted when the subquery follows =&lt;c/&gt; !=&lt;c/&gt; &lt;&lt;c/&gt; &lt;= &lt;c/&gt; &gt;&lt;c/&gt; &gt;= or when the subquery is used as an expression. 
The step failed.,00:05:10,16,0,,,,0


EDIT
The user account NT Authority\System does not seem to be the issue as this account has no problem sending other messages using the same settings.  It is just this one procedure that errors out (will provide procedure code shortly)

EDIT # 2
This is the sql syntax I am using (may not make much sense as again I changed sensative data to random garbage details).

Set @fromDate = '12/01/2015'
Set @toDate = '12/05/2015'

Declare @query varchar(MAX), @name varchar(50), @toDate datetime, @fromDate datetime, @FQN varchar(500)
Create Table #problemtable (name varchar(50))
Create Table #PD (name varchar(50), LQ int)
    INSERT INTO #Datttta (nameName)
    SELECT DISTINCT(emp)
    FROM server.database.dbo.one
    ORDER BY emp ASC
        DECLARE C1 CURSOR FOR
            SELECT name FROM #Datttta           
            OPEN C1
            FETCH NEXT FROM C1 INTO @name               
            WHILE @@FETCH_STATUS = 0
            BEGIN
                SET @FQN = (SELECT fqn FROM fulldatabasemapping WHERE rciaem = @name)
                SET @Query =    'update #Datttta '
                            + 'set onetwothree = '
                            + '(select count(*) from '+@FQN+' '
                            + 'where terminationcategory in (select '
                            + 'termcat from '
                            + 'server.database.dbo.twotwo'
                            + 'where emp in '
                            + '(''' +@name+ ''')) '
                            + 'and CAST(termdate As Date) between ''' + CONVERT(VARCHAR(10),@fromDate,101) + ''' '
                            + 'and ''' + CONVERT(VARCHAR(10),@toDate,101) + ''' '
                            + 'and managersignoff is null) '
                        Begin Try
                            exec (@query)                               
                        End Try                         
                        Begin Catch
                            insert into #problemtable 
                            (name) select @name
                        End Catch   

                    FETCH NEXT FROM C1 INTO @name       
            END         
            CLOSE C1
            DEALLOCATE C1

--Send mail piece is here
As per the error message, more than a single row is being returned in a context where that is not permitted.

Looking at your query, the following line could easily be the culprit:

SET @FQN = (SELECT fqn FROM fulldatabasemapping WHERE rciaem = @name);


You can eliminate that as the cause of the problem by modifying that line to:

SET @FQN = (SELECT TOP(1) fqn FROM fulldatabasemapping WHERE rciaem = @name)