Thanks for the help everyone! I figured out the answer to my problem
and figured I'd post it for future reference :)

Only using 1 # in #topten creates a local temp table and I needed to
create a global temp table by using ##topten. The local worked in SQL
server because I was using it within the same query window, but it
seems that submitting it through VBA submits it as if it were two
different query windows.

Also, this modified version of the code below worked. I'm sure
there's a cleaner version of the code to use, but this did work.

Sub EmailSQL3()
Dim qt As QueryTable

sqlstr = "select top 10 * into ##topten from dim_emailtemplete"

connstring = _
"ODBC;DSN=test;UID=;PWD=;Database=Kpirepositor y"

With ActiveSheet.QueryTables.Add(Connection:=connstring ,
Destination:=Range("A1"), SQL:=sqlstr).Refresh
End With

sqlstr = "select top 10 * from ##topten"

connstring = _
"ODBC;DSN=test;UID=;PWD=;Database=Kpirepositor y"

With ActiveSheet.QueryTables.Add(Connection:=connstring ,
Destination:=Range("A1"), SQL:=sqlstr).Refresh
End With

End Sub