[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Duplicate emails when using VBA and Macro to Email from a list in a table in Access 2007

Posted on 2010-09-13
14
Medium Priority
?
438 Views
Last Modified: 2013-11-27
Hi,
I have set up an Access 2007 database using the information I gathered from the link below to automatically email a list of users that are in a table called FC Doc Delays table.  (please see attached database)

http://www.jephens.com/2007/05/13/how-to-send-e-mail-from-ms-access-using-outlook/

The problem is, the module uses the MyEmailAddresses query to send the emails, but the MyEmailAddresses query contains duplicate emails, and sends the same email multiple times to the same email addresses.  

Is there a way that I can remove the duplicate email addresses from the MyEmailAddresses query before sending?
FC-Doc-Delays.accdb
0
Comment
Question by:prodempsey
14 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 33662601
prodempsey,Please upload an MDB version of your file, as my main computer does not have Access 2007 or 2010.In any event, the short answer is, modify your query so that it does not contain duplicate email addresses :)Patrick
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33662606

<Is there a way that I can remove the duplicate email addresses from the MyEmailAddresses query before sending?>

post the SQL statement of the query
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33662638
when querying FC Doc Delays for the email addresses use select distinct. somthing like:
cmd.text = "Select distinct [FC Doc Delays].emails from [FC Doc Delays]
set rst = cmd.execute
 dim myarray() as string
redim myarray(rst.count - 1)
for i = 0 to rst.count
myarray(i) = rst(i).value
rst.movenext
next
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33662659
that should be rst(1).value
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33662668
oops, rst(0).value
0
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 33662750
prodempsey,

The query "MyEmailAddresses" you mentioned is not found in the database you attached.


Ed
0
 
LVL 6

Author Comment

by:prodempsey
ID: 33663419
Thank you everyone for your quick responses.  I'll try to respond to everyone in one post to avoid confusion.
sorry matthewspatrick, I can't save the database as an earlier database format as I'm linking to external data on a sharepoint, and it's not supported in MDB.


capricorn1, attached is the SQL to the MyEmail Addresses query.
DSUPERB, sorry about that, the MyEmailAddresses query was hidden.  I've reattached the database with the unhidden query.
 MWGainesJR, Can you show me how I would insert the "Select Distinct" SQL into the query?  I'm not very good with SQL.
Thanks,
Jeremy





SELECT [LPS Processor Table].Email
FROM [Fc Doc Delays Table] INNER JOIN [LPS Processor Table] ON [Fc Doc Delays Table].FcProcessorId = [LPS Processor Table].[Fc Processor ID]
WHERE ((([Fc Doc Delays Table].[Date Added])=Date())) OR ((([Fc Doc Delays Table].Completed)=0));

Open in new window

FC-Doc-Delays.accdb
0
 
LVL 13

Accepted Solution

by:
MWGainesJR earned 2000 total points
ID: 33663436

SELECT distinct [LPS Processor Table].Email  
FROM [Fc Doc Delays Table] INNER JOIN [LPS Processor Table] ON [Fc Doc Delays Table].FcProcessorId = [LPS Processor Table].[Fc Processor ID]  
WHERE ((([Fc Doc Delays Table].[Date Added])=Date())) OR ((([Fc Doc 

Open in new window

0
 
LVL 6

Author Comment

by:prodempsey
ID: 33663465
oops, MWGainesJR, that's VB code not SQL.  Can you show me where you would insert it into the code below?

Option Compare Database
Option Explicit
' You need to declare a reference to the Outlook library, and the filesystemobject.
' this is not as hard as it sounds.
'
' Look in the menu above, and click Tools, then select References
'
' Scroll down the list until you see
' Microsoft Scripting Runtime -- and put a check next to it (if one is not there already)
'
' Microsoft Outlook Object Library -- check that.
'           There will be some version number there as well; it doesn't matter.
'           This will work with Outlook98 and Outlook2000 and OutlookXP.  It hasn't been tested on Outlook 2003 yet.


Public Function SendEMail()

Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyBodyText As String

Set fso = New FileSystemObject

 ' First, we need to know the subject.
 ' We can't very well be sending around blank messages...

Subjectline$ = InputBox$("Please enter the subject line for this mailing.", _
                 "We Need A Subject Line!")

 ' If there's no subject, call it a day.

If Subjectline$ = "" Then
    MsgBox "No subject line, no message." & vbNewLine & vbNewLine & _
        "Quitting...", vbCritical, "E-Mail Merger"
    Exit Function
End If
    
 ' Now we need to put something in our letter...
    
MyBodyText = "You have new items today on the FNMA Doc Delay list from the attorneys, or you have existing items that have not been completed yet.  Please go to the SharePoint link located here:  http://sharepoint/sites/MYD_18434/Lists/Fc%20Doc%20Delays%20Table/Open%20Items%20Only.aspx to clear up these exceptions as they will be reported to FNMA very shortly by the attorney.  If any of these exceptions cannot be cleared within the next 72 hours, please mark the item in question as complete, and comment why it cannot be cleared in 72 hours.  Otherwise, take the necessary steps to clear the exception, mark it as complete, and comment what steps were taken to clear the exception."
 

   ' Now, we open Outlook for our own device..
    Set MyOutlook = New Outlook.Application


 ' Set up the database and query connections

    Set db = CurrentDb()

    Set MailList = db.OpenRecordset("MyEmailAddresses")

 ' now, this is the meat and potatoes.
 ' this is where we loop through our list of addresses,
 ' adding them to e-mails and sending them.

Dim MyRecip As Outlook.Recipient

    Do Until MailList.EOF

        ' This creates the e-mail
        
        Set MyMail = MyOutlook.CreateItem(olMailItem)
            
            ' This addresses it
            'MyMail.To = MailList("email")
            Set MyRecip = MyMail.Recipients.Add(MailList("email"))
            MyRecip.Type = olBCC
            
            'This gives it a subject
            MyMail.Subject = Subjectline$
            
            'This gives it the body
            MyMail.Body = MyBodyText


           
          'If you want to send an attachment
'uncomment the following line
 
'MyMail.Attachments.Add "c:myfile.txt", olByValue, 1, "My Displayname"
 
' To briefly describe:
' "c:myfile.txt" = the file you want to attach
'
' olByVaue = how to pass the file. olByValue attaches it, olByReference creates a shortcut.
' the shortcut only works if the file is available locally (via mapped or local drive)
'
' 1 = the position in the outlook message where to attachment goes. This is ignored by most
' other mailers, so you might want to ignore it too. Using 1 puts the attachment
' first in line.
'
' "My Displayname" = If you don??t want the attachment??s icon string to be "c:myfile.txt" you
' can use this property to change it to something useful, i.e. "4th Qtr Report"
 
'This sends it!
 
MyMail.Send
 
'Some people have asked how to see the e-mail
'instead of automaticially sending it.
'Uncomment the next line
'And comment the "MyMail.Send" line above this.
 
'MyMail.Display
 
'And on to the next one...
MailList.MoveNext

Loop

 'Cleanup after ourselves

Set MyMail = Nothing


'Uncomment the next line if you want Outlook to shut down when its done.
'Otherwise, it will stay running.

'MyOutlook.Quit
Set MyOutlook = Nothing

MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing

End Function

Open in new window

0
 
LVL 6

Author Comment

by:prodempsey
ID: 33663506
Is that it?  I just need to add the word "distinct" after Select in the SQL for the query?
0
 
LVL 6

Author Comment

by:prodempsey
ID: 33663519
Yup, that worked MWGainesJR!  Thanks!
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33663522
You need to add the distinct into the "MyEmailAddresses" Query.  You don't need to change any VBcode.
0
 
LVL 6

Author Closing Comment

by:prodempsey
ID: 33663529
Thanks MWGainesJR!  
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33663531
That's it! All you need.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question