Brian Dunning's FileMaker Custom Functions

VBScriptSendMail ( toAddress ; messageSubject ; messageBody ; attachment_path1 ; attachment_path2 ; attachment_path3 ; openOutlook )

For use on Windows - creates a VB Script that sends out an email with multiple (3) attachments.

  Average rating: 3.9 (70 votes) Log in to vote

Rodrigo Torres   Rodrigo Torres
Challenge Day
http://www.challengeday.org

Share on Facebook Share on Twitter

  Sample input:
VBScriptSendMail ( brian@email.address , Test Email , Hi Brian! Check out these attachments. , C:\test1.txt , C:\test2.txt ,
C:\test3.txt , 0 )
  Sample output:
Option Explicit
Dim ToAddress
Dim MessageSubject
Dim MessageBody
Dim MessageAttachment (3)
Dim oFileObj
Dim ol
Dim olMailItem
Dim ns
Dim newMail
Dim myRecipient
Dim N
ToAddress = "brian@email.address"
MessageSubject = "Test Email"
MessageBody ="Hi Brian! Check out these attachments."
MessageAttachment (1) = "C:\test1.txt"
MessageAttachment (2) = "C:\test2.txt"
MessageAttachment (3) = "C:\test3.txt"
Set oFileObj = CreateObject("Scripting.FileSystemObject")
Set ol = WScript.CreateObject("Outlook.Application")
Set ns = ol.getNamespace("MAPI")
Set newMail = ol.CreateItem(olMailItem) newMail.Subject = MessageSubject newMail.Body = MessageBody &vbCrLf newMail.Recipients.Add(ToAddress)
N = 1
Do Until N > 3
If oFileObj.FileExists(MessageAttachment (N)) then newMail.Attachments.Add(MessageAttachment (N)) End If
N = N + 1
Loop
newmail.send
' Clean up
Set ToAddress = Nothing
Set MessageSubject = Nothing
Set MessageBody = Nothing
Set MessageAttachment (1) = Nothing
Set MessageAttachment (2) = Nothing
Set MessageAttachment (3) = Nothing
Set oFileObj = Nothing
Set ol = Nothing
Set olMailItem = Nothing
Set ns = Nothing
Set newMail = Nothing
Set myRecipient = Nothing

  Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

FileMaker's Send Mail script step is great until you need to send multiple attachments. FileMaker only allows one attachment. It is a limitation that has not been expanded yet.

This is a workaround for the PC Windows, using Outlook and VBScript (which will be run by Windows Script Host, at least it works for me on XP). See the comments at the top for details on the parameters and how to use it. I'm sharing this since it took me quite a while to put together and get it working, but it is provided "As is" and I make no guarantees.

Have fun!
===============
6/8/09 - Updated to handle multiple paragraphs in the message body. - RT
6/10/09 - Updated to warn user if file does not exist. -RT
12/15/11 - Minor comment changes to clarify. -RT

 

Comments

Klaas   Klaas, Utrecht, Netherlands
Mar 24, 2013
Thanks! Had to change the line:
"newMail.Recipients.Add(ToAddress)"
to
"newMail.To = ToAddress"
because I got a error "operation aborted" code 80004004 at that line.

It works now.
 
Rodrigo Torres   Rodrigo Torres, Concord, California, US
Mar 25, 2013
Thanks! I appreciate your comment, as it may help others. I have it as shown and it's working for us. - Rodrigo
 
Ryan Thompson   Ryan Thompson, Minneapolis
Jun 22, 2013
Thanks for this. I'm working on something similar, and had come up with almost the same solution (setting a text field to contain my vbs code and exporting it with open, thereby running the vbs). Mine is tasked with navigating a POST website and the parameters are taken from a filemaker record's fields. So I have my vbs code all in the set field script step, concatenating the field values (parameters) with the code for dynamic POST searches.. My question is: is there any advantage to putting my vbs code into a custom function and passing parameters?

Now this might be getting too far off topic, but I'm seeking to retrieve info from the POST searches. My current approach is to have the vbs run the find and write the resulting url to a .txt, then import the .txt and parse within FM. Anyone have a better way? Is there a way to use the webviewer to navigate post websites? Thanks!
 
Jennifer Holland   Jennifer Holland, Santa Barbara, CA
Sep 6, 2015
I have no idea if anyone is monitoring this, but I wanted to say "thanks" and offer a tiny addition.

I wanted to put the recipeints in BCC instead of the TO field. I was able to, with a variation on Klaas's modification above.

Instead of: "newMail.To = ToAddress"
I used: "newMail.BCC = ToAddress"

It worked great!

THanks so much!
 
Rodrigo Torres   Rodrigo Torres, Concord, CA
Sep 6, 2015
I get an email when anyone posts here and it's good to know this is still helping!
Thank you for posting and thank you especially for the addition!
-Rodrigo
 
Jennifer Holland   Jennifer Holland, Santa Barbara, CA
Oct 26, 2015
I have a question that reveals my lack of understanding of Visual Basic: is there any way to have this accept a variable numbers of attachments?

I tried creating a text variable in my script, and replacing the "MessageAttachment (1) = \"" & attachment_path1 & "\" lines with the name of the text variable, passing that text variable and the quantity of attachments. As you might guess, it didn't work. Basically, it would put the name of the variable into the script, not the contents.

Is there something easy and obvious that I'm missing? Or is does this need to be a fixed number of attachments? I guess I could create separate functions for 1 to 10 attachments, and call the correct one, but that seems laborious.

--Jennifer

Here's how it came out:

ToAddress = "jenninsb@gmail.com"
MessageSubject = "test multiple attachments"
MessageBody ="2 attachments"
attachment_paths

...and so on...
 
Rodrigo Torres   Rodrigo Torres, Concord, CA
Oct 26, 2015
Hi Jennifer, Yes, this script can be changed to accept variable numbers of attachments. I know because that's how I have it implemented myself. My solution is to send an array to the custom function and then process that array so that the VB Script output has the right number of attachments for that particular email.

My solution, however, breaks up the processing into 3 separate custom functions that work together, and it's a little difficult to post those here since they don't work independently. Hmm... I'm open to any ideas on how best to share those. Thanks! Rorigo
 
Shai   Shai, Modiin
Jan 20, 2018
Hi Rodrigo,
Thank you so match for your sharing. Great solution to workaround the FM limitation.
2 question:
1)How to avoid the message If you don't attach any attachment or you don't attach one of the 3 attachment?
2) How to add Bcc: and Cc: ?
Thank you for your sharing !!!
Appreciate
Shai
 
Rodrigo Torres   Rodrigo Torres, Concord, CA
Jan 20, 2018
Hi Shai,
You are welcome!
1) Which message are you referring to?
2) This function doesn’t address cc or bcc. You can see Jennifer’s comment above on how she changed the function to accommodate cc.

Another way of making changes to an email (like adding cc or bcc) is to direct FM to open the email in Outlook before sending. In Send Mail, there’s an option for dialog on/off. If you set it to on then the email will open in Outlook (and not just send automatically) and at this point cc or bcc can be added. See https://www.filemaker.com/help/15/fmp/en/index.html#page/FMP_Help/send-mail.html

Of course at this point other attachments can also be added. This function is a way of adding additional attachments programmatically.
 
Shai   Shai, Modiin
Jan 21, 2018
Hi Rodrigo,
Thank you so much for your prompt answer.
The message i refer belong to "The following file cannot be found and will not be attached:" when there is no attached at all or when do you attach only one attachment.

I'll follow Jennifer's comments.

Tank again !

Shai
 

Log in to post comments.

 

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

Under construction. Email me your wish list for improvements.