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

x
?
Solved

Email to Multiple Recipients is Dropping Recipient

Posted on 2011-02-19
5
Medium Priority
?
1,008 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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…
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…

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