Pass on filter from one form to the next.

[gemadan96]

I have a form that lists members. It includes an e-mail field. I've created a button control that will allow the user to send to all the members with e-mails or to a filtered list of e-mails. What I would like to do now is create a similar button that can send to either the entire list or a filtered list, but prior to sending I want to display a form to request input for the message body. This will be for quick messages, like a text message. Basically I want to filter the list, then click the button, a form will popup with one text box to type the message, the user enters the text, and then they hit send to send the message.

Here's the code I have now:

Dim strRS As String
strRS = "Select * FROM MembersExt "
Dim strWhere As String
strWhere = ""
If Me.FilterOn = True Then
strWhere = Me.Filter
strRS = strRS & " WHERE " & strWhere
End If
Dim appOutLook As Outlook.Application


Dim oEmailItem As MailItem
Dim rs As Recordset
Dim recipientList As String
If appOutLook Is Nothing Then
Set appOutLook = New Outlook.Application
End If
Set oEmailItem = appOutLook.CreateItem(olMailItem)
With oEmailItem
Set rs = CurrentDb.OpenRecordset(strRS)
If rs.RecordCount > 0 Then
rs.MoveFirst
Do Until rs.EOF
If IsNull(rs![E-mail]) Then
rs.MoveNext
Else
recipientList = recipientList & rs![E-mail] & ";"
.BCC = recipientList
rs.MoveNext
End If
Loop
Else
MsgBox "No one in this group has an email address"
End If
Set rs = Nothing
.TO = ""
.CC = ""
.Subject = ""
.Display
End With
Set oEmailItem = Nothing
Set appOutLook = Nothing

Any suggestions

[gemadan96]

Thinking about it some more I think that it would make sense that when the button control is clicked that it first brings up the form to enter the message body then when the user clicks the button in the message form it closes the form and passes the value on to the routine and sends the message. Just trying to wrap my head around how to code it.

[gemadan96]

Also the code to execute the sending of the message needs to wait until the message has been entered in the message form.

On the message form there will be a button to cancel which will need to close the message form and cancel execution of the rest of the code.

[June7]

Can open the message form with acDialog parameter. This will pause code execution by the first form until second form closes. Options for passing the body text back to the main form code are:

1. global variable declared in a general module so both forms can reference it or use TempVars variable

2. code in message form can set value of a textbox on the main form

[gemadan96]

Ok. I have the acDialog set. Can you give me an example of how you would declare a global variable or use a TempVars variable.?

[June7]

I've never used TempVars so will let you research that if you have interest.

As for a global variable, create a general module if you don't already have one. In the module header declare a public variable, like:

Public gstrBody As String

[gemadan96]

I have the global variable working. Now what I need is when I press the cancel button on the message form that it closes the message window and exits the code.

[June7]

You want to allow user to abort the email from the message form? Again, pass value back to the first form. Same options for methods. Then code in first form:

If booCancel = False Then
'code to send email
...
End If

[gemadan96]

Thanks for the help. I have it working as I wanted now.

[June7]

Ooops. Boolean might not be the best variable choice. The default state of Boolean is False. So unless you have code in the message form to set it True, it will always return False to the main form.

Could use a string variable and have the message form set it in Cancel button event. Then the test by main form would be like: If strContinue = "" Then

Also, at the end of the mail procedure need to reset the global variable back to its original state, in case want to run procedure again before closing db. Once set, the variable will hold value until code process is interrupted by runtime error or db is closed.

[gemadan96]

Actually I did add code to do that. If they click the cancel button it sets the value to True and if they click the send button it sets the value to false. I also added code to reset the global variable.

Once again thanks for the help.