Link to home
Start Free TrialLog in
Avatar of ummjay
ummjay

asked on

send email by filter to database list in access

hey guys.
i have a code that basically takes all the emails in my database and composes an email in outlook, adds all the emails to the BCC field, created the subject, and body (with HTML). My question is, i'm having a hard time applying filters to the code.

Basically - I want it is, whatever I'm filtering in the form view of my database, I only want to send the alert to those emails being displayed. Can someone help me with the code to ONLY send to the email address displayed from the filter?

Here is my code.
    
    Dim cat As New ADOX.Catalog
    Dim cn As DAO.Connection
    Dim rs As DAO.Recordset

    Dim olApp As Outlook.Application
    Dim objMail As Outlook.MailItem
    Set olApp = Outlook.Application
    
    'Create e-mail item
    Set objMail = olApp.CreateItem(olMailItem)

Dim strEmail As String

Set db = CurrentDb
Set rs = db.OpenRecordset("Contacts")

With rs
    Do While Not .EOF
        strEmail = strEmail & .Fields("E-mail Address") & ";"
        .MoveNext
    Loop
    .Close
End With

strEmail = Left(strEmail, Len(strEmail) - 1)

Set cmd = Nothing
On Error GoTo Err_Command281_Click

objMail.BCC = strEmail
objMail.To = "test@test.com"
objMail.Subject = "SBJECT"


    With objMail
       'Set body format to HTML
       .BodyFormat = olFormatHTML
       .HTMLBody = "<HTML><img src='C:\Documents and Settings\jf\Desktop\test2.png'><font face=arial color=#000000>test</font/></HTML>"
       .Display
    End With
    
Exit_Command281_Click:
    Exit Sub
    
Err_Command281_Click:
    MsgBox Err.Description

    Resume Exit_Command281_Click

Open in new window

Avatar of rockiroads
rockiroads
Flag of United States of America image

what is the filter used to display the contacts on the form? is this based on the contacts table?
if so then use the same filter in your recordset

either
set rs = db.OpenRecordset("select * from Contacts where <your filter goes here>")

or

set rs = db.OpenRecordset("Contacts")
rs.filter ="your filter goes here"

Avatar of ummjay
ummjay

ASKER

the filter is based on whats showing in the form view. by clicking on the header of the column. so how would i enter that in your code over there?

<No Points wanted>
AFAIK, you cannot obtain a field value by simply clicking the column heading in a normal form.

"the filter is based on whats showing in the form view."
By what's showing "Where"? ... in what Field/Control specifically?

As rockiroad's stated, you need a "Value"
Ex:
set rs = db.OpenRecordset("select * from Contacts where State='New York'")
Here the value 'New York' must be taken from a "value" somewhere on your form.

So if your Form is a continuous form or datasheet you can pull the value from a field buy using something like this:
set rs = db.OpenRecordset("select * from Contacts where State=" & "'" & me.State & "'")
(if the field is Text)
Or:...
set rs = db.OpenRecordset("select * from Contacts where StateID=" & me.StateID)
If the value is Numeric

;-)

JeffCoachman

Avatar of ummjay

ASKER

so i have a new form with 4 fields.

1 is company (Comp)
1 is type (typee)
2 check boxes (checkdnat) and (checkdnaa).

so for the 2 drop downs (Comp and typee), i would do something like:

set rs = db.OpenRecordset("select * from Contacts where Company=" & "'" & me.Comp & "'")
and
set rs = db.OpenRecordset("select * from Contacts where CustType=" & "'" & me.typee & "'")

?? is that right?

what about for the checkboxes?
how does your form work. does the user check a box and that causes the filter?
I am trying to understand how your filtering works
if you are wanting to filter on the values currently on the form?
which fields you want to check and what logic, by this I mean is on checkbox associated with a company or cust type?

back in around 10-15mins to answer your replies
Avatar of ummjay

ASKER

i have a form. the title of the form is : Send email by filter

Type: drop down, that lists: INTERNAL, EXTERNAL

2 checkboxes:
1. Trader
2. Allocator

Basically, I want it so, If I choose a either internal or external, and check either of the checkboxes (trader or allocator, or both), it will send an email to what i specified above.
what field do you want to check against and for what if internal selected as well as external?
Avatar of ummjay

ASKER

i want to check against all the fields.
you need to choose, (internal or external)

and check trader or allocator.  (some emails could be both though).
Avatar of ummjay

ASKER

anyone? having trouble..
okay, not knowing your field names on the form lets just make them up

cboType (holds the text INTERNAL or EXTERNAL)
chkTrader (checkbox for trader)
chkAllocator (checkbox for allocator)


You have a table called Contacts. I do not know the column names here but lets say its like the names above minus the 3 character prefix

Type (text), Trader (yes/no), Allocator (yes/no)

Dim sWhere As String

'If combobox value selected then check for TYPE
If nz(cboType.Value, "") <> "" Then
    sWhere = "WHERE TYPE = '" & cboType.Value & "' "
End If

'Check trader
If chkTrader.Value = True Then
    If sWhere <> "" Then sWhere = sWhere & " AND " Else sWhere = "WHERE "
    sWhere = sWhere & " TRADER = true "
End If

'Check allocator
If chkAllocator.Value = True Then
    If sWhere <> "" Then sWhere = sWhere & " AND " Else sWhere = "WHERE "
    sWhere = sWhere & " ALLOCATOR = true "
End If



Now when you open the recordset you do like this

Set rs = db.OpenRecordset("select * from Contacts " & sWhere)
Avatar of ummjay

ASKER

having a hard time. can you take a look and tell me where i'm failing please?


Private Sub sendemailbtn_Click()
  
    Dim cat As New ADOX.Catalog
    Dim cn As DAO.Connection
    Dim rs As DAO.Recordset

    Dim olApp As Outlook.Application
    Dim objMail As Outlook.MailItem
    Set olApp = Outlook.Application
    
    'Create e-mail item
    Set objMail = olApp.CreateItem(olMailItem)

'Dim strEmail As String

Set db = CurrentDb
Set rs = db.OpenRecordset("select * from Contacts " & sWhere)

Dim sWhere As String

'If combobox value selected then check for TYPE
If Nz(typee.Value, "") <> "" Then
    sWhere = "WHERE TYPE = '" & typee.Value & "' "
End If

'Check trader
If checkdnat.Value = True Then
    If sWhere <> "" Then sWhere = sWhere & " AND " Else sWhere = "WHERE "
    sWhere = sWhere & " DNAT = true "
End If

'Check allocator
If checkdnaa.Value = True Then
    If sWhere <> "" Then sWhere = sWhere & " AND " Else sWhere = "WHERE "
    sWhere = sWhere & " DNA = true "
End If

'Check FIX
If checkfix.Value = True Then
    If sWhere <> "" Then sWhere = sWhere & " AND " Else sWhere = "WHERE "
    sWhere = sWhere & " FIX = true "
End If

'If combobox value selected then check for COMPANY
If Nz(Comp.Value, "") <> "" Then
    sWhere = "WHERE COMPANY = '" & Comp.Value & "' "
End If

'If combobox value selected then check for REGION
If Nz(cmboRegion.Value, "") <> "" Then
    sWhere = "WHERE REGION = '" & cmboRegion.Value & "' "
End If
      
  ' Set rs = cmd.Execute

With rs
    Do While Not .EOF
        sWhere = sWhere & .Fields("E-mail Address") & ";"
        .MoveNext
    Loop
    .Close
End With

strEmail = Left(strEmail, Len(strEmail) - 1)

Set cmd = Nothing
On Error GoTo Err_sendemailbtn_Click

objMail.BCC = strEmail
objMail.To = "me@test.com"
objMail.Subject = "TEST SUBJECT"


    With objMail
       'Set body format to HTML
       .BodyFormat = olFormatHTML
       .HTMLBody = "TEST EMAIL"
       .Display
    End With
    
Exit_sendemailbtn_Click:
    Exit Sub
    
Err_sendemailbtn_Click:
    MsgBox Err.Description

    Resume Exit_sendemailbtn_Click
End Sub

Open in new window

your not setting filters correctly as you are overwriting it
then your appending the sql filter with email addresses and not adding to the objMail
plus you defined the recordset before you set the sql filtering!


Private Sub sendemailbtn_Click()
  
    Dim cat As New ADOX.Catalog
    Dim cn As DAO.Connection
    Dim rs As DAO.Recordset
    Dim sWhere As String

    Dim olApp As Outlook.Application
    Dim objMail As Outlook.MailItem
    Set olApp = Outlook.Application
    
    
    Set db = CurrentDb
    
    
    'If combobox value selected then check for TYPE
    If Nz(typee.Value, "") <> "" Then
        sWhere = "WHERE TYPE = '" & typee.Value & "' "
    End If
    
    'Check trader
    If checkdnat.Value = True Then
        If sWhere <> "" Then sWhere = sWhere & " AND " Else sWhere = "WHERE "
        sWhere = sWhere & " DNAT = true "
    End If
    
    'Check allocator
    If checkdnaa.Value = True Then
        If sWhere <> "" Then sWhere = sWhere & " AND " Else sWhere = "WHERE "
        sWhere = sWhere & " DNA = true "
    End If
    
    'Check FIX
    If checkfix.Value = True Then
        If sWhere <> "" Then sWhere = sWhere & " AND " Else sWhere = "WHERE "
        sWhere = sWhere & " FIX = true "
    End If
    
    'If combobox value selected then check for COMPANY
    If Nz(Comp.Value, "") <> "" Then
        If sWhere <> "" Then sWhere = sWhere & " AND " Else sWhere = "WHERE "
        sWhere = sWhere & " COMPANY = '" & Comp.Value & "' "
    End If
    
    'If combobox value selected then check for REGION
    If Nz(cmboRegion.Value, "") <> "" Then
        If sWhere <> "" Then sWhere = sWhere & " AND " Else sWhere = "WHERE "
        sWhere = sWhere & " REGION = '" & cmboRegion.Value & "' "
    End If
    
    MsgBox "Your Criteria Is " & vbCrLf & vbCrLf & sWhere & vbCrLf & vbCrLf & "If still having problems post this messagebox on EE"
          
    Set rs = db.OpenRecordset("select * from Contacts " & sWhere)
    
    strEmail = left(strEmail, Len(strEmail) - 1)
    
    On Error GoTo Err_sendemailbtn_Click
    
    'Create e-mail item
    Set objMail = olApp.CreateItem(olMailItem)
    objMail.To = "me@test.com"
    objMail.Subject = "TEST SUBJECT"
    
    'ITERATE THRU RECORDSET AND ADD EMAIL ADDRESSES TO BCC
    Do While Not rs.EOF
        objMail.BCC = rs.Fields("E-mail Address") & ";"
        .MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    
    With objMail
       'Set body format to HTML
       .BodyFormat = olFormatHTML
       .HTMLBody = "TEST EMAIL"
       .Display
    End With
        
Exit_sendemailbtn_Click:
    Exit Sub
    
Err_sendemailbtn_Click:
    MsgBox err.Description

    Resume Exit_sendemailbtn_Click
End Sub

Open in new window

Avatar of ummjay

ASKER

thanks rockiroads. as you can see i'm no expert like you ;)

I tried the above code and got an error:

"compile error:
invalid or unqualified reference"

and highlights
.MoveNext

under: 'ITERATE THRU RECORDSET AND ADD EMAIL ADDRESSES TO BCC

*please advise*

so sorry, change it to   rs.movenext

dont worry about being a novice. we are always learning new things all the time anyways
Avatar of ummjay

ASKER

thanks rockiroads. still having issues here. please see attached screenshots. i also want it so that if i don't select a company, it selects all. meaning if i wanted to email everyone thats external from the US. I also want something so that if they can't be internal and from another company other than mine. How do I enter this?
screenshot.jpg
I think I know, I missed it first time. TYPE is a reserved word. But if you do want to use reserved words then wrap in square brackets

change
sWhere = "WHERE TYPE = '" & typee.Value & "' "

to
sWhere = "WHERE [TYPE] = '" & typee.Value & "' "
Good practice when using names in your table not to used reserved words
have a butchers here http://support.microsoft.com/kb/286335
Avatar of ummjay

ASKER

getting syntax error (missing operator) in query expression 'COMPANY = 'NOMURA SECURITIES INTL".

highlights:
Set rs = db.OpenRecordset("select * from Contacts " & sWhere)

can you also help with:
also want it so that if i don't select a company, it selects all. meaning if i wanted to email everyone thats external from the US. I also want something so that if they can't be internal and from another company other than mine. How do I enter this?
So have you got quotes in your data?
If so then find the fields you have and change the where clause to remove single quotes and replace with double quotes like this

From
sWhere = sWhere & " COMPANY = '" & Comp.Value & "' "


to
sWhere = sWhere & " COMPANY = " & chr$(34) & Comp.Value & chr$(34)

So notice the removal of the single quote and appended to the string chr$(34)

Try that
Avatar of ummjay

ASKER

get run-time error '5"
invalid procedure call or argument.

highlights: strEmail = Left(strEmail, Len(strEmail) - 1)
Are emails optional or do all records have an email?

try this then, replace line

strEmail = Left(strEmail, Len(strEmail) - 1)

with

if len(strEmail) > 1 then strEmail = Left(strEmail, Len(strEmail) - 1)

Avatar of ummjay

ASKER

emails are not optional. ever contact in my database needs an email address associated with it. they all have one.

should i still use your code as advised?

can you also help with:
also want it so that if i don't select a company, (keep it blank) it selects all. meaning if i wanted to email everyone thats external from the US. I also want something so that if they can't be internal and from another company other than mine. How do I enter this?
one problem at a time otherwise you will get your knickers in a twist

remove the line

 strEmail = left(strEmail, Len(strEmail) - 1)


altogether

if u dont select a company then it wont be filtered on it so should pick all companies. remember it depends on what you select as the filter
Avatar of ummjay

ASKER

ok, its sorta working. i tested by leaving company , region, and all the checkboxes blank. just choosing INTERNAL from type. So basically i wanted it to email ALL INTERNAL users.

However, it only composed a mail w/1 person. Very odd.

I also did another test where everything was blank, except i checked the DNAT checkbox, so I can send  a mail to everyone with DNAT checked under their column, but only has 1 person as well. (someone else).
Avatar of ummjay

ASKER

forgot to say, i removed:

 strEmail = left(strEmail, Len(strEmail) - 1)

but it didnt work, so i commented it out , and replaced with:

if len(strEmail) > 1 then strEmail = Left(strEmail, Len(strEmail) - 1)
ok do this

Set rs = db.OpenRecordset("select * from Contacts " & sWhere)

msgbox "SELECT * FROM CONTACTS " & sWhere



And show me the output you get


Next after this line,
        objMail.BCC = rs.Fields("E-mail Address") & ";"

add this

        strEmail = strEmail & "|" & rs.Fields("E-mail Address")


and after this line
    Loop

add this

    msgbox "Emails are " & strEmail

how many emails are displayed?
Avatar of ummjay

ASKER

see attached screen shots. Its interesting bc msgbox shows 3 email addresses, but the test email, only shows 1 in the BBC.
ss2.png
ss.png
Ok, I think I know what the issue might be. BCC looks to be overwritten each time instead of appended

change the line which sets BCC to this

objMail.BCC = objMail.BCC & ";" & rs.Fields("E-mail Address") & ";"

try this first

Also, Ive attached the code with the last lot of updates I mentioned. I think I covered everything

Private Sub sendemailbtn_Click()
  
    Dim cat As New ADOX.Catalog
    Dim cn As DAO.Connection
    Dim rs As DAO.Recordset
    Dim sWhere As String
    Dim strEmail As String

    Dim olApp As Outlook.Application
    Dim objMail As Outlook.MailItem
    Set olApp = Outlook.Application
    
    
    Set db = CurrentDb
    
    
    'If combobox value selected then check for TYPE
    If Nz(typee.Value, "") <> "" Then
        sWhere = "WHERE [TYPE] = " & Chr$(34) & typee.Value & Chr$(34)
    End If
    
    'Check trader
    If checkdnat.Value = True Then
        If sWhere <> "" Then sWhere = sWhere & " AND " Else sWhere = "WHERE "
        sWhere = sWhere & " DNAT = true "
    End If
    
    'Check allocator
    If checkdnaa.Value = True Then
        If sWhere <> "" Then sWhere = sWhere & " AND " Else sWhere = "WHERE "
        sWhere = sWhere & " DNA = true "
    End If
    
    'Check FIX
    If checkfix.Value = True Then
        If sWhere <> "" Then sWhere = sWhere & " AND " Else sWhere = "WHERE "
        sWhere = sWhere & " FIX = true "
    End If
    
    'If combobox value selected then check for COMPANY
    If Nz(Comp.Value, "") <> "" Then
        If sWhere <> "" Then sWhere = sWhere & " AND " Else sWhere = "WHERE "
        sWhere = sWhere & " COMPANY = " & Chr$(34) & Comp.Value & Chr$(34)
    End If
    
    'If combobox value selected then check for REGION
    If Nz(cmboRegion.Value, "") <> "" Then
        If sWhere <> "" Then sWhere = sWhere & " AND " Else sWhere = "WHERE "
        sWhere = sWhere & " REGION = " & Chr$(34) & cmboRegion.Value & Chr$(34)
    End If
    
    MsgBox "Your Criteria Is " & vbCrLf & vbCrLf & sWhere & vbCrLf & vbCrLf & "If still having problems post this messagebox on EE"
          
    Set rs = db.OpenRecordset("select * from Contacts " & sWhere)
        
    On Error GoTo Err_sendemailbtn_Click
    
    'Create e-mail item
    Set objMail = olApp.CreateItem(olMailItem)
    objMail.To = "me@test.com"
    objMail.Subject = "TEST SUBJECT"
    
    'ITERATE THRU RECORDSET AND ADD EMAIL ADDRESSES TO BCC
    Do While Not rs.EOF
        strEmail = strEmail & ">" & rs.Fields("E-mail Address") & "< "
        objMail.BCC = objMail.BCC & ";" & rs.Fields("E-mail Address") & ";"
        .MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    
    With objMail
       'Set body format to HTML
       .BodyFormat = olFormatHTML
       .HTMLBody = "TEST EMAIL"
       .Display
    End With
    
Exit_sendemailbtn_Click:
    MsgBox "Email sent to " & vbCrLf & strEmail
    Exit Sub
    
Err_sendemailbtn_Click:
    MsgBox err.Description

    Resume Exit_sendemailbtn_Click
End Sub

Open in new window

Avatar of ummjay

ASKER

yea, that did the trick. its adding all the names in BCC now.

last question: so i have those 3 checkboxes, TRADER, ALLOCATOR, and FIX.
How can i make it so, If I check all 3, or even just 2, it will pull any contact with that checked.

example: if i check trader and allocator, it will pull anyone with trader OR allocator (seems like its only doing both now). you know what i mean?

on a nother note: actually just created a new question about excel macros sending emails. if you could help ;)
I missed your last post as I had to pop out.

Now regarding your last condition, we do separate checks for those as they have to be a OR not AND

I will look at your other question, if it has not already been answered

Private Sub sendemailbtn_Click()
  
    Dim cat As New ADOX.Catalog
    Dim cn As DAO.Connection
    Dim rs As DAO.Recordset
    Dim sWhere As String
    Dim sWhereOr As String
    Dim strEmail As String

    Dim olApp As Outlook.Application
    Dim objMail As Outlook.MailItem
    Set olApp = Outlook.Application
    
    
    Set db = CurrentDb
    
    
    'If combobox value selected then check for TYPE
    If Nz(typee.Value, "") <> "" Then
        sWhere = "WHERE [TYPE] = " & Chr$(34) & typee.Value & Chr$(34)
    End If
    
    'If combobox value selected then check for COMPANY
    If Nz(Comp.Value, "") <> "" Then
        If sWhere <> "" Then sWhere = sWhere & " AND " Else sWhere = "WHERE "
        sWhere = sWhere & " COMPANY = " & Chr$(34) & Comp.Value & Chr$(34)
    End If
    
    'If combobox value selected then check for REGION
    If Nz(cmboRegion.Value, "") <> "" Then
        If sWhere <> "" Then sWhere = sWhere & " AND " Else sWhere = "WHERE "
        sWhere = sWhere & " REGION = " & Chr$(34) & cmboRegion.Value & Chr$(34)
    End If
    
    
    'Add in OR conditions
    
    'Check trader
    If checkdnat.Value = True Then
        sWhereOr = sWhereOr & " DNAT = true "
    End If
    
    'Check allocator
    If checkdnaa.Value = True Then
        If sWhereOr <> "" Then sWhereOr = sWhereOr & " OR "
        sWhereOr = sWhereOr & " DNA = true "
    End If
    
    'Check FIX
    If checkfix.Value = True Then
        If sWhereOr <> "" Then sWhereOr = sWhereOr & " OR "
        sWhereOr = sWhereOr & " FIX = true "
    End If
    
    If sWhereOr <> "" Then sWhere = sWhere & " AND (" & sWhereOr & ")"
    
    
    MsgBox "Your Criteria Is " & vbCrLf & vbCrLf & sWhere & vbCrLf & vbCrLf & "If still having problems post this messagebox on EE"
          
    Set rs = db.OpenRecordset("select * from Contacts " & sWhere)
        
    On Error GoTo Err_sendemailbtn_Click
    
    'Create e-mail item
    Set objMail = olApp.CreateItem(olMailItem)
    objMail.To = "me@test.com"
    objMail.Subject = "TEST SUBJECT"
    
    'ITERATE THRU RECORDSET AND ADD EMAIL ADDRESSES TO BCC
    Do While Not rs.EOF
        strEmail = strEmail & ">" & rs.Fields("E-mail Address") & "< "
        objMail.BCC = objMail.BCC & ";" & rs.Fields("E-mail Address") & ";"
        .MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    
    With objMail
       'Set body format to HTML
       .BodyFormat = olFormatHTML
       .HTMLBody = "TEST EMAIL"
       .Display
    End With
    
Exit_sendemailbtn_Click:
    MsgBox "Email sent to " & vbCrLf & strEmail
    Exit Sub
    
Err_sendemailbtn_Click:
    MsgBox err.Description

    Resume Exit_sendemailbtn_Click
End Sub

Open in new window

Avatar of ummjay

ASKER

looks good.

lets say i want to send to all companies. it doesnt like how i leave companies blank.

how can i add so if companies is blank, it selects all? or maybe just have an ALL in the drop down and if ALL selected, then it sends to all.

How do i do that? Then i think we are good!
thats what i dont understand.

    'If combobox value selected then check for COMPANY
    If Nz(Comp.Value, "") <> "" Then
        If sWhere <> "" Then sWhere = sWhere & " AND " Else sWhere = "WHERE "
        sWhere = sWhere & " COMPANY = " & Chr$(34) & Comp.Value & Chr$(34)
    End If


the code above says if no company entered then do not filter by company. by default it picks all companies

what is the rowsource for your company combobox
Avatar of ummjay

ASKER

row source is:

SELECT DISTINCT [Contacts].Company FROM Contacts ORDER BY [Contacts].Company;
ASKER CERTIFIED SOLUTION
Avatar of rockiroads
rockiroads
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ummjay

ASKER

i did that, get syntax error on:

If Nz(Comp.Value, "") <> "_ALL' Then
Private Sub sendemailbtn_Click()
  
    Dim cat As New ADOX.Catalog
    Dim cn As DAO.Connection
    Dim rs As DAO.Recordset
    Dim sWhere As String

    Dim olApp As Outlook.Application
    Dim objMail As Outlook.MailItem
    Set olApp = Outlook.Application
    
    
    Set db = CurrentDb
    
    
   'If combobox value selected then check for TYPE
    If Nz(typee.Value, "") <> "" Then
        sWhere = "WHERE [TYPE] = " & Chr$(34) & typee.Value & Chr$(34)
    End If
    
    'If combobox value selected then check for COMPANY
    'If Nz(Comp.Value, "") <> "" Then
    If Nz(Comp.Value, "") <> "_ALL' Then
        If sWhere <> "" Then sWhere = sWhere & " AND " Else sWhere = "WHERE "
        sWhere = sWhere & " COMPANY = " & Chr$(34) & Comp.Value & Chr$(34)
    End If
    
    'If combobox value selected then check for REGION
    If Nz(cmboRegion.Value, "") <> "" Then
        If sWhere <> "" Then sWhere = sWhere & " AND " Else sWhere = "WHERE "
        sWhere = sWhere & " REGION = " & Chr$(34) & cmboRegion.Value & Chr$(34)
    End If
    
    
    'Add in OR conditions
    
    'Check trader
    If checkdnat.Value = True Then
        sWhereOr = sWhereOr & " DNAT = true "
    End If
    
    'Check allocator
    If checkdnaa.Value = True Then
        If sWhereOr <> "" Then sWhereOr = sWhereOr & " OR "
        sWhereOr = sWhereOr & " DNA = true "
    End If
    
    'Check FIX
    If checkfix.Value = True Then
        If sWhereOr <> "" Then sWhereOr = sWhereOr & " OR "
        sWhereOr = sWhereOr & " FIX = true "
    End If
    
    If sWhereOr <> "" Then sWhere = sWhere & " AND (" & sWhereOr & ")"
    
    
    MsgBox "Your Criteria Is " & vbCrLf & vbCrLf & sWhere & vbCrLf & vbCrLf & "If still having problems post this messagebox on EE"
          
    Set rs = db.OpenRecordset("select * from Contacts " & sWhere)
        
    On Error GoTo Err_sendemailbtn_Click
    
    'Create e-mail item
    Set objMail = olApp.CreateItem(olMailItem)
    objMail.To = "me@test.com"
    objMail.Subject = "TEST SUBJECT"
    
    'ITERATE THRU RECORDSET AND ADD EMAIL ADDRESSES TO BCC
    Do While Not rs.EOF
        strEmail = strEmail & ">" & rs.Fields("E-mail Address") & "< "
        objMail.BCC = objMail.BCC & ";" & rs.Fields("E-mail Address") & ";"
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    
    With objMail
       'Set body format to HTML
       .BodyFormat = olFormatHTML
       .HTMLBody = "TEST EMAIL"
       .Display
    End With
    
Exit_sendemailbtn_Click:
    MsgBox "Email sent to " & vbCrLf & strEmail
    Exit Sub
    
Err_sendemailbtn_Click:
    MsgBox Err.Description

    Resume Exit_sendemailbtn_Click
End Sub

Open in new window

guess its the quotes

If Nz(Comp.Value, "") <> "_ALL' Then

should be

If Nz(Comp.Value, "") <> "_ALL" Then
Avatar of ummjay

ASKER

hey rocki-
on that other post that was closed. do you know how i would go about changing the copied spreadsheet that goes to email , from html to plain text?
Avatar of ummjay

ASKER

also no error on that now, but doesnt send to anyone.

msgbox says: "email send to " and is blank.

all i did was check all 3 boxes, and left company, region, and type blank.
ok, I need to rejig the where logic
regarding other post, will look into it. can you post a comment in there please
ok, replace the line

 If sWhereOr <> "" Then sWhere = sWhere & " AND (" & sWhereOr & ")"


with this

    If sWhereOr <> "" Then
        If sWhere = "" Then
            sWhere = sWhereOr
        Else
            sWhere = sWhere & " AND (" & sWhereOr & ")"
        End If
    End If
Avatar of ummjay

ASKER

ok, posted in other one like you asked. even though its already closed.

adjusted as requested, still same issue. here is full code:


Private Sub sendemailbtn_Click()
  
    Dim cat As New ADOX.Catalog
    Dim cn As DAO.Connection
    Dim rs As DAO.Recordset
    Dim sWhere As String

    Dim olApp As Outlook.Application
    Dim objMail As Outlook.MailItem
    Set olApp = Outlook.Application
    
    
    Set db = CurrentDb
    
    
   'If combobox value selected then check for TYPE
    If Nz(typee.Value, "") <> "" Then
        sWhere = "WHERE [TYPE] = " & Chr$(34) & typee.Value & Chr$(34)
    End If
    
    'If combobox value selected then check for COMPANY
    'If Nz(Comp.Value, "") <> "" Then
    If Nz(Comp.Value, "") <> "_ALL" Then
        If sWhere <> "" Then sWhere = sWhere & " AND " Else sWhere = "WHERE "
        sWhere = sWhere & " COMPANY = " & Chr$(34) & Comp.Value & Chr$(34)
    End If
    
    'If combobox value selected then check for REGION
    If Nz(cmboRegion.Value, "") <> "" Then
        If sWhere <> "" Then sWhere = sWhere & " AND " Else sWhere = "WHERE "
        sWhere = sWhere & " REGION = " & Chr$(34) & cmboRegion.Value & Chr$(34)
    End If
    
    
    'Add in OR conditions
    
    'Check trader
    If checkdnat.Value = True Then
        sWhereOr = sWhereOr & " DNAT = true "
    End If
    
    'Check allocator
    If checkdnaa.Value = True Then
        If sWhereOr <> "" Then sWhereOr = sWhereOr & " OR "
        sWhereOr = sWhereOr & " DNA = true "
    End If
    
    'Check FIX
    If checkfix.Value = True Then
        If sWhereOr <> "" Then sWhereOr = sWhereOr & " OR "
        sWhereOr = sWhereOr & " FIX = true "
    End If
    
    'If sWhereOr <> "" Then sWhere = sWhere & " AND (" & sWhereOr & ")"
    If sWhereOr <> "" Then
       If sWhere = "" Then
           sWhere = sWhereOr
       Else
           sWhere = sWhere & " AND (" & sWhereOr & ")"
       End If
   End If
    
    MsgBox "Your Criteria Is " & vbCrLf & vbCrLf & sWhere & vbCrLf & vbCrLf & "If still having problems post this messagebox on EE"
          
    Set rs = db.OpenRecordset("select * from Contacts " & sWhere)
        
    On Error GoTo Err_sendemailbtn_Click
    
    'Create e-mail item
    Set objMail = olApp.CreateItem(olMailItem)
    objMail.To = "me@test.com"
    objMail.Subject = "TEST SUBJECT"
    
    'ITERATE THRU RECORDSET AND ADD EMAIL ADDRESSES TO BCC
    Do While Not rs.EOF
        strEmail = strEmail & ">" & rs.Fields("E-mail Address") & "< "
        objMail.BCC = objMail.BCC & ";" & rs.Fields("E-mail Address") & ";"
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    
    With objMail
       'Set body format to HTML
       .BodyFormat = olFormatHTML
       .HTMLBody = "TEST EMAIL"
       .Display
    End With
    
Exit_sendemailbtn_Click:
    MsgBox "Email sent to " & vbCrLf & strEmail
    Exit Sub
    
Err_sendemailbtn_Click:
    MsgBox Err.Description

    Resume Exit_sendemailbtn_Click
End Sub

Open in new window

did u get a message box saying

Your Criteria Is

if so what is it
Avatar of ummjay

ASKER

your criteria is:

WHERE COMPANY = "" AND ( DNAT = true OR DNA = true OR FIX =true)

THEN GOT A BOX:

email sent to (its blank)
ss.png
Ok, well the initial check we had on company has to go back in

so we had

    'If Nz(Comp.Value, "") <> "" Then
    If Nz(Comp.Value, "") <> "_ALL" Then

change it to

    If Nz(Comp.Value, "") <> "_ALL" and Nz(Comp.Value, "") <> "" Then


you can see from the filtering it is checking for a blank company. if you had selected ALL, would it of still worked?
Avatar of ummjay

ASKER

I selected _ALL, got a debug msg highlighting:
 Set rs = db.OpenRecordset("select * from Contacts " & sWhere)

then i replaced like you asked above, selected ALL, and same thing. even if i dont select a company and leave it blank, i get an error. same as if i select ALL

run-time error '3131'
syntax error in FROM clause

before that i get:

your criteria is
DNAT = true OR DNA = true OR FIX = true


SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ummjay

ASKER

looks great. thanks rocki.
Avatar of ummjay

ASKER

got it working.
Cool and finally! lol
Avatar of ummjay

ASKER

quick question rocki.

the same thing we did for company, does that apply for region and type?

meaning, if i dont select a region or type, it pulls all as well? or maybe we can add an _ALL under region and type?
no we only did for company

just do the same for region and type. change the rowsource accordingly like we did for company
then check the _ALL in the vba code like we did with company
Avatar of ummjay

ASKER

so row source would be:
SELECT Type FROM Contacts UNION SELECT '_ALL' FROM Contacts ORDER BY 1

SELECT Region FROM Contacts UNION SELECT '_ALL' FROM Contacts ORDER BY 1

and replace these for type and region?
'If combobox value selected then check for TYPE
    If Nz(Comp.Value, "") <> "_ALL" And Nz(typee.Value, "") <> "" Then
        If sWhere <> "" Then sWhere = sWhere & " AND " Else sWhere = "WHERE "
        sWhere = sWhere & " TYPE = " & Chr$(34) & typee.Value & Chr$(34)
    End If

 'If combobox value selected then check for Region
    If Nz(cmboRegion.Value, "") <> "_ALL" And Nz(cmboRegion.Value, "") <> "" Then
        If sWhere <> "" Then sWhere = sWhere & " AND " Else sWhere = "WHERE "
        sWhere = sWhere & " REGION = " & Chr$(34) & Comp.Value & Chr$(34)
    End If

Open in new window

yes, thats looking good. I didnt write the code for you as I wanted you to do it so it helps you understand a little more.

there is just one issue with the copy/paste you did, you have to remember to change all lines you pasted,
this doesnt look right agreed?

sWhere = sWhere & " REGION = " & Chr$(34) & Comp.Value & Chr$(34)



Avatar of ummjay

ASKER

doh! your right.