Emailing a List of Contacts from FileMaker

[SebastianWPB]

I posted this in another forum, but NO ONE bothered to even give it a go. I hope that perhaps someone can give me a little more insight in this forum.

Here's what I wish to do and here's how I assume it has to be done, but I lack the skills in scripting to make it so.

I have a field named phone1. When the data is entered into Phone1, it is also part of the data that results in text_address. Text_address should append the "@" symbol and strip away any formatting of that number for emailing. According to the value that is chosen from a pop-up menu, mobile.att.net, messages.nextel.com, etc is appended to @ in that field value. Like so: [email protected]

That's part one of the task. Secondly, I wish to have a button which will take all of those addresses and put them into my outlook client from the push of a button. This is already halfway done, because of the email script in FileMaker.

So, to review: (A) Script for formatting a field value into an email address based on two fields values. (: Pulling all email address values for text_address into my email client at the click of a button running a script.

I've looked into third party solutions, but I know this can't be that hard. I've already got the field adding the "@", now I just need to strip the special formatting and append the appropriate carrier information.

I'm including the file as an attachment, because I so desperately need to have this accomplished. Any help at all is sincerely appreciated. You're welcome to email me directly at: [email protected] or IM me via AIM: KSFCOM - this project should have been done weeks ago. This is the only part holding me up.

Copy of the_list.zip

[Fitch]

Here is part (A): TextToNum(phone1) & "@" & domain_field

[jfmcel]

This is what you need to do...

First create a calculation field with all the information required for an individual contact.

Create a value list using the calc field above.

Create a new calculation field using the design function ValueListItems. (You will need to type in the database name and value list name exactly.)

Use the newly create value list field for the body of your email.

Send email via script.

[SebastianWPB]

ifmcel,

I've created the calc field for the above. That's the text_address field. I then created the value list in a field named "domain_field" with all the possibilities and their values. Well, I thought I created the value list, but I'm not sure exactly what I did. I went to define values and told the mobile carrier field to use the value list for its values. Was this incorrect?

The third part of your post is where I get lost. I don't new what you mean by design function ValueListItems.

Take pity, this is my first time attempting custom scripting using FileMaker.

[jfmcel]

First I should make sure you are using a version that support design functions. I can't remember what version this function was implemented, but I think it is at least version 5.0.

You can check to see if the value list is defined correctly by simply selecting a field and go to field format and assign the value list to a pop-up list. Once you are sure the value list is implemented, go to define fields and create a new calculation field. The synax for the function is simple. For your database it should be...

ValueListItems("Copy of the_list.fp5", "domain_field")

[Fitch]

FYI version 4 supports design functions.

http://www.filemaker.com/ti/104673.html

Sebastian, I'm not sure you're following jfmcel's idea. I think it's probably a good method, which is:

1. Create the calculation described in my first post. Let's call that field eAddress

2. Create a value list, and instead of "Use Custom Values," select "Use values from a field" and choose the eAddress field we just made. Let's call this value list eValues

3. Create a calculated text field, let's call it eWow:

ValueListItems (Status (CurrentFileName), "eValues")

Now for the grand finale: create a script, go way down to the bottom of the commands and choose Send Mail. Then specify the eWow field from step 3 above as the CC address.

Here's where there's some confusion -- jfmcel thinks you want the addresses to go into the Body of your email. I suspect you want them to go in the To or CC. The problem is when you say, "Pulling all email address values for text_address into my email client," it's ambiguous -- what exactly do you mean? What is your goal? Is it to add these addresses to your email client's address book? Is it to send a different email to each person? The method we're showing you won't do that. It will allow you to send the same email message to a group of recipients.

[SebastianWPB]

I wish to create the text message address from two fields. Their carrier (spring, at&t, nextel, etc) and their mobile number. I then wish for my email client to pull all those final addresses into the TO area of my email client.

Eventually, I want to be able to customize which text messages are sent out by the click of the day of the week or venue selection.

I'm using 6.0 of the plain vanilla FileMaker Pro on the PC, although, this will be used on Mac and PC in the end.

[jfmcel]

I am not sure if this is relevent, but generally it is not a good idea to group email using the TO area. BCC is best, but it is not supported by the FM send email script step. If I wanted to group email from FM, I would used a looping script and individually email each recipient. This is slow, but avoids giving everyone on the list the email address of the other recipients.

[SebastianWPB]

The purpose of this list is to send text email addresses. Therefore, the number of people in the TO field is irrelevant. People who receive the text message don't seen anyone else who has also received it.

If anyone is willing to take my file and show me what you all are talking about, I would be greatly appreciative. I'm pretty confused at this point, as I'm a business man, and not a FileMaker Developer.

[jfmcel]

What step are you stuck on?

[SebastianWPB]

I'm stuck on everything. Here's my database. I don't understand what you people are talking about with all this. It sounds it would only take you two minutes to show me how to do this on my database. It only consists of one file.

Please help

[Pupiweb]

Try the attached file ..

If you've FM 6 it's even simpler :-)

GroupEmail.fp5.zip

[SebastianWPB]

Pupiweb,

Thanks for the file. However, I'm not sure what to do with it. Did you see my database, and do you understand what I'm trying to achieve? I need to grab all the text message addresses and put them in the TO line of my email client with the click on a button or running a script or whatever.

thanks in advance

[Pupiweb]

Look more carefully at the attached file, it does what you mention

I'm attaching a second, more "user friendly" version

Hope it helps

GroupEmail.fp5.zip

[SebastianWPB]

Pupiweb,

Thank you for your implementation of that script. I think I can work with it. My final issue is the value list that creates the email address from the two fields: Phone 1 and text address. I need the address to pull the value "sprint, at&t, verizon" into the email field appending the respective format. For sprint it's "Phone [email protected]", for verizon it's "Phone [email protected]" etc. Phone 1 is the number that has been entered.

I attempted to create a value list with this data, but I don't think I'm doing it correctly. Can you perhaps give me an example of how to do this?

[Pupiweb]

Fitch already provided the answer to this:

TextToNum(phone1) & "@" & domain_field

If you want to use this in a value list set the value list to take its values from the calc field

[SebastianWPB]

I read that incorrectly yesterday. That should be the answer to my problem, but one thing I can't seem to do is create a value list. How do I give the selection "sprint" the value of "messaging.sprintpcs.com" ? This is the current breakdown:

Cingular: message.mycingular.com

AT&T: mobile.att.net

Nextel: messaging.nextel.com

T-Mobile: tmomail.net

Verizon: vtext.com

I'm not sure where or how to do this. I already have a pop-up list created, but where do I give them values? Again, I'm using FileMaker 6

[jfmcel]

If you follow pubiweb's method you do not need a value list. But you do need to run a script to populate a global field. Following his method, you set the TO field in the Send Mail script step to the global you script just populated.

(My method allows you to avoid the script to populate the field. This is of course all unnecessary with FM 6.)

[SebastianWPB]

ifmcel,

I'm sorry, but what you just posted reads like Greek to me. I AM running FileMaker 6, and I need the above values to be appended to the text address. Can you show me an example of what you're talking about?

[jfmcel]

In FM 6, the Send Mail script step includes the option to use "all records in the current found set" when you specify a field value in the TO specification.

[SebastianWPB]

I don't see how what you just posted answered my question. I need to create addresses from two fields. I understand the emailing script, but it's not going to email the correct address without some doctoring in the scripting department.

[jfmcel]

I thought we had that part all figured out. Do you still not have a field with a calculated email address?

[SebastianWPB]

No, I apologize, but I never really understood the calculated email address field response. I was under the assumption that I was to do two different things. If this is not the case, please bring that to my attention.

I appreciate all of your insight and expertise!

[jfmcel]

Under Define Fields, create a new field. To do this first type a name, say "email_calc", in the text box and select the calculation radio button and the click Create. You will then get a dialog where you define the calculation. Towards the bottom click the text option. If the fields are correct, you should be able to just paste pubiweb's suggestion...

TextToNum(phone1) & "@" & domain_field

into the box and click OK. This is call concatenation and will produce a text field from the above elements. Filemaker will make sure the field name are correct and you can easily check the results.

[SebastianWPB]

I've done what you suggested. There's still the problem with getting the completed text address. So far it's just creating number@ and that's it. Well, I've actually had that part done for a long time.

Now I just need it to grab the carrier value and append the appropirately completed domain.

[jfmcel]

Looking at your database, I don't see the field that pubiweb calls "domain_field". If you don't have a field where you store the domain name of the recipients, you need to create it and populate it with the appropriate data.

[SebastianWPB]

I've got two fields now in my database:

domain_list and Phone_carrier

the Phone_carrier field contains the value list of the carriers. I don't know what to do now. I'm thoroughly confused.

[jfmcel]

You need (or have) a list and a value. The value is a simple text field with one carrier for each contact.