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
378 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
Comment Utility
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
Comment Utility

<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
Comment Utility
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
Comment Utility
that should be rst(1).value
0
 
LVL 13

Expert Comment

by:MWGainesJR
Comment Utility
oops, rst(0).value
0
 
LVL 19

Expert Comment

by:MINDSUPERB
Comment Utility
prodempsey,

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


Ed
0
 
LVL 6

Author Comment

by:prodempsey
Comment Utility
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 13

Accepted Solution

by:
MWGainesJR earned 500 total points
Comment Utility

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
Comment Utility
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
Comment Utility
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
Comment Utility
Yup, that worked MWGainesJR!  Thanks!
0
 
LVL 13

Expert Comment

by:MWGainesJR
Comment Utility
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
Comment Utility
Thanks MWGainesJR!  
0
 
LVL 13

Expert Comment

by:MWGainesJR
Comment Utility
That's it! All you need.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

743 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

18 Experts available now in Live!

Get 1:1 Help Now