Another Email Question...

[Scott]

Seems like this question has been beat to death but I've yet to find anything that works for me.

Access 2003

I've got a form with a drop down that runs a query with many email addresses in a field called "EmailAddress" by default. This field is in column 4, after a few other fields. I'd like to pull those email addresses from the results of the query, pass them to a variable, then based on that variable, create an email with those values seperated by colons without automatically sending it.

I've tried this code but could not get it to work:

Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String

Set MyDb = CurrentDb()
Set rsEmail = MyDb.OpenRecordset("My Query", dbOpenSnapshot)

With rsEmail
.MoveFirst
Do Until rsEmail.EOF
If IsNull(.Fields(4)) = False Then
sToName = .Fields(4)
sSubject = "Subject"
sMessageBody = "Email Body"

DoCmd.SendObject acSendNoObject, , , _
sToName, , , sSubject, sMessageBody, False, False
End If
.MoveNext
Loop
End With

Set MyDb = Nothing
Set rsEmail = Nothing

 

[pbaldy]

Welcome to the site. It's always helpful when you give something more specific than "could not get it to work". Do you get an error, unexpected results, or what? Offhand, that's not going to do what you describe.

 

[Scott]

Welcome to the site. It's always helpful when you give something more specific than "could not get it to work". Do you get an error, unexpected results, or what? Offhand, that's not going to do what you describe.

If I change the code up a little and run it on a query that has the results in one column, using the code below generates an email but does not loop through to add the subsuequent email addys. What I'm getting is the email with the first email address and not the others.

Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String

Set MyDb = CurrentDb()
Set rsEmail = MyDb.OpenRecordset("My Query", dbOpenSnapshot)

With rsEmail
.MoveFirst
Do Until rsEmail.EOF
If IsNull(.Fields(0)) = False Then
sToName = .Fields(0)
sSubject = "Subject"
sMessageBody = "Email Body"

DoCmd.SendObject acSendNoObject, , , _
sToName, , , sSubject, sMessageBody, True, False
End If
.MoveNext
Loop
End With

Set MyDb = Nothing
Set rsEmail = Nothing

 

[pbaldy]

I would expect that to send a separate email out to each person. To send one email, you'd move the SendObject below the loop, and change this in the loop:

sToName = sToName & ";" & .Fields(0)

to build your string of addresses separated by semicolons.

 

[Scott]

I would expect that to send a separate email out to each person. To send one email, you'd move the SendObject below the loop, and change this in the loop:

sToName = sToName & ";" & .Fields(0)

to build your string of addresses separated by semicolons.

Appreciate all your help thus far! Now all I get is:

Uknown Message Recipient(s); the message was not sent.

I set up a query to return 3 results of email addresses, all of which are correct, and ran this in the VB code for the command button on the form that launches the query and that's what I got.

 

[pbaldy]

Sounds like the code is trying to send the email. Change the last argument of the SendObject to True (the edit message argument) so you can check out the email first, making sure the addresses look valid.

 

[Scott]

Tried that one to and same error message? Strange.

 

[pbaldy]

Add this right before the SendObject so you can examine the string in the Immediate window:

Debug.Print sToName

Can you post the db?

 

[Scott]

Thanks.

I checked the Immediate Window under the View menu and all 3 email addy's appear fine with colon's however they also start with a colon too.

;email@email.com;email@email.com;email@email.com

 

[pbaldy]

Sorry, the Immediate window should be at the bottom of the Visual Basic Editor, so leave that open when you run the code. You should then see the printout there.

 

[MSAccessRookie]

Thanks.

I checked the Immediate Window under the View menu and all 3 email addy's appear fine with colon's however they also start with a colon too.

;email@email.com;email@email.com;email@email.com

Is there any way that the First Record in your Recordset could be BLANK? That would create this effect.

 

[pbaldy]

That may be a "my bad". Try this:

sToName = sToName & .Fields(0) & ";"

I don't think a trailing semicolon will bother it, but the leading one may have.

 

[Scott]

I've got a simple test query with 3 results of email addy's in the column and none are blank. Certainly, this is where the Unknown recipient is coming from though, it's expecting an email addy prior to the first ";".

 

[Scott]

winner winner chicken dinner

Now, what I need to do is make this code work on my "real" query where the email addy field is in the 4th column (eg after name, addy, blah blah). I know I know PIA.

Is there an eays way to change the field(x) to pull from that column?

 

[pbaldy]

Glad it finally worked, and sorry I goofed that earlier. Personally, I don't like using the column reference, though it's perfectly valid. I would use:

!EmailAddress

so you are explicitly specifying the field you want. That's the short version because you're within a With block; the full version would be:

rsEmail!EmailAddress

 

[Scott]

So I added this to the VB code behind the command button on the form that launches the query.

Changed the one line to:

sToName = sToName & rsEmail!EmailAddress & ";"

Now I get an error "Too few parameters. Expected 1."

 

[Scott]

The other option I have is using a seperate drop down to launch a query with only the email addy results, then run the vb code on that to create the email. I'm guessing I could "not show" the results of the query and simply click and email.

 

[pbaldy]

Can you post the db Scott?

 

[Scott]

Can you post the db Scott?

I can email it to you later.

 

[Scott]

I might just go with a seperate drop down asking the user if they want to email the list, then create a query with just the email results, no show, and run it that way. That might actually work out since they would then have the choice of picking whether they want to email or simply view the results of the query.