Results 1 to 3 of 3
  1. #1
    DKM is offline Novice
    Windows XP Access 2007
    Join Date
    Dec 2009
    Posts
    2

    Create a Recipient List and add to 'To' line in blank email msg?

    Hello. I'm hoping somebody can help me with a project I am working on.

    Here is what I'm trying to do in a nutshell.
    - From main Switchboard, user selects 'Email Tenants'
    - On the next screen user has 2 options...'Email Tenants by Property' (which I'm not concerned about just yet) or 'Email all Tenants'.
    - When user clicks the 'Email all Tenants' button, a script runs that *should* search through the Tenants table and create a string of email addresses that will automatically be added to the 'To:' line in a blank email message.

    I am very much a beginner when it comes to programming, but I am able to create a blank email message with no problem. Unfortunately it does not add the email addresses to the 'To' line. So I imagine there is something wrong with the 'Do...Loop' in my code. So here is my code, if somebody can see something wrong with it, I'd appreciate any help I could get in correcting it. Thanks.

    Private Sub cmdEmail_Click()
    Dim rs As Recordset
    Dim vRecipientList As String

    Set rs = CurrentDb.OpenRecordset("SELECT * FROM Tenants")
    If rs.RecordCount > 0 Then
    rs.MoveFirst
    Do
    If rs!Email = Not Null Then
    vRecipientList = vRecipientList & rs!Email & ";"


    rs.MoveNext
    Else
    rs.MoveNext
    End If

    Loop Until rs.EOF

    DoCmd.SendObject acSendNoObject, , , vRecipientList, , , "Subject", , True

    Else
    MsgBox "No contacts."
    End If


    End Sub

  2. #2
    Guus2005's Avatar
    Guus2005 is offline Carbon based thingy
    Windows 7 Access 2003
    Join Date
    Jan 2010
    Location
    the netherlands
    Posts
    42
    the solution is that you can't check for nulls using the = sign.
    To check for nulls you need IS:
    Code:
    If rs!Email Is Not Null then ...
    'or
    If rs!Email Not Is Null then ...
    'or
    If not IsNull(rs!Email) Then ...
    What i would do:
    Code:
    Private Sub cmdEmail_Click()
        Dim rs As Recordset
        Dim strRecipientList As String
     
        strRecipientList = ""
        Set rs = CurrentDb.OpenRecordset("SELECT Email FROM Tenants") 'Dont select more than you need.
        Do While not rs.eof
           If Not IsNull(rs.Fields(0)) Then 
               strRecipientList = strRecipientList & rs.Fields(0) & ";" 'Fields(0) is faster than Fields("Email") or rs!Email
           endif
           rs.MoveNext
        Loop
     
        if len(strRecipientList) = 0 then 'checking for length 0 is faster than checking for an empty string
            msgbox "No Contacts"
        else
            DoCmd.SendObject acSendNoObject, , , vRecipientList, , , "Subject", , True
        endif
     
    End Sub
    Enjoy!
    Last edited by Guus2005; 02-04-2010 at 09:21 AM. Reason: added comments to clarify

  3. #3
    DKM is offline Novice
    Windows 7 Access 2007
    Join Date
    Dec 2009
    Posts
    2
    That worked! Awesome. Thank you very much.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 01-06-2010, 10:32 AM
  2. Replies: 0
    Last Post: 11-23-2009, 09:19 PM
  3. Replies: 1
    Last Post: 09-05-2008, 12:07 PM
  4. I can't create a drop-down list box
    By cpuser in forum Access
    Replies: 4
    Last Post: 02-18-2008, 10:11 AM
  5. Replies: 3
    Last Post: 03-24-2006, 08:40 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums