Link to home
Start Free TrialLog in
Avatar of wwdnet
wwdnet

asked on

Email to Multiple Recipients is Dropping Recipient

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.
ASKER CERTIFIED SOLUTION
Avatar of Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Exl04
Exl04

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

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
Avatar of wwdnet

ASKER

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!
Avatar of wwdnet

ASKER

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.