CRM 4.0 stores emails tracked from Outlook as HTML within the database. So, when you open up a tracked email activity within CRM, it looks exactly the same as it would in Outlook. Unfortunately this causes some problems when you try to query the CRM database for some custom SQL reporting services reports. Any queries that use the description column from the FilteredEmail or FilteredActivity views will come up as pure HTML, befuddling users when they open up a report.
The function below uses regular expressions to parse out the HTML tags that are an eyesore for your report users, leaving behind just the text of the email message.
To call your function within the layout page of your report, use the expression "=code.RemoveHTML(Fields!Description.Value)" where Description is the name of the column that holds the HTML email message body.
Function RemoveHTML( strText ) strText = System.Text.RegularExpressions.Regex.Replace(strText, "\n", " ") strText = System.Text.RegularExpressions.Regex.Replace(strText, "( )+", " ") strText = System.Text.RegularExpressions.Regex.Replace(strText, "<( )*head([^>])*>","<head>", System.Text.RegularExpressions.RegexOptions.IgnoreCase) strText = System.Text.RegularExpressions.Regex.Replace(strText, "(<( )*(/)( )*head( )*>)","", System.Text.RegularExpressions.RegexOptions.IgnoreCase) strText = System.Text.RegularExpressions.Regex.Replace(strText, "(<head>).*()",string.Empty, System.Text.RegularExpressions.RegexOptions.IgnoreCase) strText = System.Text.RegularExpressions.Regex.Replace(strText, "<( )*script([^>])*>","<script>", System.Text.RegularExpressions.RegexOptions.IgnoreCase) strText = System.Text.RegularExpressions.Regex.Replace(strText, "(<( )*(/)( )*script( )*>)","</script>", System.Text.RegularExpressions.RegexOptions.IgnoreCase) strText = System.Text.RegularExpressions.Regex.Replace(strText, "(<script>).*(</script>)",string.Empty, System.Text.RegularExpressions.RegexOptions.IgnoreCase) strText = System.Text.RegularExpressions.Regex.Replace(strText, "<( )*style([^>])*>","<style>", System.Text.RegularExpressions.RegexOptions.IgnoreCase) strText = System.Text.RegularExpressions.Regex.Replace(strText, "(<( )*(/)( )*style( )*>)","</style>", System.Text.RegularExpressions.RegexOptions.IgnoreCase) strText = System.Text.RegularExpressions.Regex.Replace(strText, "(<style>).*(</style>)",string.Empty, System.Text.RegularExpressions.RegexOptions.IgnoreCase) strText = System.Text.RegularExpressions.Regex.Replace(strText, "<( )*br( )*>",vbCrLf, System.Text.RegularExpressions.RegexOptions.IgnoreCase) strText = System.Text.RegularExpressions.Regex.Replace(strText, "<( )*li( )*>",vbCrLf, System.Text.RegularExpressions.RegexOptions.IgnoreCase) strText = System.Text.RegularExpressions.Regex.Replace(strText, "<( )*div([^>])*>",vbCrLf, System.Text.RegularExpressions.RegexOptions.IgnoreCase) strText = System.Text.RegularExpressions.Regex.Replace(strText, "<( )*tr([^>])*>",vbCrLf, System.Text.RegularExpressions.RegexOptions.IgnoreCase) strText = System.Text.RegularExpressions.Regex.Replace(strText, "<( )*p([^>])*>",vbCrLf, System.Text.RegularExpressions.RegexOptions.IgnoreCase) strText = System.Text.RegularExpressions.Regex.Replace(strText, "<[^>]*>",string.Empty, System.Text.RegularExpressions.RegexOptions.IgnoreCase) strText = System.Text.RegularExpressions.Regex.Replace(strText, " "," ", System.Text.RegularExpressions.RegexOptions.IgnoreCase) strText = System.Text.RegularExpressions.Regex.Replace(strText, "•"," * ", System.Text.RegularExpressions.RegexOptions.IgnoreCase) strText = System.Text.RegularExpressions.Regex.Replace(strText, "<","<", System.Text.RegularExpressions.RegexOptions.IgnoreCase) strText = System.Text.RegularExpressions.Regex.Replace(strText, ">",">", System.Text.RegularExpressions.RegexOptions.IgnoreCase) strText = System.Text.RegularExpressions.Regex.Replace(strText, ""","""", System.Text.RegularExpressions.RegexOptions.IgnoreCase) strText = System.Text.RegularExpressions.Regex.Replace(strText, "‹","<", System.Text.RegularExpressions.RegexOptions.IgnoreCase) strText = System.Text.RegularExpressions.Regex.Replace(strText, "›",">", System.Text.RegularExpressions.RegexOptions.IgnoreCase) strText = System.Text.RegularExpressions.Regex.Replace(strText, "™","(tm)", System.Text.RegularExpressions.RegexOptions.IgnoreCase) strText = System.Text.RegularExpressions.Regex.Replace(strText, "⁄","/", System.Text.RegularExpressions.RegexOptions.IgnoreCase) strText = System.Text.RegularExpressions.Regex.Replace(strText, "&","&", System.Text.RegularExpressions.RegexOptions.IgnoreCase) strText = System.Text.RegularExpressions.Regex.Replace(strText, "–","–", System.Text.RegularExpressions.RegexOptions.IgnoreCase) strText = System.Text.RegularExpressions.Regex.Replace(strText, "“","""", System.Text.RegularExpressions.RegexOptions.IgnoreCase) strText = System.Text.RegularExpressions.Regex.Replace(strText, "”","""", System.Text.RegularExpressions.RegexOptions.IgnoreCase) strText = System.Text.RegularExpressions.Regex.Replace(strText, "‘","'", System.Text.RegularExpressions.RegexOptions.IgnoreCase) strText = System.Text.RegularExpressions.Regex.Replace(strText, "’","'", System.Text.RegularExpressions.RegexOptions.IgnoreCase) RemoveHTML = strText End FunctionNavigate to the layout tab of your report and right-click the whitespace around the report to modify the properties. Place the code inside the Code tab of the Report Properties as shown below.
To call your function within the layout page of your report, use the expression "=code.RemoveHTML(Fields!Description.Value)" where Description is the name of the column that holds the HTML email message body.
