VBA email module

  • Thread starter Thread starter cmichaud
  • Start date Start date
C

cmichaud

I am having trouble an email module. When i run this it says that there
are too few parameters. That it was expecting 4. I assume that these
are the criteria that is feeding my query. Right now i have a form
with 4 comboboxes that provide the criteria for the query. I have a
button on the form that runs the query. I have not been able to
successfully relate this module to it...and running it on its own
doesnt work for some reason. Any ideas. I am trying to use the form
to filter for email addresses than send an email.

Public Function SendEMail()

Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyBodyText As String

Set fso = New FileSystemObject

Subjectline$ = InputBox$("Please enter the subject line for this
mailing.", _
"We Need A Subject Line!")
If Subjectline$ = "" Then
MsgBox "No subject line, no message." & vbNewLine & vbNewLine & _
"Quitting...", vbCritical, "E-Mail Merger"
Exit Function
End If
Set MyOutlook = New Outlook.Application
Set db = CurrentDb()
Set MailList = db.OpenRecordset("MyEmailAddresses")
Set MyMail = MyOutlook.CreateItem(olMailItem)
Do Until MailList.EOF
MailList.MoveNext
Loop
MyMail.Subject = Subjectline$
MyMail.Display
Set MyMail = Nothing
Set MyOutlook = Nothing

MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing
End Function
 
when the error comes up it highlights

Set MailList = db.OpenRecordset("MyEmailAddresses")
 
That implies that your query MyEmailAddresses is expecting parameters to be
passed to it.

What does the query look like?
 
The query is expecting parameters. I have a form providing the
parameters. There are 4 combo boxes with drop down lists. Each
provides a parameter. If you leave the box blank it will search all.
The sql is rather long. SHould i post part of it to give you an idea.
I am stuck on how to make it a seamless operation. I am wanting to
use the form, pick the criteria, then run the module. That is what i
am tryin and its not working. On the command button onclick, i placed
the run macro after the run query.....
Any ideas?
 
Is the form that contains the parameters open when your code is running?

Does the query work alright when you simply open it outside of that code?
 
Yea the query works fine. And the form is open. what i have done was
but a button on the form with a onclick event

stDocName = "MyEmailAddresses"
DoCmd.OpenQuery stDocName, acNormal, acEdit

stDocName = "mcoSendEmail"
DoCmd.RunMacro stDocName

This opens my query than runs the macro for the module.
When i fill out the form and hit the button, the query results pop
up.....and a promt asking for the title of my email pops up. Meaning
that the query works...and the module at least got started. After i
enter the title, i get the expecting 4 parameters error.
 
Yea the query works fine. And the form is open. what i have done was
but a button on the form with a onclick event

stDocName = "MyEmailAddresses"
DoCmd.OpenQuery stDocName, acNormal, acEdit

stDocName = "mcoSendEmail"
DoCmd.RunMacro stDocName

This opens my query than runs the macro for the module.
When i fill out the form and hit the button, the query results pop
up.....and a promt asking for the title of my email pops up. Meaning
that the query works...and the module at least got started. After i
enter the title, i get the expecting 4 parameters error.
 
What does the SQL for your query look like?

(The fact that the query is already open really doesn't matter to the code
you're running. And you showed VBA code, not a macro, so I'm not sure what
your DoCmd.RunMacro is actually doing)
 
The macro has an action of RunCode. With a function name =SendEMail()

Your gonna kill me for this one. Here is my SQL of the query. I didnt
know how to make it simpler. I wanted to so that if i left a control
blank it would allow all for that field. I had for controls i did this
for. I also set criteria on field email to Is Not Null.

SELECT tblPersonalInfo.Email
FROM tblPersonalInfo INNER JOIN tblMembership ON
tblPersonalInfo.MemberID = tblMembership.MemberID
WHERE (((tblPersonalInfo.Email) Is Not Null) AND
((tblPersonalInfo.StatusID)=[forms]![frmContact]![statusID]) AND
((tblPersonalInfo.PositionID)=[forms]![frmContact]![PositionID]) AND
((tblMembership.ClubID)=[forms]![frmContact]![ClubID]) AND
((tblMembership.ProjectID)=[forms]![frmContact]![ProjectID])) OR
(((tblPersonalInfo.Email) Is Not Null) AND
((tblPersonalInfo.PositionID)=[forms]![frmContact]![PositionID]) AND
((tblMembership.ClubID)=[forms]![frmContact]![ClubID]) AND
((tblMembership.ProjectID)=[forms]![frmContact]![ProjectID]) AND
(([forms]![frmcontact]![statusID]) Is Null)) OR
(((tblPersonalInfo.Email) Is Not Null) AND
((tblPersonalInfo.StatusID)=[forms]![frmContact]![statusID]) AND
((tblMembership.ClubID)=[forms]![frmContact]![ClubID]) AND
((tblMembership.ProjectID)=[forms]![frmContact]![ProjectID]) AND
(([forms]![frmContact]![PositionID]) Is Null)) OR
(((tblPersonalInfo.Email) Is Not Null) AND
((tblMembership.ClubID)=[forms]![frmContact]![ClubID]) AND
((tblMembership.ProjectID)=[forms]![frmContact]![ProjectID]) AND
(([forms]![frmcontact]![statusID]) Is Null) AND
(([forms]![frmContact]![PositionID]) Is Null)) OR
(((tblPersonalInfo.Email) Is Not Null) AND
((tblPersonalInfo.StatusID)=[forms]![frmContact]![statusID]) AND
((tblPersonalInfo.PositionID)=[forms]![frmContact]![PositionID]) AND
((tblMembership.ProjectID)=[forms]![frmContact]![ProjectID]) AND
(([forms]![frmContact]![ClubID]) Is Null)) OR (((tblPersonalInfo.Email)
Is Not Null) AND
((tblPersonalInfo.PositionID)=[forms]![frmContact]![PositionID]) AND
((tblMembership.ProjectID)=[forms]![frmContact]![ProjectID]) AND
(([forms]![frmcontact]![statusID]) Is Null) AND
(([forms]![frmContact]![ClubID]) Is Null)) OR (((tblPersonalInfo.Email)
Is Not Null) AND
((tblPersonalInfo.StatusID)=[forms]![frmContact]![statusID]) AND
((tblMembership.ProjectID)=[forms]![frmContact]![ProjectID]) AND
(([forms]![frmContact]![PositionID]) Is Null) AND
(([forms]![frmContact]![ClubID]) Is Null)) OR (((tblPersonalInfo.Email)
Is Not Null) AND
((tblMembership.ProjectID)=[forms]![frmContact]![ProjectID]) AND
(([forms]![frmcontact]![statusID]) Is Null) AND
(([forms]![frmContact]![PositionID]) Is Null) AND
(([forms]![frmContact]![ClubID]) Is Null)) OR (((tblPersonalInfo.Email)
Is Not Null) AND
((tblPersonalInfo.StatusID)=[forms]![frmContact]![statusID]) AND
((tblPersonalInfo.PositionID)=[forms]![frmContact]![PositionID]) AND
((tblMembership.ClubID)=[forms]![frmContact]![ClubID]) AND
(([forms]![frmContact]![ProjectID]) Is Null)) OR
(((tblPersonalInfo.Email) Is Not Null) AND
((tblPersonalInfo.PositionID)=[forms]![frmContact]![PositionID]) AND
((tblMembership.ClubID)=[forms]![frmContact]![ClubID]) AND
(([forms]![frmcontact]![statusID]) Is Null) AND
(([forms]![frmContact]![ProjectID]) Is Null)) OR
(((tblPersonalInfo.Email) Is Not Null) AND
((tblPersonalInfo.StatusID)=[forms]![frmContact]![statusID]) AND
((tblMembership.ClubID)=[forms]![frmContact]![ClubID]) AND
(([forms]![frmContact]![PositionID]) Is Null) AND
(([forms]![frmContact]![ProjectID]) Is Null)) OR
(((tblPersonalInfo.Email) Is Not Null) AND
((tblMembership.ClubID)=[forms]![frmContact]![ClubID]) AND
(([forms]![frmcontact]![statusID]) Is Null) AND
(([forms]![frmContact]![PositionID]) Is Null) AND
(([forms]![frmContact]![ProjectID]) Is Null)) OR
(((tblPersonalInfo.Email) Is Not Null) AND
((tblPersonalInfo.StatusID)=[forms]![frmContact]![statusID]) AND
((tblPersonalInfo.PositionID)=[forms]![frmContact]![PositionID]) AND
(([forms]![frmContact]![ClubID]) Is Null) AND
(([forms]![frmContact]![ProjectID]) Is Null)) OR
(((tblPersonalInfo.Email) Is Not Null) AND
((tblPersonalInfo.PositionID)=[forms]![frmContact]![PositionID]) AND
(([forms]![frmcontact]![statusID]) Is Null) AND
(([forms]![frmContact]![ClubID]) Is Null) AND
(([forms]![frmContact]![ProjectID]) Is Null)) OR
(((tblPersonalInfo.Email) Is Not Null) AND
((tblPersonalInfo.StatusID)=[forms]![frmContact]![statusID]) AND
(([forms]![frmContact]![PositionID]) Is Null) AND
(([forms]![frmContact]![ClubID]) Is Null) AND
(([forms]![frmContact]![ProjectID]) Is Null)) OR
(((tblPersonalInfo.Email) Is Not Null) AND
(([forms]![frmcontact]![statusID]) Is Null) AND
(([forms]![frmContact]![PositionID]) Is Null) AND
(([forms]![frmContact]![ClubID]) Is Null) AND
(([forms]![frmContact]![ProjectID]) Is Null));

Thanks for you time.
 
Nothing jumps out at me (other than the fact that there are simpler ways of
writing that query).

Hopefully someone else will jump in with a suggestion. Of course, since
we've carry this thread on for a while, it's possible people will assume
that it's been solved. If no one else chimes in, I'd suggest reposting,
summarizing the details of what we've already discussed.

Sorry about that.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


The macro has an action of RunCode. With a function name =SendEMail()

Your gonna kill me for this one. Here is my SQL of the query. I didnt
know how to make it simpler. I wanted to so that if i left a control
blank it would allow all for that field. I had for controls i did this
for. I also set criteria on field email to Is Not Null.

SELECT tblPersonalInfo.Email
FROM tblPersonalInfo INNER JOIN tblMembership ON
tblPersonalInfo.MemberID = tblMembership.MemberID
WHERE (((tblPersonalInfo.Email) Is Not Null) AND
((tblPersonalInfo.StatusID)=[forms]![frmContact]![statusID]) AND
((tblPersonalInfo.PositionID)=[forms]![frmContact]![PositionID]) AND
((tblMembership.ClubID)=[forms]![frmContact]![ClubID]) AND
((tblMembership.ProjectID)=[forms]![frmContact]![ProjectID])) OR
(((tblPersonalInfo.Email) Is Not Null) AND
((tblPersonalInfo.PositionID)=[forms]![frmContact]![PositionID]) AND
((tblMembership.ClubID)=[forms]![frmContact]![ClubID]) AND
((tblMembership.ProjectID)=[forms]![frmContact]![ProjectID]) AND
(([forms]![frmcontact]![statusID]) Is Null)) OR
(((tblPersonalInfo.Email) Is Not Null) AND
((tblPersonalInfo.StatusID)=[forms]![frmContact]![statusID]) AND
((tblMembership.ClubID)=[forms]![frmContact]![ClubID]) AND
((tblMembership.ProjectID)=[forms]![frmContact]![ProjectID]) AND
(([forms]![frmContact]![PositionID]) Is Null)) OR
(((tblPersonalInfo.Email) Is Not Null) AND
((tblMembership.ClubID)=[forms]![frmContact]![ClubID]) AND
((tblMembership.ProjectID)=[forms]![frmContact]![ProjectID]) AND
(([forms]![frmcontact]![statusID]) Is Null) AND
(([forms]![frmContact]![PositionID]) Is Null)) OR
(((tblPersonalInfo.Email) Is Not Null) AND
((tblPersonalInfo.StatusID)=[forms]![frmContact]![statusID]) AND
((tblPersonalInfo.PositionID)=[forms]![frmContact]![PositionID]) AND
((tblMembership.ProjectID)=[forms]![frmContact]![ProjectID]) AND
(([forms]![frmContact]![ClubID]) Is Null)) OR (((tblPersonalInfo.Email)
Is Not Null) AND
((tblPersonalInfo.PositionID)=[forms]![frmContact]![PositionID]) AND
((tblMembership.ProjectID)=[forms]![frmContact]![ProjectID]) AND
(([forms]![frmcontact]![statusID]) Is Null) AND
(([forms]![frmContact]![ClubID]) Is Null)) OR (((tblPersonalInfo.Email)
Is Not Null) AND
((tblPersonalInfo.StatusID)=[forms]![frmContact]![statusID]) AND
((tblMembership.ProjectID)=[forms]![frmContact]![ProjectID]) AND
(([forms]![frmContact]![PositionID]) Is Null) AND
(([forms]![frmContact]![ClubID]) Is Null)) OR (((tblPersonalInfo.Email)
Is Not Null) AND
((tblMembership.ProjectID)=[forms]![frmContact]![ProjectID]) AND
(([forms]![frmcontact]![statusID]) Is Null) AND
(([forms]![frmContact]![PositionID]) Is Null) AND
(([forms]![frmContact]![ClubID]) Is Null)) OR (((tblPersonalInfo.Email)
Is Not Null) AND
((tblPersonalInfo.StatusID)=[forms]![frmContact]![statusID]) AND
((tblPersonalInfo.PositionID)=[forms]![frmContact]![PositionID]) AND
((tblMembership.ClubID)=[forms]![frmContact]![ClubID]) AND
(([forms]![frmContact]![ProjectID]) Is Null)) OR
(((tblPersonalInfo.Email) Is Not Null) AND
((tblPersonalInfo.PositionID)=[forms]![frmContact]![PositionID]) AND
((tblMembership.ClubID)=[forms]![frmContact]![ClubID]) AND
(([forms]![frmcontact]![statusID]) Is Null) AND
(([forms]![frmContact]![ProjectID]) Is Null)) OR
(((tblPersonalInfo.Email) Is Not Null) AND
((tblPersonalInfo.StatusID)=[forms]![frmContact]![statusID]) AND
((tblMembership.ClubID)=[forms]![frmContact]![ClubID]) AND
(([forms]![frmContact]![PositionID]) Is Null) AND
(([forms]![frmContact]![ProjectID]) Is Null)) OR
(((tblPersonalInfo.Email) Is Not Null) AND
((tblMembership.ClubID)=[forms]![frmContact]![ClubID]) AND
(([forms]![frmcontact]![statusID]) Is Null) AND
(([forms]![frmContact]![PositionID]) Is Null) AND
(([forms]![frmContact]![ProjectID]) Is Null)) OR
(((tblPersonalInfo.Email) Is Not Null) AND
((tblPersonalInfo.StatusID)=[forms]![frmContact]![statusID]) AND
((tblPersonalInfo.PositionID)=[forms]![frmContact]![PositionID]) AND
(([forms]![frmContact]![ClubID]) Is Null) AND
(([forms]![frmContact]![ProjectID]) Is Null)) OR
(((tblPersonalInfo.Email) Is Not Null) AND
((tblPersonalInfo.PositionID)=[forms]![frmContact]![PositionID]) AND
(([forms]![frmcontact]![statusID]) Is Null) AND
(([forms]![frmContact]![ClubID]) Is Null) AND
(([forms]![frmContact]![ProjectID]) Is Null)) OR
(((tblPersonalInfo.Email) Is Not Null) AND
((tblPersonalInfo.StatusID)=[forms]![frmContact]![statusID]) AND
(([forms]![frmContact]![PositionID]) Is Null) AND
(([forms]![frmContact]![ClubID]) Is Null) AND
(([forms]![frmContact]![ProjectID]) Is Null)) OR
(((tblPersonalInfo.Email) Is Not Null) AND
(([forms]![frmcontact]![statusID]) Is Null) AND
(([forms]![frmContact]![PositionID]) Is Null) AND
(([forms]![frmContact]![ClubID]) Is Null) AND
(([forms]![frmContact]![ProjectID]) Is Null));

Thanks for you time.
Click to expand...
 
humm....someone told me i should study
"how to pass parameters to a query when opening a recordset"

this doesnt make any sense to me....does it to you???
 
Yes, it makes sense to me, but it shouldn't be necessary given you're
referring to field forms.

Here's an example from the Help file to explain what they're talking about:

Sub ParameterX()

Dim dbsNorthwind As Database
Dim qdfReport As QueryDef
Dim prmBegin As Parameter
Dim prmEnd As Parameter

Set dbsNorthwind = OpenDatabase("Northwind.mdb")

' Create temporary QueryDef object with two
' parameters.
Set qdfReport = dbsNorthwind.CreateQueryDef("", _
"PARAMETERS dteBegin DateTime, dteEnd DateTime; " & _
"SELECT EmployeeID, COUNT(OrderID) AS NumOrders " & _
"FROM Orders WHERE ShippedDate BETWEEN " & _
"[dteBegin] AND [dteEnd] GROUP BY EmployeeID " & _
"ORDER BY EmployeeID")
Set prmBegin = qdfReport.Parameters!dteBegin
Set prmEnd = qdfReport.Parameters!dteEnd

' Print report using specified parameter values.
ParametersChange qdfReport, prmBegin, #1/1/95#, _
prmEnd, #6/30/95#
ParametersChange qdfReport, prmBegin, #7/1/95#, _
prmEnd, #12/31/95#

dbsNorthwind.Close

End Sub

Sub ParametersChange(qdfTemp As QueryDef, _

prmFirst As Parameter, dteFirst As Date, _
prmLast As Parameter, dteLast As Date)
' Report function for ParameterX.

Dim rstTemp As Recordset
Dim fldLoop As Field

' Set parameter values and open recordset from
' temporary QueryDef object.
prmFirst = dteFirst
prmLast = dteLast
Set rstTemp = _
qdfTemp.OpenRecordset(dbOpenForwardOnly)
Debug.Print "Period " & dteFirst & " to " & dteLast

' Enumerate recordset.
Do While Not rstTemp.EOF

' Enumerate Fields collection of recordset.
For Each fldLoop In rstTemp.Fields
Debug.Print " - " & fldLoop.Name & " = " & fldLoop;
Next fldLoop

Debug.Print
rstTemp.MoveNext
Loop

rstTemp.Close

End Sub

In the query, they've defined 2 parameters dteBegin and dteEnd, and declared
them both to be DateTime.
 
Another option would be to dynamically generate the SQL statement
appropriate for whatever values your parameters hold, change the SQL
property of the query to that statement, and then run your query.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas J. Steele said:
Yes, it makes sense to me, but it shouldn't be necessary given you're
referring to field forms.

Here's an example from the Help file to explain what they're talking
about:

Sub ParameterX()

Dim dbsNorthwind As Database
Dim qdfReport As QueryDef
Dim prmBegin As Parameter
Dim prmEnd As Parameter

Set dbsNorthwind = OpenDatabase("Northwind.mdb")

' Create temporary QueryDef object with two
' parameters.
Set qdfReport = dbsNorthwind.CreateQueryDef("", _
"PARAMETERS dteBegin DateTime, dteEnd DateTime; " & _
"SELECT EmployeeID, COUNT(OrderID) AS NumOrders " & _
"FROM Orders WHERE ShippedDate BETWEEN " & _
"[dteBegin] AND [dteEnd] GROUP BY EmployeeID " & _
"ORDER BY EmployeeID")
Set prmBegin = qdfReport.Parameters!dteBegin
Set prmEnd = qdfReport.Parameters!dteEnd

' Print report using specified parameter values.
ParametersChange qdfReport, prmBegin, #1/1/95#, _
prmEnd, #6/30/95#
ParametersChange qdfReport, prmBegin, #7/1/95#, _
prmEnd, #12/31/95#

dbsNorthwind.Close

End Sub

Sub ParametersChange(qdfTemp As QueryDef, _

prmFirst As Parameter, dteFirst As Date, _
prmLast As Parameter, dteLast As Date)
' Report function for ParameterX.

Dim rstTemp As Recordset
Dim fldLoop As Field

' Set parameter values and open recordset from
' temporary QueryDef object.
prmFirst = dteFirst
prmLast = dteLast
Set rstTemp = _
qdfTemp.OpenRecordset(dbOpenForwardOnly)
Debug.Print "Period " & dteFirst & " to " & dteLast

' Enumerate recordset.
Do While Not rstTemp.EOF

' Enumerate Fields collection of recordset.
For Each fldLoop In rstTemp.Fields
Debug.Print " - " & fldLoop.Name & " = " & fldLoop;
Next fldLoop

Debug.Print
rstTemp.MoveNext
Loop

rstTemp.Close

End Sub

In the query, they've defined 2 parameters dteBegin and dteEnd, and
declared them both to be DateTime.



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


humm....someone told me i should study
"how to pass parameters to a query when opening a recordset"

this doesnt make any sense to me....does it to you???
Click to expand...
Click to expand...
 
"too few parameters. That it was expecting 4. "

A bit late on this thread, but I get that sort of message whenever I've mispelled a field name, so here I would look for four mispellings...
 
Back
Top