Solved

Email to Multiple Recipients is Dropping Recipient

Posted on 2011-02-19
5
1,003 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 500 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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Progress

707 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