Mass e-mail of reports from Access

[sboll]

The database I am using is in Access 2003.

I have a database that has data for 300+ e-mail accounts. Each week I want
to send each recipient their own report out of this database. We use Novell
Groupwise for e-mail.

Is there a quick easy way to do this right out of Access?

Thanks
Steven Boll

 

[Dennis]

Use SENDOBJECT in VBA.

 

[Dale Fye]

Unfortunately, it is not quite as clean as that.

The SendObject method does not provide a way to filter the report, so that
it only sends the applicable portions to each user. The way I handle this is
to create a function that stores the userID or email address (whichever value
shows up in the report) of the person that the report is going to

I then modify the SQL of the query that the report is based upon, so that it
refers to this function.

Finally, I create a loop to loop through all of the email addresses
(userIDs). Inside this loop, I set the value of my function, then use the
sendobject method to send the report, which is not limited to the current
UserID.

The function looks something like:

Public Function fnUserID(Optional SomeValue as variant = Null) as String

Static myUserID as String

if not isnull(SomeValue) then myUserID = SomeValue
fnUserID = myUserID

End Function

Then, the query for the report might look like:

SELECT * from SomeQuery WHERE fnUserID() IS NULL OR fnUserID = [UserID]


--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

 

[sboll via AccessMonster.com]

Thanks Dale. This can get me going in the right direction.

The only difficulty I found on some test runs of manually sending out an
email is that it does no seem to like Groupwise, our resident e-mail
application.

Thanks
Steve

Unfortunately, it is not quite as clean as that.

The SendObject method does not provide a way to filter the report, so that
it only sends the applicable portions to each user. The way I handle this is
to create a function that stores the userID or email address (whichever value
shows up in the report) of the person that the report is going to

I then modify the SQL of the query that the report is based upon, so that it
refers to this function.

Finally, I create a loop to loop through all of the email addresses
(userIDs). Inside this loop, I set the value of my function, then use the
sendobject method to send the report, which is not limited to the current
UserID.

The function looks something like:

Public Function fnUserID(Optional SomeValue as variant = Null) as String

Static myUserID as String

if not isnull(SomeValue) then myUserID = SomeValue
fnUserID = myUserID

End Function

Then, the query for the report might look like:

SELECT * from SomeQuery WHERE fnUserID() IS NULL OR fnUserID = [UserID]

Use SENDOBJECT in VBA.

[quoted text clipped - 8 lines]

 

[Dale Fye]

Sorry, cannot help you with GroupWise.

You might want to check out the Office Developer news groups. They tend to
have more posts about using Office with "other" software applications.

microsoft.public.office.developer.officedev

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Unfortunately, it is not quite as clean as that.

The SendObject method does not provide a way to filter the report, so that
it only sends the applicable portions to each user. The way I handle this is
to create a function that stores the userID or email address (whichever value
shows up in the report) of the person that the report is going to

I then modify the SQL of the query that the report is based upon, so that it
refers to this function.

Finally, I create a loop to loop through all of the email addresses
(userIDs). Inside this loop, I set the value of my function, then use the
sendobject method to send the report, which is not limited to the current
UserID.

The function looks something like:

Public Function fnUserID(Optional SomeValue as variant = Null) as String

Static myUserID as String

if not isnull(SomeValue) then myUserID = SomeValue
fnUserID = myUserID

End Function

Then, the query for the report might look like:

SELECT * from SomeQuery WHERE fnUserID() IS NULL OR fnUserID = [UserID]


--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Use SENDOBJECT in VBA.