Function call

[Guest]

Good afternoon,

This question has 2 aspects to it: approach (Iwould like your opinion),
methodology (How can I make it actually work).

Here the situation...
I have a sub-routine that is used to perform validation of the form and then
based on the result send out e-mail(s).

I want to modify the sub-routine to call a function to pull the e-mail
addresses of the recipients from a table but do not know exactly how?!

I have the basic approcah/function (error trapping removed):
*****Function starts here*****
Dim db As Database
Dim rst As Recordset
Dim Address As String

Set db = CurrentDb()
Set rst = db.OpenRecordset("E-mail Tbl")

With rst
If .RecordCount > 0 Then
.MoveLast
Do Until .BOF
If [Func] = Group Then
Address = [EmailAdd]
.MovePrevious
Loop
End If
.Close
End With
*****Function ends here*****

The sub is as follows:
*****Sub Starts here*****
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg
' Add the To recipient(s) to the message.
If Forms![Drawing Management Frm]![Drawing Management Frm - Issue
Subfrm]![Drawing History Frm sous-formulaire]![Drawing Status Tbl
subform].Form![bypass check] = False Then
Call RecipientLookup("Checker")
For Each Art In Address
Set objOutlookRecip = .Recipients.Add(" & Item & ")
Next
End If

....
*****Sub Ends Here*****

I do not know how to retrieve the results from the function and use them
each to populate the TO field of the e-mail.

Thank you for your help.

Daniel

 

[SA]

Dan:

If you've got your e-mail sub in a public module, the way that you pass a
value to it is to add a parameter to the Sub's call. E.g. Say your sub is
called

Public Sub SendEMail()

You would change this to:

Public Sub SendEmail(strEmailToAddress As String)

Then in your email sub, the strEmailToAddress would be what you'd specify as
in the Recipients address

In your validation code, you'd call the SendEmail sub like this:

If [Func] = Group Then
Address = [EmailAdd]
Call SendEMail(Address)
.MovePrevious
Loop

HTH

 

[SA]

Dan:

If you've got your e-mail sub in a public module, the way that you pass a
value to it is to add a parameter to the Sub's call. E.g. Say your sub is
called

Public Sub SendEMail()

You would change this to:

Public Sub SendEmail(strEmailToAddress As String)

Then in your email sub, the strEmailToAddress would be what you'd specify as
in the Recipients address

In your validation code, you'd call the SendEmail sub like this:

If [Func] = Group Then
Address = [EmailAdd]
Call SendEMail(Address)
.MovePrevious
Loop

HTH