Solved

send email by filter to database list in access

Posted on 2010-09-09
56
496 Views
Last Modified: 2013-11-28
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

0
Comment
Question by:ummjay
  • 28
  • 27
56 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 33640854
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"

0
 

Author Comment

by:ummjay
ID: 33640994
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?

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33641285
<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

0
 

Author Comment

by:ummjay
ID: 33641567
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?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33641637
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
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33641649
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
0
 

Author Comment

by:ummjay
ID: 33641732
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.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33642309
what field do you want to check against and for what if internal selected as well as external?
0
 

Author Comment

by:ummjay
ID: 33666847
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).
0
 

Author Comment

by:ummjay
ID: 33702875
anyone? having trouble..
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33706242
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)
0
 

Author Comment

by:ummjay
ID: 33718318
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

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33731270
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

0
 

Author Comment

by:ummjay
ID: 33733896
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*

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33735732
so sorry, change it to   rs.movenext

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

Author Comment

by:ummjay
ID: 33737025
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
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33737135
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 & "' "
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33737143
Good practice when using names in your table not to used reserved words
have a butchers here http://support.microsoft.com/kb/286335
0
 

Author Comment

by:ummjay
ID: 33757224
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?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33757332
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
0
 

Author Comment

by:ummjay
ID: 33757439
get run-time error '5"
invalid procedure call or argument.

highlights: strEmail = Left(strEmail, Len(strEmail) - 1)
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33757589
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)

0
 

Author Comment

by:ummjay
ID: 33757619
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?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33757655
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
0
 

Author Comment

by:ummjay
ID: 33757711
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).
0
 

Author Comment

by:ummjay
ID: 33757721
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)
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33757939
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?
0
 

Author Comment

by:ummjay
ID: 33772208
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
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 65

Expert Comment

by:rockiroads
ID: 33773159
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

0
 

Author Comment

by:ummjay
ID: 33773504
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 ;)
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33775242
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

0
 

Author Comment

by:ummjay
ID: 33778939
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!
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33781664
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
0
 

Author Comment

by:ummjay
ID: 33782453
row source is:

SELECT DISTINCT [Contacts].Company FROM Contacts ORDER BY [Contacts].Company;
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 33782870
change to this

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


then in code change

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

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

0
 

Author Comment

by:ummjay
ID: 33784178
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

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33784763
guess its the quotes

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

should be

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

Author Comment

by:ummjay
ID: 33788419
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?
0
 

Author Comment

by:ummjay
ID: 33788440
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.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33789313
ok, I need to rejig the where logic
regarding other post, will look into it. can you post a comment in there please
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33789327
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
0
 

Author Comment

by:ummjay
ID: 33789828
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

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33789916
did u get a message box saying

Your Criteria Is

if so what is it
0
 

Author Comment

by:ummjay
ID: 33790155
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
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33790578
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?
0
 

Author Comment

by:ummjay
ID: 33792297
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


0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 500 total points
ID: 33793027
my bad I forgot the WHERE, doh!

change

sWhere = sWhereOr

to this

sWhere = "WHERE " & sWhereOr
0
 

Author Comment

by:ummjay
ID: 33793663
looks great. thanks rocki.
0
 

Author Closing Comment

by:ummjay
ID: 33793671
got it working.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33794304
Cool and finally! lol
0
 

Author Comment

by:ummjay
ID: 33799367
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?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33799471
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
0
 

Author Comment

by:ummjay
ID: 33800270
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

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33800396
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)



0
 

Author Comment

by:ummjay
ID: 33801068
doh! your right.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33801231
:)
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now