Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query returning list of e-mail addresses (XP-2003/na)

    Forgive this question if it is too simple but I have a query that returns a list of e-mail addresses depending on certain conditions. In VBA I have used a function that was posted on http://www22.brinkster.com/accessory/modules/005.shtml and it requires an e-mail address(es) as one of the arguments.

    My problem is that I can put them in individually but I want to create a string (strNewsletterEmails) with semi-colons separating each address and use that as the e-mail address argument. In essence, how do I take the result of a query and convert it to the required string?

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query returning list of e-mail addresses (XP-2003/na)

    Tell us more about the query, and how the e-mail address should be concatenated:
    - Do you have several records, each of which contain one e-mail address?
    or
    - Do you have several e-mail address fields in one record?

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query returning list of e-mail addresses (XP-2003/na)

    Hi Hans

    The query returns just a list of filtered e-mail addresses that meet a particular condition (monthly or weekly newsletter).

    Regarding that function at the site I mentioned in my previous post - reproduced below for the Lounge -

    <pre>Function fctnOutlook(Addr, Optional Subject, _
    Optional MessageText, Optional AttachmentPath, _
    Optional Urgency As Byte = 2, Optional EditMessage As Boolean = True)</pre>


    the Addr argument is what I want to make into a string that is a record returned by the query, eg " someone@microsoft.com ", is separated from the next record by a semi-colon as such " someone@microsoft.com;chiharu@hotmail.com; ..." etc.

    I suspect I need to maybe loop through the records returned by the query and then concatenate those with the ";" character but I am unsure of how to do that.

    And yes, only one e-mail address will be returned for each of the records that match the parameters of the query.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query returning list of e-mail addresses (XP-2003/na)

    You will find a custom concatenation function attached to <post#=301393>post 301393</post#>. Copy and paste the code into a standard module. You can use it when calling fctnOutlook:

    <code>Dim strNewsletterEmails As String</code>
    <code>strNewsletterEmails = Concat("QueryName", "FieldName", , , ";")</code>
    <code>fctnOutlook strNewsletterEmails, ...</code>

    where QueryName and FieldName must be replaced by the relevant names.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query returning list of e-mail addresses (XP-2003/na)

    Thanks Hans - with a little bit of revision that is exactly what I needed.
    Now, is there a limit to the number of characters that can be used in the "To:" field in an Outlook message? I imagine that I might "hit the wall" with a large number of addresses (currently around 400 - but I can't test the limit yet) and was wondering if other users had found a limit.

    Thanks again for the help...

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query returning list of e-mail addresses (XP-2003/na)

    If you are sending through an Internet Service Provider (ISP), that ISP may have set a limit on the number of recipients. If you are sending through Exchange Server, the default maximum is 5,000 recipients, but the Exchange administrator can change this, even set it to "No Limit". See <!mskb=265203>Microsoft Knowledge Base Article 265203<!/mskb>.

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query returning list of e-mail addresses (XP-2003/na)

    Thaks Hans

    I believe it is through Exchange 2000 Server so that alleviates my immediate fear that it would be limited. The fact that the default is 5 000 is great news now to check if there is involvement by the ISP...

    Leigh

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query returning list of e-mail addresses (XP-2003/na)

    You may want to send out the e-mails in batches of 50 to 100 recipients anyway - your Exchange admin may not like you sending 4,999 e-mails at once, even if the server allows it.

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query returning list of e-mail addresses (XP-2003/na)

    I think I'll worry about that when it happens. Presently there are <500 members and with filtering (depending on the type of e-mail correspondence) it doesn't seem to be greater than 100 per time.

    But your advice is, as usual, sage and definitely appreciated.

    Thanks, Leigh

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •