Email Individual Reports separately

[vickster3659]

Hello,

While searching this forum, I found some code that would work for emailing reports (thank you, June7!). When I click the button, my process is working fine, but I would like this process to select one email address from the recordset, with its' corresponding detail, and allow me to send only that detail to the email address selected. After that email is sent, this should select the next email address, and detail corresponding to that email address and so on, until the process has gone through the entire recordset.

My test data includes three email addresses, and several transactions for each. The report has a break to keep each email/dataset to it's own page, but when the process runs, the report gives me all three pages, which will then get sent to each email address. I don't mind having to click "Send" for each email, I just don't want each person to get someone else's data.

Here is the code I am using:

Private Sub cmdEmailToRequestors_Click()

Dim rs As DAO.Recordset
stCaption = "EFT Payments sent" & Format(Now(), " mm-dd-yyyy")
myPath = "\\DFS01\SHARED\CFA\VOL5\TREASURE\CASH_MGR\1- Treasury Operations\4- Database\Wire Tracking\"
stEmailMessage = "Please see the attached report for EFT Payments processed today."
stSubject = "EFT Payments"
stReport = "rptEmailToRequestors"
Set rs = CurrentDb.OpenRecordset("SELECT RequestorEmail FROM tblRequestors;")
DoCmd.OpenReport "rptEmailToRequestors", acViewPreview
While Not rs.EOF
DoCmd.SendObject acSendReport, , acFormatPDF, rs!RequestorEmail, , , stSubject, stEmailMessage, True, ""
DoCmd.OutputTo acOutputReport, , acFormatPDF, myPath & stCaption & ".pdf", False, , acExportQualityPrint
rs.MoveNext
Wend
DoCmd.Close acReport, stReport, acSaveNo
rs.Close

End Sub


Thank you, in advance!!

VCoutu

[ranman256]

I do this ....
on a form , have a listbox with the names/emails
A button will scan thru the list box
on each name, send email

OR do it 1 on demand.

Dim i As Integer
For i = 0 To lstEmail.ListCount - 1
   lstEmail= lstEmail.ItemData(i)
   docmd.sendobject "rptMyRpt"  , acFormatPDF, lstEmail, , , stSubject, stEmailMessage, True, ""
Next

[June7]

Open the report filtered by UserID, assuming UserID is included in the report RecordSource and in tblRequestors.

Set rs = CurrentDb.OpenRecordset("SELECT UserID, RequestorEmail FROM tblRequestors;")

...

DoCmd.OpenReport "rptEmailToRequestors", acViewPreview, , "UserID=" & rs!UserID

Selecting addressees from a listbox and looping as suggested by ranman is an option instead of looping the Recordset object.

[vickster3659]

Open the report filtered by UserID, assuming UserID is included in the report RecordSource and in tblRequestors.

Set rs = CurrentDb.OpenRecordset("SELECT UserID, RequestorEmail FROM tblRequestors;")

...

DoCmd.OpenReport "rptEmailToRequestors", acViewPreview, , "UserID=" & rs!UserID

Selecting addressees from a listbox and looping as suggested by ranman is an option instead of looping the Recordset object.

Thank you to both of you! June7, I'd like to try your suggestion first, but I am a little unclear on this (I am pretty new with VBA).

My report is built off a query with three tables. TblWireProcessing has most of the data, with a join from field Requestor to tblRequestor.Requestor. TblRequestor also has a field EmailNotify, which is a check box, I am filtering for EmailNotify = Yes (The third table is only there to provide an additional piece of information). I also am being prompted to enter the date.

My question: Does the Set rs = Current.Db..... that you provided replace the one in the On Click event of cmdEmailToRequestors?

Thank you, again!
VCoutu

[June7]

Yes, all I did in my example was include another field - UserID - in your code.

Why prompted to enter date? - this will get annoying.

[vickster3659]

Good morning,
Why a date prompt? I only want to send the report with the current day's activity. Is there a way to do this without prompting for a date?

Also, after making the suggested revisions, I am getting a Run-time error '3075' Syntax error (missing operator) in query expression '(Requestor=a name on my table)'. It happens to be the first name on my table, which does not have any records for the current day. How do I filter this so emails only go to those who have records for today?

Thanks so much,
VCoutu

[June7]

I don't use dynamic input parameters in query and definitely no popups from query. Criteria input should be into a control on form and then the query would reference the control or a textbox on report would have expression referencing the input control.

If you want to limit the email to a particular set, need WHERE clause in the SQL statement for the VBA recordset. This might require the SQL to join tables. Use Access query builder to help construct the SQL so the desired data is assembled and then can replicate the SQL into the VBA or just refer to query object instead of table as the data source.

[vickster3659]

Hi,
Here is the SQL from the query that builds the report:

SELECT tblWireProcessing.Requestor, tblWireProcessing.DateReceived, tblPaymentType.PaymentType, tblWireProcessing.DateDue, tblWireProcessing.ValueDate, tblWireProcessing.Payee, tblWireProcessing.Amount, tblWireProcessing.Authorizor, tblWireProcessing.EnteredInBank, tblWireProcessing.ReleasedAtBank, tblRequestors.EmailNotify, tblRequestors.RequestorEmail
FROM (tblWireProcessing LEFT JOIN tblPaymentType ON tblWireProcessing.PaymentType = tblPaymentType.ID) LEFT JOIN tblRequestors ON tblWireProcessing.Requestor = tblRequestors.Requestor
WHERE (((tblRequestors.EmailNotify)=Yes));

Is it the text in red what you are referring to? How much/where to I enter this into the On Click event?

My appologies....just when I think I understand something, then something new pops up!

Thanks, and have a great weekend!
VCoutu

[June7]

Modify the SQL statement in the VBA. If you want only records that match current date, then include that criteria in the SQL.

Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT UserID, RequestorEmail FROM ... WHERE [some date field]=Date();")

The red text needs to be whatever will provide the data you need for the looping code.

Use Access query designer to help build the correct SQL statement.

[vickster3659]

Hi June7,

Here is my modified code: Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT Requestor, RequestorEmail FROM tblRequestors WHERE [DateDue]=Date();")

I get this error message: Run-time error '3061': Too few parameters. Expected 1.

Thanks again!

[June7]

That should work. If you want to provide db for analysis, follow instructions at bottom of my post.

[vickster3659]

Hello,
I have attached my db with some test data. From the main form, view reports, email requestor reports, to get the error message.

Thanks so much for your assistance!

VCoutu

[June7]

There is no date field in tblRequestors. That is the missing parameter in the SQL.

If you need to consider some date as filter criteria for choosing email records, then need to do a query and use that as the source for the recordset. That is what I was trying to show with my last example. Looks like the same query object used for the report can be the source for the recordset.

Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT Requestor, RequestorEmail FROM qryEmailToRequestors WHERE [DateDue]=Date();")

[vickster3659]

Good morning,
I added the change to the code. I think this will work, as the error message that pops up now suggests that only the persons with current day activity will get an email. The new error message is: Run time error '3075': Syntax error (missing operator) in query expression 'Requestor=persons' name to get email)'.

When clicking Debug, this is the line that is highlighted: DoCmd.OpenReport "rptEmailToRequestors", acViewPreview, , "Requestor=" & rs!Requestor

I tried googling for myself first, but I haven't seen any examples of this that could possibly help me figure it out on my own.

I will owe a huge debt of thanks/kudos, etc. for you helping me to get this to work, as several people in my organization have been waiting patiently for this daily report!

Thank you, again!
VCoutu

[June7]

Requestor is text field. Parameters for text field need apostrophe delimiters.

DoCmd.OpenReport "rptEmailToRequestors", acViewPreview, , "Requestor='" & rs!Requestor & "'"



You are saving the Requestor name into data records instead of ID. Names are very poor keys.

If you made the ID the primary key and saved the ID instead and included it in the query and in the recordset:

DoCmd.OpenReport "rptEmailToRequestors", acViewPreview, , "ID=" & rs!ID