send multiple email...code having problem.

[wnicole]

I was wondering if someone can help me with the code below.
Table: T_Inspectors (this is where the email addresses are stored [Email])
Query: Q_WeeklyDispatch_Today (This is the parameter Date()
Report: R_WeeklyDispatch_Today. (Goal: This report sends to each employee with the jobs attached by email).

It does everything it needs to do up to the Emailing part. It is not connect the email address to the right report. It appears to be emails the report to the first person on the Table but not connecting at all to the query.


Can you take a look at the code below and let me know if I'm missing something?
Thank you
*********************************************

Private Sub Command5_Click()

Dim strCriteria As String
Dim strDate As String
Dim strEmail As String

strCriteria = "[Date]=" & strDate

DoCmd.OpenReport "R_WeeklyDispatch_Today", acViewPreview, , , acHidden
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("T_Inspectors", dbOpenDynaset) 'We need an email address
rs.MoveFirst

Do While Not rs.EOF 'Loop through every record in T_Inspectors and retreive emails

DoCmd.OpenReport "R_WeeklyDispatch_Today", acViewPreview, , "[Employee]='" & rs![Last Name] & "'"

DoCmd.SendObject acSendReport, "R_WeeklyDispatch_Today", acFormatPDF, strEmail, , , "Current Jobs", "Please contact Tracy with any concerns 415-525-0000", True

rs.MoveNext

Loop

rs.Close

Set rs = Nothing
DoCmd.Close acReport, "R_WeeklyDispatch_Today"





End Sub

[ItsMe]

How many threads are you going to start regarding this question?

https://www.accessforums.net/access/...ems-38537.html
https://www.accessforums.net/access/send-report-individually-email-address-docmd-sendobject-37936.html

https://www.accessforums.net/access/...ons-38247.html

[wnicole]

I'm trying to see if another programmer can help me with this last leg...Almost there but still not solved.

[pbaldy]

You need to close the report inside the loop.

[wnicole]

See pic...Do you mean like this? When I change for some reason this code is just not pulling up any email. The email part in Outlook is blank. Nothing when I hover over email. It just says StrEmail=
As if no Email addresses in T_Inspectors is actually connected to the report.

[pbaldy]

No, above the MoveNext.

You never set strEmail, so no surprise it's empty.

[wnicole]

Hi Pbaldy,

You mentioned that I never "set" strEmail. What do you mean by that? Can you assist me with setting it?
I'm getting all the correct people on the reports its just not connecting individually to their emails.
See Code:

Private Sub Command5_Click()

Dim strCriteria As String
Dim strDate As String
Dim strEmail As String

strCriteria = "[Date]=" & strDate

DoCmd.OpenReport "R_InspectorDailyDispatch", acViewPreview, , , acHidden
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("T_Inspectors", dbOpenDynaset) 'We need an email address
rs.MoveFirst

Do While Not rs.EOF 'Loop through every record in T_Inspectors and retreive emails

DoCmd.OpenReport "R_InspectorDailyDispatch", acViewPreview, , "LastName='" & rs![Last Name] & "'"

DoCmd.SendObject acSendReport, "R_InspectorDailyDispatch", acFormatPDF, strEmail, , , "Current Jobs", "Please contact Tracy with any concerns 415-525-0000", True
DoCmd.Close acReport, "R_InspectorDailyDispatch"
rs.MoveNext

Loop

rs.Close

Set rs = Nothing






End Sub

[pbaldy]

You never give that variable a value, like you do strCriteria.

[wnicole]

Actually I was given this code. So I'm not skilled as much in writing. Still learning.

Is this correct?

strEmail = "[Email]="

[wnicole]

Excuse me Its Me...
I was just informed that the strEmail was never set.
Can you look at the code you sent again, and let me know where I would set the strEmail?

Private Sub Command5_Click()

Dim strCriteria As String
Dim strDate As String
Dim strEmail As String

strCriteria = "[Date]=" & strDate

DoCmd.OpenReport "R_WeeklyDispatch_Today", acViewPreview, , , acHidden
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("T_Inspectors", dbOpenDynaset) 'We need an email address
rs.MoveFirst

Do While Not rs.EOF 'Loop through every record in T_Inspectors and retreive emails

DoCmd.OpenReport "R_WeeklyDispatch_Today", acViewPreview, , "[Employee]='" & rs![Last Name] & "'"

DoCmd.SendObject acSendReport, "R_WeeklyDispatch_Today", acFormatPDF, strEmail, , , "Current Jobs", "Please contact Tracy with any concerns 415-525-0000", True

rs.MoveNext

Loop

rs.Close

Set rs = Nothing
DoCmd.Close acReport, "R_WeeklyDispatch_Today"

[pbaldy]

Actually I was given this code. So I'm not skilled as much in writing. Still learning.

Is this correct?

strEmail = "[Email]="

Would you say that will result in a valid email address being passed to the email code?

[wnicole]

I have also seen this one used in code.
strEmail = !Email

However, I don't know which one I will use as this is my first time really working with email sending. Still trying to get this one complete.

[pbaldy]

Where do you expect the address to come from? If the recordset, might this give a hint?

rs![Last Name]

[wnicole]

Yes. I tried this. However, its still not connecting the query with the Table when the report is ran. I'm getting all emails in the table not the connecting ones.

rivate Sub Command5_Click()

Dim strCriteria As String
Dim strDate As String
Dim strEmail As String

strCriteria = "[Date]=" & strDate



DoCmd.OpenReport "R_InspectorDailyDispatch", acViewPreview, , , acHidden

Set rs = CurrentDb.OpenRecordset("Select [Last Name], Email From T_Inspectors", dbOpenDynaset) 'We need an email address
rs.MoveFirst

Do While Not rs.EOF 'Loop through every record in T_Inspectors and retreive emails

DoCmd.OpenReport "R_InspectorDailyDispatch", acViewPreview, , "[Last Name]='" & rs![Last Name] & "'"

DoCmd.SendObject acSendReport, "R_InspectorDailyDispatch", acFormatPDF, rs!Email, , , "Current Jobs", "Please contact Tracy with any concerns 415-525-0000", True
DoCmd.Close acReport, "R_InspectorDailyDispatch"
rs.MoveNext

Loop

rs.Close

Set rs = Nothing






End Sub

[wnicole]

Is it possible for me to just get a simpler version in this macro I created. Where I can get code for the To: section.
I just need to [First Name] and [Email] from T_Inspectors table to connect with my query that uses the same fields.
Can I just do this from the macro? Easier for me.