• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1012
  • Last Modified:

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.
0
wwdnet
Asked:
wwdnet
1 Solution
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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
 
Exl04Commented:
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
 
RemRemRemCommented:
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
 
wwdnetAuthor Commented:
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
 
wwdnetAuthor Commented:
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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now