Stored procedure doesn't recognize table created

Question

I am trying to email the results of a query to myself in the case that someone else is running a stored procedure and an error occurs. This script is part of a larger program that generates schema, tables, view, jobs dynamically.

I have configured SQL Server according to this database mail article from SQL Shack.

Below is the stored procedure that causes the error:

CREATE OR ALTER PROCEDURE Ido.ConfirmDataTypesMapped
    @ido_and_propertyNames nvarchar(1000)
AS
BEGIN
    DECLARE @idoName nvarchar(100) = (SELECT TRIM(SUBSTRING(@ido_and_propertyNames, 0, CHARINDEX(',', @ido_and_propertyNames))))
    
    -- if count > 0 then column requested has data type that is not mapped
    IF (SELECT COUNT(*) 
        FROM Ido.TypeConversionMap map 
        RIGHT JOIN
            (SELECT DISTINCT DataType 
             FROM ido.IdoProperties 
             WHERE PropertyName IN (SELECT TRIM(value) 
                                    FROM STRING_SPLIT(@ido_and_propertyNames, ',') 
                                    WHERE TRIM(value) <> @idoName 
                                      AND TRIM(Value) <> 'NULL')
               AND CollectionName = @idoName) idoDataTypes ON map.CsiDatatype = idoDataTypes.DataType
            where map.DbDataType is null
        ) > 0
        begin
    
            create table MissingDataTypes (DataTypes nvarchar(1000))
    
    -- inserts the missing data types onto a comma-separated single line
            insert into MissingDataTypes (DataTypes) values 
            
                (
                    (select string_agg(idoDataTypes.DataType, ', ') DataTypes from Ido.TypeConversionMap map 
                    right join (
                                    select distinct DataType from ido.IdoProperties where PropertyName in
                                    (
                                    select trim(value) from string_split(@ido_and_propertyNames, ',') where trim(value) <> @idoName and trim(Value) <> 'NULL'
                                    )
                                    and CollectionName = @idoName
                                ) idoDataTypes on map.CsiDatatype = idoDataTypes.DataType
                
                    where map.DbDataType is null)
                )
    
    -- email the missing data types
            exec msdb.dbo.sp_send_dbmail
            @profile_name = '___',
            @recipients = '___',
            @body = '___',
            @subject = '___',
            @from_address = '___',
            @reply_to = '___',
            @query = 'select * from MissingDataTypes;'
    
            drop table MissingDataTypes
    
        end
    end

I get this error:

Msg 22050, Level 16, State 1, Line 53
Failed to initialize sqlcmd library with error number -2147467259.

Tracing the procedure reveals:

exec sp_executesql      N'INSERT sysmail_query_transfer(uid, text_data) VALUES((@P1),
(@P2))',N'@P1 nchar(72), @P2 ntext',   N'E840B6A4-132F-430F-B821-64961EF5744C  ',
N'Msg 208, Level 16, State 1, Server ___,   Line 1 Invalid object name  
''MissingDataTypes''.'

I am explicitly creating the table in the procedure.

Why is this not working? Thanks