Results 1 to 7 of 7
  1. #1
    Paul H's Avatar
    Paul H is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Location
    Richmond, VA
    Posts
    591

    Create Address List

    I've taken this task almost all the way to the finish line, but I've hit a snag at the end. Here is the code I am using to build my list.

    Code:
    Function BuildEmailList()
        Dim strSQL As String, rst As Recordset, strEmailList As String
        
            BuildEmailList = ""
            
        strSQL = "Select * from qryContactOrganization WHERE Closed = 0 AND (Email <> '' or not isnull(Email))"
        Set rst = CurrentDb.OpenRecordset(strSQL)
        
        With rst
            .MoveFirst
        Do While Not .EOF
            strEmailList = strEmailList & !EMail & "; "
            .MoveNext
        Loop
        
        End With
                                                                     
        rst.Close
        Set rst = Nothing
        
        BuildEmailList = strEmailList
    
    End Function
    I have printed this list in the immediate window and copied it into an email. It appears to recognize most addresses as valid email addresses, but for a couple of exception. Spaces are showing up in my string about every 40 email addresses. There are around a 1,000 total addresses. When I paste it into a word document it breaks the list up into similar groups with carriage returns between them. Pasting into Excel puts the same groups in different rows. I hoping you can tell by these clues what the problem might be. When I use this string in a sendObject command I get an error.

    Runtime error 2295:
    Unknown message recipients(s): the message was not sent.

    Code:
                        DoCmd.SendObject acSendReport, strReportName, acFormatPDF, strEmailList, , , strReportName & _
                                        "_" & Me.txtOrganizationName & "_" & Format(Date, "yyyymmdd") & ".pdf" 'Me.Name & " - " & Me.Name
    This is the first time I've tried to build an address list like this. I'm hoping you can find the fly in my ointment.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I suspect your use of OR as opposed to AND here (Email <> '' or not isnull(Email))
    As soon as either of those comparisons are True, the evaluation will stop, so in your case, can one of them result in the insertion of a space.
    Would it not be ([Email] <> "" AND [Email] IS NOT NULL)
    I believe your use of IsNull is the vba function and sql uses IS NOT NULL
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Paul H's Avatar
    Paul H is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Location
    Richmond, VA
    Posts
    591
    Actually I tried it both ways and the SQL returns the same record count.

  4. #4
    Paul H's Avatar
    Paul H is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Location
    Richmond, VA
    Posts
    591
    Simple solution, I think. Clearly my code was dropping in an extra character every 40 or so addresses, so I tried this and it appears to have worked.

    Code:
    BuildEmailList = Replace(strEmailList, " ","")
    I can't exactly test it without sending out a thousand emails, but I'll give it a live test later today. It looks promising.

    I'll still entertain any comments you might have.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    It was just a wild stab since I couldn't see the table data. I wasn't going to insult your intelligence by asking if there were spaces in any of the addresses, so it was the only thing I could think of. However, since you say it works, then I suspect when the address field is null, your appended space comes from the space in this part "; "
    If that's it, the only other fix I can suggest is an If block to skip the append, or maybe change it to ""

  6. #6
    Paul H's Avatar
    Paul H is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Location
    Richmond, VA
    Posts
    591
    Not a problem. I appreciate the feedback and don't be shy about insulting my intelligence. Overlooking the obvious is one of my specialties.

  7. #7
    Paul H's Avatar
    Paul H is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Location
    Richmond, VA
    Posts
    591
    I'm going to mark this one solved. It still mangles the occasional address, but works for 99% of them which is good enough for me. The oddballs may just be invalid addresses keyed into the system.

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

Similar Threads

  1. Replies: 4
    Last Post: 01-06-2018, 03:26 PM
  2. Replies: 7
    Last Post: 06-27-2016, 12:28 PM
  3. Create address, city, state, zip columns
    By tmcrouse in forum Queries
    Replies: 5
    Last Post: 06-08-2015, 12:51 PM
  4. Replies: 2
    Last Post: 08-22-2013, 12:02 AM
  5. Input Mask for an IP Address and Mack Address
    By baksg1995 in forum Access
    Replies: 18
    Last Post: 06-23-2009, 12:33 PM

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