Another Email Question...

[Scott]

Here's an example:

The "Interests" form listbox selection runs a macro which opens a query based on the selection.

 

[pbaldy]

Ah; you're running into this:

http://support.microsoft.com/default.aspx?scid=kb;en-us;209203

I have to go meet with the boss. If you haven't been able to incorporate that, I'll do it when I get back. There are a couple of ways around it. We also need to talk about your table structure (repeating fields (interest1, 2, 3...) is a normalization issue).

 

[pbaldy]

In response to your PM, I would still strongly reconsider the design, if it's not too late. You should be able to use a multiselect listbox to select the items. It can also be done with a form/subform.

If you'd rather not use the method in the link, an alternative is to take the criteria out of the query and put them in the recordset. The relevant lines:

Dim strSQL As String

strSQL = "SELECT EmailAddress FROM Members WHERE Interest1 = '" & [Forms]![Interests]![Combo8] & "' OR Interest2 = '" & [Forms]![Interests]![Combo8] & "' OR ...

Set rsEmail = MyDb.OpenRecordset(strSQL, dbOpenSnapshot)

 

[Scott]

I'm just going to scrap the email thing, neither the reference code in the link works on this nor can I get the code you supplied to do anything. I appreciate all your help with it.

 

[pbaldy]

Try this; it works for me:

  On Error GoTo Err_Command15_Click

  '  Dim stDocName     As String
  '
  '  stDocName = "Macro2"
  '  DoCmd.RunMacro stDocName

  Dim MyDb          As DAO.Database
  Dim rsEmail       As DAO.Recordset
  Dim sToName       As String
  Dim sSubject      As String
  Dim sMessageBody  As String
  Dim strSQL        As String

  strSQL = "SELECT EmailAddress FROM Members WHERE " _
         & "Interest1 = '" & [Forms]![Interests]![Combo8] & "' " _
         & "OR Interest2 = '" & [Forms]![Interests]![Combo8] & "' " _
         & "OR Interest3 = '" & [Forms]![Interests]![Combo8] & "' " _
         & "OR Interest4 = '" & [Forms]![Interests]![Combo8] & "' " _
         & "OR Interest5 = '" & [Forms]![Interests]![Combo8] & "' " _
         & "OR Interest6 = '" & [Forms]![Interests]![Combo8] & "'"

  Set MyDb = CurrentDb()
  Set rsEmail = MyDb.OpenRecordset(strSQL, dbOpenSnapshot)

  With rsEmail
    .MoveFirst
    Do Until rsEmail.EOF
      If IsNull(!EmailAddress) = False Then
        sToName = sToName & !EmailAddress & ";"
        sSubject = "Subject"
        sMessageBody = "Email Body"

      End If
      .MoveNext
    Loop
    Debug.Print sToName
    DoCmd.SendObject acSendNoObject, , , _
                     sToName, , , sSubject, sMessageBody, True, True

  End With

Exit_Command15_Click:
  Set MyDb = Nothing
  Set rsEmail = Nothing
  Exit Sub

Err_Command15_Click:
  MsgBox Err.Description
  Resume Exit_Command15_Click

 

[Scott]

Fantastic! Paul you are the man!

I must have deleted this when I replaced the code. doh
Set MyDb = CurrentDb()

 

[pbaldy]

That is a necessary piece of the puzzle.

If you've tested and canceled the email without sending, you know it throws a 2501 error, which is normal. If you don't want the user to see the error, you add a Select/Case function to your error trap so that if the error is 2501, you just go to the exit handler, otherwise throw the message box.

Now, we have to get you to stop giving up so easily!

 

[Scott]

If the SELECT/CASE statement is any bit similar to a SWITCH/CASE statement in JavaScript I can probably figure that one out after some thorough research.

 

[pbaldy]

Don't know Java, but I've got my tools template open. It would look like:

...
ExitHandler:
  Exit Sub

ErrorHandler:
  Select Case Err
    Case 2501   
      DoCmd.Hourglass False
      Resume ExitHandler
    Case Else
      MsgBox Err.Description
      DoCmd.Hourglass False
      Resume ExitHandler
  End Select

 

[Scott]

All I get when I close the email is "The SendObject Action was cancelled." message. Is this the 2501 error you are referring too?

 

[pbaldy]

Yes. You can leave it alone or add to the trap as I showed, depending on what you want to happen if/when the user cancels without sending.

 

[Scott]

I'll try and set it up to trap it and see what happens.

 

[Scott]

Paul,
I was able to trap the error message, looks great!

 

[pbaldy]

Glad we got it sorted out for you. I'd say you could buy me a beer next time I visit NC, but my daughter that lived there moved to England! Now I'll be flying across the pond instead of across the country.

 

[Scott]

Sounds good! Next time I'm in Reno I'll send a pm your way.

 

[pbaldy]

Something tells me I'm going to get verrrrryyy thirsty waiting for that day.

 

[Scott]