Concatenate entries in a column and export to mail client? (1 Viewer)

[Toxeia]

I'm working on a pretty simple database, and one of the features we're looking to implement is creating a distribution group for email. When entering an individual into the system there's an entry for their email and then a binary option for if they want to receive emails from our client. I'm as far as generating the query that provides only the list of email addresses which have approved solicitation by email but I don't know where to go from there. I am not even sure if at this point it's a query thing.

Essentially what I'm looking to do is take a column of varying length and concatenate the entries into a single field or export. If there's a way to get it into Outlook that would be even cooler. Maybe if I could create a hyperlink that would be generated from something like: ("mailto:"+GuardianInfo.EmailAddress[Row1]+","+GuardianInfo.EmailAddress[Row2]+","...) and so on.

 

[sneuberg]

If you want to concatenate values in a column together then I suggest taking a look at Allen Browne Web page on this subject. I don't think it would be hard to modify his code to get what you want.

To pull the result into Outlook I suggested look at this threads:

http://www.utteraccess.com/forum/Outlook-VBA-Access-t1993848.html
https://www.experts-exchange.com/questions/28289787/Gather-Information-from-Access-accdb-Using-Outlook-VBA-then-Insert-new-Record.html

 

[Toxeia]

It looks like those links for the email thing are to let outlook initiate either inputting data or pulling data from Access. Is there a feature in Access that would interact directly with Outlook in that manner to fill a To: field?

I had glanced at the Allen Browne link earlier and could not make heads or tails of it. I'll try to study it some more. It looks like it might be a little difficult to concatenate a field from different entries.

Thank you sneuberg

 

[sneuberg]

It looks like those links for the email thing are to let outlook initiate either inputting data or pulling data from Access. Is there a feature in Access that would interact directly with Outlook in that manner to fill a To: field?

Your statement

If there's a way to get it into Outlook that would be even cooler.

made me think that you wanted this to work from Outlook, e.g., the user of this would be an Outlook user. Do you instead want to initiate this email from Access? That's not a problem. It probably makes it easier. Please tell me more about how you want this to work in step, e.g.

1. User opens a form
2. User select something from combo box
3. etc

I had glanced at the Allen Browne link earlier and could not make heads or tails of it. I'll try to study it some more. It looks like it might be a little difficult to concatenate a field from different entries.

If you mean different records by different entries that precisely what Allen Browne's code does. For example if you have a table with records like:

[B]Last Name[/B]          [B] Email Address[/B]
Trump            trump@nuthouse.gov
Clinton          hillary@whitehouse.gov

The code could return

trump@nuthouse.gov, hillary@whitehouse.gov

so I believe that all you would need to do it change the comma to a semicolon.

 

[MarkK]

Nice sample data!

 

[Toxeia]

Oh jeez, I totally misunderstood. I already have a query that just returns two columns, EmailAddress where EmailSolicit=yes. I should be able to take that code and just run it through that query and give me the email addresses separated by commas or semicolon. The only question then is the best way to spit it out to the mail client.
Currently we have a switch board for a main menu, I don't care what method we use as long as it's just one or two clicks on that switch board to spit out the list of emails. I'll take suggestions on what the best way to do that is.

 

[sneuberg]

e as long as it's just one or two clicks on that switch board to spit out the list of emails.

Spit out the list of email to what? Please tell us how you want this to work. Two examples might be:

1. A button click on an Access form opens an Outlook message with the To: line filled in with the results of the Query. For this case just use DoCmd.SendObject
   
   
2. The user clicks on a macro in Outlook and a new email is initiated with the To: line filled in with the results of the query from Access. This is a little more involved. I could probably give you an outline of the code in a day or two.

What do you want?

 

[Toxeia]

The first one, that initiates from within Access. Even better it looks like we can fill out BCC instead of To so that we aren't exposing their addresses to others. If I run into issues with Allen Browne's code I'll come back, but I think you just saved our bacon on a prototype deadline sneuberg!

 

[Toxeia]

Well, I feel downright a fool. A team mate figured out how to do a mail merge. This is actually far better for our client because they can make a news letter in a word doc and then send it to the emails selected from our database.

For anyone that comes across this, we have an on click event that opens the query, selects the query we opened, and then runs the WordMailMerge.

This entirely negated the need to generate the list of emails. It might still be nice to have if they decide to go with a third party mail solution like MailChimp though, so I'll still give that a try later. When there's no more crunch.

 

[sneuberg]

I feel a bit foolish too for not thinking of that and suggesting it.

 

[Toxeia]

I'm not sure you could have made a leap to that from where I was. I was asking how to create the list of emails, not make an easy way to send emails. Or maybe you did and I missed it.

Now I only have one more thing to figure out and this database is complete.