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
390 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 119

Expert Comment

by:Rey Obrero
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
 
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
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

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

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…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

911 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now