I've Run into a Wall (more than once)

[Mmccl]

Run into a Wall (Jon K can you look again)

This is where I am on this so far.

I could not get a query to run based on parameters I chose on a form from unbound combo boxes. ie. send email (yes/no), State (list of states), etc.

Now the first task works. Jon K has got me this far.

But now, Using the Code he sent me, I have been working for 2 days and can't convert it to this next parameter query / form.

The whole ideas is that EmailExp1 should send and generic email to every one in the table marked email yes. The form allows you to select criteria for this email and then calls up Outlook express with the email addresses based on your query from the form info.

This part works great - see form EmailExp1 in the zip file

Now I need to also send personalized email ie "Greetings Bob" , "Greetings Tom" etc. in the top of the body of a generic message. So far, the code I have will do this, but with the same problem as before - I can't set the parameters of the query from the form, only if i manually change them in the query. The code is just (seemingly) all different from the 1st example. Nothing I try works.

I have all the components in place in the form EmailExp2.

If someone could have a look and see what I am doing wrong.

(Note: in EmailExp2, current struggle, I have not attached it with any reference to the form that selects the parameters. Everything I tried failed).

Thanks Mike

 

[Jon K]

The DAO code and the ADO and ADOX code to reference parameters on the form
are quite similar.

Private Sub CmdEmail_Click()
  
  Dim db As DAO.Database
  Dim qDef As DAO.QueryDef
  Dim rsEmail As DAO.Recordset
  
  Dim strEmail As String
  Dim strSubject As String
  Dim strContactName As String
  
  Set db = CurrentDb
  Set qDef = db.QueryDefs("qryEmailExp2")
  
  qDef.Parameters("[Forms]![EmailExp2]![Name]").Value = _
      [Forms]![EmailExp2]![Name]
  qDef.Parameters("[Forms]![EmailExp2]![Title]").Value = _
      [Forms]![EmailExp2]![Title]
  qDef.Parameters("[Forms]![EmailExp2]![Region]").Value = _
      [Forms]![EmailExp2]![Region]
  qDef.Parameters("[Forms]![EmailExp2]![State]").Value = _
      [Forms]![EmailExp2]![State]
  
  Set rsEmail = qDef.OpenRecordset
  
  Do While Not rsEmail.EOF
    strEmail = rsEmail.Fields("PrimaryEmailAddress").Value

    strContactName = rsEmail.Fields("LastName").Value

    DoCmd.SendObject _
     , _
     , _
     , strEmail, _
     , _
     , _
     , _
     "Hello " & strContactName & vbCrLf & vbCrLf & ("" & Me!Message), _
        True
      
    rsEmail.MoveNext

  Loop
   
  Set rsEmail = Nothing
  Set qDef = Nothing
  Set db = Nothing
   
  MsgBox "Emails have been sent"
End Sub

 

[Mmccl]

Thanks Again

That was the help I needed.

Thanks Jon K