Access Public function - sending email through Outlook

[Carthesis]

Hello helpful tek community.

I'm having a minor problem with calling a user-defined module in Access. The code is some I obtained from the net that is deisgned to invoke an Outlook message that can be automatically sent from a database. I've rewritten the code slightly so that I can use it as a called function module because I want to use it to send a variety of different predefined messages.

The problem I seem to be having is in actually CALLING the module.

Code for the module below:

Option Compare Database
Option Explicit

Public Function SEND_EMAIL(Addresses As String, Subject As String, Optional BodyText As String)
On Error GoTo email_error

Dim BodyText As String
Dim Addresses As String
Dim Subject As String
Dim appOutlook As Outlook.Application
Dim MailOutlook As Outlook.MailItem
Set appOutlook = CreateObject("Outlook.Application")
Set MailOutlook = appOutlook.CreateItem(olMailItem)

    'Convert the string of addresses to lower-case, just in case...
    Addresses = LCase(Right(Addresses, Len(Addresses) - 2))
    
    'Check to make sure emails are to be sent automatically
    If MsgBox("Send reminder email to the following addresses?" & vbNewLine & vbNewLine & Addresses, vbYesNo, "Confirm e-mails") = vbYes Then
            
            Set appOutlook = CreateObject("Outlook.Application")
            Set MailOutlook = appOutlook.CreateItem(olMailItem)
            With MailOutlook
            .BodyFormat = olFormatRichText
            .To = Addresses
            .Subject = Subject
            .Body = BodyText
            '.DeleteAfterSubmit = True   'This would let Outlook send the note without storing it in your sent bin
            .Send
            End With
            
            'MsgBox MailOutLook.Body
            Exit Function
        
    Else
        MsgBox "Automatic reminder cancelled."
        Exit Function
    End If
            
email_error:
            If Err.Number = 287 Then
                MsgBox "Outlook security cancellation.  Stupid Microsoft..."
            Else
                MsgBox "An error was encountered." & vbCrLf & "The error message is: " & Err.Description
            End If
            Err.Clear
            Resume Error_out

Error_out:
End Function

And the code for the form that calls the module:

Private Sub SendReminder_Click()

Dim Recipients As String
Dim Subject As String
Dim BodyText As String

Recipients = Me![Email_Address].Value
Subject = Me![Mess_Subject].Value
BodyTest = Me![Mess_body].Value

SEND_EMAIL(Recipients, Subject, BodyText)

End Sub

I'm obviously missing a trick here, but I'll be damned if I know what it is.

Can anyone point me in the right direction?

Cheers.

 

[HarleyQuinn]

Either:

Take off the parentheses:

SEND_EMAIL Recipients, Subject, BodyText

or use Call

Call SEND_EMAIL(Recipients, Subject, BodyText)

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[Carthesis]

Awesome - that worked.

Knew I was missing something incredibly stupid. Serves me right for trying something new and clever on a Friday afternoon!

Thanks Harley!

 

[HarleyQuinn]

Glad I could help

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com