Good afternoon, I just developed a project using this code and worked without any problem:

Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim Cmd As ADODB.Command
Dim strconnect As String

strconnect = "Provider=SQLOLEDB;Data Source=SERVERNAME\INSTANCENAME,PORTNUMBER;Initial Catalog=DATABASENAME"
con.Open strconnect, "sa", "SAPASSWORD"
 
Set Cmd = New ADODB.Command
Cmd.ActiveConnection = con
Cmd.CommandType = adCmdStoredProc
Cmd.CommandText = "msdb.dbo.sp_send_dbmail"
Cmd.NamedParameters = True

MORE CODE.....

Cmd.Parameters.Append Cmd.CreateParameter("@profile_name", adVarChar, adParamInput, 50, "PROFILENAME")
Cmd.Parameters.Append Cmd.CreateParameter("@recipients", adVarChar, adParamInput, 200, "EMAIL@ADDRESS.XXX")
Cmd.Parameters.Append Cmd.CreateParameter("@subject", adVarChar, adParamInput, 200, "SUBJECT TEXT")
Cmd.Parameters.Append Cmd.CreateParameter("@body", adVarChar, adParamInput, 1800, "MESSAGE BODY TEXT")
 
Set rs = Cmd.Execute

I used variables for EMAIL@ADDRESS.XXX, SUBJECT TEXT and MESSAGE BODY TEXT and numbers (200, 200 and 1800) are the max length for every one of mentioned fields, if the field exceeds that length you will get an error.