Email Script for FileMaker

Today a client sent me his email script using our plugin. It is based on one of our examples, but got quite a few enhancements.

First like with every professional developer, you see a few comments about what it does and where data is coming from. Also changes are logged at the end. Part of the standard setup of the methods is to disallow user abort and activate error capture to avoid error dialogs.

All the configuration details are not part of the script, but come from a configuration table. For tracing the soluton, a trace script is called to log what happens. Results from sending emails are stored in the table, so you can check for each email whether it worked and what the error message is. Please read the script below:

# ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----

# Function: Send e-mail

# Modified: 24-09-2018

# ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----

Allow User Abort [ Off ]

Set Error Capture [ On ]

# ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----

# Note Mailserver data comes from the table MAILACCOUNT

# Variables This script expects the following variables ($$) to be available:

$$Body, $$Subject, $$MailTo, $$MailCC. $$MailBCC, $$ReplyTo, $$att

# ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----

Set Variable [ $AssignParams ; Value: #AssignScriptParameters ]

# ----- BASIC PARAMETER STRING:

# # ( "KEY_MailAccount" ; "" )

# ----- PARAMETERS:

# $KEY_MailAccount >>> Primary Key mailaccount

# ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----

# DEBUG - MBS Trace ON

Set Variable [ $traceID ; Value: Get ( UUID ) ]

Perform Script [ Specified: From list ; “MBS | Trace” ; Parameter: # ( "action" ; "traceOn" ) & # ( "file" ; $traceID ) ]

# Prepare Fields

If [ $test ]

Set Variable [ $$BodyHTML ; Value: MBS( "Text.TextToHTML"; MAILTEXT::Body_NL ; 16 ) ]

Set Variable [ $$BodyPlain ; Value: MAILTEXT::Body_NL ]

Set Variable [ $$Subject ; Value: MAILTEXT::Subject_NL ]

Else

Set Field [ MAIL::Message_Body_HTML ; MBS( "Text.TextToHTML"; MAIL::Message_Body ; 16 ) ]

Set Variable [ $$BodyHTML ; Value: MAIL::Message_Body_HTML ]

Set Field [ MAIL::Message_Body_Plain ; MAIL::Message_Body ]

Set Variable [ $$BodyPlain ; Value: MAIL::Message_Body_Plain ]

End If

# Get Account Data

Set Variable [ $server ; Value: SQL_Single ( MAILACCOUNT::SMTP_Server ; MAILACCOUNT::CONSTANT_c ; MAILACCOUNT::KEYP_MailAccount ; $KEY_MailAccount ) ]

Set Variable [ $ssl ; Value: SQL_Single ( MAILACCOUNT::SMTP_SSLTLS_c ; MAILACCOUNT::CONSTANT_c ; MAILACCOUNT::KEYP_MailAccount ; $KEY_MailAccount ) ]

Set Variable [ $user ; Value: SQL_Single ( MAILACCOUNT::SMTP_UserName ; MAILACCOUNT::CONSTANT_c ; MAILACCOUNT::KEYP_MailAccount ; $KEY_MailAccount ) ]

Set Variable [ $pass ; Value: SQL_Single ( MAILACCOUNT::SMTP_Pass ; MAILACCOUNT::CONSTANT_c ; MAILACCOUNT::KEYP_MailAccount ; $KEY_MailAccount ) ]

Set Variable [ $auth ; Value: SQL_Single ( MAILACCOUNT::SMTP_AuthType ; MAILACCOUNT::CONSTANT_c ; MAILACCOUNT::KEYP_MailAccount ; $KEY_MailAccount ) ]

Set Variable [ $ssl ; Value: SQL_Single ( MAILACCOUNT::SMTP_SSL_c ; MAILACCOUNT::CONSTANT_c ; MAILACCOUNT::KEYP_MailAccount ; $KEY_MailAccount ) ]

Set Variable [ $port ; Value: SQL_Single ( MAILACCOUNT::SMTP_Port ; MAILACCOUNT::CONSTANT_c ; MAILACCOUNT::KEYP_MailAccount ; $KEY_MailAccount ) ]

If [ $auth = "AUTO" ]

Set Variable [ $auth ; Value: "" ]

End If

# Build Email

Set Variable [ $EmailID ; Value: MBS ( "SendMail.CreateEmail" ) ]

Set Variable [ $fromName ; Value: SQL_Single ( MAILACCOUNT::Sender_Naam ; MAILACCOUNT::CONSTANT_c ; MAILACCOUNT::KEYP_MailAccount ; $KEY_MailAccount ) ]

Set Variable [ $fromMail ; Value: SQL_Single ( MAILACCOUNT::Sender_Address ; MAILACCOUNT::CONSTANT_c ; MAILACCOUNT::KEYP_MailAccount ; $KEY_MailAccount ) ]

Set Variable [ $r ; Value: MBS ( "SendMail.SetFrom" ; $EmailID ; $fromMail ; $fromName ) ]

Set Variable [ $r ; Value: MBS ( "SendMail.SetHTMLText" ; $EmailID ; $$BodyHTML ) ]

Set Variable [ $r ; Value: MBS ( "SendMail.SetPlainText" ; $EmailID ; $$BodyPlain ) ]

Set Variable [ $r ; Value: MBS ( "SendMail.SetSubject" ; $EmailID ; $$Subject ) ]

Set Variable [ $r ; Value: MBS ( "SendMail.SetSMTPServer" ; $EmailID ; $server ; $ssl ) ]

Set Variable [ $r ; Value: MBS ( "SendMail.SetSMTPUsername" ; $EmailID ; $user ) ]

Set Variable [ $r ; Value: MBS ( "SendMail.SetSMTPPassword" ; $EmailID ; $pass ) ]

# Send to who?

# ##### DEBUG

If [ $test ]

Show Custom Dialog [ "E-mail test" ; "Naar welk e-mailadres wil je het testbericht sturen?" ; GLOBAL::DiDi1 ]

If [ IsEmpty ( GLOBAL::DiDi1 ) ]

Set Field [ GLOBAL::DiDi1 ; "gerard@freevoicetelecom.nl" ]

End If

Set Variable [ $$Mailto ; Value: GLOBAL::DiDi1 ]

Set Variable [ $$MailCC ; Value: "" ]

Set Variable [ $$MailBCC ; Value: "" ]

End If

# ##### END DEBUG

# Check for multiple addresses [TO]

Set Variable [ $Receipients ; Value: PatternCount ( $$MailTo ; "," ) + PatternCount ( $$MailTo ; ";" ) + 1 ]

If [ $Receipients > 1 ]

Set Variable [ $MailTo ; Value: Substitute ( $$MailTo ; [ "," ; ¶ ] ; [ ";" ; ¶ ] ) ]

Set Variable [ $counter ; Value: 1 ]

Loop

Set Variable [ $r ; Value: MBS ( "SendMail.AddTO" ; $EmailID ; Trim ( GetValue ( $MailTo ; $counter ))) ]

Exit Loop If [ $counter = ValueCount ( $MailTo ) ]

Set Variable [ $counter ; Value: $counter + 1 ]

End Loop

Else

Set Variable [ $r ; Value: MBS("SendMail.AddTO"; $EmailID; Trim ( $$MailTo ) ; Trim ( $$MailToNaam )) ]

End If

# Check for multiple addresses [CC]

If [ not IsEmpty ( $$MailCC ) ]

Set Variable [ $ReceipientsCC ; Value: PatternCount ( $$MailCC ; "," ) + PatternCount ( $$MailCC ; ";" ) + 1 ]

If [ $ReceipientsCC > 1 ]

Set Variable [ $MailCC ; Value: Substitute ( $$MailCC ; [ "," ; ¶ ] ; [ ";" ; ¶ ] ) ]

Set Variable [ $counter ; Value: 1 ]

Loop

Set Variable [ $r ; Value: MBS ( "SendMail.AddCC" ; $EmailID ; Trim ( GetValue ( $MailCC ; $counter ))) ]

Exit Loop If [ $counter = ValueCount ( $MailCC ) ]

Set Variable [ $counter ; Value: $counter + 1 ]

End Loop

Else

Set Variable [ $r ; Value: MBS ( "SendMail.AddCC" ; $EmailID ; Trim ( $$MailCC )) ]

End If

# Check for multiple addresses [BCC]

If [ not IsEmpty ( $$MailBCC ) ]

Set Variable [ $ReceipientsBCC ; Value: PatternCount ( $$MailBCC ; "," ) + PatternCount ( $$MailBCC ; ";" ) + 1 ]

If [ $ReceipientsBCC > 1 ]

Set Variable [ $MailBCC ; Value: Substitute ( $$MailBCC ; [ "," ; ¶ ] ; [ ";" ; ¶ ] ) ]

Set Variable [ $counter ; Value: 1 ]

Loop

Set Variable [ $r ; Value: MBS ( "SendMail.AddBCC" ; $EmailID ; Trim ( GetValue ( $MailBCC ; $counter ))) ]

Exit Loop If [ $counter = ValueCount ( $MailBCC ) ]

Set Variable [ $counter ; Value: $counter + 1 ]

End Loop

Else

Set Variable [ $r ; Value: MBS ( "SendMail.AddBCC" ; $EmailID ; Trim ( $$MailBCC )) ]

End If

# Check for multiple addresses [ReplyTo]

If [ not IsEmpty ( $$MailReplyTo ) ]

Set Variable [ $ReceipientsReplyTo ; Value: PatternCount ( $$MailReplyTo ; "," ) + PatternCount ( $$MailReplyTo ; ";" ) + 1 ]

If [ $ReceipientsReplyTo > 1 ]

Set Variable [ $MailReplyTo ; Value: Substitute ( $$MailReplyTo ; [ "," ; ¶ ] ; [ ";" ; ¶ ] ) ]

Set Variable [ $counter ; Value: 1 ]

Loop

Set Variable [ $r ; Value: MBS ( "SendMail.AddReplyTo" ; $EmailID ; Trim ( GetValue ( $MailReplyTo ; $counter ))) ]

Exit Loop If [ $counter = ValueCount ( $MailReplyTo ) ]

Set Variable [ $counter ; Value: $counter + 1 ]

End Loop

Else

Set Variable [ $r ; Value: MBS ( "SendMail.AddReplyTo" ; $EmailID ; Trim ( $$MailReplyTo )) ]

End If

# Use attachments?

If [ $test ]

If [ IsValid ( mailtext_MAILTEXTATTACHMENT__KEYText::CONSTANT_c ) ]

Set Variable [ $KEY_Mail ; Value: MAILTEXT::KEYP_Text ]

Go to Layout [ “Mail Text Attachement [DATA]” (MAILTEXTATTACHMENT) ; Animation: None ]

Perform Find [ Restore ]

If [ not Get ( LastError ) ]

Go to Record/Request/Page [ First ]

Loop

Set Variable [ $r ; Value: MBS( "SendMail.AddAttachmentContainer" ; $EmailID ; MAILTEXTATTACHMENT::Attachment ; MAILTEXTATTACHMENT::Filename ; "" ; "" ) ]

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

End Loop

End If

Go to Layout [ original layout ; Animation: None ]

End If

Else

If [ $$att > 0 ]

Set Variable [ $KEY_Mail ; Value: MAIL::KEYP_Mail ]

Go to Layout [ “Mail Attachments [DATA]” (MAILATTACHMENT) ; Animation: None ]

Perform Find [ Restore ]

If [ not Get ( LastError ) ]

Go to Record/Request/Page [ First ]

Loop

Set Variable [ $r ; Value: MBS( "SendMail.AddAttachmentContainer" ; $EmailID ; MAILATTACHMENT::Attachment ; MAILATTACHMENT::Filename ; "" ; "" ) ]

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

End Loop

End If

Go to Layout [ original layout ; Animation: None ]

End If

# Show mail

Set Variable [ $$source ; Value: MBS( "Text.ReplaceNewline"; MBS("SendMail.GetSource"; $EmailID); 1) ]

# Send mail message

Set Variable [ $curl ; Value: MBS("CURL.New") ]

Set Variable [ $r ; Value: MBS("SendMail.PrepareCURL"; $EmailID; $curl) ]

If [ Length ( $auth ) > 0 ]

# Leave empty for automatic

Set Variable [ $r ; Value: MBS ( "CURL.SetOptionLoginOptions"; $curl; "AUTH=" & $auth ) ]

End If

If [ $ssl = 0 ]

Set Variable [ $r ; Value: MBS("CURL.SetOptionSSLVersion"; $curl; 1 ) ]

Else If [ $ssl > 0 ]

// Set Variable [ $r ; Value: MBS ( "CURL.SetOptionUseSSL" ; $curl ; $ssl ) ]

Set Variable [ $r ; Value: MBS ( "CURL.SetOptionUseSSL" ; $curl ; 3 ) ]

If [ $ssl = 2 ]

Set Variable [ $r ; Value: MBS("CURL.SetOptionSSLVersion"; $curl; 6) ]

Set Variable [ $r ; Value: MBS( "CURL.SetOptionSSLCipherList"; $curl; "AES128-SHA:DES-CBC3-SHA" ) ]

Else

Set Variable [ $r ; Value: MBS("CURL.SetOptionSSLVersion"; $curl; 0 ) ]

End If

If [ not IsEmpty ( $port ) ]

Set Variable [ $r ; Value: MBS( "CURL.SetOptionPort"; $curl; $port ) ]

End If

Set Variable [ $r ; Value: MBS( "CURL.SetOptionUsername"; $curl; $user ) ]

Set Variable [ $r ; Value: MBS( "CURL.SetOptionPassword"; $curl ; $pass ) ]

Set Variable [ $r ; Value: MBS("CURL.SetOptionSSLVerifyHost"; $curl; 0) ]

Set Variable [ $r ; Value: MBS("CURL.SetOptionSSLVerifyPeer"; $curl; 0) ]

# Fetch results

Set Variable [ $sendResult ; Value: MBS("CURL.Perform"; $curl) ]

If [ $test ]

If [ MBS("CURL.Perform"; $curl) = "OK" ]

Show Custom Dialog [ "TEST" ; "Sent!" ]

Else

Show Custom Dialog [ "TEST" ; "Failed to sent!" & ¶ & "Fout: " & $sendResult ]

End If

Set Field [ MAILTEXT::SEND_Debug_Input ; MBS("CURL.GetInputAsText"; $curl) ]

Set Field [ MAILTEXT::SEND_Debug ; MBS("CURL.GetDebugAsText"; $curl) ]

Perform Script [ Specified: From list ; “MBS | Trace” ; Parameter: # ( "action" ; "traceSave" ) & # ( "table" ; SplitFieldTable ( MAILTEXT::SEND_Trace ; "t" )) & # ( "field" ; SplitFieldTable ( MAILTEXT::SEND_Trace ; "f" )) & # ( "file" ; $traceID ) ]

Commit Records/Requests [ Skip data entry validation ; With dialog: Off ; Force Commit ]

Else

If [ MBS("CURL.Perform"; $curl) = "OK" ]

Set Field [ MAIL::Status ; "Sent" ]

Set Field [ MAIL::Send_ts ; Get ( CurrentTimestamp ) ]

Else

Set Field [ MAIL::Status ; "Error" ]

End If

Set Field [ MAIL::SEND_Debug_Input ; MBS("CURL.GetInputAsText"; $curl) ]

Set Field [ MAIL::SEND_Debug ; MBS("CURL.GetDebugAsText"; $curl) ]

Perform Script [ Specified: From list ; “MBS | Trace” ; Parameter: # ( "action" ; "traceSave" ) & # ( "table" ; SplitFieldTable ( MAIL::SEND_Trace ; "t" )) & # ( "field" ; SplitFieldTable ( MAIL::SEND_Trace ; "f" )) & # ( "file" ; $traceID ) ]

Commit Records/Requests [ Skip data entry validation ; With dialog: Off ; Force Commit ]

End If

Set Variable [ $r ; Value: MBS("CURL.Cleanup"; $curl) ]

Set Variable [ $r ; Value: MBS("SendMail.Release"; $EmailID) ]

Commit Records/Requests [ With dialog: Off ]

# ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----

# ----- HISTORY of CHANGES

# 18-07-2018 Added Trace

# 01-08-2018 Added ReplyTo

# 17-09-2018 Added Authentication

# 24-09-2018 Small changes