Generating Email Body With Line Returns? 1

[DBLoser]

Hello,

I've implemented code to open a query as a recordset and am looping through the recordset to send and email for each record. The code works fine but I can't manage to get the email body fields to show up on separate lines. I've tried both Chr$(13) and the vbcrlf. When I have the output go to a messagebox the line returns work both ways, however, when I generate emails, all of the fields are run together. I need a line return after each one.

'Here is the code calling the function and filling the email fields:

Private Sub cmdTwoWeeks_Click()

Dim CIntro, C1, C2, C3, C4, C5, C6, C7, strBody, strTo, strFrom, strCC, strSubject As Variant
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = CurrentProject.AccessConnection
Set rs = New ADODB.Recordset

CIntro = "You have a commitment due within two weeks."

With rs
    Set .ActiveConnection = cn
    .Source = "Select * FROM Comm_PastDue_Query"
    .LockType = adLockOptimistic
    .Open
    End With
    
    Do While Not rs.EOF
        C1 = "Commitment Number: " & rs![C_Num]
        C2 = "Commitment: " & rs![Commitment]
        C3 = "Responsible Person: " & rs![Responsible_Person]
        C4 = "Due Date: " & rs![Due_Date]
        C5 = "Description: " & rs![Description]
        C6 = "Job Number: " & rs![Job_Num]
        C7 = "Comments: " & rs![Comments]
        strBody = CIntro & Chr$(13) & vbCrLf & C1 & vbCrLf & C2 & vbCrLf & C3 & vbCrLf & C4 & vbCrLf & C5 & vbCrLf & C6 & vbCrLf & C7
        strTo = rs![EmailAddress]
        strFrom = "somebody@domain.com"
        strCC = ""
        strSubject = "CTS Notification"

        Call SendOneEMailViaCDO(strBody, strTo, strFrom, strCC, strSubject)
        'MsgBox strBody
        rs.Update
        rs.MoveNext
        Loop

Set rs = Nothing

End Sub

'Here is the function (which works fine):

Public Function SendOneEMailViaCDO(strBody As Variant, strTo As Variant, strFrom, strCC, strSubject As Variant) 'As Boolean
'Public Function SendOneEMailViaCDO(strBody As String, strTo As String, strFrom, strBCC, strSubject As String, bolHighImportance As Boolean) As Boolean
Const ROUTINE_NAME = "SendOneEMailViaCDO"

Dim bolResults As Boolean
Dim strServerName As String

strServerName = "x.x.x.x"

bolResults = True

Dim objCDOMsg As CDO.Message
Dim objCDOConfiguration As CDO.Configuration

Set objCDOMsg = CreateObject("CDO.Message")
Set objCDOConfiguration = CreateObject("CDO.Configuration")

With objCDOConfiguration

.Fields.Item("urn:schemas:mailheader:X-Mailer") = "Microsoft CDO for Windows 2000"

.Fields(cdoSendUsingMethod) = 2 'cdoSendUsingPort
.Fields(cdoSMTPServer) = strServerName
.Fields(cdoSMTPAuthenticate) = 0 'cdoAnonymous
.Fields(cdoSMTPServerPort) = 25
.Fields(cdoSMTPConnectionTimeout) = 10

.Fields.Update
End With

Set objCDOMsg.Configuration = objCDOConfiguration

With objCDOMsg
.MimeFormatted = False
.AutoGenerateTextBody = False
.To = strTo

.From = strFrom
.CC = strCC
.Subject = strSubject
.HTMLBody = strBody

.Send
End With

Set objCDOMsg = Nothing
Set objCDOConfiguration = Nothing

ExitRoutine:

End Function

Thanks in advance!!

 

[TheAceMan1]

How are ya brucewbrannan . . .

Perhaps this part of the code:

[blue]   Dim NL As String
   
   NL = [purple][b]vbNewLine[/b][/purple]
   
   Do While Not rs.EOF
      strbody = "You have a commitment due within two weeks." & NL & _
                "Commitment Number: " & rs![C_Num] & NL & _
                "Commitment: " & rs![Commitment] & NL & _
                "Responsible Person: " & rs![Responsible_Person] & NL & _
                "Due Date: " & rs![Due_Date] & NL & _
                "Description: " & rs![Description] & NL & _
                "Job Number: " & rs![Job_Num] & NL & _
                "Comments: " & rs![Comments]
      strTo = rs![EmailAddress]
      strFrom = "somebody@domain.com"
      strCC = ""
      strSubject = "CTS Notification"
   
      Call SendOneEMailViaCDO(strbody, strTo, strFrom, strCC, strSubject)
      'MsgBox strBody
      rs.Update
      rs.MoveNext
   Loop[/blue]

See Ya! . . . . . .

 

[Eupher]

Hiya, Bruce,

Did AceMan's suggestion work? His comments are generally right on target, but in this case I don't grasp why vbNewLine would work when vbCrLf doesn't. If vbNewLine *does* work, well then, never mind, and I will shut up and go learn some more about some of VBA's intrinsic constants.

But if it doesn't work, I am going to offer a hypothesis that perhaps the problem lies in the HTMLBody property of the CDO message object. Caveat: I've never used the CDO object, so this is just a guess. But maybe the HTMLBody property expects actual HTML markup? Like maybe a <BR> tag at the end of each line of text? Just a thought...

Ken S.

 

[TheAceMan1]

brucewbrannan . . .

Eupher is correct (I made an assumption) so scratch the post! . . . Currently digging in my library . . .

See Ya! . . . . . .

 

[DBLoser]

As you both figured out the VbNewLine did not work and it indeed is because of the HTML format. I replaced the Chr(13) with "<BR>" and it works great. This is my first time to use the CDO too but now I see the potential to use lots of nifty HTML formatting.

Thanks!