Referring to a query or table (1 Viewer)

nick5196

Registered User.
Local time
Today, 10:06
Joined
Feb 17, 2011
Messages
43
Within my DB I have a form which has a field for the students email address. I have a button within this form which runs an embedded macro and opens MS outlook (new message) with the current email as the recipient.

I would like a button on the switchboard that can send to ALL email addresses.
I have created a query which gathers all the addresses but am unsure as to how i should refer to this. I am not familier with mail merge, Ive been using a "send to" macro.

If you know how to refer to the query or table that would be great or if you have any other suggestions.

Thanks a lot, Nick
 

micks55

Registered User.
Local time
Today, 17:06
Joined
Mar 20, 2006
Messages
110
Hi Nick,
Firstly, I'm rubbish so listen to others before me.
I would...
OnClick, open the query as a recordset, loop through it until EOF, in each loop get the email address and send the email, next record.
Mike
 

nick5196

Registered User.
Local time
Today, 10:06
Joined
Feb 17, 2011
Messages
43
Firstly whats EOF? Secondly how to I open the query as a recordset (im a novice).

Thanks so much for your help
 

micks55

Registered User.
Local time
Today, 17:06
Joined
Mar 20, 2006
Messages
110
Ok. Firstly, I use automation so sending the email might be a bit different to what your doing but you should be able to figure it out. Just change my line that starts with SendMessage

Make your button (mine's calles btnProcess) then put an Event procedure in its OnClick Event.

Private Sub btnProcess_Click()
'open the recordset query.
'I use DAO and have a reference to DAO 3.6 in Tools References.
'If you don't understand that try removing the 'DAO.' Parts that follow.

'lets get going
Dim DB As DAO.Database, rs As DAO.Recordset
Set DB = CurrentDb

'now we get the query recordset
'(paste the SQL of your query in place of my example - leave the quotes in)
Set rs = DB.OpenRecordset("Select * From OrderDetails Where [OrderID] = " & [OrderID] & " ORDER BY SupplierID")
'you can also try Set rs = DB.OpenRecordset("yourqueryname")

'set up a variable for the email address
Dim nextAddress

'start a loop that stops when you get to EOF (end of file)
Do Until rs.EOF = True
If rs!myEmailField Like "*@*" Then 'this is doing a very basic test to avoid failures
nextAddress = rs!myEmailFiled
'use nextAddress to send your email
SendMessage (nextAddress, cc, subj, body, attach) 'change this to suit you
End If
'move to the next record. Very important or you'll never get out!
rs.MoveNext
Loop

MsgBox "Finished"

'tidy up. Also very important
rs.Close
DB.Close

End Sub

Let me know how you get on.
Mike
 

nick5196

Registered User.
Local time
Today, 10:06
Joined
Feb 17, 2011
Messages
43
I am having problems with the line
SendMessage (nextAddress, cc, subj, body, attach)
Also with specifying my query using SQL, i have changed the view to SQL but am unsure as to what i should add...it is only a simple query, one field-The email addresses from one table.

Thanks again for your help, i really appreciate it.
 

boblarson

Smeghead
Local time
Today, 10:06
Joined
Jan 12, 2001
Messages
32,059
Firstly whats EOF? Secondly how to I open the query as a recordset (im a novice).

EOF means End of File and that is what a recordset provides you to be able to specify to go to the end.

Here's how you can open a recordset with your query:
Let's say you have an Outlook object for Recipients already
Code:
Dim rst As DAO.Recordset
 
 
Set rst = CurrentDb.OpenRecordset ("YourQueryNameHere")
 
If rst.RecordCount > 0 Then
   Do Until rst.EOF
       olRecipients.Add(rst![COLOR=red]EmailFieldNameHere)[/COLOR]
       rst.MoveNext
   Loop
 
rst.Close
Set rst = Nothing
End If
 

nick5196

Registered User.
Local time
Today, 10:06
Joined
Feb 17, 2011
Messages
43
I have filled in the appropriate information to the code "boblarson" suggested, how should I use this in relation to Mike's code?

Thanks again
 

boblarson

Smeghead
Local time
Today, 10:06
Joined
Jan 12, 2001
Messages
32,059
It would be better if you post what you have and we can steer you that way.
 

nick5196

Registered User.
Local time
Today, 10:06
Joined
Feb 17, 2011
Messages
43
At the moment just this:
Dim rst As DAO.Recordset


Set rst = CurrentDb.OpenRecordset("qrystudentemail")

If rst.RecordCount > 0 Then
Do Until rst.EOF
olRecipients.Add (rst!email)
rst.MoveNext
Loop

rst.Close
Set rst = Nothing

Heres a screenshot of my query (not sure if it helps)
 

Attachments

  • QUERY.PNG
    QUERY.PNG
    17.7 KB · Views: 124

boblarson

Smeghead
Local time
Today, 10:06
Joined
Jan 12, 2001
Messages
32,059
Code:
Private Sub btnProcess_Click()
'open the recordset query.
' have a reference to DAO 3.x (x could be .51 or .6) in Tools References.
    Dim DB As DAO.Database
    Dim rs As DAO.Recordset
    Dim olApp As Object
    Dim olMsg As Object

    On Error Resume Next
    Set olApp = GetObject(, "Outlook.Application")
    If olApp Is Nothing Then
        Set olApp = CreateObject("Outlook.Appliction")
    End If
    If Err.Number <> 0 Then
        MsgBox "Outlook not installed", vbCritical, "Error"
        Err.Clear
        ExitHere
    End If
On Error Goto Error_Handler
    Set olMsg = olApp.CreateItem(olMailItem)
 
    Set DB = CurrentDb
    'now we get the query recordset
    '(paste the name of your query in place of my example - leave the quotes in)
    Set rs = DB.OpenRecordset("qryEmailAllStudents")
 
    With objMsg

        'start a loop that stops when you get to EOF (end of file)
        Do Until rs.EOF
 
            If rs!email Like "*@*" Then    'this is doing a very basic test to avoid failures
                .Recipients.Add (rst!email)
            End If
 
            'move to the next record. Very important or you'll never get out!
            rs.MoveNext

        Loop
 
        .Subject = "Whatever"
        .Body = "Whatever in the email"
        .Send

    End With
 
    MsgBox "Finished"
 
    'tidy up. Also very important
    rs.Close
    DB.Close
    Set rs = Nothing
    Set DB = Nothing
End Sub
 
ExitHere:
Exit Function
 
Error_Handler:
MsgBox "Error " & Err.Number & " - " & " (" & Err.Description & ") in procedure SendMail of Module Module2", , CurrentDb.Properties("AppTitle")
Resume ExitHere
Resume
End Sub
 

JANR

Registered User.
Local time
Today, 18:06
Joined
Jan 21, 2009
Messages
1,623
Here is a code you can use:

Code:
Function SendMassMail(Message As String, strSubject As String)
Dim rs As DAO.Recordset
Dim oApp As Object
Dim oMail As Object
Dim oRecipients As Variant
Const olMailItem = 0
On Error GoTo ErrorHandel
 
Set rs = CurrentDb.OpenRecordset("qryEmailAllStudents")
 
If rs.RecordCount > 0 Then
    rs.MoveFirst
    On Error Resume Next
        Set oApp = GetObject(, "Outlook.Application")
            If Err.Number <> 0 Then   ' Outlook is not open
                Err.Clear
                Set oApp = CreateObject("Outlook.Application")
            End If
   
 On Error GoTo ErrorHandel
    
    Do Until rs.EOF
            oRecipients = oRecipients & (rs.Fields("email")) & ";"
            rs.MoveNext
    Loop
    
    Set oMail = oApp.CreateItem(olMailItem)
    
    With oMail
        .To = oRecipients
        .Subject = strSubject
        .Body = Message
        .Display               '<--- Uncomment if you want to get control over Outlook mail
        '.Send                   '<--- Comment out if you want to get control over Outlook mail
    End With
End If
    
ExitPoint:
rs.Close
Set rs = Nothing
Set oMail = Nothing
Set oApp = Nothing
On Error GoTo 0
Exit Function
 
ErrorHandel:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure SendMassMail of Module mdlSendingEmail"
    Resume ExitPoint
      
End Function

Paste this in a Standard module so you can access it from anywhere. to call it you can put this on the click-event of a button

=SendMassMail("","")

or if you have a predefined message and subject:

=SendMassMail("Hello world","subjecttext")

JR
 

nick5196

Registered User.
Local time
Today, 10:06
Joined
Feb 17, 2011
Messages
43
So sorry, still having some issues.
With boblarsons code
If Err.Number <> 0 Then
MsgBox "Outlook not installed", vbCritical, "Error"
Err.Clear
ExitHere
in specific "exit here" throws up an error.

with JRs it is the naming of my button and which seems to be an issue. Its not even starting to run the code. I have named the button =SendMassMail("","") is that right?

Sorry for being so stupid!
Thanks a bunch for all your help.
 

boblarson

Smeghead
Local time
Today, 10:06
Joined
Jan 12, 2001
Messages
32,059
For JanR's code you put the call in the click PROPERTY of the event and not in the VBA window. For mine I forgot to put

GoTo ExitHere
 

Users who are viewing this thread

Top Bottom