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

multiple queries to multiple contacts email - vba

P: 7
Hello everyone, need some help on figuring out.

I have a table called EmailReports table, it has:
Unit, Unitcode, POC, Emails, QueryName, and Notes

so far, i have been exporting each query and sending it out manually, i would like to automate it.

So, when a member clicks email button, i would like:

for each unit, create an email, attach the query (that is in that record) set email, and insert certain text into body (html'd) and send it....I can def review before sending or just straight up send it.


Now i have tried, macro, but issue with that is body characters are limited and i have to add SendObject macro for each query, and now exploring other alternatives.

I have tried vba, but not good enough to figure out how to run a loop and do what i want it to do...(like instead of to box, have it pull email from recordset..)

I can attach a sample of the database of what i have so far.

Any assistance is much appreciated, Im some what savy at figuring stuff out just need to see an example..and google isnt helping as much tooday...
Nov 6 '12 #1
Share this Question
Share on Google+
10 Replies


P: 7
attached docs..trying to be sneaky..cant seem to add the database..
Attached Files
File Type: docx New Microsoft Office Word Document.docx (115.8 KB, 288 views)
Nov 6 '12 #2

zmbd
Expert Mod 5K+
P: 5,287
Djsaix:
First: as a kindness you should zip/compress your files before uploading them to any website.
Second: This is not personal; however, very few of the Experts/Mods will download your file just as a precaution. Even the most well meaning inidividual can have something lurking in their system

There are a few ways of accomplishing what you are after... MS has a page that explains one way ( docmdsendobject()) This works well for fairly straight forward reports and queries; however, there are some limitations.

Then there is the Application Automation that you can look at for some ideas.

Please post your code, remember to format it using the <CODE/> button and we'll take a look.
Nov 7 '12 #3

P: 7
Hey zmbd,
This computer that i am using doesnt seem to have zip (darn gov pc's).

I have tried doing doCmdsendobject(), but i am getting lost at how to properly loop it through that it sends out all my queries. I'll add in what i can, and once im hope ill zip it and add on what i have..(which isnt much sadly).

EmailReportTable (table)

Unit Unit Codes POC Emails QueryName Notes
xx1 ak SomeGuy 34@re xx1 msg1
xx2 ac SomeO 48@29 xx2 msg2

^this is what it contains(example)


main table (table)

queries (different queries that pull from main table)

button: Command65 once it clicked, here is the code
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command65_Click()
  2. 'testing button on going through all records...but no success on setting up emailing for each record..
  3. 'some variables are just for testing...
  4. 'tried few other ways didnt seem to work..
  5.  
  6. Dim db As Database
  7. Dim rst As Recordset
  8. Set db = CurrentDb()
  9. Set rst = db.OpenRecordset("EmailReportsTable")
  10. Dim count As Integer
  11.  
  12. Do While Not rst.EOF
  13.  
  14. DoCmd.SendObject acSendQuery, QueryName, acFormatXLS, Emails, , , Unit, Notes, True, False
  15.     rst.MoveNext
  16.     count = count + 1
  17. Loop
  18.  
  19. MsgBox "Counting records " & count, vbDefaultButton1, "counting"
  20. 'Close what you opened.
  21. rst.Close
  22.  
  23. 'Message box for notification
  24. MsgBox "Done", vbOKOnly, "Confirmation"
  25.  
  26. End Sub
  27.  
I know its incorrect, i cant seem to figure out how to properly call each field from the table... right now its opening each email, only sending out the first record of EmailReportsTalble (with correct fields...its not moving on to the next record...grr_)
Nov 7 '12 #4

P: 7
still having issues..any advice?
Nov 14 '12 #5

zmbd
Expert Mod 5K+
P: 5,287
OK,
Well... you said you were working on the project; thus, when you didn't post back I for one figured you had fixed the issues.

- Did you follow the links in #3? The MS site is fairly clear (which is unusual for them) and the automation does take a read or two to understand?

- Are the following two lines the VERY first two lines in every module and every code:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database 
  2. Option Explicit 
- Have you done a Compile of the code... you'd be surprised how many errors are fixed that way.

- Have you modified your code since posted in #4? Once compiled and any errors that show up are fixed please repost.

-
still having issues..any advice?
Please be more specific. You don't tow your car into the shop and say... it doesn't work and walk away (if you did that to some shops there would be a whole new engine and some body work, maybe new tires... brakes... $20K-US invoice) no... you try to give the shop as much information as you can.
Nov 15 '12 #6

P: 7
My appologies, thought I provided everything needed. The code has few minor changes, as I been playing around to get it to work..


Here is my current code:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Command65_Click()
  5. 'testing button on going through all records...but no success on setting up emailing for each record..
  6. 'some variables are just for testing...
  7. 'tried few other ways didnt seem to work..
  8.  
  9. Dim db As Database
  10. Dim rst As Recordset
  11. Set db = CurrentDb()
  12. Set rst = db.OpenRecordset("EmailReportsTable")
  13.  
  14. 'rst.MoveFirst     'this line doesnt seem to be working
  15. DoCmd.RunCommand acCmdRecordsGoToFirst
  16.  
  17. Do While Not rst.EOF
  18.  
  19.  
  20.     DoCmd.SendObject acSendQuery, QueryName, acFormatXLS, Emails, , , Unit, Notes, True
  21.     DoCmd.RunCommand acCmdRecordsGoToNext
  22.     'rst.MoveNext
  23.     'for some reason above line doesnt work but
  24.     'Docmd...GotoNext does...
  25.  
  26. Loop
  27.  
  28.  
  29. 'Close what you opened.
  30. rst.Close
  31. db.Close
  32. Set rst = Nothing
  33. Set db = Nothing
  34.  
  35. 'Message box for notification
  36. MsgBox "Done", vbOKOnly, "Confirmation"
  37.  
  38. End Sub
  39.  
I've checked out all the links you have provided, top two lines are there and this button doesnt call on any functions or modules.

Right now, their are 5 records in "EmailReportsTable", the command button pressed, it opens each record (attahces file, to block, subject, and text) all is fine, once i pres send it opens the other. So i send 5 emails with attachments (like i want) but once the last email is sent i get an error message:

Run-Time error '2498':
An Expression you entered is the wrong data type for one of the arguments.

Upon debug, it goes to line:20, identifying all the object that are called as null. So how do i get rid of the error message?
(I dont get to the msg box saying its been done)
Nov 15 '12 #7

zmbd
Expert Mod 5K+
P: 5,287
I have an example database that will send demonstrate the send all queries by the send object and also by merging three quries into one workbook as seperate worksheets using the transferworksheet method that is then attached using automation.,. If interested let me know before 5pm-CST today otherwise it will have to wait till Monday


Regarding code in post #7:


Backup your db:

Line 9: DIM db as DAO.Database
Line 10: DIM rst as DAO.Recordset

BEFORE line 14: rst.movelast
Line 14: uncomment

Line 15: remove

Lines 17 thru 26 are wrong logic loop for code
Change
Expand|Select|Wrap|Line Numbers
  1. Do
  2. (running code)
  3. Loop until rst.eof
Line 21: remove
Line 22: uncomment


Line 14 and 22 did not work more than likely due to the incorrect database libraray. By explicitly calling DAO in lines 9 and 10 the movefirst/last/next should become available.

This is just from the few momemnts I've take to look.
Nov 15 '12 #8

P: 7
i think i love you..

Thank you..
Dec 14 '12 #9

zmbd
Expert Mod 5K+
P: 5,287
You're very welcome.
How are things going with this project?
Dec 14 '12 #10

P: 7
The error has gone away.

And loop works both way (i changed it to yours since it made more sence.)

I sent out 40+ emails( some with 1 and others with 2-3 attachements), to over 250 people in about 5mins. YaY for automation..

Thank you.
Dec 21 '12 #11

Post your reply

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