?
Solved

Email to Multiple Recipients is Dropping Recipient

Posted on 2011-02-19
5
Medium Priority
?
1,005 Views
Last Modified: 2013-11-27
I am using the following code to send an Access report to multiple recipients:
Dim rst As DAO.Recordset

Dim sEmails As String

Set rst = CurrentDb.OpenRecordset("SELECT [EMAILADR] FROM [EMAILTEMP]")

Do Until rst.EOF = True

  sEmails = rst("EMAILADR") & ";" & sEmails

  sEmails = Left(sEmails, Len(sEmails) - 1)

  rst.MoveNext

Loop

DoCmd.SendObject acSendReport, "rptDOCNEW", acFormatPDF, sEmails, , , "TEST EMAIL - DISCARD", "A new document is awaiting your approval. Please review as soon as possible.", False

*******************************
Of the 3 listed entries, only 2 were emailed. I have tried this without the (= True) after rst.EOF and a couple of other ways, but the result is the same.

I am querying the email addresses into a temporary table prior to running the code, and all recipients are added to the temporary table.

Any help would be greatly appreciated. This database is going into production within a week and I need to get the code working to use in several other forms.
0
Comment
Question by:wwdnet
[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
5 Comments
 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 2000 total points
ID: 34935464
Try this:

sEmails = ""

Do Until rst.EOF = True

  If Len(sEmails) < 1 Then  
     sEmails = rst("EMAILADR")
  Else
     sEmails = sEmails & "; " & rst("EMAILADR") 
  End IF
 
  rst.MoveNext

Loop

Open in new window

0
 
LVL 1

Expert Comment

by:Exl04
ID: 34935496
Why don’t create the reports and just attach them to your e-mail,…also why not  just create a distribution list (if several recipients) in Outlook, so you don’t have to be managing this list in you database if it changes constantly (let the owner of this report manage this .DL) and hard code the .DL in your code like this sample;
Dim olApp1 As Object
        Dim olMsg1 As Object

        Set olApp1 = CreateObject("Outlook.Application")
        Set olMsg1 = olApp1.CreateItem(0)
        With olMsg1
            .To = ".DL this is your just created distribution list"
            .CC = ""
            .BCC = ""
            .Subject = "Your Report subject"
            .Body = "Description of your Body"
            .Attachments.Add "any attachmnets location you want to include"
            .Attachments.Add "may be 2, or 3"
            .Send
        End With

        Set olMsg1 = Nothing
        Set olApp1 = Nothing

Open in new window

0
 
LVL 7

Expert Comment

by:RemRemRem
ID: 34935544
Dim rst As DAO.Recordset

Dim sEmails As String
DIM X AS INTEGER, Y AS INTEGER

Set rst = CurrentDb.OpenRecordset("SELECT [EMAILADR] FROM [EMAILTEMP]")

RST.MOVELAST

X=RST.RECORDCOUNT

RST.MOVEFIRST

FOR Y=1 to X

  sEmails = rst("EMAILADR") & ";" & sEmails

  sEmails = Left(sEmails, Len(sEmails) - 1)

  rst.MoveNext

NEXT Y
0
 

Author Closing Comment

by:wwdnet
ID: 34935616
I am breathing a sigh of relief! Thank you, and also RemRem. It took a while to test this because there were some other issues in the module. I chose the first solution as the simplest option for a complex process in which some of the list members will be notified one day, and others added later requiring notification of the new members. There are a number of related forms that depend on the notification and related responses, and each will need what will basically be the same code with minor changes.

I got this to work for all the non-notified members after commenting out subsequent code that appeared to be running ahead of the loop. I can work out the rest of the code in the morning!
0
 

Author Comment

by:wwdnet
ID: 34935619
Exl04: I did not intentionally leave you out of the thank you. I will try the other suggested codes and see if they will work for this project.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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 …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

770 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