03. Mail [11:34]

Description: This video provides an overview of exchanging data between FileMaker and Microsoft Entourage Mail using the Entourage Manipulator.

YouTube Preview Image


0:0 In this video we are going to look at exchanging data between FileMaker and Entourage Mail. At the top I have my FileMaker demo file, which comes complete with the demo you download. And at the bottom is Microsoft Entourage. Let’s navigate to the mail tab in our demo file, and we will perform a clean import from Entourage. As you can see in Entourage I currently have three existing email messages. One has an attachment and the other two do not. In FileMaker let’s first choose a mail folder to extract from. I will select my inbox, push clean import from Entourage. This message will alert me if I would like to save mail attachments in a temporary directory and import them into my FileMaker database. I will select save in order to imbed the attachments in FileMaker.

0:47 With a push of a button you have now successfully brought in three of your mail messages from Entourage into FileMaker. To obtain specific details for each FileMaker record, simply click the blue arrow here. As you can see, I now have one of three records in FileMaker and the third message has my attachment which is imbedded in FileMaker in this container field here.

1:10 Once looking at the specific record in FileMaker, you have the option to delete this message in Entourage. Let’s navigate to my first message, and I will select delete in Entourage. Let’s cascade this side by side with Entourage, select delete and as you can see the message has now been permanently deleted from Entourage. Likewise, I can also create a new FileMaker record and push that record into Entourage. Simply select “New FileMaker record”; let’s expand this a little bit here so you can see, at the top you will note that Entourage ID and the modified date have not been assigned yet. In our demo file you have the option to set as HTML, set as Rich Text or set as Plain Text. You could even go one step further and actually preview the email before sending.

1:58 Here I can add an attachment if I please. Then once ready to send, simply click send mail and off it goes. In my outbox you can see that I have my one sent message ready for delivery. We will delete this message as it is not a valid email address. Likewise I can also move an email message in Entourage directly through FileMaker by using this “Move Mail” tab here. First I will confirm that the mail message we are going to move is the message without the attachment. Let’s navigate over to the “Move Mail” tab, select my current folder, which is the inbox, the destination folder which is my junk email, simply select move email and I have successfully moved this mail to the junk email folder from FileMaker. As you can see below in Entourage the mail message has been moved from my inbox into my junk email. Select close or done.

2:47 I now have all mail, including attachments in FileMaker. By creating a simple relationship that matches email addresses, I can build a complete archive of all emails sent or received related by contact that spans the entire organization. Imagine looking at a single contact in FileMaker and being able to review all emails sent or received from all users. It is now possible with the Entourage Manipulator. Let’s here from one of our FileMaker developers and have a look under the hood in order to better assist you with your FileMaker development

3:24 Let’s take a closer look at the script the pulls mail to see what’s happening under the hood. Here I have Entourage with a couple of emails and lets pull from the inbox. So I have pulled two emails. Let’s take a look at the script that does that. The script is called “Extract Email Records”. In the first part of the script we simply check the plug in, if it’s there and installed. Then we validate that the folder that you’ve selected here is actually populated with something. Then we go to FileMaker, create a new window and create some new records after we delete all the existing records so it gives us a place to put the new email in FileMaker. Then we authenticate with the plug in, and that just establishes a one time hand shake with the plug in between Entourage and FileMaker.

4:11 Here is where it really begins. There is a command in the plug in called PCEM_OpenFolder. The open folder command tells Entourage which folder we are looking at. In this case it would be set to inbox. Then this command asks the plug in if there are records. So PCEM_GetRecordCount determines if there are records in that folder. Then we prompt the user to save attachments or not. Then this command is PCEM_GetFirstRecord. This tells the plug in to have Entourage go to the first record and open it. At this point, once the record is open, we can simply start extracting the fields directly from Entourage. So here we are setting mail fields in FileMaker, such as the Entourage ID and the BCC and the body with data from Entourage. And we pull that field data using a command called “PCEM_GetFieldData” and it simply pulls by name. So you put the name of the field, and Entourage will provide that to the plug in and the plug in will provide it to you into FileMaker.

5:20 Then we check for attachments. Here is another great command: “PCEM_GetAttachmentCount”. That tells you if there are attachments on that associated email that you have opened in Entourage. If there are, then we do this logic and we simply grab the attachment, name here, this will provide the attachment name, and this will save the attachment. So it copies the attachment from the email and puts it on a specified path that you decide on your hard drive. Then after that you can either insert the attachment directly into FileMaker or leave it in the hard drive and reference it via a container field. And finally after we pull that one record, that one email record, we call the command “PCEM_GetNextRecord”. That tells Entourage to go to the next record in the folder. If you are at the last record, this will return the word end, E.N.D, and then you will know there are no more records in the Entourage mail folder that you’ve opened. So getting mail is simply a matter of opening the folder, going to the first record, pulling all the fields, go to the next record and you’re done.

6:24 Let’s take a look at what it takes to send mail. Send mail is a lot easier in the sense that it is a single command to send mail. So like the other one, we check for the existence of the plug in and that it’s activated, we authenticate, which is again a one time connection between the plug in and Entourage. In fact you only need to call this once per FileMaker session. So once you open FileMaker you can call authenticate and then you can run as many scripts as you would like after that. So to send mail, if you are using this command here, which we have stored here in a variable. Let’s take a closer look. So in this command we provide a comment here so you can see the breakdown. It’s PCEM_SendMail and the parameters are simply the To, the CC, the BCC, these are the email addresses that you are going to be sending it to. The subject that you would like, the body that you would like, the attachments and the optional use of a HTML formatted email or a plain text. So you can see here we have broken it down: PCEM_SendMail, we provide the To, the CC, the BCC. These are pulling right from fields in FileMaker, the Subject. Then here’s an option where you can actually use rich text, use this function here in FileMaker called “GetAsCSS”. And that function will send the plain text you have in FileMaker and provide the cascading style sheet information as it is passing it to the plug in. Therefore you can get rich text as a result.

7:56 And then this is just some simple logic that determines if there is an attachment in my first record of the portal, then go ahead and send that attachment, and you’re providing it a path to the attachment. And then finally here is that optional use HTML parameter. You can kind of see in FileMaker how we construct that. These are the fields that determine if it’s rich text or HTML. These buttons here when you click on them it just provides a sample HTML formatted email like you saw earlier in the video you could preview that to see what it looks like. But if I were to send that, I would go to my outbox; you can see there is the email that we just send from FileMaker.

8:33 In the case of rich text, it’s simply text that you format using the traditional FileMaker command, Bold, Italic, point size and color. And then notice how the rich text indicator is on. So we are going to use that GetAsCSS function to send this email and here it is plain text. So that makes it an easy way for users to format text in FileMaker and actually send that formatted text through Entourage. If you use the traditional send mail command in FileMaker it does not preserve the stylized text.

9:05 Let’s take a look at what else we have regarding mail. The script here actually pulls the mail folders. It’s and interesting script because we use the open folder commands so you open the folder inbox, but instead of getting the first record, you actually get the first folder. Then you can pull the folder type and loop through that to get all the folders within your inbox. In fact we use that script in this part of the demo when we gather the folder list that just went through and got all my folders within the email part of Entourage.

9:37 Then we can delete and individual email record. This might be handy, let’s say for instance you imported mail and wanted to delete it from Entourage, so you could actually delete the email and that’s quite simple. You simple just call the PCEM_DeleteRecord and give it the Entourage ID. So it’s one command to delete records.

9:58 And let’s take a quick look at another feature here called “Move Mail”. This allows you to specify the current folder where this is. For instance I have this in the inbox and I can specify, let’s say the drafts folder, let’s say I having something in my inbox that I want to put in the drafts folder or the junk email like you saw earlier in the video. The script to do that is called “Move Mail”. And in this script, we open the folder, then we open the records so that in order to know what mail we need to move we actually have to have a record ID. So you will actually have to pull the email into FileMaker before you can actually move it in Entourage. So assuming that we’ve pulled it and we have the Entourage ID, I open the record and then it’s one simple command: “PCEM_MoveRecord”, and this just tells it where you want to move it. Of course this has to be a valid folder in Entourage. In this case it would be /Junk E-mail, just like so. So moving mail is a single command. We created the move mail for people who, let’s say that you imported everything from the inbox and you wanted to move that to a folder called archive. It makes it a really handy way to know what email has been moved and imported rather than just importing from the inbox every time and going through all the old mail again. It just makes it an easy way to keep organized and to let the user know that that email has been imported.

11:19 Now imagine the power your organization can have to perform a bidirectional data exchange between Entourage and FileMaker. Once you can get your data into Entourage from FileMaker or vise versa, the possibilities are endless in your organization.