October 28, 2010 at 11:52 am
hi there,
i am also having problem generating the html. It seems to be working just fine but html data column are empty. for example there are 10 records, it is putting 10 empty rows in there instead of data filled.
November 10, 2010 at 8:56 am
Hey Jeff, I like your simplified T-SQL only solution a lot, I just used it for a small project. Here are a couple small tips for others trying to use the code Jeff posted, you need to specify body_format='HTML' for the send mail call, and you also need to add blanks to the end of all data that could be blank, so that grid lines are rendered in all versions of outlook/internet explorer (it will work in some versions without them). The thing is, you can't use a regular " " space, and you can't use " ", you have to use the alternate space generated by holding down the alt key, typing 255, then releasing the alt key. It looks the same as a regular space but is treated differently by outlook/internet explorer. The column width issues can drive you crazy because people running different machines with different versions of windows/outlook/internet explorer could potentially all see different formatting of the HTML table. If there are good solutions to this issues, please post them. I used OP's SSIS solution a while ago in another project and I can't remember now if that solution suffered from the same formatting problem?? Maybe that is the value added in the (albeit very complicated) SSIS version.
At any rate, here is my working version of the t-sql only solution previously posted (obviously you would have to specify your own table name and query to test:
DECLARE @Body NVARCHAR(MAX),
@Email NVARCHAR(300),
@ccNVARCHAR(300)
-- @FirstName NVARCHAR(15) --If you want to use the recipients name in the email
;
--===== You could get the email address and name from a table or hard code it below
--can use multiple email addresses separated with a ";"
select @EMail = 'myEmail@domain.com'
select @cc = 'someotheremail@domain.com'
--SELECT @FirstName = 'x',
-- FROM #MyEmailTable
-- WHERE ID = @SomeParameter
;
--===== Create the body of the message including a formatted HTML table of query results data
SELECT @Body =
------- Create the table and the table header. (Easily readable HTML)
--You could use a greeting here: <p>Dear ' + @FirstName + ',</p>
--Note: Your table column headings are defined below, you can add spaces ( ) to make the columns wider, but this will only work on certain versions of internet explorer / outlook, some renderers will ignore the spaces.
'
<html>
<body>
<p>The following table shows blah blah blah....</p>
<table border="1" cellspacing="0" style="text-align:center">
<caption>Coverage Policy Exceptions</caption>
<tr style="background-color:AliceBlue"><th>Exception Order</th><th> Exception_Name </th><th>Plan_Name</th><th>Internal_Policy_Nm</th><th>Policy_Nm </th><th> Policy_Nbr </th><th> Status </th><th> Drug_Nm </th><th> Indication </th><th> SubIndication </th><th> Prev_Internal_Policy_Nm </th><th> Prev_Policy_Nm </th><th> Prev_Policy_Nbr </th><th> Prev_Status </th><th> Change_Date_Time </th><th> Changed_By_ID </th><th> Changed_By_Name </th>
</tr>
'
------- Create the rest of the table. Filled in from data in the table.
+ SPACE(8)
+ REPLACE(--This just indents each row to make rows in the HTML apparent and easy to read
CAST((SELECT td = Exception_Order, N'', --<td></td> = "data" element in a row
--You might want to format dates, or convert things to text as in the examples below:
--td = CONVERT(NCHAR(11), OpenToEnrollDate, 106), N'',
--td = CAST(XXX AS NVARCHAR(10)), N''
--You need to add something to the end of any column that could be blank if you want grid lines to appear in all versions of windows/internet explorer/outlook (some versions work fine, others remove grid lines around blank cells)
--Please note, this is NOT a regular space - hold down alt key, type 255 then release alt key, this creates something like a space. A regular space does NOT resolve the grid line problem for SOME versions of windows/internet explorer/outlook.
td = Exception_Name + ' ', N'',
td = [Plan_Name] + ' ', N'',
td = [Internal_Policy_Nm] + ' ', N'',
td = [Policy_Nm] + ' ', N'',
td = [Policy_Nbr] + ' ', N'',
td = [Status] + ' ', N'',
td = [Drug_Nm] + ' ', N'',
td = [Indication] + ' ', N'',
td = [SubIndication] + ' ', N'',
td = [Prev_Internal_Policy_Nm] + ' ', N'',
td = [Prev_Policy_Nm] + ' ', N'',
td = [Prev_Policy_Nbr] + ' ', N'',
td = [Prev_Status] + ' ', N'',
--A date with time can be formated as below
td = replace(convert(varchar(8), [Change_Dt], 10), '-', '/') + ' ' + substring(convert(varchar(20), [Change_Dt], 9), 13, 5) + ' ' + substring(convert(varchar(30), [Change_Dt], 9), 25, 2) + ' ', N'',
td = cast([Change_By] as varchar(6)) + ' ', N'',
td = [Change_By_Name] + ' ', N''
FROM SomeTableName --Add where clause and/or order by clause as needed
FOR XML PATH('tr'),TYPE --<tr></tr> = row encapsulation
)AS NVARCHAR(MAX))
,'</tr>','</tr>'+CHAR(10)+SPACE(8))
------- Finalize the HTML
+ '
</table>
</body>
</html>'
;
-- All set. Send the email. (google sp_send_dbmail if you need help setting it up)
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SpecifyYourProfileNameHere',
@recipients = @EMail,
@copy_recipients = @cc,
@subject = 'Specify Email Subject here',
@body = @Body,
@body_format = 'HTML' --If you don't include this, you will see all HTML tags in the email and it will not render correctly
;
GO
September 13, 2011 at 6:14 pm
Quoting the last sentense of the article:
"Deploy the package on your SSIS server, schedule it as desired and you're done."
How can we schedule the email so that it can be sent, let's say at 3.00 AM every day?
Also, how can we pass parameters, so that the query is executed with the date just before sending the emails.
Thanks,
Marco
October 29, 2011 at 2:46 pm
Great article. Thank you very much.
Could you please tell me how I can add a chart in the report so that I can send a chart besides the table
Thanks in advance,
Marco
October 31, 2011 at 5:07 am
marco.yandun (10/29/2011)
Could you please tell me how I can add a chart in the report so that I can send a chart besides the table
Just click the 'display as chart' option, which is right next to the 'make me a cappucino' check box. 😎
October 31, 2011 at 9:57 am
Hey David,
Could you please clarify where I can find that option?
Thanks,
Marco
November 1, 2011 at 3:09 am
Sorry Marco...I was being sarcastic. Generating a chart in HTML is way outside of the scope of this article / discussion.
As a general guideline, I would advise that you make sure that you understand code before you use it - or else you're going to find yourself out of your depth.
Have you thought about reporting services perhaps, as a sensible way to get charts from SQL Server?
November 1, 2011 at 8:59 am
Yes David,
I thought of it. Using RS is an easy way to create charts. My problem is I need to create the report and send it by email automatically.
I already followed the steps as explained in this excellent article and managed to send the tabular report automatically using SQL Server Agent, but now the new requirement to send a chart besides the tabular report has been asked.
Any advice from you will be highly appreciated,
Marco
November 1, 2011 at 9:54 am
Marco,
You can setup a subscription in SSRS by clicking the "Subscriptions" tab. You can configure it to send an email.
November 1, 2011 at 6:51 pm
Thank you very much Geoffrey for your advice,
Got another different problem now: The Object Datasources just disappear from my Visual Studio 2008 when opening a report. Do you know how can I get it back? last time I had to format my machine and got it for some months until yesterday that I had the same problem. Now I just have formatted it again, but this time, on my second time I edited my project, it just vanished again... this is making me crazy...
Thanks,
Marco
November 2, 2011 at 7:08 am
Is it the "Report Data" window you're missing? It shows you the Built-in Fields, Parameters, Images, Data Sources and Datasets? If so, try Ctrl + Alt + D. Or View > Report Data (at the bottom).
November 2, 2011 at 8:32 am
That's just it Geoffrey!!
Thank you very much for your help. Your advice just saved me a lot of time and headaches 😀
Marco
November 21, 2011 at 12:58 pm
Nice article it got me going with SSIS and E-mail
I have a question is it possible to send Multiple Recipent. If we add recipent e-mail address in the variable "varMailTo" and How??
I Tried with xxx@aaa.com;sss@ss.com but the package failed 1 email works fine.
Thanks again
Steve
November 21, 2011 at 1:24 pm
Yes you can, try with the following code:
Dim varHTMLMail As MailMessage
Dim varSMTPClient As SmtpClient
Dim varMailBody As Object
Dim varAddresses As String
varMailBody = Dts.Variables("varProductionSummaryHTML").Value
varAddresses = Dts.Variables("varMailTo").Value.ToString
varHTMLMail = New MailMessage()
varHTMLMail.From = New MailAddress("name1@xx.com")
varHTMLMail.Subject = "xxxxxxx"
varHTMLMail.Bcc.Add("name2@xx.com")
varHTMLMail.Bcc.Add("name3@xx.com")
varHTMLMail.Body = varMailBody
varHTMLMail.IsBodyHtml = True
varSMTPClient = New SmtpClient("mail.acitus.com") 'Your_SMTP_Server_Name
varSMTPClient.UseDefaultCredentials = True
varSMTPClient.Send(varHTMLMail)
November 21, 2011 at 2:07 pm
Thanks marco.yandun
That will work for me.
Viewing 15 posts - 61 through 75 (of 84 total)
You must be logged in to reply to this topic. Login to reply