By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,374 Members | 1,764 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,374 IT Pros & Developers. It's quick & easy.

Send Email based on Query?

P: 11
Hello again , well I have pretty much completed my project but have a small problem in regards to sending email's based on a Query.

I have a Query that returns results based on due date. This is working fine.Problem is I would like all records that contain an email_address to be sent a custom email using the fields from the query. for Example the Query returns

Title: Mr
FirstName:BOB
Lastname:Jones
ExpiredDate:1/1/10
Days:-186
Notified:No
------------------
Title: Mrs
FirstName:Rachael
LastName:Jones
ExpiredDate:2/1/10
Says:-185
Notified:No
------------------
etc..

how would I Implement this into a custom email which would look like this:

Dear Mr Jones,
our records indicate that your subscription has expired on the 1/1/10 which is -186 days old.
Please let us know weather you would like us to update your subscription by replying to this email.

Thank You!

After the Email has been sent would it be possible to change the Notified: Yes, or would manual be best.

I am wondering if something like the following code could be modified to do what I need?. if so how?

Expand|Select|Wrap|Line Numbers
  1. dim qdf as dao.querydef
  2. dim rst as dao.recordset
  3.  
  4. set qdf=DBEngine(0)(0).Querydefs("YourSelectQuery")
  5. set rst=qdf.OpenRecordset
  6.  
  7. do until rst.EOF
  8. DoCmd.SendObject acSendNoObject,,, rst.Fields("EMailAddress")
  9. '<--Fill in the args as necessary from the recordset
  10. rst.MoveNext
  11. Loop
  12.  
  13. rst.Close
  14. set rst=nothing
  15. set qdf=nothing
or using this code:

Expand|Select|Wrap|Line Numbers
  1. # Dim MyDB As DAO.Database
  2. # Dim rst As DAO.Recordset
  3. #  
  4. # Set MyDB = CurrentDb
  5. # Set rst = MyDB.OpenRecordset("Orders Qry", dbOpenForwardOnly)
  6. #  
  7. # With rst
  8. #   Do While Not .EOF
  9. #     Mailbody = Mailbody & ![OrderID] & " | " & ![CustomerID] & " | " & ![ShippedDate] & vbCrLf
  10. #       .MoveNext
  11. #   Loop
  12. # End With
  13. #  
  14. # rst.Close
  15. # Set rst = Nothing
  16. #  
  17. # oMail.Body = Mailbody
Thank you.
Jul 6 '10 #1

✓ answered by Jim Doherty

@Weise
Depends how clever you want to be with this in your design. You have a query that defines a 'Subsription Due' namely a list of people containing each persons email address as part of your query.

The prettiest thing would be to see in the 'same query list' a virtual column containing the actual contents of the email body 'before' it gets sent. I say this because all you need then to do is refer the .body element element of any email to the specific column in your query. You could build this email body so to speak by looking up a template stored in a table, assembling the body content on the fly and returning the elements of the body to your query as a callback from a function.

It largely depends on how flexible you want you emailing functionality to be. If this is a one off then you can hard code it job done finished, but if you forward think what happens if you want to amend the body structure sometime in the future. Storing the textual structure either in a table or externally as a text file and reading it in allows for you not to have to revisit and amend your VBA code base which you would otherwise have to do if you hard code.

The mechanics of actually sending the email via outlook are pretty straightforward there being lots of references available how to do that. If you are automating this however and relying on the email actually being sent rather than being sat in your outbox or failing on the send (for one reason or another) before you update any 'emailsent' field then you can see the obvious problems that arise in keeping a handle on that. Then again, you dont want to really do it manually if you can at all avoid it.

Depending what version office (service pack included) you have you may likely encounter the 'security model outlook pop up' that tells you your email account is being accessed and if you wish to allow it to take place.

I won't go into the 'ins and outs' of this, as it is/was a well documented problematic issue to a point in working around it. Suffice it to say this.......You do not always have to use Outlook merely to send an email. In fact a very useful way is to click a button and it send an email DIRECTLY to an SMTP server bypassing outlook altogether. You can then define whether a copy of that email gets SENT BACK to your email inbox (that is your actual confirmation that the email has been sent)

Take a look at the attached db to give you an idea. take a look at the query calling a function and the module itself. This is merely an idea of one way to do these things

It does NOT include the emailing code required to send this I am sure you can work that out given the email body text is NOW part of the actual query itself. If you wish to pursue the SMTP direct email capability as referred to then let me know if you are struggling...unless of course you are happy with other content contribution coming to this thread as alternatives.

Regards

FOOTNOTE...The below mention file is now superceded by a more functional representation at Post 7

Share this Question
Share on Google+
8 Replies


FishVal
Expert 2.5K+
P: 2,653
Maybe both code snippets together will do the job somehow. Did you try to combine the code altogether?

Kind regards,
Fish.
Jul 6 '10 #2

Jim Doherty
Expert 100+
P: 897
@Weise
Depends how clever you want to be with this in your design. You have a query that defines a 'Subsription Due' namely a list of people containing each persons email address as part of your query.

The prettiest thing would be to see in the 'same query list' a virtual column containing the actual contents of the email body 'before' it gets sent. I say this because all you need then to do is refer the .body element element of any email to the specific column in your query. You could build this email body so to speak by looking up a template stored in a table, assembling the body content on the fly and returning the elements of the body to your query as a callback from a function.

It largely depends on how flexible you want you emailing functionality to be. If this is a one off then you can hard code it job done finished, but if you forward think what happens if you want to amend the body structure sometime in the future. Storing the textual structure either in a table or externally as a text file and reading it in allows for you not to have to revisit and amend your VBA code base which you would otherwise have to do if you hard code.

The mechanics of actually sending the email via outlook are pretty straightforward there being lots of references available how to do that. If you are automating this however and relying on the email actually being sent rather than being sat in your outbox or failing on the send (for one reason or another) before you update any 'emailsent' field then you can see the obvious problems that arise in keeping a handle on that. Then again, you dont want to really do it manually if you can at all avoid it.

Depending what version office (service pack included) you have you may likely encounter the 'security model outlook pop up' that tells you your email account is being accessed and if you wish to allow it to take place.

I won't go into the 'ins and outs' of this, as it is/was a well documented problematic issue to a point in working around it. Suffice it to say this.......You do not always have to use Outlook merely to send an email. In fact a very useful way is to click a button and it send an email DIRECTLY to an SMTP server bypassing outlook altogether. You can then define whether a copy of that email gets SENT BACK to your email inbox (that is your actual confirmation that the email has been sent)

Take a look at the attached db to give you an idea. take a look at the query calling a function and the module itself. This is merely an idea of one way to do these things

It does NOT include the emailing code required to send this I am sure you can work that out given the email body text is NOW part of the actual query itself. If you wish to pursue the SMTP direct email capability as referred to then let me know if you are struggling...unless of course you are happy with other content contribution coming to this thread as alternatives.

Regards

FOOTNOTE...The below mention file is now superceded by a more functional representation at Post 7
Attached Files
File Type: zip Email_Query_Using_Template_Text_As_Body.zip (17.1 KB, 759 views)
Jul 6 '10 #3

P: 11
Thank you Jim , for a well thought out and in depth explanation into this problem. Also for the sample which you have comprised for me above. A++++

The Query is awesome and I like the way that you have used a separate table for the compilation of the output email to be sent.

in respect to not using outlook as the application for sending the email and using instead the SMTP service , this would be advantageous indeed!.

How would I do this with the db that you have provided. I will admit that there are several mechanisms ie; query callback that I have not seen before but having studied your code I can grasp the workings of these things.

Ideally I would like to perhaps click a button to send the emails rather then it being an automatic process as such.

also when trying to edit the template in the table Iam having difficulty in using the [enter] key to drop down a line in the EmailBody cell. Is there a way around this?

I am extremely happy with your well thought out process and endeavor to incorporate it into my current project.

if perhaps you could incorporate the SMTP crudentuals into this DB I think I can then integrate your system into my db and complete this project yay!.

Again Thank you

Legend.

btw: Iam using Access 2003 SP3 to compile the DB but would like it to work with access 2000 if possible.Cheers
Jul 6 '10 #4

Jim Doherty
Expert 100+
P: 897
@Weise
Hi am glad it helps you :)

To drop down a line in the memo field that contains the template text in the table itself, all you need to do is instead of using just the 'enter' key in isolation, simply combine it with the 'ctrl' key so it becomes 'ctrl+enter' both pressed at same time. This places the cursor on a new line in the field. (although I admit in raw query interface it can hop around sometimes indiscriminately to the next record and so on)

As you develop this, you would in reality build a form based on a query servicing the template table, in which case your text box control servicing the memo field can then be set to allow a 'new line' in the control when enter is depressed as opposed to moving to the next field. (look at textbox control properties and you will see the relevant property listed)

Insofar as the emailing aspect is concerned you can of course pump into some kind of 'activity log' table the CustomerID, TemplateID and 'Date Time Sent' automatically once any email has been sent via the 'on click' event of a button. This trail is in addition to any email copy that is sent back to your inbox (in effect a double whammy audit primarily)

Give me a little time tomorrow and I will put together the essential ingredients based on my existing example contribution as incremental development and post an 'additional' file taboot.

Obviously you will amend it to suit your needs but the code will be essentially generic, you only having to amend certain elements to reflect your environment ie the address of your smtp server and other aspects that become obvious to you (typically your isp mailbox smtp address or other such facility on any internal network)

I have written this in Access 2000 format (given most of my commercial apps support practically is for that version) so you should have no problems in 2003
Jul 6 '10 #5

P: 11
This is Great News! Thank you very much. I believe that alot of people will benefit from these samples as I have seen many questions and 1/2 answers on the web in regards to different less functional methods to cater for this problem.

this one has already by far superseded the many forums I have visited and as is with my last query here I was able to overcome other such problems with the help of members of this board , which I am also Grateful for.

in regards to the CTRL+ENTER scenario what a tip , I would have been using the space key which would have in effect stuffed it all up no doubts there lol.

Again thank you Very much for your Time and your efforts.

after coming to grips with your creation I will endeavor to utilize it in several projects so that I can fully grasp all concepts there-in.

Kindest Regards

Weise.
A++++
Jul 7 '10 #6

Jim Doherty
Expert 100+
P: 897
@Weise
OK ....here it is for you to rip apart and disect. A fully functional Demo database that took me most of today to put together dealing with sending emails via Collaboration Data Objects (CDO) using Access. (written in Access 2000)

This demo is much more advanced than the earlier version but continues along the same lines and includes a setup screen for inserting the SMTP server Address and the necessary email defaults that you would use for bulk sends.

I wont go into it too much as I would be here all evening. I am sure you will see everything as logical and benefit by ripping apart the code and examining it.

In short, A table of customers is in evidence here, against which is an email address per customer. You MUST put some real live data into this to get any benefit. It pretty much works as I expect having run it through a few times and I truly hope I dont need to fall into the 'Service pack' scenario hahah :) remember it is a demo purely to assist in learning. I have left out the functionality of including a file attachment as part of any send process and yes this can be done but I am mindful of keeping it within the current scope of what it is you are doing

The system includes a multi select list box with supporting controls and all events.A button to send mail derived from a functional retrieval of and merging with recordset data / templated data material. A record of an email sent to a customer is pumped into an Activity table as part of the email VBA Code (THAT is your minimalist audit evidencing a send)

Regards..... (and thankyou for your very kind words we try to do our best on this site) :)

The below file tested to work on Access 2000 through to and including Access 2007
Attached Files
File Type: zip CDO_Email.zip (122.9 KB, 676 views)
Jul 7 '10 #7

P: 11
Hello Jim :D , Very sorry sorry not to replay earlier - however I am in the middle of moving house and have been doing several trailer loads of Furniture to our new humble abode. :\

Iam amazed at the thought and construction that you have put into this project! It is well setup and has answered several questions that I have had in respect to other things like copyright notices etc and author details pop-ups etc which I love very much.

However I think Iam going to have to address the Service pack issue lol as for the life of me I can't figure out how to get past the entry of details/email screen as it refers to missing library's and such. Please see image for details;



Sorry actual error that is recieved below;

Compile error - can't find project or library

I have looked this error up and have read the Ms site in regards to it but for some reason I seem to be missing a vital component or something from my version of access which is the References
link under Tools > . It's not there as discussed here: http://support.microsoft.com/kb/840926 @ Ms :(

I did try to nut-out how to remedy this but am bewildered as to how to proceed beyond this point.

Apart from that I will say this. I love your work and I will be studying every inch of it over the next few weeks once I have settled into my new abode.
I haven't got long to go into extreme details as yet as this is as far as I have gotten with the CDO email DB and will be back again to give a full report when I can do so - next week or so.

Again I can't say this enough your help and assistance as well as your attention to detail is amazing a perfectionist I am sure. Thank you and the other members of this board for being the number one support group for access and other area's IMO, no other board even comes close , and Iam a Member of pretty much all of them Iam sure.

Thank you.
Jul 10 '10 #8

Jim Doherty
Expert 100+
P: 897
Hi Weise :)

Best of luck with the house move no need to apologise :)

Open the VB Editor and open the References dialog window by selecting the
Tools -> References menu. Note the name of the library that are ticked and Remove any "MISSING:" References and then add the
same named References with the correct paths. You will see the filepath for the library file that is refered to in the lower half of the references window Close the References dialog window and attempt to recompile by selecting the Debug menu -> Compile repeatedly until the code compiles without errors. Once it compiles
without errors, your References Collection should be complete.

If you don't see any "MISSING:" References listed, then add another library reference (any one that isn't already listed), close the References dialog window and attempt to recompile, then open the References dialog window
again and remove the library reference you just added. Close the References dialog window and attempt to recompile again.

It might be helpful if you can let me know which references are listed as missing references and causing the problem so that i can address it.

Incidentally since you downloaded I added one more button to the email screen simply to open the template window, allow zero length strings to the activity log and some minor default synchronisation issues, so you might wish to download the file and try again.
Jul 10 '10 #9

Post your reply

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