Field reference in VB 6.0 code for Access 2000

[abiart]

Hi everyone,

I was wondering if you might be able to shed some light on what is probably a simple problem:

I have read carefully the post titled:

Access and Fields of Recordsets using DAO3.6 [Resolved]

I am experiencing a similar problem, though I have checked and checked again for typos to no avail. I have also tried to enter a Stop statement as suggested in the above thread but before the Stop happens the error occurs, so I can't step in and take a look.

My only incling is that perhaps the code is looking for Distributors.Email in the wrong place (it is a field called "Email" in a table called "Distributors" - though I did hope this was explicit).

If anyone can spot my mistake I would be delighted to hear what it is, otherwise if the Stop statement could be explained further to help me uncover the problem that would be much appreciated!

Many thanks in advance!

Abiart

Private Sub EmailDistributorsEnquiryDetails_Click()
Dim rsEmail As DAO.Recordset
Dim strEmail As String
Set rsEmail = CurrentDb.OpenRecordset("qryDistributorsEnquiryDetails")

Do While Not rsEmail.EOF
strEmail = rsEmail.Fields("Distributors.Email").Value
Stop
DoCmd.SendObject acSendQuery, acqryDistributorsEnquiryDetails, acFormatXLS, Distributors.Email, [[email protected]], , "Company Name Sales Leads", "Dear [DistributorEmails.FirstName] Attached is your monthly update of sales leads. We would appreciate if you could go through these records and let us know the status of the enquiries. Thank you for your kind assistance in this matter. Kind regards Company Name Sales Team", 1

rsEmail.MoveNext

Loop
Set rsEmail = Nothing

End Sub

[salvelinus]

Well, you may have to do a .MoveFirst first. But I think you're on the right track for the error. The field name in the recordset isn't the table & field name, it's just the field name - Email, in this case, unless you used AS in the query to call it something else.
You're also going to run into problems in your .SendQuery statement, but see if the above helps first.

[abiart]

Dear Savelinus,

Thanks for your suggestions. I haven't yet put in a .MoveFirst, but I found changing ("Distributors.Email") to ("Email") has solved the first problem.

You're right that my .SendQuery statement was problematic, however I changed the object name from acqryDistributorsEnquiryDetails to "qryDistributorsEnquiryDetails", and I am now getting to the stage where the compose email form appears !

I think my final problem is what syntax to use when I want the To arguement to lookup a field in another table. This is the confusing part: I have the query I want to send: a list of enquiries which includes a combo field which looks up a Distributor ID in table Distributors. For each distributor I want to filter the query to show only the enquiries with that distributor's ID in the record and then cross-reference that distributor ID in the Distributors table to find the Email to send the filtered query to.

I think I have the right code for this (and I've made a dummy where all the email addresses in the distributors table are mine, so I can test it), but it's just the syntax to get the To arguement to lookup a field that I need.

I have tried combinations of different parentheses/inverted commas etc but suspect I'll need something a little more complex?

To clarify, here is the updated code:

Private Sub EmailDistributorsEnquiryDetails_Click()
Dim rsEmail As DAO.Recordset
Dim strEmail As String
Set rsEmail = CurrentDb.OpenRecordset("qryDistributorsEnquiryDetails")

Do While Not rsEmail.EOF
strEmail = rsEmail.Fields("Email").Value
DoCmd.SendObject acSendQuery, "qryDistributorsEnquiryDetails", acFormatXLS, ("Email"), "[email protected]", , "Company Name Sales Leads", "Dear [DistributorEmails.FirstName] Attached is your monthly update of sales leads. We would appreciate if you could go through these records and let us know the status of the enquiries. Thank you for your kind assistance in this matter. Kind regards Company Name Sales Team", 1

rsEmail.MoveNext

Loop
Set rsEmail = Nothing

End Sub

I can't attach a a screensave of the Send Mail form that appears, but note the text "Email" appears in the To field and [DistributorsEmails.FirstName] appears in the message body instead of Joe/Fred etc. Also the attached Excel file is not filtered.

Many thanks again!

Abby

[salvelinus]

Do you mean where you have "[email protected]"? Replace that with strEmail, that's why you got that value. You don't have to use the variable, you could use just rsEmail.Fields("Email").Value. I use variables, though.
"Dear [DistributorEmails.FirstName]..." is also incorrect, for a similar reason to your first problem. Additionally, since it's in quotes, that wording is exactly what you'll get.

[abiart]

I have just read the following article:

Using Automation in Microsoft Office Access 2003 to Work with Microsoft Office Outlook 2003

Maybe the automation method will give me the flexibility I need? Is it even possible to lookup field data for To arguments with SendObject commands?

[salvelinus]

Automation is fine. I have Access & Outlook 2K, not 2003, but here's an example of sending an email, using various variables (haven't dimmed or set them all in this example, but this is the idea):

'send Outlook email
Private Sub MailNoCommentQuestions(strSubj As String, strBody As String)
On Error GoTo errMailing
      Dim objOutlook As New Outlook.Application
      Dim objMail As MailItem
      
      Set objMail = objOutlook.CreateItem(olMailItem)
      With objMail
            .To = strTo
            .CC = strCC
            .Subject = strSubj
            .Body = strBody
            .Display
      End With
      
Cleanup:
      If Not objOutlook = Nothing Then
            Set objMail = Nothing
            Set objOutlook = Nothing
      End If
      
errMailing:
      MsgBox "Email not sent!" & vbCrLf & vbCrLf & Err.Number & vbCrLf & Err.Description, vbCritical, "Email Not Sent"
      Resume Cleanup
End Sub

There are other properties for the email, like attachments, etc. The code above doesn't actually send this email; it displays it for you to send. Use .Send to send right away. Also, you'll need to set a reference to Outlook in the code IDE window to use Outlook automation.
The SendObject method doesn't look up any values; you have to supply them. That's what you have your recordset for.
For your "Distributors.Firstname" section, try:

strBody = "Dear " & rsEmail!FirstName & ", " & vbcrlf & "Attached..."

assuming your query for rsEmail also returns the first name. The rsEmail!FirstName notation is just an alternate method to rsEmail.Fields("FirstName").Value.