Email difficulty

[Malcy]

Hi
I have a form to indicate potential attendees at a workshop. Not all have email addresses. The form shows them all.
I created two separate queries to show (a) those with emails - to email to and (b) those without - to print off addressed letter.
I have put two buttons on the form, one for each function.
I have copied/adapted code posted here a few times for the email send but have hit a problem.
My sample data is three people, two with email addresses and one without. The queries catch this fine.
When I hit the email button I get the following message:
Invalid SQL statement: expected DELETE, INSERT, PROCEDURE, SELECT or UPDATE
Can someone point me in the right direction please?
I am using Access 2003 but in Access 2000 format.

The code I used for the email button is

code___________________
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strEmail As String

Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset

rs.Open "WkShAttEm", cn

With rs
Do While Not .EOF
strEmail = strEmail & .Fields("CdEm") & ";"
.MoveNext
Loop
.Close
End With

strEmail = Left(strEmail, Len(strEmail) - 1)

DoCmd.SendObject _
, _
, _
, _
("" & Me!CdEm), _
, _
, _
("" & "SWE Workshop"), _
("" & "This text needs to be worked out"), _
, _
True
_____________________

Would I be better trying to loop through the recordset that builds the form somehow using an IF CdEm is null then .... else ....?

Any help greatly appreciated.
Thanks

Malcy

 

[Malcy]

Have been a bit stupid and misnamed the query for the recordset. Sorry about that!
However, it now only sends to the first person in the query and not to the second or subsequent people.
Can anyone help on this one?

Yours red-faced

Malcy

 

[boblarson]

Looks like you're using a loop to build each email address but:
1. Not including the SendObject event in the loop
2. Not even using strEmail that you're building using the loop.

If you're building the To: address string, you seem to be constructing it but not using it. You're going back to the

Me!CdEm

which would select the current recordset object as the To: and not your strEmail string.

 

[Malcy]

Thanks Bob
Changing Me!CdEm to strEmail now gives me one email message with multiple recipients in the To: box.
I think that really I would like individual emails since it appears (at least to those who don't enquire too deeply) to be more personal.
So you have helped me move ahead but I am still not quite where I want to be.
Best wishes

Malcy

 

[boblarson]

So then move the strEmail part into the loop and then put the DoCmd.SendObject at the end of the loop (within the loop). That way it will loop through and send an individual email to each person separately.

 

[Malcy]

Thanks Bob
I tried that and it sent the first email to the first person - 100% correct and then the second email went to both the first and second person. Presumably the thrid would have gone to the first three etc.
Sadly I do not quite follow what the code is doing so will accept one email going to all participants. As I get more skilled I will look back and see if I can find out how to change it.
Your help very much appreciated.
Best wishes

Malcy

 

[Smee]

An attempt

Hey Malcy

Saw that you were browsing the forum so hope you find this before you go.

I'm not brilliant at reading the code, but from the rest of the posts I think I may have an idea.

Could you not clear strEmail to null after each loop, so it doesn't include the previous addresses for the next email. It would have to be cleared either at the very end, or very beginning of the loop.

You are right - it does look better when you recieve a message addressed only to you.

Hopefully this may help towards achieving that.

Apologies if I'm talking rubbish.

 

[Malcy]

Thanks Smee
Yes I think I worked it out whilst walking the dog round the park - am sure Einstein must have had a bout a hundred dogs!
Reckon the problem lies with the line
strEmail = strEmail & .Fields("CdEm") & ";"
and also with what I have now worked out is the clearing down bit.
So if I amend the line to
strEmail = .Fields("CdEm")
and then bring the SendObject stuff into the loop and remove the line
strEmail = Left(strEmail, Len(strEmail) - 1)
after the EndWith, I think I may be OK
Dashing out into the rain again now so will experiment this evening.
Thanks for your help
Best wisehs

Malcy