October 31, 2016 at 11:34 am
I can't figure out how to pass a small amount of text (40 characters or so) to dbmail from vba to show up in the body. It only seems to show up in the subject line. Nothing worked until I put the "space" parameter in and then I can only get text to show up in subject line.
Initialize the ADO command to run the "sp_Senddbmail" Stored Procedure
Dim objCom As New ADODB.Command
'Initialize the parameters for the command
Set objProfileName = objCom.CreateParameter("@Profile_Name", adVarChar, adParamInput, 40, "workflow")
Set objParamReceipient = objCom.CreateParameter("@Recipients", adVarChar, adParamInput, 40, "user@somewhare.com") ' Email Subject line
Set objParamSubject = objCom.CreateParameter("@Subject", adVarChar, adParamInput, 20, "Subject Value") ' Email Body subject
Set objParamSpace = objCom.CreateParameter("@Space", adVarChar, adParamInput, 1, ",") ' Email Body subject
Set objParamBody = objCom.CreateParameter("@Body", adVarChar, adParamInput, 40, "Email_Body") ' Email Body text
objCom.CommandText = "msdb.dbo.sp_send_dbmail" 'Specify Stored Procedure name
objCom.CommandType = adCmdStoredProc 'Specify command type as Stored Procedure
objCom.Parameters.Append objProfileName 'Specify Stored Procedure input parameter
objCom.Parameters.Append objParamReceipient 'Specify Stored Procedure input parameter
objCom.Parameters.Append objParamSpace 'Specify Stored Procedure input parameter
objCom.Parameters.Append objParamSubject 'Specify Stored Procedure input parameter
objCom.Parameters.Append objParamBody 'Specify Stored Procedure input parameter
objCom.ActiveConnection = "PROVIDER=SQLOLEDB.10;Server=RHNT13S\DAPS;Database=DAPS;User ID=daps_user;Password=password;Trusted_Connection=False;"
'Execute Stored Procedure
objCom.Execute
October 31, 2016 at 11:57 am
ken.schilstra 74493 (10/31/2016)
I can't figure out how to pass a small amount of text (40 characters or so) to dbmail from vba to show up in the body. It only seems to show up in the subject line. Nothing worked until I put the "space" parameter in and then I can only get text to show up in subject line.Initialize the ADO command to run the "sp_Senddbmail" Stored Procedure
Dim objCom As New ADODB.Command
'Initialize the parameters for the command
Set objProfileName = objCom.CreateParameter("@Profile_Name", adVarChar, adParamInput, 40, "workflow")
Set objParamReceipient = objCom.CreateParameter("@Recipients", adVarChar, adParamInput, 40, "user@somewhare.com") ' Email Subject line
Set objParamSubject = objCom.CreateParameter("@Subject", adVarChar, adParamInput, 20, "Subject Value") ' Email Body subject
Set objParamSpace = objCom.CreateParameter("@Space", adVarChar, adParamInput, 1, ",") ' Email Body subject
Set objParamBody = objCom.CreateParameter("@Body", adVarChar, adParamInput, 40, "Email_Body") ' Email Body text
objCom.CommandText = "msdb.dbo.sp_send_dbmail" 'Specify Stored Procedure name
objCom.CommandType = adCmdStoredProc 'Specify command type as Stored Procedure
objCom.Parameters.Append objProfileName 'Specify Stored Procedure input parameter
objCom.Parameters.Append objParamReceipient 'Specify Stored Procedure input parameter
objCom.Parameters.Append objParamSpace 'Specify Stored Procedure input parameter
objCom.Parameters.Append objParamSubject 'Specify Stored Procedure input parameter
objCom.Parameters.Append objParamBody 'Specify Stored Procedure input parameter
objCom.ActiveConnection = "PROVIDER=SQLOLEDB.10;Server=RHNT13S\DAPS;Database=DAPS;User ID=daps_user;Password=password;Trusted_Connection=False;"
'Execute Stored Procedure
objCom.Execute
I must admit it's been quite a long time since I've done anything like this, but what's objParamSpace for? The sp_send_dbmail doesn't have a parameter named @space.
October 31, 2016 at 1:10 pm
I have no clue what the "space" does. If I remove it from the object I get the result that "subject, body and one other item" is required. If I put in "space" which is a comma the call to dbmail works.
October 31, 2016 at 1:14 pm
Ken, I really wish I could help you but it's been way too long since I've done anything like this from VBA.
October 31, 2016 at 1:18 pm
Thanks at least for looking.
May 26, 2017 at 2:27 pm
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.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply