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!
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!
