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

Topic

New Reply

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%5B/url%5D 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

Reply | Quote

Replies

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?

Reply | Quote

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 –

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

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.

Reply | Quote

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

Dim strNewsletterEmails As String
strNewsletterEmails = Concat("QueryName", "FieldName", , , ";")
fctnOutlook strNewsletterEmails, ...

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

Reply | Quote

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…

Reply | Quote

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 Microsoft Knowledge Base Article 265203.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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 Microsoft Knowledge Base Article 265203.

Reply | Quote

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…

Reply | Quote

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

Dim strNewsletterEmails As String
strNewsletterEmails = Concat("QueryName", "FieldName", , , ";")
fctnOutlook strNewsletterEmails, ...

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

Reply | Quote

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 –

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

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.

Reply | Quote

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?

Reply | Quote