Embed an Crystal Export into Email Body

[pontupo]

I've got an ASP script using a report document that allows me to launch the web page at a scheduled time and export my report to disk as a PDF. I can then grab the PDF and attach it to an email and send it out. This is great for sending daily updates to our staff. I've received a request, however, to embed the report results into the email body, rather than as an attachment. So I've been fooling around a bit and have been able to export to HTML, but haven't come up with a way to take an HTML file and place it into the body of the email. Is there an easy way to do this? Do I need to ExportToStream() and if so, how would this work? Since emails can be HTML, all I really want to do is grab the content of the HTML file I've created and throw it into the body of the email. Ideas? Thanks in advance,

Pont

 

[pontupo]

Answered my own question:

The best that I could come up with was to use a System.IO.FileStream and parse the HTML export from Crystal, placing the entire contents in a single String which was then assigned to the Body of the Mail object. This worked very well. The only mild concern that I have is that the String object is going to be limited by size (so presumably is the body of a Mail object since it's a string) so I hope that Crystal doesn't put out any HTML that hits that limit.

Pont

 

[tjw2006]

Hi, any chance you could share some of the ASP code for running a Crystal Report from? I've been trying to find a way to run a Crystal Report straight from a ASP page, preferably in PDF format - but have been having terrible trouble trying to get anything to work.

Thanks,
Tim

 

[IdoMillet]

Pont, keep in mind that if the report contains charts or images, you end up with extra files besides the main HTML export. So you may need to do some extra work to correctly reference & embed these files in the HTML email message.

I have this functionality in my Visual CUT software. The main limitation is that while the the recipient is happy to see the Crystal report -- including charts, images, and logos -- inside the email message, if they decide to forward the message to someone else, formatting typically gets lost.

Also, if the email recipient uses Outlook 2007, they may have trouble viewing the message (Outlook 2007 is a step back in terms of HTML rendering capabilities).

- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[pontupo]

IdoMillet: I've also noticed the difficulties you mentioned concerning viewing/forwarding. This seems to be mainly due to the limitations of the Microsoft Word HTML parser, which is commonly used (you can turn it off, I think) for email composition with Outlook and is also used to view emails with some clients.

As for the images, I have just kept the reports very simple so that this process works. I will, however, keep this in mind if I end up having to make some more complicated reports.

Tim: I'll post some of the code I use in a moment.

Pont

 

[pontupo]

Here's the code that I use:

Option Strict On
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
'Imports System.Diagnostics
Imports System.Net.Mail

Partial Class _Default
    Inherits System.Web.UI.Page

    Private report As String
    Private reportDoc As ReportDocument
    Private reportDB As Database
    Private reportTables As Tables
    Private reportTable As Table
    Private reportLogOnInfo As TableLogOnInfo
    Private dateToday As Date = Date.Today
    Private subreports As Subreports
    Private subreport As ReportDocument
    Private reportPath As String = Server.MapPath("reports\")
    Private exportPath As String
    Private sessionString As String

    'the on-load function merely launches the ConfigureCrystalReport() method
    'that method will do any actual setup that's required.
    Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init
        GetReportInformation()
        ConfigureCrystalReport()
        ExportSetup()
        ExportReport()
        EmailReport()
    End Sub

    'just build a string to reference the report location
    Private Sub GetReportInformation()
        report = reportPath & Request.QueryString("report") & ".rpt"
        sessionString = Request.QueryString("report")
        exportPath = Server.MapPath("exports\")
    End Sub

    'checks that the report file exists at the expected location of report
    'then tries to load the report to make sure it's a valid report document
    Public Function IsReportValid() As Boolean
        Dim reportIsValid As Boolean = False
        Try
            If (System.IO.File.Exists(report)) Then 'does the file exist?
                'if it does, try to load it to confirm it's a valid crystal report
                Dim tryReportLoad As New CrystalDecisions.CrystalReports.Engine.ReportDocument()
                tryReportLoad.Load(report)
                tryReportLoad.Close()
                reportIsValid = True
            End If
        Catch ex As Exception
            reportIsValid = False
        End Try

        Return reportIsValid
    End Function

'our configuration method. Controls the session setup, loading of report, setting of
    'connection information, etc.
    Private Sub ConfigureCrystalReport()
        If (Session(sessionString) Is Nothing) Then 'if the report isn't in the session (first load)
            reportDoc = New ReportDocument()
            'load the report document
            If (IsReportValid()) Then
                reportDoc.Load(report) 'web server
            Else
                Response.Redirect("error.aspx")
            End If

            'authenticate to the database
            reportDB = reportDoc.Database 'get the database
            reportTables = reportDB.Tables 'get the tables 
            'iterate through all of the tables, setting the correct login information for all of them
            For Each reportTable In reportTables
                reportLogOnInfo = reportTable.LogOnInfo 'get the LogOnInfo object
                If (reportLogOnInfo.ConnectionInfo.ServerName = "server") Then
                    'reportLogOnInfo.ConnectionInfo.ServerName = "server"
                    'reportLogOnInfo.ConnectionInfo.DatabaseName = "database" 'specify the database
                    'reportLogOnInfo.ConnectionInfo.UserID = "user" 'specify the user name
                    reportLogOnInfo.ConnectionInfo.Password = "pass" 'specify the password
                ElseIf (reportLogOnInfo.ConnectionInfo.ServerName = "server2") Then
                    'reportLogOnInfo.ConnectionInfo.ServerName = "server2" 
                    'reportLogOnInfo.ConnectionInfo.DatabaseName = "database" 'specify the database
                    'reportLogOnInfo.ConnectionInfo.UserID = "user" 'specify the user name
                    reportLogOnInfo.ConnectionInfo.Password = "pass" 'specify the password
                End If
                reportTable.ApplyLogOnInfo(reportLogOnInfo) 'save the changes
            Next reportTable

            'iterate through all sub-reports and authenticate there as well
            For Each subreport In reportDoc.Subreports
                reportDB = subreport.Database 'get the database
                reportTables = reportDB.Tables 'get the tables 
                'iterate through all of the tables, setting the correct login information for all of them
                For Each reportTable In reportTables
                    reportLogOnInfo = reportTable.LogOnInfo 'get the LogOnInfo object
                    If (reportLogOnInfo.ConnectionInfo.ServerName = "server") Then
                        'reportLogOnInfo.ConnectionInfo.ServerName = "server" 
                        'reportLogOnInfo.ConnectionInfo.DatabaseName = "database" 'specify the database
                        'reportLogOnInfo.ConnectionInfo.UserID = "user" 'specify the user name
                        reportLogOnInfo.ConnectionInfo.Password = "pass" 'specify the password
                    ElseIf (reportLogOnInfo.ConnectionInfo.ServerName = "server2") Then
                        'reportLogOnInfo.ConnectionInfo.ServerName = "server2" 
                        'reportLogOnInfo.ConnectionInfo.DatabaseName = "database" 'specify the database
                        'reportLogOnInfo.ConnectionInfo.UserID = "user" 'specify the user name
                        reportLogOnInfo.ConnectionInfo.Password = "pass" 'specify the password
                    End If
                    reportTable.ApplyLogOnInfo(reportLogOnInfo) 'save the changes
                Next reportTable
            Next

            Session(sessionString) = reportDoc 'save the report to the session
        Else
            reportDoc = CType(Session(sessionString), ReportDocument) 'otherwise load the report
        End If

            Stage7DetailViewer.DisplayGroupTree = False 'hide the group tree
            Stage7DetailViewer.ReportSource = reportDoc 'set the viewer source to the report object
    End Sub

'Setup the file export. Basically we want to make sure the export path is valid
    Public Sub ExportSetup()
        'Note:   If you want to place the Exported folder within the Web directory of your Web server, 
        'prefix the folder name with the Request.PhysicalApplicationPath property. (from VS help)
        
        If Not System.IO.Directory.Exists(exportPath & sessionString) Then 'check if the directory exists
            System.
End Sub

'This function exports the report to disk. Initially, this was setup to export to .pdf
    'however, HTML embedded into an email was requested instead. 
    Public Sub ExportReport()
        'this is for exporting to PDF
        'Dim myFileName As String = ""
        'myFileName = exportPath & "OrderManagementReport_" & Format(dateToday, "MMddyyyy") & ".pdf"
        'stage7Detail.ExportToDisk(ExportFormatType.PortableDocFormat, myFileName)
        'export to HTML
        Dim myExportOptions As ExportOptions
        myExportOptions = reportDoc.ExportOptions()
        If (Request.QueryString("export") = "htm") Then
            myExportOptions.ExportDestinationType = ExportDestinationType.DiskFile
            myExportOptions.ExportFormatType = ExportFormatType.HTML40
            Dim html40FormatOptions As HTMLFormatOptions = New HTMLFormatOptions()
            html40FormatOptions.HTMLBaseFolderName = exportPath
            'exportPath & "Html40Folder"
            html40FormatOptions.HTMLFileName = sessionString & "_" & Format(dateToday, "MMddyyyy") & ".htm"
            html40FormatOptions.HTMLEnableSeparatedPages = False
            html40FormatOptions.HTMLHasPageNavigator = False
            'html32FormatOptions.FirstPageNumber = 1
            'html32FormatOptions.LastPageNumber = 3
            html40FormatOptions.UsePageRange = False
            myExportOptions.FormatOptions = html40FormatOptions
            reportDoc.Export()
        ElseIf (Request.QueryString("export") = "mobile") Then
            myExportOptions.ExportDestinationType = ExportDestinationType.DiskFile
            myExportOptions.ExportFormatType = ExportFormatType.HTML32
            Dim html32FormatOptions As HTMLFormatOptions = New HTMLFormatOptions()
            html32FormatOptions.HTMLBaseFolderName = exportPath
            'exportPath & "Html40Folder"
            html32FormatOptions.HTMLFileName = sessionString & "_" & Format(dateToday, "MMddyyyy") & ".htm"
            html32FormatOptions.HTMLEnableSeparatedPages = False
            html32FormatOptions.HTMLHasPageNavigator = False
            'html32FormatOptions.FirstPageNumber = 1
            'html32FormatOptions.LastPageNumber = 3
            html32FormatOptions.UsePageRange = False
            myExportOptions.FormatOptions = html32FormatOptions
            reportDoc.Export()
        End If

    End Sub

'this function emails the report. Assumes it is located in the same directory as this
    'script and that it is titled "OrderManagementReport_<date>.pdf"
    Public Sub EmailReport()
        Dim message As New MailMessage()
        message.From = New MailAddress("webmanager@blahblah.com")
        message.To.Add(New MailAddress(Request.QueryString("email") & "@blahblah.com"))
        message.Subject = sessionString & " for " & Format(dateToday, "MM/dd/yyyy")
        If (Request.QueryString("export") = "htm") Then
            message.Body = getFileAsString(exportPath & sessionString & "\" & _
                                            sessionString & "_" & Format(dateToday, "MMddyyyy") & _
                                                ".htm")
        ElseIf (Request.QueryString("export") = "mobile") Then
            message.Body = getHTMLAsText(getFileAsString(exportPath & sessionString & "\" & _
                                                sessionString & "_" & Format(dateToday, "MMddyyyy") & _
                                                        ".htm"))
        End If
        'Dim data As New Attachment(exportPath & sessionString & "\" & _
        'sessionString & "_" & Format(dateToday, "MMddyyyy") _
        ' & "." & Request.QueryString("export"))
        'message.Attachments.Add(data)

        message.Priority = MailPriority.Normal
        'Dim data As New Attachment("C:\Documents and Settings\cfry\Desktop\Web_Reports\AutomaticExportStage7Detail\AutomaticDailyStage7-Detail\" & "OrderManagementReport_" & Format(dateToday, "MMddyyyy") & ".htm")
        'message.Attachments.Add(data)
        If (Request.QueryString("export") = "htm") Then
            message.IsBodyHtml = True
        Else
            message.IsBodyHtml = False
        End If
        'message.BodyEncoding = System.Text.UTF8Encoding.UTF8

        Dim SmtpMail As New SmtpClient()
        SmtpMail.Host = "mail.carnegielearning.com"
        SmtpMail.Send(message)
        'data.Dispose() 'this line is required to free the attachment, if there is one
    End Sub

 'This function takes the location of a file and parses the entire contents into a single string
    'I'm not entirely sure about what would happen if the file was really really large, so this 
    'function probably has a limit to the files it will handle.
    
Private Function getFileAsString(ByVal file As String) As String
        Dim reader As System.IO.FileStream
        Try
            reader = New System.IO.FileStream(file, IO.FileMode.Open)
        Catch e As Exception
            Response.Redirect("error.aspx")
        End Try

        Dim resultString As String = ""
        Dim b(1024) As Byte
        Dim temp As UTF8Encoding = New UTF8Encoding(True)

        Do While reader.Read(b, 0, b.Length) > 0
            resultString = resultString & temp.GetString(b)
            Array.Clear(b, 0, b.Length)
        Loop

        reader.Close()
        Return resultString
    End Function

Private Function getHTMLAsText(ByVal html As String) As String
        Dim result As String = ""

        '// Remove HTML Development formatting
        '// Replace line breaks with space
        '// because browsers inserts space
        result = html.Replace("\r", String.Empty)
        '// Replace line breaks with space
        '// because browsers inserts space
        result = result.Replace("\n", String.Empty)
        result = result.Replace(vbNewLine, String.Empty)
        '// Remove step-formatting
        result = result.Replace("\t", String.Empty)
        '// Remove repeating speces becuase browsers ignore them
        result = System.Text.RegularExpressions.Regex.Replace(result, _
                                                              "( )+", String.Empty)

        '// Remove the header (prepare first by clearing attributes)
        result = System.Text.RegularExpressions.Regex.Replace(result, _
                 "<( )*head([^>])*>", String.Empty, _
                 System.Text.RegularExpressions.RegexOptions.IgnoreCase)
        result = System.Text.RegularExpressions.Regex.Replace(result, _
                 "(<( )*(/)( )*head( )*>)", String.Empty, _
                 System.Text.RegularExpressions.RegexOptions.IgnoreCase)
        result = System.Text.RegularExpressions.Regex.Replace(result, _
                 "(<head>).*(</head>)", String.Empty, _
                 System.Text.RegularExpressions.RegexOptions.IgnoreCase)

        '// remove all scripts (prepare first by clearing attributes)
        result = System.Text.RegularExpressions.Regex.Replace(result, _
                 "<( )*script([^>])*>", String.Empty, _
                 System.Text.RegularExpressions.RegexOptions.IgnoreCase)
        result = System.Text.RegularExpressions.Regex.Replace(result, _
                 "(<( )*(/)( )*script( )*>)", String.Empty, _
                 System.Text.RegularExpressions.RegexOptions.IgnoreCase)
        '//result = System.Text.RegularExpressions.Regex.Replace(result, 
        '//         @"(<script>)([^(<script>\.</script>)])*(</script>)",
        '//         string.Empty, 
        '//         System.Text.RegularExpressions.RegexOptions.IgnoreCase);
        result = System.Text.RegularExpressions.Regex.Replace(result, _
                 "(<script>).*(</script>)", String.Empty, _
                 System.Text.RegularExpressions.RegexOptions.IgnoreCase)


        'remove the table tags and add in a line break at a <table> tag
        'added by CF - 9/17/2007
        '// remove all scripts (prepare first by clearing attributes)
        'result = System.Text.RegularExpressions.Regex.Replace(result, _
        ' "<( )*table([^>])*>", String.Empty, _
        '  System.Text.RegularExpressions.RegexOptions.IgnoreCase)
        ' result = System.Text.RegularExpressions.Regex.Replace(result, _
        '   "(<( )*(/)( )*table( )*>)", String.Empty, _
        ' System.Text.RegularExpressions.RegexOptions.IgnoreCase)
        '//result = System.Text.RegularExpressions.Regex.Replace(result, 
        '//         @"(<script>)([^(<script>\.</script>)])*(</script>)",
        '//         string.Empty, 
        '//         System.Text.RegularExpressions.RegexOptions.IgnoreCase);
        result = System.Text.RegularExpressions.Regex.Replace(result, _
                 "(<table>).*(</table>)", String.Empty, _
                 System.Text.RegularExpressions.RegexOptions.IgnoreCase)

        'remove the title
        result = System.Text.RegularExpressions.Regex.Replace(result, _
         "(<title>).*(</title>)", String.Empty, _
         System.Text.RegularExpressions.RegexOptions.IgnoreCase)


        '// remove all styles (prepare first by clearing attributes)
        result = System.Text.RegularExpressions.Regex.Replace(result, _
                 "<( )*style([^>])*>", String.Empty, _
                 System.Text.RegularExpressions.RegexOptions.IgnoreCase)
        result = System.Text.RegularExpressions.Regex.Replace(result, _
                 "(<( )*(/)( )*style( )*>)", String.Empty, _
                 System.Text.RegularExpressions.RegexOptions.IgnoreCase)
        result = System.Text.RegularExpressions.Regex.Replace(result, _
                 "(<style>).*(</style>)", String.Empty, _
                 System.Text.RegularExpressions.RegexOptions.IgnoreCase)

        '// insert tabs in spaces of <td> tags
        result = System.Text.RegularExpressions.Regex.Replace(result, _
                 "<( )*td([^>])*>", String.Empty, _
                 System.Text.RegularExpressions.RegexOptions.IgnoreCase)

        '// insert line breaks in places of <BR> and <LI> tags
        result = System.Text.RegularExpressions.Regex.Replace(result, _
                 "<( )*br( )*>", String.Empty, _
                 System.Text.RegularExpressions.RegexOptions.IgnoreCase)
        result = System.Text.RegularExpressions.Regex.Replace(result, _
                 "<( )*li( )*>", String.Empty, _
                 System.Text.RegularExpressions.RegexOptions.IgnoreCase)

        '// insert line paragraphs (double line breaks) in place
        '// if <P>, <DIV> and <TR> tags
        result = System.Text.RegularExpressions.Regex.Replace(result, _
                 "<( )*div([^>])*>", String.Empty, _
                 System.Text.RegularExpressions.RegexOptions.IgnoreCase)
        result = System.Text.RegularExpressions.Regex.Replace(result, _
                 "<( )*tr([^>])*>", vbNewLine, _
                 System.Text.RegularExpressions.RegexOptions.IgnoreCase)
        result = System.Text.RegularExpressions.Regex.Replace(result, _
                 "<( )*p([^>])*>", String.Empty, _
                 System.Text.RegularExpressions.RegexOptions.IgnoreCase)

        '// Remove remaining tags like <a>, links, images,
        '// comments etc - anything thats enclosed inside < >
        result = System.Text.RegularExpressions.Regex.Replace(result, _
                 "<[^>]*>", String.Empty, _
                 System.Text.RegularExpressions.RegexOptions.IgnoreCase)

        '// replace special characters:
        result = System.Text.RegularExpressions.Regex.Replace(result, _
                 "&nbsp;", " ", _
                 System.Text.RegularExpressions.RegexOptions.IgnoreCase)

        result = System.Text.RegularExpressions.Regex.Replace(result, _
                 "&bull;", " * ", _
                 System.Text.RegularExpressions.RegexOptions.IgnoreCase)
        result = System.Text.RegularExpressions.Regex.Replace(result, _
                 "&lsaquo;", "<", _
                 System.Text.RegularExpressions.RegexOptions.IgnoreCase)
        result = System.Text.RegularExpressions.Regex.Replace(result, _
                 "&rsaquo;", ">", _
                 System.Text.RegularExpressions.RegexOptions.IgnoreCase)
        result = System.Text.RegularExpressions.Regex.Replace(result, _
                 "&trade;", "(tm)", _
                 System.Text.RegularExpressions.RegexOptions.IgnoreCase)
        result = System.Text.RegularExpressions.Regex.Replace(result, _
                 "&frasl;", String.Empty, _
                 System.Text.RegularExpressions.RegexOptions.IgnoreCase)
        result = System.Text.RegularExpressions.Regex.Replace(result, _
                 "<", "<", _
                 System.Text.RegularExpressions.RegexOptions.IgnoreCase)
        result = System.Text.RegularExpressions.Regex.Replace(result, _
                 ">", ">", _
                 System.Text.RegularExpressions.RegexOptions.IgnoreCase)
        result = System.Text.RegularExpressions.Regex.Replace(result, _
                 "&copy;", "(c)", _
                 System.Text.RegularExpressions.RegexOptions.IgnoreCase)
        result = System.Text.RegularExpressions.Regex.Replace(result, _
                 "&reg;", "(r)", _
                 System.Text.RegularExpressions.RegexOptions.IgnoreCase)
        '// Remove all others. More can be added, see
        '// [URL unfurl="true"]http://hotwired.lycos.com/webmonkey/reference/special_characters/[/URL]
        result = System.Text.RegularExpressions.Regex.Replace(result, _
                 "&(.{2,6});", String.Empty, _
                 System.Text.RegularExpressions.RegexOptions.IgnoreCase)

        '// for testng
        '//System.Text.RegularExpressions.Regex.Replace(result, 
        '//       this.txtRegex.Text,string.Empty, 
        '//       System.Text.RegularExpressions.RegexOptions.IgnoreCase);

        '// make line breaking consistent
        'result = result.Replace("\n", "\r")

        '// Remove extra line breaks and tabs:
        '// replace over 2 breaks with 2 and over 4 tabs with 4. 
        '// Prepare first to remove any whitespaces inbetween
        '// the escaped characters and remove redundant tabs inbetween linebreaks
        result = System.Text.RegularExpressions.Regex.Replace(result, _
                 "(\r)( )+(\r)", String.Empty, _
                 System.Text.RegularExpressions.RegexOptions.IgnoreCase)
        result = System.Text.RegularExpressions.Regex.Replace(result, _
                 "(\t)( )+(\t)", String.Empty, _
                 System.Text.RegularExpressions.RegexOptions.IgnoreCase)
        result = System.Text.RegularExpressions.Regex.Replace(result, _
                 "(\t)( )+(\r)", String.Empty, _
                 System.Text.RegularExpressions.RegexOptions.IgnoreCase)
        result = System.Text.RegularExpressions.Regex.Replace(result, _
                 "(\r)( )+(\t)", String.Empty, _
                 System.Text.RegularExpressions.RegexOptions.IgnoreCase)
        '// Remove redundant tabs
        result = System.Text.RegularExpressions.Regex.Replace(result, _
                 "(\r)(\t)+(\r)", String.Empty, _
                 System.Text.RegularExpressions.RegexOptions.IgnoreCase)
        '// Remove multible tabs followind a linebreak with just one tab
        result = System.Text.RegularExpressions.Regex.Replace(result, _
                 "(\r)(\t)+", String.Empty, _
                 System.Text.RegularExpressions.RegexOptions.IgnoreCase)
        '// Initial replacement target string for linebreaks
        Dim breaks As String = vbNewLine & vbNewLine '\r
        '// Initial replacement target string for tabs
        'Dim tabs As String = vbCr '\t
        'Dim lineBreaks As String = vbLf '\r\n

        For index As Integer = 0 To result.Length
            result = result.Replace(breaks, vbNewLine)
            'result = result.Replace(tabs, String.Empty)
            'result = result.Replace(lineBreaks, String.Empty)
            'breaks = breaks + "\r"
            'tabs = tabs + "\t"
        Next

        '// Thats it.
        Return result
    End Function

A couple of notes:
1) I have two data sources where my reports may be directed, which is why you see "server" and "server2", etc. in the authentication section. The way that I handled the situation is for the time being just hardcoding two different servers, but this is not at all ideal. This script is for a small company and won't be used long-term. =)
2) The report name is passed in the URL like so: ?report=CrystalReport and the export format export=html and an email address to send it to like email=blahblah@blahblah.com
3) This supports two different export formats (I have a seperate set of scripts for hosting an Intranet portal for viewing. This is just for auto-exporting): html and text. There is also the necessary lines for doing PDF, they are just currently commented out. To get text, I use regular expressions to parse the HTML output (thanks for text exporting in the CrystalReportsViewer object Business Objects *rolls eyes*) removing all tags. This is so that I can attach an email that will be readable on Blackberries/mobile devices which was requested immediately after I got the HTML working. Credit for the bulk of the HTML parsing is due to someone else (I'm sorry, I can't remember who =/), but I modified it as necessary to suit my purposes and the particular style of HTML that Crystal produces.