Hi Elizabeth,
What you are doing now is fine.
After rs.EOF, the Recipient List has been prepared
so all you should have to do is add,
Call SendEmail(strplannum, strEmpName, _
strRecipients, strplanname)
By my reckoning, the output lines should be,
1100 aaanametesting another_name test2@...
1340 abtestingname test_name test@...;test1@...
1450 anametest name_name test@...;test3@...;test4@...
Please let me know if I'm wrong.
There are a couple of redundant lines (apart from the
debug commands). You can get rid of the If and End If lines
in the following snippet,
If Right(strRecipients, 1) = ";" Then
strRecipients = Left(strRecipients, (Len(strRecipients) -1))
End If
since there is always a ';' at that point.
Regards, Clive.
--- In MS_Access_Professionals@yahoogroups.com, "glcass58" <glcass58@...> wrote:
>
> Hi Clive,
>
> Thanks so much for your help. I see what you're saying.
>
> I played around with lots of test recordsets and the Debug Window shows I capture all the data for that last email, but as we know the email doesn't get created. The funny thing is now that you pointed out my problem, I am surprised the last record's email recipient makes it into the recipient list. I kept thinking it wouldn't because of the Do While Not rs.EOF.
>
> To create the last email I simply added another Call procedure outside of the loop and it took care of it. Does that seem problematic to you? I tried to think of a scenario where that would cause an error but it seems okay....
>
> Thanks again.
>
> --- In MS_Access_Professionals@yahoogroups.com, "horastacatto" <zctek@> wrote:
> >
> > Hi Elizabeth,
> >
> > There is one problem that I have just noticed before
> > finishing for the night.
> >
> > In the Else clause with the line,
> >
> > ' Get the current Empname and plan_num AND PLAN NAME
> >
> > you get the currentvalues ready for the next Loop
> > which will end if you get EOF so you fall out of the
> > loop with an unprocessed record.
> >
> > You need some cleanup code after the Loop instruction
> > to handle that unprocessed record.
> >
> > Hope that helps.
> >
> > Regards, Clive.
> >
> > --- In MS_Access_Professionals@yahoogroups.com, "horastacatto" <zctek@> wrote:
> > >
> > > Hi Elizabeth,
> > >
> > > Could you post the DebugWindow results for running
> > > your code and indicate what is missing from your
> > > results.
> > >
> > > Otherwise could you post an mdb file that demonstrates
> > > the problem to the AssistanceNeeded Folder.
> > > (nothing confidential and zipped).
> > >
> > > I can't see the problem just eyballing the code.
> > >
> > > Regards, Clive.
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com, "glcass58" <glcass58@> wrote:
> > > >
> > > > Hi everyone,
> > > >
> > > > I was wondering if someone could tell me why my loop always seems to skip one plan.
> > > >
> > > > My recordset is this:
> > > > plan_num plan_name clcemail empname
> > > > 1340 abtestingname test@ test name
> > > > 1340 abtestingname test1@ test name
> > > > 1100 aaanametesting test2@ another name
> > > > 1450 anametest test3@ name name
> > > > 1450 anametest test4@ name name
> > > > 1450 anametest test@ name name
> > > >
> > > > I want one email to go to each plan_num/plan_name (a client). There can be multiple clcemails (client email addresses) for each plan. The empname is the name of the employee that will be sending the email.
> > > >
> > > > mysql = "SELECT * from test order by plan_name"
> > > >
> > > >
> > > > Set db = CurrentDb()
> > > > Set rs = db.OpenRecordset(mysql)
> > > >
> > > > ' Use "FirstRecord" so we can check if this is the first record
> > > > strplannum = "FirstRecord"
> > > >
> > > > 'Loop through the recordset
> > > > Do While Not rs.EOF
> > > >
> > > > ' Get the Client Email
> > > > strClcEmail = rs![ClcEmail]
> > > >
> > > > ' Check if this is the First Record
> > > > If strplannum = "FirstRecord" Then
> > > >
> > > > ' Get the Empname and the plan_num AND PLAN NAME
> > > > strEmpName = rs![EmpName]
> > > > strplannum = rs![plan_num]
> > > > strplanname = rs![Plan_Name]
> > > >
> > > > ' Start building the strRecipients
> > > > strRecipients = strClcEmail & ";"
> > > > Else
> > > > ' This is not the first Record
> > > > ' Verify this is the same client
> > > > If strplannum = rs![plan_num] Then
> > > > ' Still with the same client
> > > > ' Add the client's email to strRecipients
> > > > strRecipients = strRecipients & strClcEmail & ";"
> > > > Debug.Print strRecipients
> > > > Else
> > > > ' This is the next Employee - send the email
> > > > ' First remove the trailing ";" from strRecipients
> > > > If Right(strRecipients, 1) = ";" Then
> > > > strRecipients = Left(strRecipients, (Len(strRecipients) - 1))
> > > > End If
> > > >
> > > > Debug.Print "strRecipients: " & strRecipients
> > > > Debug.Print "strEmpName: " & strEmpName
> > > > Debug.Print "plan_num: " & strplannum
> > > >
> > > > '*** Call Procedure to send email here ***
> > > > Call SendEmail(strplannum, strEmpName, strRecipients, strplanname)
> > > >
> > > > ' Get the current Empname and plan_num AND PLAN NAME
> > > > strEmpName = rs![EmpName]
> > > > strplannum = rs![plan_num]
> > > > strplanname = rs![Plan_Name]
> > > >
> > > > ' Start building the new strRecipients string
> > > > ' If we have a client email
> > > > strRecipients = strClcEmail & ";"
> > > > End If
> > > > End If
> > > > rs.MoveNext
> > > > Loop
> > > >
> > > > Thank you as always for the great help.
> > > > Elizabeth
> > > >
> > >
> >
>
Tidak ada komentar:
Posting Komentar