I've been using the following code (see bottom) attached to a command button
that automatically creates a report in snapshot format and then inserts it
into an email as an attachment. The email addresses are also auto-added based
on a prior list box selection.

It works great. I would just like to now use it in conjunction with a report
that requires the user to make a selection before the report can run. In my
example, I want to generate a report that displays every customer assigned to
a specific salesperson that has not purchased anything in 90 days and has
equipment on loan.

So normall when I run the report I have a window appear with a combo box and
the user selects the salesperson.

I just don't know how incorporate the salesperson selection process in the
auto-email feature.

I want to select a salesperson in the listbox and have the report for that
salesperson created and inserted into an email in snapshot format.

Any help would be great! Thx!

Also, I'm told Access is going by the wayside - is that true?


Private Sub cmdMailRep_ort_Click()

Dim MailList As String
Dim strCCTo As String
Dim strBccTo As String
Dim strMsg As String
Dim Address As Variant
'Report variables
Dim strFilter As String
Dim rpt As String

'Create string for list of mail recipients
'lstMailer is a multiselect listbox
For Each Address In Me.lstMailer.ItemsSelected
MailList = MailList & ";" & """" & Me.lstMailer.ItemData(Address) & """"
Next Address
If MailList = "" Then
MailList = ""
Else
MailList = right(MailList, Len(MailList) - 1)
End If
'**********************
'set report to print
rpt = "Open Workorders" 'report name goes here. This is the name of the
report you wish to send.
strFilter = "" 'add code to create filter for report if necessary

strCCTo = "(E-Mail Removed); " 'Additional email addreses go here
strBccTo = "" 'Additional blind email addrresses go here
strMsg = "Attached to this email, you'll find a report containing all open
workorders." 'Default message content goes here

DoCmd.OpenReport rpt, acPreview, , strFilter
'The next line sends the report in RTF format.
'This can be changed to several formats, search the help file for acFormatRTF

DoCmd.SendObject acSendReport, rpt, acFormatSNP, MailList, strCCTo,
strBccTo, "Open Workorders", strMsg, -1
DoCmd.Close
MsgBox "The Unfinished Workorders Report has been sent!", , "Send E-Mail"
Exit_cmdPrint_Click:
Exit Sub

Err_cmdPrint_Click:
MsgBox Err.Description
Resume Exit_cmdPrint_Click

End Sub

Hi,
you can do like this. Say your report's recordsource is qryOpenWorkorders.
make a new query qryOpenWorkorders2 with such SQL:
Select * from qryOpenWorkorders
and set this query as a recordsource for the report.

now before sending or opening report add such line:
currentdb.querydefs("qryOpenWorkorders2").SQL="Select * from
qryOpenWorkorders Where SalesPerson='" & me.lstSalesPerson & "'"

lstSalesPerson - is a list box where sales person selected

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com


"JK" <jasonk at necoffeeco dot com> wrote in message
news:35542400-DFC6-43E6-B688-(E-Mail Removed)...
> I've been using the following code (see bottom) attached to a command
> button
> that automatically creates a report in snapshot format and then inserts it
> into an email as an attachment. The email addresses are also auto-added
> based
> on a prior list box selection.
>
> It works great. I would just like to now use it in conjunction with a
> report
> that requires the user to make a selection before the report can run. In
> my
> example, I want to generate a report that displays every customer assigned
> to
> a specific salesperson that has not purchased anything in 90 days and has
> equipment on loan.
>
> So normall when I run the report I have a window appear with a combo box
> and
> the user selects the salesperson.
>
> I just don't know how incorporate the salesperson selection process in the
> auto-email feature.
>
> I want to select a salesperson in the listbox and have the report for that
> salesperson created and inserted into an email in snapshot format.
>
> Any help would be great! Thx!
>
> Also, I'm told Access is going by the wayside - is that true?
>
>
> Private Sub cmdMailRep_ort_Click()
>
> Dim MailList As String
> Dim strCCTo As String
> Dim strBccTo As String
> Dim strMsg As String
> Dim Address As Variant
> 'Report variables
> Dim strFilter As String
> Dim rpt As String
>
> 'Create string for list of mail recipients
> 'lstMailer is a multiselect listbox
> For Each Address In Me.lstMailer.ItemsSelected
> MailList = MailList & ";" & """" & Me.lstMailer.ItemData(Address) &
> """"
> Next Address
> If MailList = "" Then
> MailList = ""
> Else
> MailList = right(MailList, Len(MailList) - 1)
> End If
> '**********************
> 'set report to print
> rpt = "Open Workorders" 'report name goes here. This is the name of the
> report you wish to send.
> strFilter = "" 'add code to create filter for report if necessary
>
> strCCTo = "(E-Mail Removed); " 'Additional email addreses go here
> strBccTo = "" 'Additional blind email addrresses go here
> strMsg = "Attached to this email, you'll find a report containing all open
> workorders." 'Default message content goes here
>
> DoCmd.OpenReport rpt, acPreview, , strFilter
> 'The next line sends the report in RTF format.
> 'This can be changed to several formats, search the help file for
> acFormatRTF
>
> DoCmd.SendObject acSendReport, rpt, acFormatSNP, MailList, strCCTo,
> strBccTo, "Open Workorders", strMsg, -1
> DoCmd.Close
> MsgBox "The Unfinished Workorders Report has been sent!", , "Send E-Mail"
> Exit_cmdPrint_Click:
> Exit Sub
>
> Err_cmdPrint_Click:
> MsgBox Err.Description
> Resume Exit_cmdPrint_Click
>
> End Sub
>