Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-12-2018, 08:46 AM   #31
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,491
Thanks: 14
Thanked 272 Times in 270 Posts
Mark_ will become famous soon enough
Re: recipients multiple emails from table

Code:
Set rs = CurrentDb.OpenRecordset("Select * from Mail where Mail.Summary_chk=Yes")
should be
Code:
Set rs = CurrentDb.OpenRecordset("Select * from Mail where Mail.Summary_chk= 'Yes' ")
If you use
Code:
Mail.Summary_chk=Yes
the SQL treats Yes is a number. To make sure it understands that Yes is text, you need to put it in quotes.

Suggestion, create a query that produces the recordset you are looking for. Change to SQL view. Copy the SQL. This way you get to start with a known working string.


Last edited by Mark_; 01-12-2018 at 08:48 AM. Reason: Elaboration of explination
Mark_ is offline   Reply With Quote
Old 01-12-2018, 09:41 AM   #32
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 31,929
Thanks: 9
Thanked 3,844 Times in 3,787 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: recipients multiple emails from table

I think the problem is that you've declared an ADO recordset but used DAO syntax.
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 01-12-2018, 09:47 AM   #33
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 366
Thanks: 56
Thanked 1 Time in 1 Post
lookforsmt is on a distinguished road
Re: recipients multiple emails from table

Thanks for the suggestion Mark, but i am still getting below error

i have amended the code as you had provided and now getting the below error

Run-time error '3464'
Date type mismatch in criteria expression

So as advised i have changed to SQL view and now getting below error.

Code:
  strSQL = "SELECT Mail " & _
                "From [Mail] " & _
                "WHERE (((Mail.Summary_chk)=Yes)"
Run-time error '91'
Object variable or With block variable not set

lookforsmt is offline   Reply With Quote
Old 01-12-2018, 10:14 AM   #34
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 366
Thanks: 56
Thanked 1 Time in 1 Post
lookforsmt is on a distinguished road
Re: recipients multiple emails from table

Thanks pbaldy, but i have no idea about that.
lookforsmt is offline   Reply With Quote
Old 01-12-2018, 11:12 AM   #35
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,491
Thanks: 14
Thanked 272 Times in 270 Posts
Mark_ will become famous soon enough
Re: recipients multiple emails from table

Quote:
"WHERE (((Mail.Summary_chk)=Yes)"
You are looking at a field that is defined as a Yes/No. It is helpful in these kinds of questions to be clear on what your fields are defined as. I can tell because ACCESS put in the Mail.Summary_chk=Yes.

Please check HERE for more info on ADO OpenRecordSet

as pbaldy posted, I think you are referencing the OpenRecordSet incorrectly.

Prior to this line of code, I would add the following pair of line;

Code:
Dim asOpenRecordSet as STRING
asOpenRecordSet = "Select * from Mail where Mail.Summary_chk=Yes"
I would change the line itself to read

Code:
Set rs = CurrentDb.OpenRecordset asOpenRecordSet
OpenRecordSet takes multiple optional parameters.

I haven't tried this, but I ran into a similar issue previously that was solved by NOT using parenthesis when passing parameters. I also like using a string to hold all passed values to avoid issues with what is being passed and how its parsed.
Mark_ is offline   Reply With Quote
Old 01-12-2018, 11:54 AM   #36
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 366
Thanks: 56
Thanked 1 Time in 1 Post
lookforsmt is on a distinguished road
Re: recipients multiple emails from table

Thanks Mark, i have tried the suggestions and amended the code but still getting error.
I am adding my db, if you can assist me this Pls.
form name is "frm_entry_3bc to 9_4
Thanks
Attached Files
File Type: accdb Test3.accdb (744.0 KB, 20 views)
lookforsmt is offline   Reply With Quote
Old 01-12-2018, 12:23 PM   #37
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,491
Thanks: 14
Thanked 272 Times in 270 Posts
Mark_ will become famous soon enough
Re: recipients multiple emails from table

In your code you have

Code:
Set rs = CurrentDb.OpenRecordset asOpenRecordSet

Dim asOpenRecordSet As String
asOpenRecordSet = "Select * from Mail where Mail.Summary_chk=Yes"
This should be

Code:
Dim asOpenRecordSet As String
asOpenRecordSet = "Select * from Mail where Mail.Summary_chk=Yes"

Set rs = CurrentDb.OpenRecordset(asOpenRecordSet)
That got passed the first error...

For the next set... what table are you trying to use to build up your body? You are still referencing the Mail table.

Mark_ is offline   Reply With Quote
Old 01-12-2018, 12:31 PM   #38
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 366
Thanks: 56
Thanked 1 Time in 1 Post
lookforsmt is on a distinguished road
Re: recipients multiple emails from table

Thanks Mark for the help.
The mail table is only to send to the recipients where the checkbox is "Yes"

The email is intended to show the details of table "tbl_Dispatch" and "tbl_Summary" in the body of the email.
lookforsmt is offline   Reply With Quote
Old 01-12-2018, 12:40 PM   #39
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 366
Thanks: 56
Thanked 1 Time in 1 Post
lookforsmt is on a distinguished road
Re: recipients multiple emails from table

i changed the code and getting the run-time error 3265, "Item not found in this collection"
lookforsmt is offline   Reply With Quote
Old 01-12-2018, 02:01 PM   #40
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,491
Thanks: 14
Thanked 272 Times in 270 Posts
Mark_ will become famous soon enough
Re: recipients multiple emails from table

Quote:
Originally Posted by lookforsmt View Post
i changed the code and getting the run-time error 3265, "Item not found in this collection"
Same error I received as your existing code references the MAIL table. Hence why I was asking which table it should reference.
Mark_ is offline   Reply With Quote
Old 01-12-2018, 02:09 PM   #41
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,491
Thanks: 14
Thanked 272 Times in 270 Posts
Mark_ will become famous soon enough
Re: recipients multiple emails from table

Quote:
Originally Posted by lookforsmt View Post
The email is intended to show the details of table "tbl_Dispatch" and "tbl_Summary" in the body of the email.
What code is supposed to do this? You do not have code referencing either of these tables. You are filling in a variable with SQL for q_Tab_2222, but you don't do anything with this code.

I'd advise coming up with a naming convention also. The name "q_Tab_2222" is pretty meaningless. Same with "frm_email_5_2" or "frm_entry_3bc".

You also have a "Tbl_Entry_Date" that seems to only hold a single date and an autonumber key. This does seem rather pointless as you would normally store the date in a file rather than a pointer to a date.

You will want to read up on data normalization. Tbl_Dispatch seems to be intended to hold references to 6 locations. This will become very problematic the first time you have a seventh location you will need to deal with. Seems like it should have those location in a child record. Same with "Tbl_test", though I've no idea what it would be used for.
Mark_ is offline   Reply With Quote
Old 01-12-2018, 09:00 PM   #42
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 366
Thanks: 56
Thanked 1 Time in 1 Post
lookforsmt is on a distinguished road
Re: recipients multiple emails from table

Thanks Mark for pointing out the Inconsistency in the db. I am not sure how to answer you as my knowledge in access is very poor. But i will try to explain you hope if that helps you to provide a solution.

You are right, the names of some of the forms are meaningless but when i try to do some changes in the code or form layout, i simply give the name to identify the latest version.

Once i achieve my goal then i apply to the actual project.

From the attached table i just want to give background:

a) i want to achieve is emailing or auto trigger at a fixed time once a day to a set of people whose names are checked in the table "Mail"

b) There would be two summary tables, tbl_Summary and another table similar to tbl_Summary. But i agree definitely not tbl_Dispatch since the location field in near future will only increase and it would be difficult to fit in the screen.

c) The summary tables will be refreshed on daily basis by overwriting when new data is uploaded. I don't intend to save any data in any of the tables.

d) The table tbl_Entry_Date & tbl_Test were part of another example which i have missed to remove from these db.

As i said, i try to learn something in access vba and then apply it in the original db.

Hope the above was clear to you.

In short i only want to achieve is send email with 2 tables in the body of the email and both have fixed fields which will be derived from two separate queries which one of them i have named as q_Tab_2222. The second query would also be similar to this but with slight changes.

For this example if you can advice me the code how to display this on email that would be great.

I am not sure if i have replied to all your concerns. Sorry for the late response by the time i receive your post it was 2am here.
lookforsmt is offline   Reply With Quote
Old 01-12-2018, 10:22 PM   #43
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 1,491
Thanks: 14
Thanked 272 Times in 270 Posts
Mark_ will become famous soon enough
Re: recipients multiple emails from table

I have cut down the app to JUST what is needed to send the Emails. I haven't checked if the Emailing part works yet as I don't have outlook set up on my home computer.

Especially when testing you will want to use meaningful names. Doing so lets you post what you are working on without a lot of confusion from other programmers. It is a really really really really really good habit to get into.

As I am showing with the sample, you won't want to use your temporary tables. The queries themselves work just fine. The sample loops through the queries as I am letting THEM do all the work rather than messing with SQL.

In the future, rather than trying to work out what the SQL should be, get a query that does what you want and use it instead.

I am also using Sub and Function calls to break out what needs to be done into logical pieces. It is far easier to debug a dozen lines of code in a single function than to hunt through hundreds of lines to see where you could have made a mistake.

I would recommend looking at each section, pulling up GOOGLE, and seeing what MSDN says about each part. That can help you identify quickly what else you may want to do or use.
Attached Files
File Type: accdb SendEmail.accdb (528.0 KB, 27 views)
Mark_ is offline   Reply With Quote
The Following User Says Thank You to Mark_ For This Useful Post:
lookforsmt (01-13-2018)
Old 01-13-2018, 12:06 AM   #44
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 366
Thanks: 56
Thanked 1 Time in 1 Post
lookforsmt is on a distinguished road
Re: recipients multiple emails from table

Thanks Mark, i will go through the db and check through the net for better understanding.

i have referenced the outlook again for the compile error can't find project or library.

Will revert in case of any help required.
I want to thank you for all the support provide and patient to listen to my query.
Thank you very much.
lookforsmt is offline   Reply With Quote
Old 01-13-2018, 12:40 AM   #45
lookforsmt
Newly Registered User
 
Join Date: Dec 2011
Posts: 366
Thanks: 56
Thanked 1 Time in 1 Post
lookforsmt is on a distinguished road
Re: recipients multiple emails from table

Hi! Mark, i am trying to understand the code.

Just wanted to know which part of the code should i change if i want to send the email to outlook .Send or .Display and not display on the form screen itself.

lookforsmt is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Emails generated from Access 2003 include ATT00001.bin for some recipients lespritdelescalier Modules & VBA 2 09-19-2017 09:50 AM
[SOLVED] CDO emails to defined array of recipients SazzleTWG Modules & VBA 3 09-19-2013 05:36 AM
[SOLVED] Sending emails: selecting recipients from a table? Access 2007 CazB Modules & VBA 5 07-19-2013 04:58 AM
Sending emails with Access to multiple recipients pnevilm Modules & VBA 2 06-01-2011 11:04 PM
Email multiple recipients from a table skwilliams Modules & VBA 3 12-30-2003 07:50 AM




All times are GMT -8. The time now is 07:54 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World