Send email & merged report attachment to all recipients in a table

[pooksa]

Good Morning,

I am having trouble sending an email with report attachment to all recipients in a table. The code I have written creates a report merged with the recipeint's specific information and then emails that report to that recipient. The report merge works correctly and moves through all the records, but it's sending all the emails to the first recipient. Here is what I have:

Public Function Preview()

DoCmd.SetWarnings False
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim List As Object
Dim strState As String
Dim strRpt As String
Dim strRecipientEmail As String
Dim strName As String
Dim strLetter As String
Set db = DBEngine(0)(0)
Set rst = db.OpenRecordset("tblSendTo", dbOpenDynaset)
strRpt = "rptDonorLetter"
strRecipientEmail = rst.Fields("EmailAddress&quot
strLetter = Forms![frmLetter]![Letter]

Do Until rst.EOF
With List

DoCmd.OpenReport "rptDonorLetter", acViewPreview
DoCmd.SendObject acReport, strRpt, "Rich Text Format(*.rtf)", _
strRecipientEmail, , , "Test Email Letter & attachment", strLetter, False, ""
DoCmd.Close acReport, "rptDonorLetter"
DoCmd.Close acForm, "frmLetter"
Forms![frmDonorEmail]![EmailAddress].SetFocus
rst.MoveNext

If rst.EOF Then
Set db = Nothing
Set rst = Nothing
Set List = Nothing
Exit Function
End If

DoCmd.GoToRecord , , acNext

End With
Loop
Set db = Nothing
Set rst = Nothing
Set List = Nothing

End Function

I'd sure appreciate it if someone could tell me how to fix this so the report goes to the right recipient, I'd be very grateful.

Thank you in advance!
Chris

 

[arebelli]

It is quite simple: you have put the the code
strRpt = "rptDonorLetter"
strRecipientEmail = rst.Fields("EmailAddress&quot
strLetter = Forms![frmLetter]![Letter]

only out of the loop.

you have to puti it after

Do Until rst.EOF

Doing so you can solve your problem.

But there is another question: why do you "scroll" both te recordset and the form (I think that form recordsource is the same table "tblSendTo&quot?

In this case if you need to retrieve any information you can use the form fields as source.

 

[vb6novice]

Chris,

I only see where you have set the value of strRecipientEmail once just below your variable declarations. It's not clear how your report gets the information needed to make it specific to a recipient, however, your code needs to update the value of strRecipientEmail after you move to the next record. I suggest you move the line of code

strRecipientEmail = rst.Fields("EmailAddress")

inside the Do Loop right after

With List

.

 

[pooksa]

Thank you for responding! I read all 135 entries in the other email thread and still couldn't fix this code

I actually had to do both things suggested to get this to work.

This is a database I inherited with the job & got the lovely task of "fixing". The command button that creates the report attachments and sends the emails is on the frmDonorEmail. All the information for both processes in in the tblSendTo. I realized it's going through the same information twice, once to create the report for attachment and once to send the email, but couldn't figure out how to streamline it.

Now it's working great. Thank you both so much for your assistance!

Chris
Chris

 

[pooksa]

Well, so much for getting it working Now the powers that be have changed what they want! I have read all the email treadss & faq, but haven't seen this addressed. Please forgive me if my questions doesn't make sense - I am pulling my hair out!

Now, my db queries data by state from a table(tblData), and exports the results as an Excel spreadsheet named with that state's abbreviation to a file on the server. So all data for the state of NY data is exported to an Excel file named "NY" - it runs through all state and creates files for each.

I have another table, tblSendTo, that stores email addresses of the people who should receive these files - about 3 people per file, with a field showing which state's report they should receive.

I need to create an email that attaches the correct file and sends to the multiple recipients.

Is this possible?

Thank you,
Chris

 

[vb6novice]

Sure it's possible. The trick will be to keep it straight in your code. I would run the code in 2 parts.

The first part creates all the Excel files.
The next part emails the files to the correct recipients. Assuming you have a table that lists which recipients receive which files, it shouldn't be too difficult to write a procedure that runs through that list of recipients and sends the right file to him/her. Does each recipient get only one file?

 

[pooksa]

Good Morning,

Thank you for responding Ideally, each recipient should get only one file. Unfortunately I have several states that don't use Excel, so for those states I have to create the file in Rich Text Format - I know that can be accomplised with another If---Then, I just haven't gotten that far yet.

Following is the code I have to create the Excel files for each state. I have created the tblSendTo that lists all the recipients and which state's file they get, and a select query that matches the tblShipTo to the tblData by state called "qS_StatesWithData". There is also a table called tblLetter where the body of the email is stored (as above)

Public Function CreateMonthlyReports()
DoCmd.SetWarnings False
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim list As Object
Dim strState, strRpt, sfile As String
strRpt = "rptStateReport"

Set db = DBEngine(0)(0)
Set rst = db.OpenRecordset("tblStatesWithData", dbOpenDynaset)


Do Until rst.EOF
With list
DoCmd.OpenForm "frmState", acNormal
DoCmd.OpenReport strRpt, acViewPreview
sfile = rst.Fields("st&quot
DoCmd.OutputTo acOutputReport, strRpt, acFormatXLS, "\\svfile\vfwdept\marketing_svcs\monthlyDEPTrpts\RptsToDept\" & sfile & ".xls"

DoCmd.Close acReport, strRpt, acSaveYes
rst.MoveNext

If rst.EOF Then
DoCmd.Close acReport, "rptStateReport", acSaveNo
MsgBox "Reports Have Been Created on \\svfile\VFWDept\Marketing_Svcs\MonthlyDeptRpts\RptsToDept", vbInformation, "Reports Sent"
DoCmd.Close acForm, "frmState", acSaveNo
DoCmd.Close acForm, "frmMainMenu", acSaveNo
DoCmd.Close acReport, "rptLastImportDate", acSaveNo
DoCmd.OpenReport "rptLastImportDate", acViewPreview
DoCmd.OpenForm "frmMainMenu", acNormal

Set db = Nothing
Set list = Nothing
Set rst = Nothing
Exit Function
End If

DoCmd.Close acReport, "rptStateReport"
DoCmd.OpenForm "frmState"
Forms![frmstate]![St].SetFocus
DoCmd.GoToRecord , , acNext

End With
Loop
Set db = Nothing
Set list = Nothing
Set rst = Nothing

End Function

I am in and out of meetings all day, so it may take me a little bit to get back to you if you need more information in order to assist me.

In case I haven't said it enough, thank you so much! I am a secretary, not a programmer

Chris