multiple queries to multiple contacts email - vba

[djsaix]

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...

[djsaix]

attached docs..trying to be sneaky..cant seem to add the database..

[zmbd]

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.

[djsaix]

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

Private Sub Command65_Click()
'testing button on going through all records...but no success on setting up emailing for each record..
'some variables are just for testing...
'tried few other ways didnt seem to work..
 
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("EmailReportsTable")
Dim count As Integer
 
Do While Not rst.EOF
 
DoCmd.SendObject acSendQuery, QueryName, acFormatXLS, Emails, , , Unit, Notes, True, False
    rst.MoveNext
    count = count + 1
Loop
 
MsgBox "Counting records " & count, vbDefaultButton1, "counting"
'Close what you opened.
rst.Close
 
'Message box for notification
MsgBox "Done", vbOKOnly, "Confirmation"
 
End Sub
 

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_)

[djsaix]

still having issues..any advice?

[zmbd]

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:

Option Compare Database 
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.

[djsaix]

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:

Option Compare Database
Option Explicit
 
Private Sub Command65_Click()
'testing button on going through all records...but no success on setting up emailing for each record..
'some variables are just for testing...
'tried few other ways didnt seem to work..
 
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("EmailReportsTable")
 
'rst.MoveFirst     'this line doesnt seem to be working
DoCmd.RunCommand acCmdRecordsGoToFirst
 
Do While Not rst.EOF
 
 
    DoCmd.SendObject acSendQuery, QueryName, acFormatXLS, Emails, , , Unit, Notes, True
    DoCmd.RunCommand acCmdRecordsGoToNext
    'rst.MoveNext
    'for some reason above line doesnt work but
    'Docmd...GotoNext does...
 
Loop
 
 
'Close what you opened.
rst.Close
db.Close
Set rst = Nothing
Set db = Nothing
 
'Message box for notification
MsgBox "Done", vbOKOnly, "Confirmation"
 
End Sub
 

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)

[zmbd]

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

Do
(running code)
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.

[djsaix]

i think i love you..

Thank you..

[zmbd]

You're very welcome.
How are things going with this project?

[djsaix]

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.