Solved

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

Posted on 2004-08-25
20
250 Views
Last Modified: 2010-04-17
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
0
Comment
Question by:swill2003
  • 11
  • 9
20 Comments
 

Author Comment

by:swill2003
ID: 11896921
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
0
 

Author Comment

by:swill2003
ID: 11898042
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
0
 
LVL 2

Expert Comment

by:zonaltech
ID: 11898119
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?
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 2

Expert Comment

by:zonaltech
ID: 11898146
[Forms]![Organisation Details1]![Company name]

I noticed that table isn't in the JOIN's.
0
 

Author Comment

by:swill2003
ID: 11898205
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! :)
0
 

Author Comment

by:swill2003
ID: 11898218
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?
0
 

Author Comment

by:swill2003
ID: 11898476
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
0
 
LVL 2

Expert Comment

by:zonaltech
ID: 11899906
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).
0
 
LVL 2

Expert Comment

by:zonaltech
ID: 11899910
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.
0
 

Author Comment

by:swill2003
ID: 11900567
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.

0
 
LVL 2

Accepted Solution

by:
zonaltech earned 500 total points
ID: 11901981
" WHERE [contact details2].[Organisation] = '" & name & "';"
0
 

Author Comment

by:swill2003
ID: 11903878
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
0
 

Author Comment

by:swill2003
ID: 11904751
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
0
 
LVL 2

Expert Comment

by:zonaltech
ID: 11904756
Cool, so what's left to figure out now?
0
 

Author Comment

by:swill2003
ID: 11904758
( i changed rst to Pop as it didnt seem to like rst )
0
 
LVL 2

Expert Comment

by:zonaltech
ID: 11904768
Haha, talk about timing! We both posted at the same time :D
No problem! I'm glad we could get it solved so quickly.
0
 

Author Comment

by:swill2003
ID: 11904957
lol :)

0
 
LVL 2

Expert Comment

by:zonaltech
ID: 11905589
So does everything work now?
0
 

Author Comment

by:swill2003
ID: 11906128
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
0
 
LVL 2

Expert Comment

by:zonaltech
ID: 11906168
Sounds great, Good luck!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question