Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2005
    Posts
    154
    Provided Answers: 1

    Unanswered: Need help with code filtering email addresses

    Hello All,

    I have a membership database with two different types of membertypes. Yearly and three year.
    Yearly membership type also has boardmembers that are separated by a separate checkbox field (also resides on the main form) so I can keep the boardmembers separately.

    I am trying to accomplish the following:
    1. I am trying to send email with a click of a button to all expired yearly non-board members with a custom message template and all email addresses showing up in BCC field.
    2. I am trying to send email with a click of a button to all Active yearly non-board members with a custom message template and all email addresses showing up in BCC field.
    3. I am trying to send email with a click of a button to all Active yearly non-board members with a custom message template and all email addresses showing up in BCC field.
    4. I am trying to send birthday reminder email with a click of a button to all active yearly board and non-board members with a custom message template and all email addresses showing up in BCC field.

    Items 1,2, 3 & 4 are partially working.

    When I click on the the send email buttons on the form of any of the 4 items above I get the following:
    1. Email addresses for both regular and board members in the BCC field. All of those email addresses are from both Active and Expired membership status.
    2. Custom message just the way I like it.

    on my main form I have a button called expired memberships which takes me to a new form that has a combo box (where I choose yearly or three year) and below the combo box there is an another form displaying as datasheet filters results based on combo box. When I click run on the query builder it shows me the correct results. I can see yearly expired non board members with no problem.

    Question:
    What do I need to type in my code which will display just the expired yearly non-board members?

    once the above problem is working, I am guessing I can use the same code to display active yearly members and active board members?

    My code:
    Sub SendEmailToMany()
    Dim oOutlook As Outlook.Application
    Dim oEmailItem As MailItem
    Dim strBody As String
    Dim recipientList As String
    Dim rs As Recordset
    Dim CustType As String

    ' prevent 429 error, if outlook not open
    On Error Resume Next
    Err.Clear
    Set oOutlook = GetObject(, "Outlook.application")
    If Err.number <> 0 Then
    Set oOutlook = New Outlook.Application
    End If

    Set oEmailItem = oOutlook.CreateItem(olMailItem)
    With oEmailItem
    If IsNull(Me.Combo28) Then
    ' send email for all customers who have an email address
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM Members")
    CustType = ""
    Else
    'send email to customers whose type matching with selected customer type
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM Members where MemberType = " & Me.Combo28 & "")
    CustType = Me.MemberType
    End If

    If rs.RecordCount > 0 Then
    rs.MoveFirst
    Do Until rs.EOF
    If IsNull(rs!EmailAddress) Then
    rs.MoveNext
    Else
    recipientList = recipientList & rs!EmailAddress & ";"
    .BCC = recipientList
    rs.MoveNext
    End If
    Loop
    Else
    MsgBox "No one has email address"
    End If
    rs.Close
    Set rs = Nothing

    .Subject = "www Membership expiration reminder!! Previous Membership Type: " & CustType
    strBody = ""
    strBody = strBody & "<HTML><table border='2' cellspacing='0' cellpadding='5' width='500' style='font-size: 12pt;'><tr bgcolor= ""#000099""><td colspan='2' align='Center'><b>Membership Expiration Reminder</b></td></tr>"
    strBody = strBody & "<tr><td width='300'><b><font color=""blue""> Membership Dues: </font></b></td><td width='200'>" & MemberDues & "</td></tr>"
    strBody = strBody & "<tr><td><b><font color=""blue"">Membership Type: </font></b></td><td>" & Me.MemberType & "</td></tr>"
    strBody = strBody & "<tr><td><b><font color=""red"">Due Date: </font></b></td><td>" & "</td></tr>"
    strBody = strBody & "<tr bgcolor= ""#000099""><td colspan='2'><font color=""#000099"">Blue text</font></td></tr></table></HTML>"
    strBody = strBody & ""
    strBody = strBody & ""
    strBody = strBody & ""
    strBody = strBody & "<br>More information @ our website: <a href=http://www.www.com>www.www.com</a></b>" ' link URL
    strBody = strBody & "<br></b>"
    strBody = strBody & "<br> Please renew your membership on timely manner.</b>"
    strBody = strBody & "<br>Overdue fees will result in cancellation of your membership.</b>"
    strBody = strBody & "<br></b>"
    strBody = strBody & "<br>Best Regards,</b>"
    strBody = strBody & "<br></b>"
    strBody = strBody & "<br><font color= ""red"">President Bruce</b>"
    .HTMLBody = strBody
    'Send without showing Outlook
    '.Send
    'Open Outlook to see email
    .Display

    End With
    Set oEmailItem = Nothing
    Set oOutlook = Nothing

    End Sub

    Please let me know.

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    602
    Provided Answers: 31
    answered on other board.

  3. #3
    Join Date
    Jul 2005
    Posts
    154
    Provided Answers: 1
    Hello Ranman256, not sure what you mean by this?

  4. #4
    Join Date
    Jul 2005
    Posts
    154
    Provided Answers: 1
    anyone else can help with this question? I am hoping its something simple you can just point me towards it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •