Stop Outlook from sending multiple copies of same message

[Guest]

Multiple copies of Outlook message is sent to recipients. The recipients are
on an Access list that is called from a VBA module. The list contains 145
recipients. The VBA code works without sending multiple copies on other
lists. Suggestions?

 

[David C. Holley]

By any chance are you using a query to select the recipients to receive
the EMAIL? If you are, open the query and confirm that the query is
selecting UNIQUE values. Otherwise, please post the code and explain
what you mean by 'ACCESS LIST'. Are you storing the email messages in
Access and loading up the TO field from there? Are you storing CONTACT
information, then loading up the TO field letting Outlook fill in the
email addresses? (Hint: If your doing the later, double check that you
don't have a distribution list in the table).

 

[Guest]

The email addresses are contained in an Access table of unique values. Here
is the SQL view of the query that accesses the table of addresses that "get
all data" and the code from the module that builds the bcc list in Outlook:

SELECT tblReceiveAllData.EmailAddress
FROM tblReceiveAllData
ORDER BY tblReceiveAllData.LastName;

Public Sub ReceiveAllData()

Dim db As DAO.Database, rs As Recordset
Dim strSQL, varBCC As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("Select * From qryReceiveAllData",
dbOpenDynaset)

'Check to see if recordset is empty
If rs.RecordCount < 0 Then
MsgBox ("Gotta problem - no records")
End If

rs.MoveLast
rs.MoveFirst
varBCC = ""
Do While Not rs.EOF
varBCC = varBCC & rs![EmailAddress] & ";"
rs.MoveNext
Loop
Call SendEmail(varBCC)
rs.Close
Set rs = Nothing
Set db = Nothing


End Sub

 

[David C. Holley]

Can you also send the code for sendMail?

The code seems fine from what you've posted. You may want to add
Debug.Print varBCC right before you call sendMail to snoop wether or not
duplicates exist there, although I don't believe that they will. I take
that you've run the SQL statement as a QUERY to inspect the records?

FYI typically when you're looping through a recordSet you can use the
following code to test if records exist and then start the loop. This
eliminates the need to .MoveLast, .MoveFirst and allows you to open the
RS as dbOpenForwardOnly which can be a bit faster.

If rs.EOF then 'Check if we're at End Of File
Msgbox("no records returned")
else
While NOT rs.EOF
[Code Here]
wend
end if

The email addresses are contained in an Access table of unique values. Here
is the SQL view of the query that accesses the table of addresses that "get
all data" and the code from the module that builds the bcc list in Outlook:

SELECT tblReceiveAllData.EmailAddress
FROM tblReceiveAllData
ORDER BY tblReceiveAllData.LastName;

Public Sub ReceiveAllData()

Dim db As DAO.Database, rs As Recordset
Dim strSQL, varBCC As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("Select * From qryReceiveAllData",
dbOpenDynaset)

'Check to see if recordset is empty
If rs.RecordCount < 0 Then
MsgBox ("Gotta problem - no records")
End If

rs.MoveLast
rs.MoveFirst
varBCC = ""
Do While Not rs.EOF
varBCC = varBCC & rs![EmailAddress] & ";"
rs.MoveNext
Loop
Call SendEmail(varBCC)
rs.Close
Set rs = Nothing
Set db = Nothing


End Sub


:

By any chance are you using a query to select the recipients to receive
the EMAIL? If you are, open the query and confirm that the query is
selecting UNIQUE values. Otherwise, please post the code and explain
what you mean by 'ACCESS LIST'. Are you storing the email messages in
Access and loading up the TO field from there? Are you storing CONTACT
information, then loading up the TO field letting Outlook fill in the
email addresses? (Hint: If your doing the later, double check that you
don't have a distribution list in the table).

 

[Guest]

I've looked at the address list in the Outlook BCC window before sending the
message and there are no duplicates there. Here's the sendMail code:

Public Sub SendEmail(varOut As String)
Dim mli As Outlook.MailItem, strMsg As String

InitOutlook
Set mli = ola.CreateItem(olMailItem)

mli.Subject = "Message to Church Member/Friend"
mli.Body = strMsg
mli.BCC = varOut & ""
mli.Display
Set mli = Nothing
CleanUp
End Sub

Can you also send the code for sendMail?

The code seems fine from what you've posted. You may want to add
Debug.Print varBCC right before you call sendMail to snoop wether or not
duplicates exist there, although I don't believe that they will. I take
that you've run the SQL statement as a QUERY to inspect the records?

FYI typically when you're looping through a recordSet you can use the
following code to test if records exist and then start the loop. This
eliminates the need to .MoveLast, .MoveFirst and allows you to open the
RS as dbOpenForwardOnly which can be a bit faster.

If rs.EOF then 'Check if we're at End Of File
Msgbox("no records returned")
else
While NOT rs.EOF
[Code Here]
wend
end if

The email addresses are contained in an Access table of unique values. Here
is the SQL view of the query that accesses the table of addresses that "get
all data" and the code from the module that builds the bcc list in Outlook:

SELECT tblReceiveAllData.EmailAddress
FROM tblReceiveAllData
ORDER BY tblReceiveAllData.LastName;

Public Sub ReceiveAllData()

Dim db As DAO.Database, rs As Recordset
Dim strSQL, varBCC As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("Select * From qryReceiveAllData",
dbOpenDynaset)

'Check to see if recordset is empty
If rs.RecordCount < 0 Then
MsgBox ("Gotta problem - no records")
End If

rs.MoveLast
rs.MoveFirst
varBCC = ""
Do While Not rs.EOF
varBCC = varBCC & rs![EmailAddress] & ";"
rs.MoveNext
Loop
Call SendEmail(varBCC)
rs.Close
Set rs = Nothing
Set db = Nothing


End Sub


:

By any chance are you using a query to select the recipients to receive
the EMAIL? If you are, open the query and confirm that the query is
selecting UNIQUE values. Otherwise, please post the code and explain
what you mean by 'ACCESS LIST'. Are you storing the email messages in
Access and loading up the TO field from there? Are you storing CONTACT
information, then loading up the TO field letting Outlook fill in the
email addresses? (Hint: If your doing the later, double check that you
don't have a distribution list in the table).

lcJim wrote:

Multiple copies of Outlook message is sent to recipients. The recipients are
on an Access list that is called from a VBA module. The list contains 145
recipients. The VBA code works without sending multiple copies on other
lists. Suggestions?

 

[David C. Holley]

Based on that statement and the code provided, my thinking is that you
might have persons that have multiple email addresses. That is the only
way that they can be receiving duplicates. You've confirmed that things
are fine on your side based on the following...

The table indicated in the original message is the only one that seems
to have the problem.
You are only looping through the recordset object once.
You have visually confirmed that there aren't any duplicate addresses in
the MailItem.
You have confirmed that there are no duplicate records produces by the
SQL statement by running it as a standalone query.

I would advise your receipients that if they have multiple email
addresses to send you ALL of them and to indicate which one is their
preferred email.

I've looked at the address list in the Outlook BCC window before sending the
message and there are no duplicates there. Here's the sendMail code:

Public Sub SendEmail(varOut As String)
Dim mli As Outlook.MailItem, strMsg As String

InitOutlook
Set mli = ola.CreateItem(olMailItem)

mli.Subject = "Message to Church Member/Friend"
mli.Body = strMsg
mli.BCC = varOut & ""
mli.Display
Set mli = Nothing
CleanUp
End Sub

:

Can you also send the code for sendMail?

The code seems fine from what you've posted. You may want to add
Debug.Print varBCC right before you call sendMail to snoop wether or not
duplicates exist there, although I don't believe that they will. I take
that you've run the SQL statement as a QUERY to inspect the records?

FYI typically when you're looping through a recordSet you can use the
following code to test if records exist and then start the loop. This
eliminates the need to .MoveLast, .MoveFirst and allows you to open the
RS as dbOpenForwardOnly which can be a bit faster.

If rs.EOF then 'Check if we're at End Of File
Msgbox("no records returned")
else
While NOT rs.EOF
[Code Here]
wend
end if

The email addresses are contained in an Access table of unique values. Here
is the SQL view of the query that accesses the table of addresses that "get
all data" and the code from the module that builds the bcc list in Outlook:

SELECT tblReceiveAllData.EmailAddress
FROM tblReceiveAllData
ORDER BY tblReceiveAllData.LastName;

Public Sub ReceiveAllData()

Dim db As DAO.Database, rs As Recordset
Dim strSQL, varBCC As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("Select * From qryReceiveAllData",
dbOpenDynaset)

'Check to see if recordset is empty
If rs.RecordCount < 0 Then
MsgBox ("Gotta problem - no records")
End If

rs.MoveLast
rs.MoveFirst
varBCC = ""
Do While Not rs.EOF
varBCC = varBCC & rs![EmailAddress] & ";"
rs.MoveNext
Loop
Call SendEmail(varBCC)
rs.Close
Set rs = Nothing
Set db = Nothing


End Sub


:



By any chance are you using a query to select the recipients to receive
the EMAIL? If you are, open the query and confirm that the query is
selecting UNIQUE values. Otherwise, please post the code and explain
what you mean by 'ACCESS LIST'. Are you storing the email messages in
Access and loading up the TO field from there? Are you storing CONTACT
information, then loading up the TO field letting Outlook fill in the
email addresses? (Hint: If your doing the later, double check that you
don't have a distribution list in the table).

lcJim wrote:


Multiple copies of Outlook message is sent to recipients. The recipients are
on an Access list that is called from a VBA module. The list contains 145
recipients. The VBA code works without sending multiple copies on other
lists. Suggestions?

 

[Guest]

Thanx for independent confirmation that the problem does not appear to be in
the code. I'll check on the recipients addresses for "hidden" duplications.
Thanx again.

Based on that statement and the code provided, my thinking is that you
might have persons that have multiple email addresses. That is the only
way that they can be receiving duplicates. You've confirmed that things
are fine on your side based on the following...

The table indicated in the original message is the only one that seems
to have the problem.
You are only looping through the recordset object once.
You have visually confirmed that there aren't any duplicate addresses in
the MailItem.
You have confirmed that there are no duplicate records produces by the
SQL statement by running it as a standalone query.

I would advise your receipients that if they have multiple email
addresses to send you ALL of them and to indicate which one is their
preferred email.

I've looked at the address list in the Outlook BCC window before sending the
message and there are no duplicates there. Here's the sendMail code:

Public Sub SendEmail(varOut As String)
Dim mli As Outlook.MailItem, strMsg As String

InitOutlook
Set mli = ola.CreateItem(olMailItem)

mli.Subject = "Message to Church Member/Friend"
mli.Body = strMsg
mli.BCC = varOut & ""
mli.Display
Set mli = Nothing
CleanUp
End Sub

:

Can you also send the code for sendMail?

The code seems fine from what you've posted. You may want to add
Debug.Print varBCC right before you call sendMail to snoop wether or not
duplicates exist there, although I don't believe that they will. I take
that you've run the SQL statement as a QUERY to inspect the records?

FYI typically when you're looping through a recordSet you can use the
following code to test if records exist and then start the loop. This
eliminates the need to .MoveLast, .MoveFirst and allows you to open the
RS as dbOpenForwardOnly which can be a bit faster.

If rs.EOF then 'Check if we're at End Of File
Msgbox("no records returned")
else
While NOT rs.EOF
[Code Here]
wend
end if

lcJim wrote:

The email addresses are contained in an Access table of unique values. Here
is the SQL view of the query that accesses the table of addresses that "get
all data" and the code from the module that builds the bcc list in Outlook:

SELECT tblReceiveAllData.EmailAddress
FROM tblReceiveAllData
ORDER BY tblReceiveAllData.LastName;

Public Sub ReceiveAllData()

Dim db As DAO.Database, rs As Recordset
Dim strSQL, varBCC As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("Select * From qryReceiveAllData",
dbOpenDynaset)

'Check to see if recordset is empty
If rs.RecordCount < 0 Then
MsgBox ("Gotta problem - no records")
End If

rs.MoveLast
rs.MoveFirst
varBCC = ""
Do While Not rs.EOF
varBCC = varBCC & rs![EmailAddress] & ";"
rs.MoveNext
Loop
Call SendEmail(varBCC)
rs.Close
Set rs = Nothing
Set db = Nothing


End Sub


:



By any chance are you using a query to select the recipients to receive
the EMAIL? If you are, open the query and confirm that the query is
selecting UNIQUE values. Otherwise, please post the code and explain
what you mean by 'ACCESS LIST'. Are you storing the email messages in
Access and loading up the TO field from there? Are you storing CONTACT
information, then loading up the TO field letting Outlook fill in the
email addresses? (Hint: If your doing the later, double check that you
don't have a distribution list in the table).

lcJim wrote:


Multiple copies of Outlook message is sent to recipients. The recipients are
on an Access list that is called from a VBA module. The list contains 145
recipients. The VBA code works without sending multiple copies on other
lists. Suggestions?

 

[David C. Holley]

Users can't live with 'em, can't kill 'em.

Thanx for independent confirmation that the problem does not appear to be in
the code. I'll check on the recipients addresses for "hidden" duplications.
Thanx again.


:

Based on that statement and the code provided, my thinking is that you
might have persons that have multiple email addresses. That is the only
way that they can be receiving duplicates. You've confirmed that things
are fine on your side based on the following...

The table indicated in the original message is the only one that seems
to have the problem.
You are only looping through the recordset object once.
You have visually confirmed that there aren't any duplicate addresses in
the MailItem.
You have confirmed that there are no duplicate records produces by the
SQL statement by running it as a standalone query.

I would advise your receipients that if they have multiple email
addresses to send you ALL of them and to indicate which one is their
preferred email.

I've looked at the address list in the Outlook BCC window before sending the
message and there are no duplicates there. Here's the sendMail code:

Public Sub SendEmail(varOut As String)
Dim mli As Outlook.MailItem, strMsg As String

InitOutlook
Set mli = ola.CreateItem(olMailItem)

mli.Subject = "Message to Church Member/Friend"
mli.Body = strMsg
mli.BCC = varOut & ""
mli.Display
Set mli = Nothing
CleanUp
End Sub

:



Can you also send the code for sendMail?

The code seems fine from what you've posted. You may want to add
Debug.Print varBCC right before you call sendMail to snoop wether or not
duplicates exist there, although I don't believe that they will. I take
that you've run the SQL statement as a QUERY to inspect the records?

FYI typically when you're looping through a recordSet you can use the
following code to test if records exist and then start the loop. This
eliminates the need to .MoveLast, .MoveFirst and allows you to open the
RS as dbOpenForwardOnly which can be a bit faster.

If rs.EOF then 'Check if we're at End Of File
Msgbox("no records returned")
else
While NOT rs.EOF
[Code Here]
wend
end if

lcJim wrote:


The email addresses are contained in an Access table of unique values. Here
is the SQL view of the query that accesses the table of addresses that "get
all data" and the code from the module that builds the bcc list in Outlook:

SELECT tblReceiveAllData.EmailAddress

FROM tblReceiveAllData

ORDER BY tblReceiveAllData.LastName;

Public Sub ReceiveAllData()

Dim db As DAO.Database, rs As Recordset
Dim strSQL, varBCC As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("Select * From qryReceiveAllData",
dbOpenDynaset)

'Check to see if recordset is empty
If rs.RecordCount < 0 Then
MsgBox ("Gotta problem - no records")
End If

rs.MoveLast
rs.MoveFirst
varBCC = ""
Do While Not rs.EOF
varBCC = varBCC & rs![EmailAddress] & ";"
rs.MoveNext
Loop
Call SendEmail(varBCC)
rs.Close
Set rs = Nothing
Set db = Nothing


End Sub


:




By any chance are you using a query to select the recipients to receive
the EMAIL? If you are, open the query and confirm that the query is
selecting UNIQUE values. Otherwise, please post the code and explain
what you mean by 'ACCESS LIST'. Are you storing the email messages in
Access and loading up the TO field from there? Are you storing CONTACT
information, then loading up the TO field letting Outlook fill in the
email addresses? (Hint: If your doing the later, double check that you
don't have a distribution list in the table).

lcJim wrote:



Multiple copies of Outlook message is sent to recipients. The recipients are
on an Access list that is called from a VBA module. The list contains 145
recipients. The VBA code works without sending multiple copies on other
lists. Suggestions?