Okay, maybe up to speed now. Modify Groups and CitiesState queries and export the Members query.
More like this:
Code:
Dim myDB As Database
Dim qdf As QueryDef
Dim i As Integer
Dim strWhere As String, strGroups As String, strCities As String, strState As String, strSQL As String, strStateCities As String, flgAll As Boolean
Set myDB = CurrentDb()
With Me
For i = 0 To .MemberGroupSelector.ListCount - 1
If .MemberGroupSelector.Selected(i) Then
strGroups = strGroups & "'" & .MemberGroupSelector.Column(0, i) & "',"
End If
Next I
If strGroups <> "" Then strGroups = "WHERE [MemberType] IN (" & Left(strGroups, Len(strGroups) - 1) & ")"
If Not IsNull(.cboStateCode) Then
strState = "[State] ='" & .cboStateCode.Column(1) & "'"
End If
For i = 0 To lstCities.ListCount - 1
If .lstCities.Selected(i) Then
If .lstCities.Column(0, i) Like " All*" Then
flgAll = True
Exit For
End If
strCities = strCities & "'" & .lstCities.Column(0, i) & "',"
End If
Next i
If strCities <> "" Then strCities = "[City] IN (" & Left(strCities, Len(strCities) - 1) & ")"
strStateCities = strState & IIf(strState <> "", " AND ", "") & strCities
End With
If strGroups = "" And strStateCities = "" And Not flgAll Then
MsgBox "No criteria selected"
Exit Sub
End If
myDB.QueryDefs.Delete "Member-Groups"
Set qdf = myDB.CreateQueryDef("Member-Groups", "SELECT * FROM MemberGroups " & strGroups)
myDB.QueryDefs.Delete "qryCitiesAndStates"
Set qdf = myDB.CreateQueryDef("qryCitiesAndStates", "SELECT States.State, Cities.City, States.StateCode " & _
"FROM States INNER JOIN Cities ON States.StateCode = Cities.StateCode " & _
IIf(strStateCities <> "", " WHERE ", "") & strStateCities & " ORDER BY States.State, Cities.City;")
DoCmd.OutputTo acOutputQuery, "MemberEmailList", "ExcelWorkbook(*.xlsx)", "C:\Temp\ZipCodeRangeMemberEmailList.xlsx", True, "", , acExportQualityScreen