Email multiple documents to multiple recipients

[Oyseka]

Hi All, I want to email statements from my solution to all recipients who have agreed to accept them this way and I have no idea how to script this. Having done a search on the statements to go out and then determining which of those will accept Email is fine so my found set now gives me my email list of clients. Some of the statements are multiple pages and some are single pages. Can someone assist please with how to script how I then print a PDF of a statement, attach it and send it and then move on to the next one.

System Windows 7, FMPA 11.03 probably going to use SMTPit from 360Works as there appears to be all sorts of issues with Outlook

Edited April 8, 2011 by Oyseka

[comment]

how to script how I then print a PDF of a statement, attach it and send it and then move on to the next one.

Hard to say without knowing what (and where) the "statement" is, in Filemaker terms. If it's only a matter of printing the current layout, then roughly:

# after finding the recipients

Go to Record [First]

Loop

Set Variable [ $filepath; Value: <path to file & file name> ]

Save Records as PDF [ File Name: “$filepath”; Current record ]

Send Mail [ Send via E-mail Client <or SMPT Server>; To: Clients::Email; Attachment: “$filepath” ]

Go to Record [Next, Exit after last]

End Loop

[Oyseka]

Hard to say without knowing what (and where) the "statement" is, in Filemaker terms. If it's only a matter of printing the current layout, then roughly:

# after finding the recipients

Go to Record [First]

Loop

Set Variable [ $filepath; Value: <path to file & file name> ]

Save Records as PDF [ File Name: “$filepath”; Current record ]

Send Mail [ Send via E-mail Client <or SMPT Server>; To: Clients::Email; Attachment: “$filepath” ]

Go to Record [Next, Exit after last]

End Loop

Thank you Comment,

It was seeing it that made me realise where I was making life difficult for myself. Because it is being implemented on several variants of windows and on different computers and drive set ups, I was trying to be clever and get the various path names for all implementations instead of just giving a relative path. Thank you for your help.

How does Filemaker handle the the replacement of the PDF if you are on a slow connection and the first one has not been sent, does it pause until sent or do you have to build in a pause ?

Edited April 11, 2011 by Oyseka

[Oyseka]

Hard to say without knowing what (and where) the "statement" is, in Filemaker terms. If it's only a matter of printing the current layout, then roughly:

# after finding the recipients

Go to Record [First]

Loop

Set Variable [ $filepath; Value: <path to file & file name> ]

Save Records as PDF [ File Name: “$filepath”; Current record ]

Send Mail [ Send via E-mail Client <or SMPT Server>; To: Clients::Email; Attachment: “$filepath” ]

Go to Record [Next, Exit after last]

End Loop

The Statements are from the Invoice Table and lists the records where there is an outstanding balance on a transaction. As there are any number of transaction that may not yet be complete this scripting does not work as you want to sent all records belonging to a client at the same time. It works if you only have the records for one client in the found set but how do you step through all the clients with outstanding balances. As an aside when I tested the sending of a clients statement I get an error message each time which says the mail could not be sent successfully despite the fact that it was sent and received and the script takes 17 seconds to run with only seven records in the found set.

[comment]

It works if you only have the records for one client in the found set but how do you step through all the clients with outstanding balances.

One by one. From the first client record, find the related transaction that need to be reported and send the PDF. Go back to the Clients table and advance to the next record.

[Oyseka]

One by one. From the first client record, find the related transaction that need to be reported and send the PDF. Go back to the Clients table and advance to the next record.

Hi Comment, the find criteria is simple, Outstandind Blance>0 and Receive Via Email ="Yes". The records are then sorted by Client No. I don't know if this is the right idea but after that find,should I the select the Client ID in the found set and go to the related records in the client table, this would limit the clients to only those with outstanding balances. Sort the client records by Client number then loop, go to record first select the Client No field and copy. Go to the statement layout in the invoice table enter find, paste the client number and add a find for outstanding balance, save as PDF and Email, go to Client table select record next, exit after last etc

[comment]

I am afraid there's not enough information here to answer that. I understand you want to mail transaction records - but which transaction records exactly? If you have a relationship that shows the transaction records you need, then a simple GTRR would find them. Otherwise, you would need to perform a find (or constrain the found set) using additional criteria.

[Oyseka]

I am afraid there's not enough information here to answer that. I understand you want to mail transaction records - but which transaction records exactly? If you have a relationship that shows the transaction records you need, then a simple GTRR would find them. Otherwise, you would need to perform a find (or constrain the found set) using additional criteria.

Hi Comment, The transaction records are invoice summaries, but only the ones with outstanding balances. I did not think of using the client table to start to sequence as I was fixed on the Invoice table. There is only one relationship which is Client_Table- Client_ID / Invoice -Table- Client_ID. The Script steps are:

Go to Layout ["Invoicing" (Invoicing)]

Enter Find Mode[]

Perform Find[Restore] The finds are Outstanding_Balance>0 AND Accept_Email="Yes"

Sort Records[Restore;No Dialog]

Go to Field{Select/Perform; Invoicing::Client_No]

Go to Related Record [show only related records; Match found set; From table "Clients"; Using layout"Clients" (Clients)

SortRecords[Restore;No Dialog]

Go to Record/Request/Page[First]

Loop

Copy[select;Clients::Client_No]

Go to Layout["Statement" (Invoicing)

Print Setup[Restore;No Dialog]

Enter Find Mode[Restore] The Find is Outstanding_Balance>0

Paste [select;Invoicing::Client_No

Perform Find[]

Sort Records [Restore; No Dialog]

Set Variable[$filepath; Value:"file:/Avello/Avello.fp7"]

Save Records as PDF [Restore; No Dialog;"$filepath"; Records Being Browsed]

Send Mail [send via SMTP Server; No Dialog; to Clients::Email:; Subject "Avello_Statement" & " " & Invoicing::Month; Statement.pdf

Go to Layout[Clients" (Clients)]

Go to Record/Request/Page [Next; Exit after last]

End Loop

Go to Layout ["Statement' (Invoicing)]

The script steps do work, thanks to you, but I don't know if this is efficient and I still keep getting the error message from Filemaker "Email(s) could not be sent successfully" although some times they are.

[comment]

If I understand this correctly, if a client has two outstanding invoices they will get two e-mails - each with the same attachment containing both invoices.

[Oyseka]

If I understand this correctly, if a client has two outstanding invoices they will get two e-mails - each with the same attachment containing both invoices.

Hi Comment, No exactly the opposite, If a client has eight outstanding invoices he gets one email with a summary of all outstanding invoices. I have attached a screenshot of the output from the script.

[comment]

I have attached a screenshot of the output from the script.

Try again.

[Oyseka]

Try again.

Trying Again

Statement.pdf

[comment]

OK, I see. Well, I think your script could be simplified somewhat - but first I need to ask this:

Is Accept_Email a field in Invoices, or is it a client-wide attribute?

[Oyseka]

OK, I see. Well, I think your script could be simplified somewhat - but first I need to ask this:

Is Accept_Email a field in Invoices, or is it a client-wide attribute?

It is a Client specific field that is set to "No" on creation so the client has to opt in to receiving invoices and statements via Email. The field is looked up on invoice creation.

[comment]

So how about:

Go to Layout [Clients]

Perform Find [Restore][Criteria: Clients::Accept_Email="Yes" AND Invoicing::Outstanding_Balance>0]

Set Variable [$filepath; "filemac:" & Get (TemporaryPath) & "Avello_Statement.pdf"]

Go to Record [First]

#

Loop

Go to Related Record [show only related records; Match current record only; From table: "Invoicing"; Using layout: "Statement"]

Constrain Found Set [Restore] [Criteria: Invoicing::Outstanding_Balance > 0]

Save Records as PDF [Restore; No Dialog;"$filepath"; Records Being Browsed]

Send Mail [send via SMTP Server; No Dialog; to Clients::Email:; Subject "Avello_Statement " & Invoicing::Month; "$filepath"]

Go to Layout [Clients]

Go to Record [Next; Exit after last]

End Loop

Note that you could move the Set Variable [] step into the loop, and create a personalized filename for each client.

[Oyseka]

So how about:

Go to Layout [Clients]

Perform Find [Restore][Criteria: Clients::Accept_Email="Yes" AND Invoicing::Outstanding_Balance>0]

Set Variable [$filepath; "filemac:" & Get (TemporaryPath) & "Avello_Statement.pdf"]

Go to Record [First]

#

Loop

Go to Related Record [show only related records; Match current record only; From table: "Invoicing"; Using layout: "Statement"]

Constrain Found Set [Restore] [Criteria: Invoicing::Outstanding_Balance > 0]

Save Records as PDF [Restore; No Dialog;"$filepath"; Records Being Browsed]

Send Mail [send via SMTP Server; No Dialog; to Clients::Email:; Subject "Avello_Statement " & Invoicing::Month; "$filepath"]

Go to Layout [Clients]

Go to Record [Next; Exit after last]

End Loop

Note that you could move the Set Variable [] step into the loop, and create a personalized filename for each client.

That works great , thank you very much. I have just changed the start and end layout to the Statement layout as they may get confused if they hit the "Run Statements" button and look at the client file rather than the Statement Layout. Thank you for your perseverance.