Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

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
?
435 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

636 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