Sending Multiple Emails

[thingssocomplex]

I have table that contains Employee Name and Email; I have a query that includes 'Employee Name'; I have user form with two text boxes named txtname and txtemail. Within the query criteria for 'Employee Name' I have added [Forms]![Control]![txtname]

I also have a button with the below code however I cannot get this to run can anybody please assist me with making the below code work?

Private Sub Command4_Click()
Dim MySet As DAO.Recordset


Set MySet = New DAO.QueryDef

MySet.OpenRecordset "Example", CurrentDb.Connection

Do Until MySet.EOF

[Forms]![Control].[qadviser].Value = MySet![AdviserName]
[Forms]![Control].[qemail].Value = MySet!

DoCmd.SendObject acSendQuery, "Example", "MicrosoftExcel(*.xls)", [Forms]![Control].[qemail]

MySet.MoveNext
Loop

End Sub
[/QUOTE]

 

[boblarson]

Why are you trying to set the form reference? You don't do that. Also, you are opening the recordset really strange (at least from my experience). It is much simpler than you are doing it. Try this instead:

Private Sub Command4_Click()
Dim MySet As DAO.Recordset
 
 
[B][COLOR=red]Set MySet = CurrentDb.OpenRecordset("Example")[/COLOR][/B]
 
Do Until MySet.EOF
 
 
[COLOR=red][B]DoCmd.SendObject acSendQuery, "Example", acFormatXLS, MySet!email[/B][/COLOR]
 
 
MySet.MoveNext
 
Loop
 
End Sub

 

[thingssocomplex]

Hi Bob,

Sorry for the late response I had to travel for work and I greatly appreciate any help you can offer in this area.

I am setting the form reference because I need to send multiple reports for each person named in the database and the figures/numbers for each person are different. In my query I have the text box in the person name and the other text box sets the email address for each person. The idea is one report will be sent and then it will move on to the next report and so on, I therefore need something in my query in the criteria field that refers to a text box on a form for the loop to EOF. I hope this makes sense?

 

[Trevor G]

Have you looked at the Datapig website which will assist you.

http://www.datapigtechnologies.com/flashfiles/accesssendemail.html

 

[thingssocomplex]

Hi Trevor,

Tried that method but it wont work in Access 07/10 something to do with opening the connection command not recognised.

 

[thingssocomplex]

I have tried to be more specific taking the example code from the link provided above much of the code does not work in Access 2010, the conditions/commands simply do not exist, when presented with the auto-fill selection box, I am beginning to this this is impossible

Function SendMyEmail ()
Dim Myset  As ADODB.Recordset 'in Access 2010 ADOB does not exist from the selection presented

My Set = New ADODB.Recordset 'in Access 2010 ADODB does not exist you can choose DOB but then you dont have recordset you have QueryRef!'
MySet.Open "EmailAddresses, CurrentProject.Connection, 'adOpenStatic 'does not work in Access 2010 you can have openrecordset, either way the above two conditions don't work'