Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-10-2011, 11:58 PM   #1
nick5196
Newly Registered User
 
Join Date: Feb 2011
Location: West Sussex England
Posts: 43
Thanks: 17
Thanked 1 Time in 1 Post
nick5196 is on a distinguished road
Referring to a query or table

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

nick5196 is offline   Reply With Quote
Old 03-11-2011, 12:49 AM   #2
micks55
Newly Registered User
 
Join Date: Mar 2006
Location: Cambridge UK
Posts: 96
Thanks: 7
Thanked 4 Times in 4 Posts
micks55 is on a distinguished road
Re: Referring to a query or table

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
micks55 is offline   Reply With Quote
The Following User Says Thank You to micks55 For This Useful Post:
nick5196 (03-11-2011)
Old 03-11-2011, 07:24 AM   #3
nick5196
Newly Registered User
 
Join Date: Feb 2011
Location: West Sussex England
Posts: 43
Thanks: 17
Thanked 1 Time in 1 Post
nick5196 is on a distinguished road
Re: Referring to a query or table

Firstly whats EOF? Secondly how to I open the query as a recordset (im a novice).

Thanks so much for your help

nick5196 is offline   Reply With Quote
Old 03-11-2011, 08:53 AM   #4
micks55
Newly Registered User
 
Join Date: Mar 2006
Location: Cambridge UK
Posts: 96
Thanks: 7
Thanked 4 Times in 4 Posts
micks55 is on a distinguished road
Re: Referring to a query or table

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
micks55 is offline   Reply With Quote
The Following User Says Thank You to micks55 For This Useful Post:
nick5196 (03-11-2011)
Old 03-11-2011, 09:50 AM   #5
nick5196
Newly Registered User
 
Join Date: Feb 2011
Location: West Sussex England
Posts: 43
Thanks: 17
Thanked 1 Time in 1 Post
nick5196 is on a distinguished road
Re: Referring to a query or table

I am having problems with the line
Quote:
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.
nick5196 is offline   Reply With Quote
Old 03-11-2011, 09:53 AM   #6
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,823 Times in 1,577 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: Referring to a query or table

Quote:
Originally Posted by nick5196 View Post
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!EmailFieldNameHere)
       rst.MoveNext
   Loop
 
rst.Close
Set rst = Nothing
End If
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
The Following User Says Thank You to boblarson For This Useful Post:
nick5196 (03-11-2011)
Old 03-11-2011, 09:58 AM   #7
nick5196
Newly Registered User
 
Join Date: Feb 2011
Location: West Sussex England
Posts: 43
Thanks: 17
Thanked 1 Time in 1 Post
nick5196 is on a distinguished road
Re: Referring to a query or table

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

nick5196 is offline   Reply With Quote
Old 03-11-2011, 09:59 AM   #8
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,823 Times in 1,577 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: Referring to a query or table

It would be better if you post what you have and we can steer you that way.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
The Following User Says Thank You to boblarson For This Useful Post:
nick5196 (03-11-2011)
Old 03-11-2011, 10:05 AM   #9
nick5196
Newly Registered User
 
Join Date: Feb 2011
Location: West Sussex England
Posts: 43
Thanks: 17
Thanked 1 Time in 1 Post
nick5196 is on a distinguished road
Re: Referring to a query or table

At the moment just this:
Quote:
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)
Attached Images
File Type: png QUERY.PNG (17.7 KB, 84 views)
nick5196 is offline   Reply With Quote
Old 03-11-2011, 10:25 AM   #10
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,823 Times in 1,577 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: Referring to a query or table

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
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
The Following User Says Thank You to boblarson For This Useful Post:
nick5196 (03-11-2011)
Old 03-11-2011, 10:26 AM   #11
JANR
Newly Registered User
 
Join Date: Jan 2009
Location: Norway
Posts: 1,623
Thanks: 7
Thanked 166 Times in 159 Posts
JANR has a spectacular aura about JANR has a spectacular aura about
Re: Referring to a query or table

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
JANR is offline   Reply With Quote
The Following User Says Thank You to JANR For This Useful Post:
nick5196 (03-11-2011)
Old 03-11-2011, 11:12 AM   #12
nick5196
Newly Registered User
 
Join Date: Feb 2011
Location: West Sussex England
Posts: 43
Thanks: 17
Thanked 1 Time in 1 Post
nick5196 is on a distinguished road
Re: Referring to a query or table

So sorry, still having some issues.
With boblarsons code
Quote:
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.
nick5196 is offline   Reply With Quote
Old 03-11-2011, 11:19 AM   #13
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,823 Times in 1,577 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: Referring to a query or table

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
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
The Following User Says Thank You to boblarson For This Useful Post:
nick5196 (03-11-2011)
Old 03-11-2011, 11:25 AM   #14
nick5196
Newly Registered User
 
Join Date: Feb 2011
Location: West Sussex England
Posts: 43
Thanks: 17
Thanked 1 Time in 1 Post
nick5196 is on a distinguished road
Re: Referring to a query or table

Thanks so much, JanR's code is working a treat.

nick5196 is offline   Reply With Quote
Reply

Tags
mail merge

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Two fields in a table referring to the same field in a lookup table lmcc007 Queries 6 07-20-2009 10:02 PM
Referring to a VB variable in a query MatMac Modules & VBA 2 03-25-2008 10:23 AM
Lookup in table - how to make it find referring value? Nongkiau Forms 6 12-21-2007 01:48 AM
Referring to other table Ankabout Reports 0 11-07-2007 03:41 AM
Referring to fields in a table Cocis91 Reports 0 01-23-2002 12:14 PM




All times are GMT -8. The time now is 04:11 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World