Unique queries then email!

[jon34]

Hi
Hope someone can help with this. I think i am trying to punch above my weight now !! but here goes
I have a database. one table with customer names and their email and another table with all the customer data.
Table 1
<TABLE style="WIDTH: 120pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=160><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=64>Cust</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 72pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=96>Email</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>Cust1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>email1@co.uk</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>Cust2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>email2@co.uk</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>Cust3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>email3@co.uk</TD></TR></TBODY></TABLE>

Table 2
<TABLE style="WIDTH: 238pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=316><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" span=2 width=78><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=64>Cust</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 72pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=96>Phone</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=78>Address</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 59pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=78>Post Code</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>Cust1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Cust1DataA</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Cust1DataB</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Cust1DataC</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>Cust2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Cust2DataA</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Cust2DataB</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Cust2DataC</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>Cust3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Cust2DataA</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Cust2DataB</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Cust2DataC</TD></TR></TBODY></TABLE>

What i need is to Individaully take each of the customers in table 1 and produce a query just for that customer from table 2 (could be multiple lines). i dont need to see or save the finished data really, just email it to the corresspnding email in table 1 for that customer. Then move to the next customer on table one and again produce the query just with their data from table 2 and email again. and so on through the whole list in table one.
I presume it will be code and macros but way beyond my experience !! im using access 2010(if that makes a difference)
Thanks in advance

 

[HiTechCoach]

Here is a n example that could be easily modified to send out emails.

Batch Printing and Save As PDF

 

[pooratlas]

This code creates rtf from your query and creates email.

Public Function fncEmailJon34Query() As Boolean
'http://www.mrexcel.com/forum/showthread.php?t=582742
'joe lavery / pooratlas Oct 2011

'error handling
On Error GoTo Email_Err

Dim db As Database
Dim recE As Recordset   'recordset of emails
Dim qdf As QueryDef     'Microsoft DAO 3/6 Object reference
Dim strQueryName As String  'name of query to be revised
Dim strSQL As String        'revised SQL for the query

Dim strCustEmail As String
Dim strCustName As String
Dim strESubject As String

Set db = CurrentDb
strQueryName = "qJon34ResultQuery"

'create a recordset of all emails from Table1
Set recE = db.OpenRecordset("SELECT Jon34Table1.Cust, Jon34Table1.Email " & _
"FROM Jon34Table1 " & _
"WHERE (((Jon34Table1.Email) Is Not Null)) ;")

While Not recE.EOF      'for each record in the recordset

    strCustEmail = recE!Email
    strCustName = recE!Cust
    Debug.Print Time(); strCustEmail
    
        '******** query def update ***********
        'create an SQL using each Customer Email to replace the data query's static SQL
        strSQL = ("SELECT Jon34Table2.Cust, Jon34Table2.Phone, Jon34Table2.Address, Jon34Table2.[Post Code] " & _
        "FROM Jon34Table1 INNER JOIN Jon34Table2 ON Jon34Table1.Cust = Jon34Table2.Cust " & _
        "WHERE (((Jon34Table1.Email)=""" & strCustEmail & """)); ")
        
        'revise the query definition
        Set qdf = CurrentDb.QueryDefs(strQueryName)
        qdf.SQL = strSQL
        '******* end query def update ********
    
    strESubject = "email subject"
    'during testing override the cust email destination with your email
    strCustEmail = "jon34@yourdomain.com"
    
    'create the email w/ attached rtf of query
    'last parameter allows editing of the email before sending
    'to send without viewing email use: False
    DoCmd.SendObject acSendQuery, strQueryName, acFormatRTF, strCustEmail, , , strESubject, "Dear " & strCustName, True
  
    'move to next record
    recE.MoveNext
    
Wend

recE.Close
db.Close
qdf.Close
RefreshDatabaseWindow

Exit Function

Email_Err:
    MsgBox "Error sending object: " & strCustEmail & Err.Description
    Exit Function
    
End Function

Forgive any novice coding