Sending emails / flat files

[szag via AccessMonster.com]

I have a dilemma on how send out emails to multiple users. I realize if I
knew VBA I could probably code something but I don't and when I try to look
at somebody else's I just get confused. So I thought I would take the simple
route and just send it via a mail merge in Word - this works perfectly fine
except for one thing....one email recipient may have 50-60 records. I have a
relationship between the email receipient and the parts (up to 50-60) he/she
monitors. Obviously I don't want to send an email for the same person for
each part but I don't know how to take the 60 records in access and make them
one record so when I merge it into Word I can have one email per user and
include all the related information in that Word document. Any ideas?

 

[Brian]

In Access, create a query to select the user's e-mail address.

Switch to SQL view in the query. It will look something like this
(EMailAddress being the field and Table1 being the table)

Select EMailAddress from Table1

Change it to this:

Select DISTINCT EMailAddress from Table1

Save the query and use it, rather than the table, as the source for your
mail merge.

 

[szag via AccessMonster.com]

Thanks Brian - probably didn't explain myself clearly enough. Maybe if I show
the data simplified:
Record 1: (e-mail address removed), Part A,100 units
Record 2: (e-mail address removed), Part B,200 units
Record 3 (e-mail address removed), Part C,300 units

A query like this will force the mail merge to send 3 different emails to
"Steve" one with the info for part A, B, and C respectively. Don't know if it
is possible but I would like to write a query that would run like this:
(e-mail address removed),Part A,100 units, Part B,200 Units,Part C,300 units

...almost like I am creating a flat file. Now I can include all the data for
this one person in one email for the mail merge.
Does this help?

In Access, create a query to select the user's e-mail address.

Switch to SQL view in the query. It will look something like this
(EMailAddress being the field and Table1 being the table)

Select EMailAddress from Table1

Change it to this:

Select DISTINCT EMailAddress from Table1

Save the query and use it, rather than the table, as the source for your
mail merge.

I have a dilemma on how send out emails to multiple users. I realize if I
knew VBA I could probably code something but I don't and when I try to look

[quoted text clipped - 6 lines]

one record so when I merge it into Word I can have one email per user and
include all the related information in that Word document. Any ideas?

 

[Brian]

Then make it a crosstab query. I made a sample with three tables:

Users - 2 fields
UserName
EMail
Parts - 1 field
Part
Quantites - 3 fields
EMail (relationship to Users.EMail)
Part (relationship to Parts.Part)

Then I made this crosstab query:

RANSFORM Sum(Quantities.Quantity) AS SumOfQuantity
SELECT Users.EMail
FROM Users INNER JOIN Quantities ON Users.UserName = Quantities.UserName
GROUP BY Users.EMail
PIVOT Quantities.Part;


The query uses the Users & Quantities tables (joined on UserName) with these
settings in the query:
Field: EMail
Table: Users
Total: Group By
Crosstab: Row Heading

Field: Part
Table: Quantities
Total: Group By
Crosstab: Column Heading

Field: Quantity
Table: Quantities
Total: Sum
Crosstab: Value

This shows one row for each EMail, one column for each Part, and the
Quantity for each.

Thanks Brian - probably didn't explain myself clearly enough. Maybe if I show
the data simplified:
Record 1: (e-mail address removed), Part A,100 units
Record 2: (e-mail address removed), Part B,200 units
Record 3 (e-mail address removed), Part C,300 units

A query like this will force the mail merge to send 3 different emails to
"Steve" one with the info for part A, B, and C respectively. Don't know if it
is possible but I would like to write a query that would run like this:
(e-mail address removed),Part A,100 units, Part B,200 Units,Part C,300 units

...almost like I am creating a flat file. Now I can include all the data for
this one person in one email for the mail merge.
Does this help?

In Access, create a query to select the user's e-mail address.

Switch to SQL view in the query. It will look something like this
(EMailAddress being the field and Table1 being the table)

Select EMailAddress from Table1

Change it to this:

Select DISTINCT EMailAddress from Table1

Save the query and use it, rather than the table, as the source for your
mail merge.

I have a dilemma on how send out emails to multiple users. I realize if I
knew VBA I could probably code something but I don't and when I try to look

[quoted text clipped - 6 lines]

one record so when I merge it into Word I can have one email per user and
include all the related information in that Word document. Any ideas?

 

[szag via AccessMonster.com]

Thanks Brian - I will give it a shot. I know it would work better if I could
do it in a VBA loop but I don't have an expertise in that area.

Then make it a crosstab query. I made a sample with three tables:

Users - 2 fields
UserName
EMail
Parts - 1 field
Part
Quantites - 3 fields
EMail (relationship to Users.EMail)
Part (relationship to Parts.Part)

Then I made this crosstab query:

RANSFORM Sum(Quantities.Quantity) AS SumOfQuantity
SELECT Users.EMail
FROM Users INNER JOIN Quantities ON Users.UserName = Quantities.UserName
GROUP BY Users.EMail
PIVOT Quantities.Part;

The query uses the Users & Quantities tables (joined on UserName) with these
settings in the query:
Field: EMail
Table: Users
Total: Group By
Crosstab: Row Heading

Field: Part
Table: Quantities
Total: Group By
Crosstab: Column Heading

Field: Quantity
Table: Quantities
Total: Sum
Crosstab: Value

This shows one row for each EMail, one column for each Part, and the
Quantity for each.

Thanks Brian - probably didn't explain myself clearly enough. Maybe if I show
the data simplified:

[quoted text clipped - 30 lines]

 

[Brian]

Here is a quick piece of sample code that loops through recipients, then
builds a message for each one, sending the e-mail one at a time. I didn't
test this, though, so you may have to work on it a bit to make sure it works.
You may need something like ClickYes to automatically click Yes when Outlook
security pops up its "Another program is attempting to send a message" as
each message is being sent.

Dim rsRecipients As DAO.Recordset
Dim rsParts As DAO.Recordset
Dim Recipient As String
Dim Subject As String
Dim MailString As String
Subject = "Parts & quantities"

Set rsRecipients =
CurrentDb.OpenRecordset("<NameOfQueryOrTableThatReturnsOneRecordPerRecipient>", dbOpenSnapshot)
rsRecipients.MoveFirst
Do While Not rsRecipients.EOF
Recipient = rsRecipients.Fields("EMail").Value
Set rsParts =
CurrentDb.OpenRecordset("<NameOfQueryOrTableThatIncludesPartsAndQuantitiesForTheCurrentRecipient>", dbOpenSnapshot)
rsParts.MoveFirst
MailString = "" 'give it a starting value for this recipient
Do While Not rsParts.EOF
'get current part
MailString = MailString & rsParts.Fields("Part").Value
MailString = MailString & ": "
MailString = MailString & rsParts.Fields("Quantity").Value
MailString = MailString & Chr(13) 'line feed
rsParts.MoveNext
Loop ' process next part/quantity record
rsParts.Close
'send the message
DoCmd.SendObject acSendNoObject, , , Recipient, , , Subject,
MailString, False, False
rsRecipients.MoveNext
Loop
rsRecipients.Close

Thanks Brian - I will give it a shot. I know it would work better if I could
do it in a VBA loop but I don't have an expertise in that area.

Then make it a crosstab query. I made a sample with three tables:

Users - 2 fields
UserName
EMail
Parts - 1 field
Part
Quantites - 3 fields
EMail (relationship to Users.EMail)
Part (relationship to Parts.Part)

Then I made this crosstab query:

RANSFORM Sum(Quantities.Quantity) AS SumOfQuantity
SELECT Users.EMail
FROM Users INNER JOIN Quantities ON Users.UserName = Quantities.UserName
GROUP BY Users.EMail
PIVOT Quantities.Part;

The query uses the Users & Quantities tables (joined on UserName) with these
settings in the query:
Field: EMail
Table: Users
Total: Group By
Crosstab: Row Heading

Field: Part
Table: Quantities
Total: Group By
Crosstab: Column Heading

Field: Quantity
Table: Quantities
Total: Sum
Crosstab: Value

This shows one row for each EMail, one column for each Part, and the
Quantity for each.

Thanks Brian - probably didn't explain myself clearly enough. Maybe if I show
the data simplified:

[quoted text clipped - 30 lines]

one record so when I merge it into Word I can have one email per user and
include all the related information in that Word document. Any ideas?