Email all records = to a rec from other table and rpt 4 nxt rec

[sna1maa14]

I have a query that selects all Contracts that are not complete. I need to email the Area manager with these contracts for his Area only. I’m able to send an email to each Area but it contains all the contracts not just his/her area. How do I send all records that are equal to one area only and then do this again for the next set of records? Oh and there are over 100 Areas so I don’t want to have to create 100 queries! Here is the table info I have tblArea with: fldArea, fldEmail, fldName. Tblcontracts with: fldArea, fldCompany, fldSinged (y/n). Any code or suggestions would be grateful. I do have a little knowledge in VBA and comments in code are real helpful.

 

[PHV]

I’m able to send an email to each Area
How you do that ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[sna1maa14]

By using the follwing code or you can use a loop methood which is what i will use if i can get seperate reports.
Dim otk As Outlook.Application
Dim eml As Outlook.MailItem
Dim rs As Recordset
Dim strList As String

' Open your table and build your distribution list
Set rs = New ADODB.Recordset

With rs
.Open "Table1", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTable
.MoveFirst
Do While Not rs.EOF
strList = strList & rs.Fields("EmailAddress").Value & ";"
rs.MoveNext
Loop
.Close

Set otk = CreateObject("Outlook.Application")
Set eml = otk.CreateItem(olMailItem)
eml.To = strList
eml.Subject = "Test Message"
eml.Body = "This is a test."
eml.Attachments.Add "d:data\matttest.xls"
eml.Send
End With

Set eml = Nothing
otk.Quit

 

[PHV]

So, Table1 hold the list of Area managers ?
How and where you convert the query to a spreadsheet ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[sna1maa14]

I have a command button rigth now on a form that I click.

On Error GoTo Export_Err

'Transfers query to spreadsheet DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryContracts", "d:\data\matttest.xls"

Export_Exit:
Exit Sub

Export_Err:
MsgBox Error$
Resume Export_Exit

Thanks for the help
Sorry rplies so slow I'm trying to work and check back.

 

[sna1maa14]

Yes Table 1 hold the area list with the email address for each area.