473,326 Members | 2,111 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Sending multiple emails from access qry to outlook

I borrowed the code posted by ADezii in my database.
I get run time error 3061
too few parameters expected 2. in the openrecordset line
Any suggestions?


Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdGenerateEmail_Click()
  2. On Error GoTo err_cmdGenerateEmail_Click
  3.     Dim strEMail As String
  4.     Dim oOutlook As Object
  5.     Dim oMail As Object
  6.     Dim strAddr As String
  7.     Dim MyDB As DAO.Database
  8.     Dim rstEMail As DAO.Recordset
  9.  
  10.     Set oOutlook = CreateObject("Outlook.Application")
  11.     Set oMail = oOutlook.CreateItem(0)
  12.  
  13.     'Retrieve all E-Mail Addressess in qrySheduleEmail
  14.     Set MyDB = CurrentDb
  15.     Set rstEMail = MyDB.OpenRecordset("Select * From qryScheduleEmail", dbOpenSnapshot, dbOpenForwardOnly)
  16.  
  17.     With rstEMail
  18.       Do While Not .EOF
  19.         'Build the Recipients String
  20.         strEMail = strEMail & ![EmailAddress] & ";"
  21.           .MoveNext
  22.       Loop
  23.     End With
  24.     '--------------------------------------------------
  25.  
  26.      With oMail
  27.       .To = Left$(strEMail, Len(strEMail) - 1)        'Remove Trailing ;
  28.       .Body = InputBox("Please enter your email text here:")
  29.       .Subject = InputBox("Please enter your Subject Line Here:")
  30.         '.Send
  31.         .Display
  32.  
  33.     End With
  34.  
  35.     Set oMail = Nothing
  36.     Set oOutlook = Nothing
  37.  
  38.     rstEMail.Close
  39.  Set rstEMail = Nothing
  40.  
  41. End Sub
Apr 10 '13 #1
8 1550
the qryScheduleEmail does contain criteria to select the date scheduled and tech if that helps any
Apr 10 '13 #2
Seth Schrock
2,965 Expert 2GB
Very important information. qryScheduleEmail isn't getting the data to populate those criteria. Are those criteria based a value in a form or do you enter them manually? However it is, you need to get values to the criteria. That is why it says that it is expecting two parameters. It is possible that it has one of the values, but needs the second or maybe it isn't getting either value.
Apr 10 '13 #3
@Seth Schrock
I enter them manually. they are set up in the criteria box as [Please input the Scheduled Date:] so when the form loads the msgbox appears
Apr 10 '13 #4
How should I pass the values to the qry. Thru form controls or another method??
Apr 10 '13 #5
Seth Schrock
2,965 Expert 2GB
You can either do it through a form or you can write out the query in your VBA and use inputboxes to get the values. I usually make my choice based on the complexity of the query.

Also, you don't need to query your query. This doesn't fix your problem, but you can just make line 15
Expand|Select|Wrap|Line Numbers
  1. Set rstEMail = MyDB.OpenRecordset("qryScheduleEmail", dbOpenSnapshot, dbOpenForwardOnly)
Apr 10 '13 #6
@Seth Schrock
I gave it a shot but I am getting all kinds of syntax errors, expected end of statements etc. Can you use the [space]_ in a SQL statement to bring a long line of code back in line so it doesn't go off the page? Here is line 15 now.
Expand|Select|Wrap|Line Numbers
  1. docmd.RunSQL "set rstEmail =  SELECT Customers.[Customer#], Customers.CompanyName, Customers.ContactName, Customers.EmailAddress, _
  2.         PropertyLocations.PropertyScheduledTestDate, Tech.[Tech#], Tech.TechName
  3.         FROM (Tech INNER JOIN (Customers INNER JOIN PropertyLocations ON Customers.CustomerID = PropertyLocations.[Customer#]) _
  4.         ON Tech.[Tech#] = PropertyLocations.PropertyTech) INNER JOIN PropertyDevices ON PropertyLocations.PropertyId = PropertyDevices.PropertyID
  5.         WHERE (((PropertyLocations.PropertyScheduledTestDate) = [Enter Scheduled Test Date:]) And ((Tech.[Tech#]) = [Enter Tech #:]))"
  6.  
Apr 14 '13 #7
@Seth Schrock
Second try gives a runtime error 13 type mismatch error.
Revised line 15 code:
Expand|Select|Wrap|Line Numbers
  1.  Set rstEMail = MyDB.OpenRecordset("qryScheduleEmail where(((PropertyLocations.PropertyScheduledTestDate) = [Enter Scheduled Test Date:])" And "((Tech.[Tech#]) = [Enter Tech #:]))", dbOpenSnapshot, dbOpenForwardOnly)
Apr 14 '13 #8
Seth Schrock
2,965 Expert 2GB
You can't just put the name of the query and then attach a WHERE clause to it. Either you need to put the WHERE clause inside qryScheduleEmail, or you can use something like
Expand|Select|Wrap|Line Numbers
  1. Set rstEmail = MyDB.OpenRecordset("SELECT * FROM qrySchedule WHERE ...", dbOpenSnapshot, dbOpenForwardOnly
Also, after just a quick glance at your WHERE clause, you need to include the word AND inside the quotes. Also, I'm not sure if the openrecordset command will allow you to use a query that asks you for the values like you have setup (I have never tried it). If you do get stuck, I've got a solution.

I'm exhausted right now, so I'll try to take a better look at it tomorrow.
Apr 15 '13 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

40
by: ian | last post by:
Hi, I'm a newbie (oh no I can here you say.... another one...) How can I get Python to send emails using the default windows email client (eg outlook express)? I thought I could just do the...
0
by: Thomas Mandelid | last post by:
A client I`m working for want to be able to send multiple emails to customers of their own. I have developed a simple app to accomplish this, but the application has a few flaws and I`m looking for...
1
by: Joey Martin | last post by:
I have about 5000 emails in my database. I wrote a basic script that sends an email to all of those email addresses. Usually I have to break it up and only send 300-400 at a time so my script does...
2
by: Bob | last post by:
Hi Everybody A tough one!!! Is there any way that incoming eMails (MailItems) into Ms Outlook can be used to automatically create records in a ms Access table or sub table. Smiley Bob
1
by: Devonish | last post by:
I am composing an email with Access VB and then sending it from within Access. Everything works correctly (the email actually goes!) but Outlook ask some irritating questions that the user is...
2
by: obroie | last post by:
Hi all, I am trying to move emails from Outlook into an Access table, the code below worked for me before, but now I am having a problem as this error message comes up: Run-time error 438 'Object...
3
by: bcanter | last post by:
I am setting up a user request form to help our IT staff get all of the information required to setup a user account, I would like to send the form to a static address each time that it is submitted...
10
by: crazycooter | last post by:
I found an old thread on this (http://groups.google.com/group/alt.php/ browse_thread/thread/751edb9c723316c4/ea9bf92a9c6b807c?lnk=gst&q=mail() +duplicate&rnum=7#ea9bf92a9c6b807c), but there didnt...
1
by: Graham2107 | last post by:
I have a small database of people who wish to be placed on a mailing list. I have stored the usual parameters like firts name, last name, email address etc. How do I read from the database and...
5
by: wktarin | last post by:
Hi. I'm a relative newcomer to the world of php, and I keep bumping into a problem with a mail () form. I need to send an automatic email to two addresses, but I can't seem to get it to work. One...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.