Automatically Emailing to Query Result with Outlook

[alfonsomozo]

I have this query which searches according to a forms contents and displays the results. Based on these results i have made another query that only displays the EmailName field and only unique values and values that are not null.
Want i want to do is to be able to send an e-mail to all of these people using Access.
I have grabbed the great code for this from the FAQs in which it will copy an e-mail address from within a text field.
Basically i want to change this to grabbing the data from this query and placing "; " inbetween the data from each row.
Any help will be greatly appreciated.

 

[Remou]

Can you post the code you have and a reference for the FAQ please?

 

[alfonsomozo]

ok so the FAQ I was looking at is available from

http://www.tek-tips.com/faqs.cfm?fid=2921

And i have used this code thus far except i have omitted any code relating to the subject or body of the e-mail as I will not be using them.
In the email field of the code i somehow have to get it equal to the rows of data from my query sperated by ;

Private Sub Emails_Click()
'******begin code******
Dim Email As String

'**create variables for Outlook
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

'**gathers information from your form. this sets the string variable to your fields
Email = [THIS IS WHAT IM NOT SURE ABOUT]

'***creates an instance of Outlook
Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

'***creates and sends email
With objEmail
.To = Email
.Display 'sends the email in Outlook. Change to DISPLAY if you want to be able to
'modify or see what you have created before sending the email
End With

'**closes outlook
'objOutlook.Quit
'Set objEmail = Nothing

Exit Sub
'****end code****
End Sub

 

[Remou]

You seem to want to sent one email to all these people, if this is true:

Dim rs as DAO.Recordset
Set rs = CurrentDB.OpenRecordset("[i]InsertNameOfEmailQuery[/i]")
Do while Not rs.eof
  strTo = strTo & rs![i]EmailAddress[/i] & ";"
  rs.MoveNext
Loop
strTo = Left(strTo,Len(strTo)-1)

If I typed that right, strTo should contain a list of email addresses. If there are any nulls or blanks, it won't work, but you say your query has removed these.

 

[alfonsomozo]

On this line of code
Set rs = CurrentDb.OpenRecordset("Email")

i get Run-Time error 3061
Too few paramaters. Expected 19.

Any ideas? The query "email" is based up "contacts query" so i'm thinking that may be effecting things. here is the SQL code

SELECT DISTINCT [Contacts Query].EmailName
FROM [Contacts Query]
WHERE ((([Contacts Query].EmailName) Is Not Null));

thanks for looking

 

[alfonsomozo]

Sorry for double post but yes Remou you are right in that i am wanting to send one e-mail to all those people.

 

[Remou]

No problem. I think the glitch might be back a little further. What does Contacts Query look like?
OR
Because you are using a record set, the query may not be necessary, it just seemed simpler at the time.

Dim rs as DAO.Recordset
'Add more to the Where statement, as you require.
Set rs = CurrentDB.OpenRecordset("SELECT * From Contacts WHERE Trim(EmailName) & '' <>''")
Do while Not rs.eof
  strTo = strTo & rs!EmailAddress & ";"
  rs.MoveNext
Loop
strTo = Left(strTo,Len(strTo)-1)

I changed Email to strTo in my snippet: this is because I am always nervous of words that look like they might be reserved. I think that Nathan in writing the FAQ named the variable Email as a prompt, strEmail or strTo would be safer. Having said this, I found the whole thing, in mock-up, work ok for me.

 

[alfonsomozo]

thanks for the swift response Remou. I have tried the revised code and it runs without errors. The only problem is that it grabs the e-mail addresses for every contact, not just the contacts i have just queried.
I tried something along similar lines by changing that one line to

Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT [Contacts Query].EmailName FROM [Contacts Query] WHERE ([Contacts Query].EmailName) Is Not Null")

but still get the same error message. What am i doing wrong?

 

[Remou]

The general idea is to set up the line of SQL to match Contacts Query, where the problem seems to be. Perhaps you had better post the SQL for Contacts Query, to see if we can figure it out between us.

 

[alfonsomozo]

This is my SQL code for Contacts Query. PHV helped me out alot with writing it as Access kept on butchering my code.
If i view the results of the email query in access in datasheet view all the details there are correct.


SELECT Contacts.ContactID, Contacts.SalutationID, Contacts.FirstName, Contacts.LastName, Contacts.Address1, Contacts.Address2, Contacts.City, Contacts.StateID, Contacts.PostalCode, Contacts.CompanyName, Contacts.Position, Contacts.WorkPhone, Contacts.WorkExtension, Contacts.MobilePhone, Contacts.FaxNumber, Contacts.EmailName, Contacts.ContactTypeID, Contacts.Notes, Contacts.[Authorised By], Contacts.[Authorised Date]
FROM Contacts
WHERE (Contacts.SalutationID Like '*' & [Forms]![Search]![Salutation] & '*' OR [Forms]![Search]![Salutation] Is Null)
AND (Contacts.FirstName Like '*' & [Forms]![Search]![FirstName] & '*' OR [Forms]![Search]![FirstName] Is Null)
AND (Contacts.LastName Like '*' & [Forms]![Search]![LastName] & '*' OR [Forms]![Search]![LastName] Is Null)
AND (Contacts.Address1 Like '*' & [Forms]![Search]![Address] & '*' OR [Forms]![Search]![Address] Is Null)
AND (Contacts.Address2 Like '*' & [Forms]![Search]![Address2] & '*' OR [Forms]![Search]![Address2] Is Null)
AND (Contacts.City Like '*' & [Forms]![Search]![City] & '*' OR [Forms]![Search]![City] Is Null)
AND (Contacts.StateID Like '*' & [Forms]![Search]![State] & '*' OR [Forms]![Search]![State] Is Null)
AND (Contacts.PostalCode Like '*' & [Forms]![Search]![PostalCode] & '*' OR [Forms]![Search]![PostalCode] Is Null)
AND (Contacts.CompanyName Like '*' & [Forms]![Search]![CompanyName] & '*' OR [Forms]![Search]![CompanyName] Is Null)
AND (Contacts.Position Like '*' & [Forms]![Search]![Title] & '*' OR [Forms]![Search]![Title] Is Null)
AND (Contacts.WorkPhone Like '*' & [Forms]![Search]![WorkPhone] & '*' OR [Forms]![Search]![WorkPhone] Is Null)
AND (Contacts.WorkExtension Like '*' & [Forms]![Search]![WorkExtension] & '*' OR [Forms]![Search]![WorkExtension] Is Null)
AND (Contacts.MobilePhone Like '*' & [Forms]![Search]![MobilePhone] & '*' OR [Forms]![Search]![MobilePhone] Is Null)
AND (Contacts.FaxNumber Like '*' & [Forms]![Search]![FaxNumber] & '*' OR [Forms]![Search]![FaxNumber] Is Null)
AND (Contacts.EmailName Like '*' & [Forms]![Search]![EmailName] & '*' OR [Forms]![Search]![EmailName] Is Null)
AND (Contacts.ContactTypeID Like '*' & [Forms]![Search]![ContactTypeID] & '*' OR [Forms]![Search]![ContactTypeID] Is Null)
AND (Contacts.Notes Like '*' & [Forms]![Search]![Notes] & '*' OR [Forms]![Search]![Notes] Is Null)
AND (Contacts.[Authorised By] Like '*' & [Forms]![Search]![Authoriser] & '*' OR [Forms]![Search]![Authoriser] Is Null)
AND (Contacts.[Authorised Date] Like '*' & [Forms]![Search]![Authorised Date] & '*' OR [Forms]![Search]![Authorised Date] Is Null);

 

[Remou]

Phew! The 'expected 19', is, I think, the 19(?) times you referenced the form. So, is the form open when you try the email code?

 

[alfonsomozo]

I have tried it with the form either open or close. Thus far i haven't noticed any difference. Generally speaking though the search form will almost always be open when the email button is clicked.

 

[Remou]

Let's try a short form to test the email idea:

Set rs = CurrentDB.OpenRecordset("SELECT Contacts.EmailName " _
& "FROM Contacts " _
& "WHERE (Contacts.FirstName Like '*' "
& [Forms]![Search]![FirstName] & " '*'")

If this works out, the rest of the lines can be added in, I think.

 

[Remou]

Oops:

Set rs = CurrentDB.OpenRecordset("SELECT Contacts.EmailName " _
& "FROM Contacts " _
& "WHERE (Contacts.FirstName Like '*' " [red]_[/red]
& [Forms]![Search]![FirstName] & " '*'")

 

[alfonsomozo]

I changed the code like you suggested but now i am getting the same error, different message.
too few paramaters, expected 1.

Again i ran the query and it returned just the EmailName field with a list of e-mail addresses for every record in the database. The Search form was open when i did this.

Perhaps there is a cruder way in which we can simulate Access copying and pasting the data from each row of the query?

 

[Remou]

I do not think you can copy and paste in the way that you mean. When I tried this with a mock-up, I only got errors if the form was not open or not filled in. You can show this by changing the line above to:

Set rs = CurrentDB.OpenRecordset("SELECT Contacts.EmailName " _
& "FROM Contacts " _
& "WHERE (Contacts.FirstName Like '*' " _
& "Alfonso" & " '*'")

You can also test that the form field contains something with message box or the immediate window. A message box would look like:

MsgBox "Name: " & Nz([Forms]![Search]![FirstName])

The line can go anywhere in the early part of the email code.

 

[alfonsomozo]

All right i have been doing some reading up and have figured out that it is not working properly because the fields from the form that the query references were not defined as paramaters. Ihave changed the code to fix this but now get a new error
Run-Time error 3066
Query must have at least one destination field.
Below is the revised code. The error is occuring on
qdf.Parameters("[Forms]![Search]![FirstName]") = [Forms]![Search]![FirstName]

'**gathers information from your form. this sets the string variable to your fields
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("email")
qdf.Parameters("[Forms]![Search]![FirstName]") = [Forms]![Search]![FirstName]
qdf.Parameters("[Forms]![Search]![Salutation]") = [Forms]![Search]![Salutation]
qdf.Parameters("[Forms]![Search]![LastName]") = [Forms]![Search]![LastName]
qdf.Parameters("[Forms]![Search]![Address]") = [Forms]![Search]![Address]
qdf.Parameters("[Forms]![Search]![Address2]") = [Forms]![Search]![Address2]
qdf.Parameters("[Forms]![Search]![City]") = [Forms]![Search]![City]
qdf.Parameters("[Forms]![Search]![State]") = [Forms]![Search]![State]
qdf.Parameters("[Forms]![Search]![PostalCode]") = [Forms]![Search]![PostalCode]
qdf.Parameters("[Forms]![Search]![CompanyName]") = [Forms]![Search]![CompanyName]
qdf.Parameters("[Forms]![Search]![Title]") = [Forms]![Search]![Title]
qdf.Parameters("[Forms]![Search]![WorkPhone]") = [Forms]![Search]![WorkPhone]
qdf.Parameters("[Forms]![Search]![WorkExtension]") = [Forms]![Search]![WorkExtension]
qdf.Parameters("[Forms]![Search]![MobilePhone]") = [Forms]![Search]![MobilePhone]
qdf.Parameters("[Forms]![Search]![FaxNumber]") = [Forms]![Search]![FaxNumber]
qdf.Parameters("[Forms]![Search]![EmailName]") = [Forms]![Search]![EmailName]
qdf.Parameters("[Forms]![Search]![ContactTypeID]") = [Forms]![Search]![ContactTypeID]
qdf.Parameters("[Forms]![Search]![Notes]") = [Forms]![Search]![Notes]
qdf.Parameters("[Forms]![Search]![Authoriser]") = [Forms]![Search]![Authoriser]
qdf.Parameters("[Forms]![Search]![Authorised Date]") = [Forms]![Search]![Authorised Date]
Set rs = qdf.OpenRecordset(dbOpenDynaset)
Do While Not rs.EOF
strTo = strTo & rs!EmailName & ";"
rs.MoveNext
Loop
strTo = Left(strTo, Len(strTo) - 1)
Email = strTo

 

[alfonsomozo]

I've had a minor breakthrough now. If i change the query from "email" to "contacts query" it opens up outlook and copies over all the e-mail adresses. The only problem is that it copies duplicates and null entries. There were two ways i thought could fix this.
1. still use "email" as the basis for the query but then somehow setup some kind of join to the "contacts query". This i have no idea how to do.

2. Somehow use the SQL code of the "email" query.
I have tried using the line
Set qdf = CurrentDb.QueryDefs("SELECT DISTINCT [Contacts Query].EmailName FROM [Contacts Query] WHERE ([Contacts Query].EmailName) Is Not Null")
but i get run-time error 3265
item not found in this collection

Does anyone have any suggestions?
thanks.

 

[Remou]

You need the name of a query, for example:

Set qdf = CurrentDb.QueryDefs("Query1")
qdf.sql = "SELECT DISTINCT [Contacts Query].EmailName FROM [Contacts Query] WHERE ([Contacts Query].EmailName) Is Not Null"

 

[alfonsomozo]

thanks for your help Remou. The code you gave me for copying the email names to outlook works like a charm. I managed to get it to work by creating a new query that was the same as "contacts query" except it only showed the email field. Everything is working as it should for now.