Creating an Email with File Attachment from Hyperlink

The following requirement from one of our customers shows how using [Function:..] expressions you can implement many useful features.

I have a form that contains a field that is a hyperlink to a document stored on a shared drive.  I want to create an automated mail merge that creates an email message an automatically attaches the document that is stored as in the field: Document.  Again it is a hyperlink to the shared drive where the document is.

Create a function to get the file from the hyperlink

  1. In your database, press CTRL-G to open the VBA editor
  2. Insert a new module (menu: insert module)
  3. copy the following code into it

Function getFileFromHyperlink()  As  String
Dim  str  As  String
Dim ar()  As  String
     str = DLookup("Hyperlink", "Documents", "ID=97")
    ar = Split( str, "#")
    getFileFromHyperlink = ar(1)
' If FileExists(getFileFromHyperlink) Then
' Debug.Print getFileFromHyperlink
' Else
' Debug.Print "file missing"
' End If
End  Function

  1. Save the module (CTRL-S), accept Module1 as name.
  2. Make sure the code compile (menu: debug compile)

Create the email mail merge using the wizard, set attachment using function

In the demo database, I start from the Documents table, as it contains a hyperlink. If you try it, make sure the link works (the linked document exists)

1.  Start the Mail Merge Wizard, select 'Email with file attachment(s)'

Create an Email with a PDF invoice attached  

In this demo you see how to set up an email mail merge. Starting point is a form, the result is a pdf invoice (already existed as Wordtemplate and made PDF), attached to an email (already existed). You also see the automatic file naming and storing.

2.  Give a name, and press [Next]

3.  enter an email address (there is no email field in the documents table)

4.  Create the email template, just drop a field or enter some text, it doesn't matter what, [Next].

5. Now we can enter the function that picks up the file from the Documents table, press [Add]

You should no see it in the attachments list

6. Press [Next], and [OK] to finish. Next the dialog appears that allows you to try it. You can verify that it works.