Code:
Public Sub SendSelectCRs_Click()
Dim r As DAO.Recordset
Dim sSQL As String
Dim MsgChanges As String
Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
sSQL = "SELECT DISTINCT Format(([CRNo]+([SubNo]*0.01)),'Fixed') AS CRNumber, tblChangeRequest.CRID,"
sSQL = sSQL & " qrySwitching.Units, qrySwitching.HBVers, qrySwitching.MTOEParas, qrySwitching.People,"
sSQL = sSQL & " qrySwitching.DaysOpen, qrySwitching.DateIDs, tblChangeRequest.ChangeRequested,"
sSQL = sSQL & " tblChangeRequest.Rationale, tblChangeRequest.NIE, tblChangeRequest.ChangeType,"
sSQL = sSQL & " qrySwitching.Levelz, qrySwitching.Status, tblChangeRequest.NOTES, tblChangeRequest.ActionItems, tblChangeRequest.AOVote, tblChangeRequest.SubNo"
sSQL = sSQL & " FROM tblChangeRequest INNER JOIN qrySwitching ON tblChangeRequest.CRID = qrySwitching.CRID"
sSQL = sSQL & " WHERE (((tblChangeRequest.CRNo) <> 0))"
sSQL = sSQL & " GROUP BY Format(([CRNo]+([SubNo]*0.01)),'Fixed'), tblChangeRequest.CRID, qrySwitching.Units,"
sSQL = sSQL & " qrySwitching.HBVers, qrySwitching.MTOEParas, qrySwitching.People, qrySwitching.DaysOpen,"
sSQL = sSQL & " qrySwitching.DateIDs, tblChangeRequest.ChangeRequested, tblChangeRequest.Rationale,"
sSQL = sSQL & " tblChangeRequest.NIE, tblChangeRequest.ChangeType, qrySwitching.Levelz, qrySwitching.Status,"
sSQL = sSQL & " tblChangeRequest.NOTES, tblChangeRequest.ActionItems, tblChangeRequest.AOVote, tblChangeRequest.SubNo"
sSQL = sSQL & " ORDER BY tblChangeRequest.CRID;"
Set r = CurrentDb.OpenRecordset(sSQL)
If Not (r.BOF And r.EOF) Then
r.MoveLast
r.MoveFirst
MsgChanges = " Change Request(s) - "
' loop through record set to get CR number
Do While Not r.EOF
MsgChanges = MsgChanges & r!CRNumber & ", "
Next
If Len(MsgChanges) > 0 Then
' remove trailing comma
MsgChanges = Left(MsgChanges, Len(MsgChanges) - 2)
End If
With objOutlookMsg
.Subject = NIE & MsgChanges & " - " & Tod
.Body = SigBlock
DoCmd.OutputTo 3, "rptSelectChanges", acFormatPDF, "C:\Temp\" & NIE & " Selected Changes - " & Tod & ".pdf", , 0
.Attachments.Add ("C:\Temp\" & NIE & " Selected Changes - " & Tod & ".pdf")
.Display
Kill "C:\Temp\" & NIE & " Selected Changes - " & Tod & ".pdf"
End With
DoCmd.Close acReport, "rptSelectChanges"
DoCmd.OpenForm "frmStart"
End If
r.Close
Set r = Nothing
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
Set objOutlookAttach = Nothing
Exit Sub
End Sub
So, basicly, open a recordset, loop through the record set creating a string, remove the last two characters (comma and space - ", ").