Emailing reports from access

[doopml]

Hello All, I am trying to write code that will allow me to send emails in which the recipients are autopopulated based on the responses in combo boxes on a form. I have tried using the dLookup function with the Send Object function. Below is my code:

Private Sub Command14_Click()
On Error GoTo Err_Command14_Click

Dim stDocName As String

stDocName = "Open_Queries_Study_Site_Macro"
DoCmd.RunMacro stDocName

Exit_Command14_Click:
Exit Sub

Err_Command14_Click:
MsgBox Err.Description
Resume Exit_Command14_Click


End Sub

Private Sub Command14_Exit(Cancel As Integer)

Dim stDocName As String
Dim stToName As String
Dim stCCName As String
Dim stBCCName As String
Dim stSubject As String
Dim stMessage As String

stDocName = "Open_Queries_by_Study_And_Site"

stToName = DLookup("[TO]", "tblContacts", "[[Forms]![Select_Study_Site]![Protocol_Number]=&Me.Combo4&]" And "[[Forms]![Select_Study_Site]![Site_Number]=&Me.Combo6&]")
stCCName = DLookup("[CC]", "tblContacts", "[[Forms]![Select_Study_Site]![Protocol_Number]=&Me.Combo4&]" And "[[Forms]![Select_Study_Site]![Site_Number]=&Me.Combo6&]")


stBCCName = DLookup("[BCC]", "tblContacts", "[[Forms]![Select_Study_Site]![Protocol_Number]=&Me.Combo4&]" And "[[Forms]![Select_Study_Site]![Site_Number]=&Me.Combo6&]")
stSubject = "Quotation Test"
stMessage = "Attached is a report. This is a test."
DoCmd.SendObject acSendReport, stDocName, acFormatPDF, stToName, stCCName, stBCCName, stSubject, stMessage

End Sub


So the first part is an event procedure where the user selects a protocol number and siite from the combo boxes on the form and when the command button is clicked a report is generated. The second part of the code is where I am struggling. I want the next procedure to run automatically and select the recipients of the email based on a table in the database (tblContacts) in which the email recipients correspond the what is selected on the form.

when I try to run the code, i get a runtime error -13: mismatch type.

The control source for the combo boxes come from a different table and query respectively (e.g. the first combo box is from the field "Protocol Number" from the table "Patients" and the second combo box is from a query that selects all sites that are conducting that study...same table though).

But tblContacts isn't "related" to the form....but it also has fields for "Protocol Number" and "Site"

So for example, when combo box 4 says ABC123, i would like the code to looko up in tblContacts the email addresses that are associated with ABC123, then when combo box 6 says 1234, I want the code to go through tblContacts and for all the email addresses associated with ABC123, find the email addresses associated with site 1234.


Please help! Thanks!

[pbaldy]

Well, a recordset would be more efficient since you're getting multiple values, but see if this helps:

DLookup Usage Samples

[doopml]

I have no idea how to use record sets. How would I go about using that?

[pbaldy]

I happen to have this db open, so here's an example:

Public Function GetLastZone(lngCarNum As Long) As String
  Dim strSQL        As String
  Dim db            As DAO.Database
  Dim rs            As DAO.Recordset

  On Error GoTo ErrorHandler

  Set db = CurrentDb()

  strSQL = "SELECT Zone FROM tblLabels WHERE CarNum = " & lngCarNum
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

  If Not rs.EOF Then
    GetLastZone = rs!Zone
  Else
    GetLastZone = "U"
  End If

ExitHandler:
  Set rs = Nothing
  Set db = Nothing
  Exit Function

ErrorHandler:
  Select Case Err
    Case Else
      MsgBox Err.Description & " in GetLastZone"
      DoCmd.Hourglass False
      Resume ExitHandler
  End Select
End Function