insert lines breaks for emailed information

[slobad23]

i have a script that was designed for our company. it sends information out to our lawyers by email each week to let them know if they are within their limits. (trying to be as vague as possible and still get the point across!)

This is what the script looks like (sorry for adding it all but i want to make sure i dont miss anything relevant from the post)

<code>
<package>
<job id="LOLAlert">
<script language="VBScript">
<![CDATA[
Const intAlertPercent = 90
Const strSubject = "Warning Message"
Const strCopyTo = "example@example.com"

'Do Not Alter anything below here unless you KNOW what you are doing!
Const blnDebug = false
Const strConnectDB = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=X:\Shared\DJB DB\DJB Case Management System.mdb"
Const cdoSendUsingMethod = _
"

http://schemas.microsoft.com/cdo/configuration/sendusing"

Const cdoSendUsingPort = 2
Const cdoSMTPServer = _
"

http://schemas.microsoft.com/cdo/configuration/smtpserver"

Const cdoSMTPServerPort = _
"

http://schemas.microsoft.com/cdo/configuration/smtpserverport"

Const cdoSMTPConnectionTimeout = _
"

http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout"

Const cdoSMTPAuthenticate = _
"

http://schemas.microsoft.com/cdo/configuration/smtpauthenticate"

Const cdoBasic = 1
Const cdoSendUserName = _
"

http://schemas.microsoft.com/cdo/configuration/sendusername"

Const cdoSendPassword = _
"

http://schemas.microsoft.com/cdo/configuration/sendpassword"

Dim strQueryString, strEmailBody, strLOLAlerts
Dim objConn, objRS
strLOLAlerts = ""
Set objConn = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")
If blnDebug Then wscript.echo "Starting LOLAlert"
objConn.Open strConnectDB
strQueryString = "SELECT [Case Profile Design].CLIENT, [Case Profile Design].OUR_REFERE, [Case Profile Design].LAWYER, Sum([MOD C2].LIMIT_OF_L) AS SumOfLIMIT_OF_L" & _
" FROM [Case Profile Design] LEFT JOIN [MOD C2] ON [Case Profile Design].OUR_REFERE = [MOD C2].OUR_REF" & _
" GROUP BY [Case Profile Design].LAWYER,[Case Profile Design].CLIENT, [Case Profile Design].OUR_REFERE, [Case Profile Design].STATUS_OF_, [Case Profile Design].CLIENT" & _
" HAVING ((([Case Profile Design].STATUS_OF_)=""O""))" & _
" ORDER BY [Case Profile Design].LAWYER, [Case Profile Design].OUR_REFERE;"
objRS.Open strQueryString, objConn
If objRS.EOF Then
If blnDebug Then wscript.echo "No records returned!"
Else
strLawyer = objRS("LAWYER")
Do While Not objRS.EOF
If blnDebug Then wscript.echo "Ref:" & objRS("OUR_REFERE")
If ( ( NOT(strLawyer = objRS("LAWYER") ) ) AND ( NOT(strLOLAlerts = "") ) ) Then
If blnDebug Then wscript.echo strLawyer & vbCrLf & strLOLAlerts
fncEmail strLawyer, strSubject, strLOLAlerts
strLOLAlerts = ""
End If
strLOLAlerts = strLOLAlerts & fncCheckLOL(objRS("CLIENT"), objRS("OUR_REFERE"), objRS("LAWYER"), objRS("SumOfLIMIT_OF_L"))
strLawyer = objRS("LAWYER")
objRS.MoveNext
Loop
End if
objRS.Close
wscript.quit

Function fncEmail(strLawyer, strSubject, strLOLAlerts)
Dim objRS, strQueryString, strEmail, dblCosts
Set objRS = CreateObject("ADODB.Recordset")
strQueryString = "SELECT strEmail FROM Lawyer WHERE [Lawyer Name] = """ & strLawyer & """;"
If blnDebug Then wscript.echo strQueryString
objRS.Open strQueryString, objConn
strEmail = objRS("strEmail")
objRS.Close
If Len(strLOLAlerts) > 0 Then
Dim objMail
Dim objConfig ' As CDO.Configuration
Dim Fields ' As ADODB.Fields

' Get a handle on the config object and it's fields
Set objConfig = CreateObject("CDO.Configuration")
Set Fields = objConfig.Fields

' Set config fields we care about
With Fields
.Item(cdoSendUsingMethod) = cdoSendUsingPort
.Item(cdoSMTPServer) = "djbserver02"
.Item(cdoSMTPServerPort) = 25
.Item(cdoSMTPConnectionTimeout) = 10
.Update
End With
Set objMail = CreateObject("CDO.Message")
Set objMail.Configuration = objConfig
objMail.From = "example@example.com"
objMail.To = strEmail
objMail.Cc = strCopyTo
objMail.Subject = strSubject
objMail.TextBody = strLOLAlerts
If blnDebug Then wscript.echo "Sending Mail To: " & strEmail & vbCrLf
If blnDebug Then wscript.echo "Subject: " & strSubject & vbCrLf
If blnDebug Then wscript.echo "Email Body: " & vbCrLf & strLOLAlerts
objMail.Send
Set objMail = Nothing
End If
End Function

Function fncCheckLOL(strCLIENT, strRef, strLawyer, dblLimit)
Dim objRS, strQueryString, strWarning, dblCosts
dblCosts = 0
Set objRS = CreateObject("ADODB.Recordset")
strQueryString = "SELECT * FROM qry_Billed WHERE Matter = """ & strRef & """;"
objRS.Open strQueryString, objConn
If NOT objRS.EOF Then dblCosts = objRS("SumOfCost")
objRS.Close
strQueryString = "SELECT * FROM qry_WIP WHERE Matter = """ & strRef & """;"
objRS.Open strQueryString, objConn
If NOT objRS.EOF Then dblCosts = dblCosts + objRS("SumOfCost")
objRS.Close
If dblCosts > dblLimit*intAlertPercent/100 Then
If dblCosts > dblLimit Then
strWarning = " has EXCEEDED "
ElseIf dblCosts = dblLimit Then
strWarning = " is at "
Else
strWarning = " is close to "
End If
fncCheckLOL = "Case number " & strRef & " for client " & strCLIENT & _
strWarning & "LOL (Limit = " & FormatCurrency(dblLimit) & ", current costs= " & _
FormatCurrency(dblCosts) & ")" & vbCrLf
Else
fncCheckLOL = ""
End If
End Function
]]>
</script>
</job>
</package>
</code>

(if you have read all this, i thank you very much for your time!!)

the problem is, that when it sends the emails out, the format looks like this:

Case number xxxxx for client xxxxx is close to LOL (Limit = £xxxxx, current costs= £xxxxx), Case number xxxxx for client xxxxx is close to LOL (Limit = £xxxxx, current costs= £xxxxx), Case number xxxxx for client xxxxx is close to LOL (Limit = £xxxxx, current costs= £xxxxx), Case number xxxxx for client xxxxx is close to LOL (Limit = £xxxxx, current costs= £xxxxx), Case number xxxxx for client xxxxx is close to LOL (Limit = £xxxxx, current costs= £xxxxx),

I would prefer (as would the lawyers) that it looked like this:

Case number xxxxx for client xxxxx is close to LOL (Limit = £xxxxx, current costs= £xxxxx),

Case number xxxxx for client xxxxx is close to LOL (Limit = £xxxxx, current costs= £xxxxx),

Case number xxxxx for client xxxxx is close to LOL (Limit = £xxxxx, current costs= £xxxxx),

Case number xxxxx for client xxxxx is close to LOL (Limit = £xxxxx, current costs= £xxxxx),

Hope this was not too long a post. Many thanks in advance for any help/information received.

Jake

 

[tsuji]

Not that sure.
> strLOLAlerts = strLOLAlerts & fncCheckLOL(objRS("CLIENT"), objRS("OUR_REFERE"), objRS("LAWYER"), objRS("SumOfLIMIT_OF_L"))
[tt] strLOLAlerts = strLOLAlerts & fncCheckLOL(objRS("CLIENT"), objRS("OUR_REFERE"), objRS("LAWYER"), objRS("SumOfLIMIT_OF_L")) [blue]& vbcrlf[/blue][/tt]

 

[dm4ever]

You can also try changing
objMail.TextBody to objMail.HTMLBody and use HTML tags to format your output accordingly.

--------------------------------------------------------------------------------
dm4ever
My philosophy: K.I.S.S - Keep It Simple Stupid