Using Dlookup for SendObject Emails

[SQL Brad]

I want to make sure this will work before I rack my brains out! I am running
an antique mall where we will have over 200 vendors. I will get a download
of sales that will include a department number, item number and dollar
amount. My goal is to run a macro that will email each vendor their sales
from the prior day (assuming they had sales)....I have set up the following
two tables....

Department
Departmentnumber
email
name

Sales
departmentnumber
date
item
amount

I want to use the macro to send each vendor their information. Here are my
questions:

1. Can I use dlookup to retrieve the email address asssociated with the
departmentnumber?
2. Will I have to write a report for each of the 200 vendors? or can I just
use the dlookup to retrieve the sales amount?

Ideally, this is what would happen...
Assume in the example that I have the following departments with the
following sales:

department 101, $100
department 103, $200

In this case, two emails would go separately when I run the macro..email 1
should say $100, etc.

I hope this makes sense.....

Also, assuming I can use dlookup, can you please help me with syntax for the
macro..

thanks in advance.

[SQL Brad]

Here is the error I am getting when running the macro...

the Condition is TRUE
Action Name is Sendobject

Under Arguments this is the text:
3, salesdaily, RichTextFormat(*.rtf),
rbr...@gmail.com#http://rbr...@gmail.com#, rbr...@gmail.com, , Grapevine
Antique Mall Sales Report, Here is your Daily Report, No,

here is the text of the macro itself:
Send Object
Object type=report
Object name=salesdaily
output format: rich text format
to: =DLookUp("[email]","departments")
cc: rbr...@mydomain.com
subject: Sales Report
Message Text: here is your daily report
edit message: no

[SQL Brad]

Update:

I fixed the problem so that there are no error messages, I mistakingly had
the email field set as an html field.....I also took out the cc email address
in the macro itself. Anyway, the report is only going to the email address
in the first record....

[Steve Schapel]

Brad,

If I understand you correctly, you want the report to be sent to each
vendor, with just the data specific to them. Whereas this would be
possible using a macro, it would be awkward and clunky. Because it
involves cycling through the records, and acting on them one at a time,
you really would be better to use a VBA procedure, which provides for
looping logic. In that case, you would open a Recordset based on the
Vendors table, and loop through it, sending the email, and filtering the
report for each one as you go.

--
Steve Schapel, Microsoft Access MVP

[SQL Brad]

Steve,

I created a macro that imports about 200 items between 5 vendors. I created
a macro that 1. imports the data from excel. I also wrote 5 queries (one for
each vendor) then I created the macro and wrote 5 lines in it, one for each
query....it works like a charm, it sends emails to all 5 vendors just like
that! I wish Outlook was faster, I am using express click yes so I don't
have to sit and watch it! Anyways, I am not a programmer, so I like these
macros that are pretty self sufficient....if you send me email or something I
can send db back to you so you can see.....thanks for your help.

[Steve Schapel]

Glad you got it going, Brad. I misunderstood you before - I thought you
had 200 vendors, so sending 200 emails, each with different data. 5 is
different! :-)

[ricogrande]

Steve and Brad, this sounds very similar to what I need to do. I need to
email each manager a list of their employees plus a bunch of fields related
to each employee. I cannot set up the email address of each maanger in each
query like you did because I have way more than 5 and I want to loop through
the list. I do not know how to pull the data out of the table unique to each
manger and then send an excel spreadsheet as an attachement in the emails.
Any advice?