Auto Email Feature



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 = ""
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 address 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
MsgBox "The Unfinished Workorders Report has been sent!", , "Send E-Mail"
Exit Sub

MsgBox Err.Description
Resume Exit_cmdPrint_Click

End Sub

Alex Dybenko

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)

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question