Add multiple iterations into subject line

[Thompyt]

For clarification:
These 2 buttons are on the REPORT rptSelectChanges, not a form????

"Email" on rptSelectChanges - on the Report

"Send" is on the actual email message as designed on MS Outlook. I didn't make that one.




I have two buttons on the form frmSelectChanges

"Cancel" and "Select" the "Select" button drives the MyChanges VBA

[ssanfu]

OK, that helped clear things up (I hope).

(I have never use buttons on a report. )


Try this (on a copy of the dB for safety sake):
1) On "frmSelectChanges" create an unbound text box. Name it "tbWHERE". At some point it could/should be hidden.

2) In the routine "SelectChanges_Click", add the line(in blue)

Public Sub SelectChanges_Click()     
Dim strWhere As String, ctl As Control, varItem As Variant
    Set ctl = Me.MyChanges
   
If Me.MyChanges.ItemsSelected.Count = 0 Then
     MsgBox "Nothing was selected"
        Exit Sub
    End If

For Each varItem In ctl.ItemsSelected
   strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"

Next varItem
   strWhere = Left(strWhere, Len(strWhere) - 1)
   Me.tbWHERE = strWhere
   DoCmd.OpenReport "rptSelectChanges", acViewReport, , "CRNumber IN(" & strWhere & ")"
   DoCmd.Close acForm, "frmSelectChanges"
End Sub

3) Change "SendSelectCRs_Click" to this:

Public Sub SendSelectCRs_Click()

    Set objOutlook = CreateObject("Outlook.Application")
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)


    With objOutlookMsg
         .Subject = NIE & " Selected Changes(s) - " & Forms!frmSelectChanges.tbWHERE & " - " & 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"

    Set objOutlookMsg = Nothing
    Set objOutlook = Nothing
    Set objOutlookAttach = Nothing

End Sub

[Thompyt]

Much thanks for your assistance Steve.
I got it to work with what you provided. I did make a small change from Form! to Form_frmSelectChanges.tbWhere

I did add this in also:

Dim strCRs As String
strCRs = Form_frmSelectChanges.tbWhere
strCRs = Replace(strCRs, "'", "")
strCRs = Replace(strCRs, ",", ", ")

I took it from '2.00','3.01',.....

to 2.00,3.01,.....

to 2.00, 3.01, 4.77....

Numbers are an possible example.

I didn't see an easier way except the replace.

Thanks again

[ssanfu]

You might try changing the code in "Sub SelectChanges_Click() "

1) adding the space after the comma:

For Each varItem In ctl.ItemsSelected
   strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"  '<<-- add a space after the comma. (won't need one Replace statement)

Next varItem

2) Also try removing the single quotes and see if the report has the same data.
The line would look like

For Each varItem In ctl.ItemsSelected
   strWhere = strWhere & ctl.ItemData(varItem) & ", "   'no single quotes and added space after comma
Next varItem

Glad you got it working the way you want....

[Thompyt]

Steve,
I went this route:
in frmSelectChanges I added

strWhere2 = strWhere2 & " " & ctl.ItemData(varItem) & ", "

Me.tbWhere = strWhere2

and then referenced it in the email subject line and the report output line.

Thanks again very much.

[ssanfu]

Happy to help...