Merge Letter Help

[CandiceCM]

Hello, I created a letter within my database that I want to send to my contacts through email with their name and company changing. I have created a layout with a text box with my letter within. I am able to change the contact name and company automatically but when I send the email my letter is not included. Can someone tell me what I'm doing wrong. I've written multiple scripts to no avail. As I've only been using FM12 for about a month I know I am missing something simple. Any advice or help will be very much appreciated.

[bcooney]

Do you want the "letter" to be a PDF attachment?

Perhaps this demo will help you.

[CandiceCM]

No, I can do that. What I want to do is send a mass email to all of my new contacts with the letter in my database being the body of the email. The contact and company name will change for each email. Maybe what I want isn't even possible?

[bcooney]

Yes, that is possible. My approach is to create a calc field that concatenates all the components. That calc field is the body of the email.

For example:

EmailBody_c = "Dear " & Contacts::FullName_c & "¶¶" & "Some text here " & Contacts::Company & " more text here."

[CandiceCM]

Thank you. So I was on the right track. Just seemed like a difficult way to do it. I got some of it to work. Here is what I wrote so far:

"Dear " & Concrete Contractors::First & ¶ &

"I understand you are involved with the " & Concrete Contractors::Project Name & "project. We would like to introduce our technology to you which provides 2x-4x the durability of typical Portland cement concrete at virtually the same cost. Additionally, if you have any secondary containment applications, we have materials that are extremely resistant to most chemicals without the need for coatings. I have included links to information for your review and if you would like to learn more about our technology and how we can decrease your maintenance costs and increase the lifespan of your concrete, please let me know. & ¶ &

"http://www.ceratechinc.com/Content/PDFs/CERATECH-Industrial-Cement-Solutions-6.12.pdf & ¶ &

"http://www.ceratechinc.com/Content/PDFs/KEMROK-BULK-Cement-Data-Sheet.pdf & ¶ &

"http://www.ceratechinc.com/Content/PDFs/ekkomaxx-BULK-Cement-Data-Sheet.pdf & ¶ &

"Sincerely, & ¶ &

"Maja Gray & ¶ &

"CeraTech, Inc. & ¶ &

"1500 N. Beauregard St., Ste. 320 & ¶ &

"Alexandria, VA 22311 & ¶ &

"Tel: 703-894-1130 x24 & ¶ &"

"Fax: 703-894-1068 & ¶ &

I'm still getting errors with it. I know there are certain text items you can't use like "x" "/" and "-" etc. How can I make these items appear in my email? I really appreciate your help. I've spent hours on this.

[bcooney]

Here are the rules. All hard-coded text is within quotes. So, "http://www.ceratechi...tions-6.12.pdf" & ¶ & (you've missed the closing quote here and elsewhere).

Also, you need some spaces. For example, before "project" you need a space. Otherwise Concrete Contractors::Project Name and project will run together. So, Concrete Contractors::Project Name & " project.

[CandiceCM]

Thank you so much. Going to take me awhile to get it right but this is the best way for me to learn.

[CandiceCM]

Okay, so now I have the letter as the body of my email but I can't get the contacts to automatically send to each specific email. I need to put this somewhere in my script right?

[CandiceCM]

I can get it to send the email to everyone in the "Email" category but is there a way to get to send to each person individually? I don't want to send the email addressed to 100s of people at a time.

[Lee Smith]

Hi Candice, and welcome to the Forum,

I deleted your duplicate post, and moved this Thread from the General Topic Area.

Please do NOT double post your questions.

Automatic message

This topic has been moved from "FileMaker Product Family → FileMaker Pro 12" to "The Presentation Layer → Reports, Printing & Publication → Email".

The FileMaker Pro 12 topic is reserved for questions regarding the new tools that were new to the FMP 12 version.

Lee

[CandiceCM]

Hello Lee, can you help with my problem?

[bcooney]

Candice,

I usually use a looping script.

Find your recipient records

Go to first record

Loop

Send Mail (contactEmail, bodycalc)

Go to Next, exit after last

End Loop

[CandiceCM]

Never used the Loop feature. I'll see if I can figure it out. Not even sure where that is but hopefully it is easy to find. Thank you

do I add this loop script to my original script with my calculation of my letter?

[CandiceCM]

I added loop to my script and it went crazy! Kept producing an email with all the recipients in one email over and over again. Had to force quit FM. Couldn't even force quit Mail. It kept launching the program over and over again. Crazy!

[Lee Smith]

Post a copy of your script. You probably have not structured it so it will exit properly.

You can copy your script by selecting Manage Script, highlighting the script in question, and then hitting the Print Icon. This will take you to Preview, then just select all, copy and then paste it into a Reply to this Thread.

If you had the Advance Edition of FileMaker, you could watch your script run, and stop it where it breaks within the Script Debugger.

You can exit a script by using the Cmd + . keys (aka Apple + Period). If you tried this, and it didn't work, I'm guessing you're using the User Abort script step, if so, disable it until you have your script doing what you are wanting.

HTH

Lee

[beverly]

Check the "help topic" on Send Mail script step:

=== copy ===

Click Specify to display the Send Mail dialog box, where you can set options for your mail. Choose the method by which to send mail, E-Mail Client or SMTP Server. (If you choose SMTP Server, the SMTP Options dialog box appears. For information about setting SMTP options, see Entering or editing SMTP options.) Then choose to create One email using data from the current file, or create Multiple emails (one for each record in found set).

=== end ===

note the "Multiple emails (one for each record in found set)",

If you are looping AND checked this, you'll run into problems. So, yes, if you can please post your script!

Beverly

[CandiceCM]

Lee and Beverly, thank you so much for your advice. Here is a copy of my script:

Send Ceratech Letter by Email

Set Error Capture [ On ]

Freeze Window

Go to Layout [ “Ceratech Materials Letter” (Concrete Contractors) ]

Insert Text [ ] [ Select ]

Set Field [ ]

Send Mail [ Send via E-mail Client; To: Concrete Contractors::Work Email (collect addresses); Subject: "CERATECH Materials"; Message: "Dear " & Concrete Contractors::First& ":¶¶"

&"I understand you are involved with the "& Concrete Contractors::Project Name &" project. We would like to introduce our technology to you which provides 2x-4x the durability of typical Portland cement concrete at virtually the same cost. Additionally, if you have any secondary containment applications, we have materials that are extremely resistant to most chemicals without the need for coatings. I have included links to information for your review and if you would like to learn more about our technology and how we can decrease your maintenance costs and increase the lifespan of your concrete, please let me know. " &¶&¶ &"http://www.ceratechi...utions-6.12.pdf " &¶&¶ &"http://www.ceratechi...-Data-Sheet.pdf " &¶&¶ &"http://www.ceratechi...-Data-Sheet.pdf " &¶&¶ &"Sincerely, " &¶ &"Maja Gray " &¶ &"CeraTech, Inc. " &¶ &"1500 N. Beauregard St., Ste. 320 " &¶ &"Alexandria, VA 22311 " &¶ &"Tel: 703-894-1130 x24 " &¶ &"Fax: 703-894-1068 " &¶; Attachment: “$PATH” ]

I had to take a break as this was wearing me out! I think I took the loop command out before I left for the day on Friday because I almost blew my email up. Please let me know what you see and what I can do to get this script to send to each person individually. Thank you!

Candice

[beverly]

In the dialog:

1. Send via: E-mail client (ok)

2. Create: (needs to be) One email using data from the current record

3. To: Concrete Contractors::Work Email (this is ok)

4. (do NOT check) Collect addresses across found set

5. the rest should be ok (subject, message, etc.)

if you LOOP like this:

Go to Record/Request First

Loop

Send Mail (with the above settings)

Go to Record/Request Next, Exit after last

End Loop

The above *will* create ONE email (dynamic "To" and "Message") per record. It did for me.

I advise that you be disconnected from Internet and let it create from a found set of 3-4 records to test. Run the script and see if you get what you want. You can always delete the test records from your email client. If you are trying to run this from a Hosted file, obviously you'd need to be ON a network and you can't test. See if you can create a small test database with the few necessary fields, to test OFF the internet. The script would be exactly the same for the hosted file.

The Send Mail dialog is tricky in the way that you have things selected and whether you loop or not.

HTH,

Beverly

[LaRetta]

Hi Candice,

I'm on iPad right now so I can't see whether you have Advanced but if you are ever working in a looping script and you can't step through it with debugger then you should back up your file first. You can File > Save A Copy As 'copy'. When it crashes your file can be damaged and it actually shouldn't be used again because you won't necessarily see the damage. One should back up when playing with recursive custom functions as well!

We've all found this one out the hard way! ;-)

[Lee Smith]

Hi LeRatta,

I didn't know that the profile didn't reflect on the iPad. Anyway, she is using client 12. Also, I went into a long explaination about the Debugger in my post above #15.

[LaRetta]

LOL, I only see one sentence about debugger Lee. But my point was simply in response to her run-away file and her crash ... as input for whenever working in scripts ... how working in loops and recursions should trigger a thought to back up. :-)

[CandiceCM]

Thank you everyone for your help. I'm still not quite getting the result I want. Now I can get it to only send to one person but it is only sending one email. Not to everyone in my found set. I'm sure I did something wrong in my loop command as this is the first script I've ever written in FM. I could not find a command that said "Got to Record/Reques tNext, Exit after last". Here is what I did:

Set Error Capture [ On ]

Freeze Window

Go to Layout [ “Ceratech Materials Letter” (Concrete Contractors) ]

Insert Text [ ] [ Select ]

Set Field [ ] Go to Record/Request/Page

[ First ]

Loop

Send Mail [ Send via E-mail Client; To: Concrete Contractors::Work Email (collect addresses); Subject: "CERATECH Materials"; Message: "Dear " & Concrete Contractors::First& ":¶¶"

&"I understand you are involved with the "& Concrete Contractors::Project Name &" project. We would like to introduce our technology to you which provides 2x-4x the durability of typical Portland cement concrete at virtually the same cost. Additionally, if you have any secondary containment applications, we have materials that are extremely resistant to most chemicals without the need for coatings. I have included links to information for your review and if you would like to learn more about our technology and how we can decrease your maintenance costs and increase the lifespan of your concrete, please let me know. " &¶&¶ &"http://www.ceratechinc.com/Content/PDFs/CERATECH-Industrial-Cement-Solutions-6.12.pdf " &¶&¶ &"http://www.ceratechinc.com/Content/PDFs/KEMROK-BULK-Cement-Data-Sheet.pdf " &¶&¶ &"http://www.ceratechinc.com/Content/PDFs/ekkomaxx-BULK-Cement-Data-Sheet.pdf " &¶&¶

&"Sincerely, " &¶ &"Maja Gray " &¶ &"CeraTech, Inc. " &¶ &"1500 N. Beauregard St., Ste. 320 " &¶ &"Alexandria, VA 22311 " &¶

&"Tel: 703-894-1130 x24 " &¶ &"Fax: 703-894-1068 " &¶ ]

Go to Record/Request/Page

[ Next ] Exit Script [ Result: Last ( Concrete Contractors::Work Email ) ]

End Loop

What am I doing wrong????? Once I learn this I will never forget. I appreciate your help.

[CandiceCM]

Hi LaRetta,

Thanks for the advice on backing up. With the crazy auto saving in FM I usually make a copy of my database every morning with a new date at the end in-case I screw something up. Do you have any advice on my looping problem?

Candice

[LaRetta]

Do you want to send to ALL Concrete Contractors or just some of them? I do not see where you are either showing all records once you go to Ceretech Materials or perform a find for only certain contractors? Anyway moving forward, it should look something like this (note the MAJOR error in the Go To Record/Request/Page[next] line particularly) ...

Set Error Capture [ On ] <--- remove this. You place this right before a step you want to test for an error but you have nothing to test here AND even if you did, you are not performing If [ Get ( LastError ) ] to do something in case you DID get an error.

Freeze Window

Go to Layout [ “Ceratech Materials Letter” (Concrete Contractors) ]

Show All Records ( or perform a find here ) <--- added this line

Insert Text [ ] [ Select ] <---- leave this off ... not sure why it is there.

Set Field [ ] <--- leave this off ... not needed

Go to Record/Request/Page [ First ]

Loop

Send Mail [ ... list your strings here like you have them ...]

Go to Record/Request/Page [ Next ; Exit after Last ] Exit Script [ Result: Last ( Concrete Contractors::Work Email ) ] <--- here you want the checkbox after you select that step. The checkbox is the lower left and it just says 'EXIT AFTER LAST.'

End Loop

Go To Layout [ original layout ] < added

The only other thing is that you have "To: Concrete Contractors::Work Email (collect addresses);" But you are not collecting addresses but rather listing one email address per email, looping each Concrete Contractor's record to grab their email address and first name as you go. So roughly it is:

1. Go to a layout based upon Concrete Contractors
   
2. Find certain contractors or remember to Show All Records
   
3. Go to first record and begin the loop
   
4. Send mail to first record
   
5. Go to next record, exiting after LAST RECORD
   
6. Return to the layout from which the script was fired
   

The biggest error was the Go To Record/Request/Page where you used Exit Script[]. Be sure and remove that Result and change to checkbox. I hope this moves you closer to the results you are looking for. Back up before each individual process so that, if it crashes, you can immediately replace the file and not lose the work you did just before that (and work you did after your morning back up). :^)

Edited September 27, 2012 by LaRetta

[LaRetta]

BTW, you are referencing $PATH in this script but it is a script variable (single $) so it will die at the end of the script. This also means if it is not set within this script, it will not exist. It is possible you are passing it from another script but even then I do not see where you are transferring it and it shouldn't be in quotes - it should be treated same as a field (without quotes). Just giving you a heads-up on it if the attachment doesn't work right. :^)

[CandiceCM]

I got it to work without making any changes to my script. I was performing a find and trying to send to only my found set. Once I changed to "multiple emails (one for each found set)" it did what I wanted it to do. I'm going to make a copy make the changes you suggested and see if I still get the same result or do you think I should leave it alone since it is working?

[CandiceCM]

Thank you LaRetta, I made the changes you suggested and it is now doing what I want. I had to remove "Show all Records" as it was sending the email to all of my 1100 contacts. Once I took that out it worked perfectly. Thanks for pointing out that checkbox. Totally overlooked it. You saved me! Thanks again for making my life easier!