Link to home
Start Free TrialLog in
Avatar of swill2003
swill2003

asked on

URGENT: collecting email addresses through tables based on a recordset of a form VBA

hi

i have a form which shows organisation details from an organisation table once a search has been performed on the organisation table.

the organisation table is linked to a contacts table which holds employees information.

i want there to be a button on the organisation details form that will get a list of all the employees email addresses for all the organisations in the recordset for whatever has been searched. the email addresses need to be seperated by " ; " . this will then be used to send an email.

 i have got my self all in a muddle and the code i need is very urgent.

the code i have i have pasted below and there are errors to do with the sql. and setting the recordset rst.

if you can see of a better way to do this than i have done or can see where my problem lies please tell me :)

many thanks
Sarah
x
Avatar of swill2003
swill2003

ASKER

Private Sub commemailall_Click()
'On Error GoTo Err_commemailall_Click
  Dim addressall2 As String
  Dim mysql As String
      Dim rst As Recordset
Dim emadd As String
Dim dbs As DAO.Database



DoCmd.GoToRecord , , acFirst
addressall2 = ""
Set Ors = Me.RecordsetClone
Ors.MoveFirst
With Ors
    Do While Not .EOF
       
    mysql = "SELECT [Organisation Details].[Company Name], [Contact Details2].Forename, [Contact Details2].Surname, [Contact Details2].Organisation, [Contact Details2].Email" & _
" FROM [Organisation Details] LEFT JOIN [Contact Details2] ON [Organisation Details].[Company Name] = [Contact Details2].Organisation" & _
" WHERE ((([Organisation Details].[Company Name])=[Forms]![Organisation Details1]![Company name]));"


Debug.Print mysql


    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("query 4")


        'Set rst = CurrentDb.OpenRecordset(mysql)
       
            With rst
                .MoveFirst
                    Do While Not .EOF
                        addressall2 = addressall2 & .Fields("Email") & ";"
                        If .Fields("Email") <> "" Then
                 CurrentDb.Execute "INSERT INTO tblLetters ( email, datesent, CompanyName, forename, surname ) SELECT '-1'" & ", '" & Now() & "', '" & .Fields("organisation") & "', '" & .Fields("forename") & "', '" & .Fields("surname") & "'"
                        End If
                        .MoveNext
                Loop
        End With

       
        .MoveNext
    Loop
End With

If addressall2 = "" Then
MsgBox ("There are no email addresses for any of the contacts")
Else


   
    Set myolapp = CreateObject("Outlook.Application")
    'Email.SetFocus
    emadd = addressall2
    Set myitem = myolapp.CreateItem(olMailItem)
    Set myRecipient = myitem.Recipients.add(emadd)
    myitem.subject = "Cardiff Credit Union"
    myitem.Display


End If
Set rst = Nothing
  Set Ors = Nothing


End Sub
where it says

 Set rst = dbs.OpenRecordset("query 4")

it actually says

 Set rst = dbs.OpenRecordset(mySql)
and this gets highlighted with a problem saying too few parameters expected 1
Ok, heres a KB article explaining the cause:

http://support.microsoft.com/default.aspx?scid=kb;en-us;216425

So it seems Access thinks one of the fields you passed to it doesnt exist, can you double-check each field in the SQL to make sure it exists/is in the table specified?
[Forms]![Organisation Details1]![Company name]

I noticed that table isn't in the JOIN's.
ok... i have read the microsoft bit...

 mysql = "SELECT [Organisation Details].[Company Name], [Contact Details2].Forename, [Contact Details2].Surname, [Contact Details2].Organisation, [Contact Details2].Email" & _
" FROM [Organisation Details] LEFT JOIN [Contact Details2] ON [Organisation Details].[Company Name] = [Contact Details2].Organisation" & _
" WHERE ((([contact details2].[Organisation])=[Forms]![Organisation Details1]![Company name]));"

the only fields on the form are the ones from the organisation details table.

is this why?

or if it is as you say

[Forms]![Organisation Details1]![Company name]));" maybe i could write this in as a variable and refer to it that way?

the form is bound to the organisation details table

thankyou for taking a look :) currently just gone midnight here! :)
maybe like this?

Dim name As String


DoCmd.GoToRecord , , acFirst
addressall2 = ""
Set Ors = Me.RecordsetClone
Ors.MoveFirst
With Ors
    Do While Not .EOF

       name = Company_Name.Text

    mysql = "SELECT [Organisation Details].[Company Name], [Contact Details2].Forename, [Contact Details2].Surname, [Contact Details2].Organisation, [Contact Details2].Email" & _
" FROM [Organisation Details] LEFT JOIN [Contact Details2] ON [Organisation Details].[Company Name] = [Contact Details2].Organisation" & _
" WHERE ((([contact details2].[Organisation])= name;"

what is the syntax to refer to a variable in this context?
ok i am giving up lol
i have to go to bed!

thanks 4 the help neway. i will still need to fix this problem so if you see ne miracles in the code please let me know

Good night
x
When you refer to variables, you must include them like:
" WHERE [ThisField] = '" & VarName & "'" since anything in the quotes is a literal text (unless it's referring to something within the remote Access database).
I'll be here tomorrow (I'm in EST time zone, by the way), and I'll try and help you get this resolved if the variable syntax doesn't fix it.
hi :) back again. i have been given an extensionm to get it sorted so i am a bit more relaxed now. it still says type mismatch when using the variable instead of the reference to the form.

ASKER CERTIFIED SOLUTION
Avatar of zonaltech
zonaltech

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
i created a new form and pasted this code behind a command button and it worked

Private Sub Command0_Click()

 Dim addressall2 As String
  Dim mysql As String
      Dim rst As Recordset
Dim emadd As String
Dim dbs As DAO.Database
Dim name1 As String
Dim Ors


addressall2 = ""



        [Form_Organisation Details1].Company_Name.Enabled = True
        [Form_Organisation Details1].Company_Name.SetFocus
   
       name1 = [Form_Organisation Details1].Company_Name.Text
       
 mysql = "SELECT [Contact Details2].Forename, [Contact Details2].Surname, [Contact Details2].Organisation, [Contact Details2].Email" & _
" FROM [Contact details2] " & _
" WHERE [Contact details2].[Organisation] = '" & name1 & "';"
Set dbs = CurrentDb
    Set Ors = dbs.OpenRecordset(mysql)
   
   

With Ors
                .MoveFirst
                    Do While Not .EOF
                        addressall2 = addressall2 & .Fields("Email") & ";"
                        If .Fields("Email") <> "" Then
                 CurrentDb.Execute "INSERT INTO tblLetters ( email, datesent, CompanyName, forename, surname ) SELECT '-1'" & ", '" & Now() & "', '" & .Fields("organisation") & "', '" & .Fields("forename") & "', '" & .Fields("surname") & "'"
                        End If
                        .MoveNext
                Loop
        End With
       
        MsgBox (addressall2)
       
End Sub
yey! i fixed it! it all works now :)
thankyou again for all your help!

i have posted the final code below...

thanks again
:)
Sarah
x

Private Sub commemailall_Click()


'On Error GoTo Err_commemailall_Click
  Dim addressall2 As String
  Dim mysql As String
      Dim Pop
Dim emadd As String
Dim dbs As DAO.Database
Dim name As String
Dim Ors

DoCmd.GoToRecord , , acFirst
addressall2 = ""
Company_Name.Enabled = True
        Company_Name.SetFocus
   
Set Ors = Me.RecordsetClone
Ors.MoveFirst
MsgBox ("This may take a few seconds please wait")
With Ors
    Do While Not .EOF
       
       name = .Fields("Company Name")
       
       If InStr(name, "'") > 0 Then
    name = Left(name, InStr(name, "'")) & Right(name, (Len(name) - InStr(name, "'")) + 1)
End If


   mysql = "SELECT [Contact Details2].Forename, [Contact Details2].Surname, [Contact Details2].Organisation, [Contact Details2].Email" & _
" FROM [Contact details2] " & _
" WHERE [Contact details2].[Organisation] = '" & name & "';"


Debug.Print mysql


    Set dbs = CurrentDb
    Set Pop = dbs.OpenRecordset(mysql)


        'Set rst = CurrentDb.OpenRecordset(mysql)
       
            With Pop
           
           
           
            If Pop.RecordCount <> 0 Then
                .MoveFirst
                    Do While Not .EOF
                    If .Fields("Email") <> "" Then
                        addressall2 = addressall2 & .Fields("Email") & ";"
                       
                 CurrentDb.Execute "INSERT INTO tblLetters ( email, datesent, CompanyName, forename, surname ) SELECT '-1'" & ", '" & Now() & "', '" & .Fields("organisation") & "', '" & .Fields("forename") & "', '" & .Fields("surname") & "'"
                        End If
                        .MoveNext
                Loop
                End If
        End With

       
        Ors.MoveNext
    Loop
    Command80.SetFocus
    Company_Name.Enabled = False
End With

If addressall2 = "" Then
MsgBox ("There are no email addresses for any of the contacts")
Else


   
   Set myolapp = CreateObject("Outlook.Application")
    'Email.SetFocus
    emadd = addressall2
    Set myitem = myolapp.CreateItem(olMailItem)
    Set myRecipient = myitem.Recipients.add(emadd)
    myitem.subject = "Cardiff Credit Union"
    myitem.Display


End If
Set Pop = Nothing
  Set Ors = Nothing


End Sub
Cool, so what's left to figure out now?
( i changed rst to Pop as it didnt seem to like rst )
Haha, talk about timing! We both posted at the same time :D
No problem! I'm glad we could get it solved so quickly.
lol :)

So does everything work now?
i think so yes!

thankyou for looking at my other open questions :)
the server one i wont know until i take the database to the company either 2 morrow or saturday so i shall try it out then if the way i have done it doesnt work.

The opening outlook one i gave up on as i figured as many different people are using the database with different outlook accounts they wouldnt be able to open each others ne way.

Kinda wish i had read that b4 thought then i could look into it more but i dont have time now really :(
might have a look at it 2morrow

Thankyou very much for all your help, ill close the other questions when i give them a try over the weekend :)
Thanks again!
S.
xx
Sounds great, Good luck!