Emailing from Access using recordset and querydef.

Why isn't this working? Can get past the "Set rst = qdf.OpenRecordset()" line.  My references are all there. Using other similar code and references that work.  Getting "3601: Too few parameters. Expected 1."  I guess I just don't get the whole QueryDefs thing.
Public Sub EmailDistribution()
Dim db As Database
Set db = CurrentDb
Dim ol As Outlook.Application
Dim olMail As Outlook.MailItem
Dim qdf As QueryDef
Dim rst As DAO.Recordset
Set qdf = db.QueryDefs("EmailDistributionQ")
Set rst = qdf.OpenRecordset()
rst.MoveFirst

While Not rst.EOF
  
  Set olMail = ol.CreateItemFromTemplate("C:\Users\js061256\AppData\Roaming\Microsoft\Templates\SLWBDAY.oft")

  With olMail
     .To = rst!Email
     '.CC = ""
     '.BCC = ""
     '.Attachments.Add "c:\file1.txt"
     '.Attachments.Add "c:\file2.txt"
     .Subject = rst!Subject
     .Body = rst!Description
      
     .Display
  End With
  rst.MoveNext
  
    Sleep 60000  'change the value of 100 accordingly
    DoEvents


Wend

Set olMail = Nothing
Set ol = Nothing

End Sub

Open in new window

Jay WilliamsOwnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kelvin SparksCommented:
The only thing that I can think of trying is to add DAO. into the DIM qdf line.

Kelvin
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
you can also change:

...
Set qdf = db.QueryDefs("EmailDistributionQ")
Set rst = qdf.OpenRecordset()
...

to:

...
Set rst = db.OpenRecordset("Select * from EmailDistributionQ")
...

and see if this works?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If your goal is to open that query and loop through the records, use the suggestion from Ryan Chong, and do away with the qdf variable. The first part of your code would look like this:

Dim db As Database
Set db = CurrentDb
Dim ol As Outlook.Application
Dim olMail As Outlook.MailItem
Dim rst As DAO.Recordset
Set rst = db.OpenRecordset("SELECT * FROM EmailDistributionQ")

However, your "Too few parameters" error may mean troubles with your EmailDistributionQ query. Open that query in design view and make sure it's correct. "Too Few Parameters" often means something is misspelled. Could also mean you've modified one of the source tables, and your query needs to be changed to remove any fields you may have removed/renamed in the source tables.
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Jay WilliamsOwnerAuthor Commented:
Thanks, guys.  I'll keep you posted.
Helen FeddemaCommented:
If your query is an action query, or has parameters, you may have to create a make-table query and then use the table it generates for the export.  You can run the make-table query in code before the line that creates the emails.  Also, I think you need a query here, not a QueryDef.  This line works for setting a recordset based on a query:

   Set rst = dbs.OpenRecordset("qryContacts")

Open in new window


Here is the full procedure for sending emails to all records in a query:

Public Sub EMailAllContacts()
'Created by Helen Feddema 31-Oct-2009
'Last modified by Helen Feddema 31-Oct-2009

On Error GoTo ErrorHandler

   Dim dbs As DAO.Database
   Dim rst As DAO.Recordset
   Dim appOutlook As Outlook.Application
   Dim msg As Outlook.MailItem
   Dim strEmail As String
   
   Set dbs = CurrentDb
   Set rst = dbs.OpenRecordset("qryContacts")
   Set appOutlook = GetObject(, "Outlook.Application")
   
   Do While Not rst.EOF
      strEmail = Nz(rst![EmailName])
      If strEmail <> "" Then
         'Create email
         Set msg = appOutlook.CreateItem(olMailItem)
         msg.To = strEmail
         msg.Subject = "Subject"
         msg.Body = "Message"
         
         'Comment out next line and uncomment Send line
         'to send automatically
         msg.Display
         'msg.Send
      End If
      rst.MoveNext
   Loop
   
ErrorHandlerExit:
   rst.Close
   Set rst = Nothing
   Set appOutlook = Nothing
   Exit Sub

ErrorHandler:
   'Outlook is not running; open Outlook with CreateObject
   If Err.Number = 429 Then
      Set appOutlook = CreateObject("Outlook.Application")
      Resume Next
   Else
      MsgBox "Error No: " & Err.Number _
         & " in EMailAllContacts procedure" _
         & "; Description: " & Err.Description
      Resume ErrorHandlerExit
   End If

End Sub

Open in new window

Jay WilliamsOwnerAuthor Commented:
Thanks for everything!  I did take your advice and got it working using this:
Public Sub EmailDistribution()
Dim db As Database
Set db = CurrentDb
Dim myOlApp As Object
    Dim MyItem As Object
    Set myOlApp = CreateObject("Outlook.Application")
    Set MyItem = myOlApp.CreateItemFromTemplate("C:\Users\js061256\AppData\Roaming\Microsoft\Templates\SLWBDAY.oft")
'Dim olMail As Outlook.MailItem
Dim rst As DAO.Recordset
Set rst = db.OpenRecordset("SELECT * FROM EmailDistributionQ")


While Not rst.EOF
rst.MoveFirst
  
  With MyItem

     .To = rst.Fields.Item("EmailAddress").Value
     '.CC = ""
     '.BCC = ""
     '.Attachments.Add "c:\file1.txt"
     '.Attachments.Add "c:\file2.txt"
     .Subject = rst.Fields.Item("Subject").Value
     .Body = rst.Fields.Item("Description").Value
      
     .Display
  End With
  rst.MoveNext
  
    Sleep 30000  'change the value of 100 accordingly
    DoEvents


Wend

Set myOlApp = Nothing
Set MyItem = Nothing

End Sub

Open in new window

For some reason, though, it's not grabbing the template.  Any idea why?
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>For some reason, though, it's not grabbing the template

What's the error message you get now?

Make sure the script got enough privileges to access to C:\Users\js061256\AppData\Roaming\Microsoft\Templates\SLWBDAY.oft and the template file is exist?
Jay WilliamsOwnerAuthor Commented:
That's just it--there is no error.  The template does exist and the path is correct.  The default template is used instead.  Privileges?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Try putting the template in a different folder temporarily and change your code to reflect that new path. If that works, then it may be a permissions issue.

If it does not work, then make sure the Template file you're calling is a valid file, and that you can create a new Email message using that Template in Outlook.
Jay WilliamsOwnerAuthor Commented:
I put the template on the desktop and opened it just fine.  Changed the path in my code, still pulled the normal template.  Put the file back home and opened it in Outlook manually no problem.  I can't imagine it's a permissions issue; this is my personal laptop and software.  Do you think there may be a reference library missing?
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
I tested similar scripts on my machine it works for me...

If you double click SLWBDAY.oft can it be opened? What if you creating a new template in same folder and let your script pointing to that new template, will this work?

In additional, you can do debugging by pressing F8 in your scripts to check the properties of object MyItem.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
May be restrictions on the use of it outside of Outlook, imposed by Outlook and email security rules. I'm on Outlook expert, so cannot tell you if that's the case (and you really should ask this as a separate question anyhow).

However, since you can move it to a different folder and use it, but you cannot do so via VBA, then something is preventing that, and the most obvious answer is some form or permissions restriction.
Jay WilliamsOwnerAuthor Commented:
Although no solution was found for the template issue, I did use suggestions from each expert that resulted in a solid overall result that works flawlessly and is sufficient for my purposes.  Thank you all.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.