create html email

  • I have a MSSql table that I would like to create an HTML email with totals. The SQL table has 4 fields

    GroupName,Dept,Desc and ExpAmt.  The GroupName would be the high Level then break on Desc and dept within the Desc.

    GroupName  Dept    Desc   ExpAMt

    XZB                  100       Exp     100

    XZB                  300       Exp     400

    XZB                  200      Deb     200

    TFT                   100       Exp       50

    sample output:

    XZB

    exp   100 100

    300  400

    total               500

    XZB

    deb   200 200

    total               200

    TFT      100    50

    total                50

    Grand Total  750

    Can this be coded in PS?

    Thanks.

  • Is there a reason you NEED to do this in powershell?  it feels like it is the wrong tool to me when SQL Server can handle this without the need for a different application jumping in front.

    If Powershell is the route you want (or need) to go, it can be used to pull data from SQL AND can be used to send email, so the answer is yes it can be coded in Powershell.

    I'd recommend doing some research into getting data from SQL Server into Powershell as step 1.  Once you have that working how you want, then sending the email is pretty easy and there are a lot of examples out there.

    Note it does depend on your powershell version which is another reason I'd do it in SQL Server instead.  SQL Server can handle it AND it works with every version of SQL Server that I know of.  Note - I've only tested sending email on 2008 R2 through 2016, but I know it works in 2017 and 2019 and azure and I highly suspect it works in 2005 and 2000 as well.  Heck, it probably works in 7 and 6.5 too.

    Getting the formatting 100% right in SQL may take some trial and error, but I don't see why that couldn't be done.  Probably will result in either a CTE (or multiple) or nested select(s).

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • Before looking at which tool to use to send an email - you need to build the query to return the results that you need.  If you provide sample data and expected results in the form of create/insert statements - we can create a query to return those results.

    For this - you want to look at GROUPING/GROUPING_ID functions with GROUPING SETS/ROLLUP/CUBE.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey Williams is spot-on with his post above.  I'll add that you should post the example data in a "Readily Consumable Format".  Please see the article at the first link in my signature line for one way to do such a thing.  Doesn't matter how you get there, though.  It's the end result that would be much appreciated to help us help you.  Thanks...

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Shifting gears a bit, here' a search that will lead you to many different fairly easy methods of doing simple things in HTML from T-SQL.

    https://duckduckgo.com/?q=create+html+table+in+t-sql&ia=web

    You can easily get a whole lot more sophisticated with things like the application of background colors, etc, based on the value of each "cell" in the output.

    For example... I use the following code to have each machine send me a "Morning Jobs Report".  I install it on each machine so that it can operate autonomously and I add a job on each machine to call the proc every morning at 8:00AM.  Mind you, I do NOT have hundreds of machines.  We've consolidated most of our stuff to just a few big ones.  It gives me something to look at when having coffee in the morning.  😀  It color codes failures in cells as Red and also color codes last run dates as Yellow if more than a week old and Red if more than a month old.  If even changes the color of the last updated date if a job was updated in the last week.

    Note that there is a TODO in the code... look for TODO.  You also obviously need to have email setup and enabled on your SQL Server.  If you' rather not do the later, you could (I suppose) send the results to something else to do the emailing for you.  And, yeah... I could have used CSS in the HTML but I felt that it was an unnecessary bit of extra effort because it's a super simple report that I've not needed to change since the last rev more than 6 years ago.

    Even if you don't use it for it's intended purpose, it's not a bad example of what you can actually do in T-SQL with very little overhead.  And, since I didn't do anything crazy, it's also self-scaling width-wise for most display devices.  If you expect to read it on a smart phone, be advised that I never intended to ever use it on a smart phone and so have not only not tested it on a smart phone, I made no attempts at trying to make it fit the display of a smart phone or any similar small device.

    Also... it's not nearly as long as it looks on this forum... which seems to nearly double-space single spaced lines of code.

    CREATE PROCEDURE [dbo].[MorningReportJobs]
    /**********************************************************************************************************************
    Purpose:

    Create a "Morning Report" of the status of all jobs on the Server/Instance

    Usage:
    EXEC dbo.MorningReportJobs 'somename@somedomain.com'; --Can have many addresses if needed.

    Revision History:
    Rev 00 - 07 Nov 2012 - Jeff Moden - Initial creation and test.
    Rev 01 - 11 Nov 2012 - Jeff Moden - Redaction to more easily avoid the "Null Aggregate" messages.
    Rev 02 - 09 Mar 2013 - Jeff Moden - 1. Use 2 part naming convention
    2. Changes to make @pEmailList take direct email addresses.
    Rev 03 - 11 Apr 2013 - Jeff Moden - Add fix for "-" as a RunDate
    Rev 04 - 28 Dec 2015 - Jeff Moden - 1. Added color corrections for old NextRunDates
    2. Added correction for IsScheduled
    3. Added color if job modified in previous 7 days
    4. Added/colorized the Last Run Date column.
    **********************************************************************************************************************/
    --=====================================================================================================================
    -- Presets
    --=====================================================================================================================
    --===== Declare the I/O for this procedure
    @pEMailList VARCHAR(MAX)
    AS

    --===== Environmental presets
    SET NOCOUNT ON;
    SET ANSI_WARNINGS OFF;

    --===== Declare the local variables
    DECLARE @Server SYSNAME,
    @From VARCHAR(8000),
    @To VARCHAR(MAX),
    @Subject VARCHAR(8000),
    @Body VARCHAR(MAX)
    ;
    --=====================================================================================================================
    -- Create the address parts of the email
    --=====================================================================================================================
    --===== Create the parts of the email.
    SELECT @Server = @@SERVERNAME,
    @From = @Server + '@domain.com', --TODO! CHANGE THE DOMAIN NAME TO WHAT YOUR DOMAIN IS FOR THIS SERVER!
    @To = @pEMailList,
    @Subject = 'Morning Report: ' + QUOTENAME(@Server) + ' Jobs'
    ;
    --=====================================================================================================================
    -- Create the HTML body of the email
    --=====================================================================================================================
    --===== Create the BODY of the email as HTML
    WITH
    cteJobInfo AS
    (
    SELECT job.job_id,
    JobName = job.name,
    IsEnabled = CASE WHEN job.[enabled] = 1 THEN 'Yes' ELSE 'No' END,
    IsScheduled = CASE
    WHEN ot.FreqType = 1 THEN 'One Time'
    WHEN ot.IsScheduled > 0 THEN 'Yes'
    ELSE 'No'
    END,
    LastRunDate = lr.LastRunDate, --Added by Rev 04
    NextRunDate = CONVERT(CHAR(16),nxt.SSDateTime,120),
    LastModified = REPLACE(CONVERT(CHAR(19),job.date_modified,111),'/','-')
    FROM msdb.dbo.sysjobs job
    LEFT OUTER JOIN msdb.dbo.sysjobschedules sch
    ON job.job_id = sch.job_id
    OUTER APPLY util.IntsToSSDateTime(sch.next_run_date,sch.next_run_time) nxt
    OUTER APPLY (SELECT ss.freq_type, ss.enabled --Rev 04 added ss.enabled
    FROM msdb.dbo.sysschedules ss
    WHERE ss.schedule_id = sch.schedule_id) ot (FreqType,IsScheduled) --Rev 04 added IsScheduled
    OUTER APPLY (SELECT CONVERT(CHAR(16),MAX(SSDateTime),120) --Sub-Query Added by Rev 04
    FROM msdb.dbo.sysjobsteps stp
    CROSS APPLY util.IntsToSSDateTime(stp.last_run_date,stp.last_run_time)
    WHERE stp.job_id = job.job_id) lr (LastRunDate)
    )
    ,
    cteJobHistory AS
    (
    SELECT hist.job_id,
    MinRunDate = CONVERT(CHAR(16),MIN(run.SSDateTime),120),
    MaxRunDate = CONVERT(CHAR(16),MAX(run.SSDateTime),120),
    MaxDuration = CONVERT(CHAR(8),MAX(dur.SSDateTime),114),
    TotalRuns = CONVERT(VARCHAR(10),COUNT(*)),
    Passed = CONVERT(VARCHAR(10),SUM(CASE WHEN hist.run_status = 1 THEN 1 ELSE 0 END)),
    Failed = CONVERT(VARCHAR(10),SUM(CASE WHEN hist.run_status = 0 THEN 1 ELSE 0 END)),
    Retried = CONVERT(VARCHAR(10),SUM(CASE WHEN hist.run_status = 2 THEN 1 ELSE 0 END)),
    Cancelled = CONVERT(VARCHAR(10),SUM(CASE WHEN hist.run_status = 3 THEN 1 ELSE 0 END))
    FROM msdb.dbo.sysjobhistory hist
    OUTER APPLY util.IntsToSSDateTime(hist.run_date,hist.run_time) run
    OUTER APPLY util.IntsToSSDateTime(19000101,hist.run_duration) dur --19000101 is the "zero date"
    WHERE run.SSDateTime >= DATEADD(mi,-1445,GETDATE()) --Finds last 24 hours and 5 minutes
    AND hist.step_id = 0
    GROUP BY hist.job_id
    )
    ,
    cteAssembleReport AS
    (
    SELECT jobinfo.JobName,
    jobinfo.IsEnabled,
    jobinfo.IsScheduled,
    jobinfo.LastRunDate, --Added by Rev 04
    NextRunDate = ISNULL(jobinfo.NextRunDate,'-'),
    MinRunDate = ISNULL(jobhist.MinRunDate,'-'),
    MaxRunDate = ISNULL(jobhist.MaxRunDate,'-'),
    MaxDuration = ISNULL(jobhist.MaxDuration,'-'),
    TotalRuns = ISNULL(jobhist.TotalRuns,'-'),
    Passed = ISNULL(jobhist.Passed,'-'),
    Failed = ISNULL(jobhist.Failed,'-'),
    Retried = ISNULL(jobhist.Retried,'-'),
    Cancelled = ISNULL(jobhist.Cancelled,'-'),
    jobinfo.LastModified
    FROM cteJobInfo jobinfo
    LEFT OUTER JOIN cteJobHistory jobhist
    ON jobinfo.job_id = jobhist.job_id
    )
    --===== Create the starting tags, table definition, title caption for the table, and column headers.
    SELECT @Body =
    '
    <html>
    <Body>
    <table border="1" cellpadding = "4" cellspacing="0" style="font-family:arial; color:black; font-size:10pt;">
    <caption style=" border:1; background-color:White; font-weight:bold; font-size:14pt;">
    Morning Report - 24 Hour Job Summary for ' + @@SERVERNAME + '<br>
    ~ ' + CONVERT(CHAR(17),GETDATE(),113) + ' ~
    </caption>
    <tr style="background-color:Blue; color:White; ">
    <th>Job Name</th>
    <th>Is Enabled</th>
    <th>Is Scheduled</th>
    <th>Last Run Date</th>
    <th>Next Run Date</th>
    <th>Min Run Date</th>
    <th>Max Run Date</th>
    <th>Max Duration</th>
    <th>Total Runs</th>
    <th>Passed</th>
    <th>Failed</th>
    <th>Retried</th>
    <th>Cancelled</th>
    <th>Last Modified</th>
    </tr>
    --'
    + REPLACE(REPLACE(CAST(
    ( --=== This formats each column as HTML. CASE statements provide changes in background color.
    SELECT
    ---------------------------------------------------------------------------------------------------------------
    = 'left', td = JobName,'',
    ---------------------------------------------------------------------------------------------------------------
    = CASE WHEN IsEnabled = 'Yes' THEN 'background-color:White;' ELSE 'background-color:Yellow;' END,
    = 'center', td = IsEnabled,'',
    ---------------------------------------------------------------------------------------------------------------
    = CASE
    WHEN IsScheduled ='Yes' THEN 'background-color:White;'
    WHEN IsScheduled ='One Time' THEN 'background-color:DarkOrange;'
    ELSE 'background-color:Yellow;'
    END,
    = 'center', td = IsScheduled,'',
    ---------------------------------------------------------------------------------------------------------------
    --Added by Rev 04
    = CASE
    WHEN LastRunDate < DATEADD(dd,-30,GETDATE()) THEN 'background-color:Red;'
    WHEN LastRunDate < DATEADD(dd, -7,GETDATE()) THEN 'background-color:Yellow;'
    ELSE 'background-color:White;'
    END,
    = 'center', td = LastRunDate,'',
    ---------------------------------------------------------------------------------------------------------------
    = CASE
    WHEN NextRunDate = '-' THEN 'background-color:White;' -- Rev 03
    WHEN IsScheduled = 'One Time' AND NextRunDate < DATEADD(dd,-30,GETDATE())
    THEN 'background-color:Red;'
    WHEN IsScheduled = 'One Time' AND NextRunDate < DATEADD(dd,-7,GETDATE())
    THEN 'background-color:Yellow;'
    WHEN NextRunDate < DATEADD(dd,-30,GETDATE()) THEN 'background-color:Red;' --Added by Rev 04
    WHEN NextRunDate < DATEADD(dd, -7,GETDATE()) THEN 'background-color:Yellow;' --Added by Rev 04
    ELSE 'background-color:White;'
    END,
    = 'center', td = NextRunDate,'',
    ---------------------------------------------------------------------------------------------------------------
    = 'center', td = MinRunDate,'',
    ---------------------------------------------------------------------------------------------------------------
    = 'center', td = MaxRunDate,'',
    ---------------------------------------------------------------------------------------------------------------
    = 'center', td = MaxDuration,'',
    ---------------------------------------------------------------------------------------------------------------
    = CASE
    WHEN IsScheduled = 'Yes' AND TotalRuns = '0'
    THEN 'background-color:Yellow;'
    ELSE 'background-color:White;'
    END,
    = 'right', td = TotalRuns,'',
    ---------------------------------------------------------------------------------------------------------------
    = CASE WHEN Passed = TotalRuns THEN 'background-color:White;' ELSE 'background-color:Red;' END,
    = 'right', td = Passed,'',
    ---------------------------------------------------------------------------------------------------------------
    = CASE WHEN Failed IN ('0','-') THEN 'background-color:White;' ELSE 'background-color:Red;' END,
    = 'right', td = Failed,'',
    ---------------------------------------------------------------------------------------------------------------
    = CASE WHEN Retried IN ('0','-') THEN 'background-color:White;' ELSE 'background-color:Red;' END,
    = 'right', td = Retried,'',
    ---------------------------------------------------------------------------------------------------------------
    = CASE WHEN Cancelled IN ('0','-')THEN 'background-color:White;' ELSE 'background-color:Red;' END,
    = 'right', td = Cancelled,'',
    ---------------------------------------------------------------------------------------------------------------
    --Style added by Rev 04
    = CASE WHEN LastModified <= GETDATE()-7 THEN 'background-color:White;' ELSE 'background-color:LightGreen;' END,
    = 'center', td = LastModified,''
    FROM cteAssembleReport
    ORDER BY JobName
    FOR XML PATH('tr'), TYPE --This concatenates everything nicely in <tr></tr> tags for each row in the final table.
    )
    --===== This formats the HTML for human readability for troubleshooting purposes. (End of CAST and REPLACE's)
    AS VARCHAR(MAX)), '</tr>','</tr>'+CHAR(13)+CHAR(10)), '<tr>',SPACE(12)+'<tr>')
    + --=== This finalizes the starting tags.
    ' </table>
    </body>
    </html>
    '
    ;
    --=====================================================================================================================
    -- Send the email
    --=====================================================================================================================
    --===== Send the email.
    EXEC MSDB.dbo.sp_send_dbmail
    @from_address = @From,
    @recipients = @To,
    @subject = @Subject,
    @body = @Body,
    @body_format = 'HTML'
    ;
    GO

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Here is a template I use for simple table based 'reports' sent from SQL Server.  This can be used directly in an agent job - or used in a stored procedure.  The first thing that is needed is a query that returns the 'detail' rows to be displayed in the report, then you can define the headers to match.

        Set Nocount On;

    Declare @body nvarchar(max)
    , @xmlResults varchar(max)
    , @tableHeader varchar(max)
    , @recipients varchar(max) = 'user1@domain.com'
    , @cc_recipients varchar(max) = 'user2@domain.com;user3@domain.com';

    --==== Create the table header
    Set @tableHeader = cast((Select html.hdr1 As th, ''
    , html.hdr2 As th, ''
    , html.hdr3 As th, ''
    , html.hdr4 As th, ''
    , html.hdr5 As th, ''
    , html.hdr6 As th, ''
    From (
    Values ('Header1', 'Header2', 'Header3', 'Header4', 'Header5', 'Header6')
    ) As html(hdr1, hdr2, hdr3, hdr4, hdr5, hdr6)
    For xml Path('tr'), elements) As varchar(max));

    --==== Get the results as an XML table
    Set @xmlResults = cast(( Select t.Detail1 As td, ''
    , t.Detail2 As td, ''
    , t.Detail3 As td, ''
    , t.Detail4 As td, ''
    , t.Detail5 As td, ''
    , t.Detail6 As td, ''
    From dbo.MyTable
    For xml Path('tr'), elements) As varchar(max));

    --==== Send Notification if we have results
    If @xmlResults Is Not Null
    Begin

    --==== Setup the CSS style of the message
    Set @body = '<style type=''text/css''>';
    Set @body += '{font-family:Tahoma, Arial, Helvetica, sans-serif; font-smoothing:always; width:100%; border-collapse:collapse;}';
    Set @body += 'td {font-size:9pt; text-align:center; border:1px DarkCyan solid; padding:1px 5px 1px 5px;}';
    Set @body += 'th {font-size:10pt; text-align:center; padding:2px 5px 2px 5px; background-color:DarkCyan; color:White;}';
    Set @body += 'name tr {color:Black; background-color:DarkCyan;}';
    Set @body += '</style>'

    --==== Setup the body of the message
    Set @body += '<html><body>

    Simple paragraph description - can build out as needed

    ';

    --==== Setup the table with the header and details
    Set @body += '<table style="margin-left:30px">' + @tableHeader + @xmlResults + '</table>';

    --==== Close the body and html
    Set @body += '</body></html>';

    --==== Send the HTML formatted email message
    Execute msdb.dbo.sp_send_dbmail
    @profile_name = 'PrivateOrPublicProfileName'
    , @from_address = 'SomeAddress@somedomain.com'
    , @reply_to = 'AValidReplyToAddress@somedomain.com'
    , @recipients = @recipients
    , @copy_recipients = @cc_recipients
    , @subject = 'Subject'
    , @body_format = 'HTML'
    , @body = @body;

    End
    Go

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Here is a simple template for sending an attachment in 'Excel' format.  The trick here is to tell Excel that the delimiter in the CSV file is a comma so it will open correctly when you open the file directly from email.  You can define your delimiter as anything - a pipe, caret, semi-colon, etc. and Excel will open the file as a 'CSV' file.

        Set Nocount On;

    Declare @query nvarchar(max) = ''
    , @recipients varchar(max) = 'user1@domain.com'
    , @cc_recipients varchar(max) = 'user2@domain.com';

    Set @query = '
    Set Nocount On;

    Select t.Column1 As [Sep=,' + char(13) + char(10) + 'Column1]
    , t.Column2
    , t.Column3
    , t.Column4
    , t.Column5
    , t.Column6
    From dbo.SomeTable;'

    Execute msdb.dbo.sp_send_dbmail
    @profile_name = 'PublicOrPrivateProfile'
    , @query = @query
    , @subject = 'Subject'
    , @body = 'Body Message Here'
    , @recipients = @recipients
    , @copy_recipients = @cc_recipients
    , @execute_query_database = 'UserDatabase'
    , @attach_query_result_as_file = 1
    , @query_result_width = 8000
    , @query_attachment_filename = 'Filename.csv'
    , @query_result_header = 1
    , @query_result_separator = ','
    , @query_result_no_padding = 1;

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply