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
414 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 92

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 500 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

831 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