Access Report to Send to Multiple Recipients

[kjhyder]

After making the suggested changes (and removing the "Stop" and "Debug.Prin t", outlook opened with the first recipient (Brent) and attached the report. Except it attached the report as a whole and not just his unique page of the report. Also it sent one email per transaction line. Example: Brent had five transactions listed on his report and outlook sent 5 emails. Then it moved to the next recipient and create 6 emails since they had 6 transactions lines on their page.

So it does not seem to be breaking the report into distinct reports based on the Pay name.

Thanks,

[zmbd]

> I notice that you have a new post under review for approval.
[Sending Unique Emails to Distinct Recipeints]

1. How does this differ from this thread?
2. You posted in the general forum when it should be in the MS Acces/VBA forum.

To the problem at hand:
Good News, The code ran without error.

Bad news, it sounds as if your data-base-schema is not properly structured, the report is malformed, and/or the records that you are using to filter on are not unique on the field that you have chosen.

I know that this process works, I have dozens of databases that use the same construct as the one I posted; however, a few of them I inherited and had to "fix" in order to run properly.

> Please make a copy of your database.
> In your copy remove all but say a dozen customer records and related data in the tables. Leave enough records to make sure that your reports and queries run properly and will provide enough records for grouping etc...

> If your data has sensitive information then make it generic

For example a social security number should be changed to 999-00-#### - these are safe to use (SSN.GOV) as they will never be assigned.
Similarly Telephone numbers should be changed to 555-555-#### the 555 area code is designated as a "dead" code, streets etc... all should be "cleaned - credit-card information first number is fine as is change all but the last four to zeros and (i.e. 5000-0000-0000-#### is a generic visa/master card using this format the number will not pass the checksum test) and so forth with names etc... in my attached database you will have a tbl_people, use those names look at the emails, those are all temp emails use the same @mailinator.com
> Any company logos, addresses, etc... should be changed to "Northwind Traders" use one of the built-in symbols for the logo and use 123 AnyStreet, AnyTown, 00000.
>zip-codes are basically generic enough not to warrant changing; however, if you want you can make them 000##

It is important to keep the same type and format of data as you are currently using.
Do not change " Brent Kendrick " to "123 456" or "K B" but using something like changing all of the " Brent Kendrick" to "Kilo Peter" will work.

Once you have this done - zip the cleaned database, click on the [Advanced] button at the next to hte [Post Reply] button.
Using the advanced editor you will find an [Upload Files] button, click, in the popup select one of the [chose File] buttons, find your zipped file and select, once the file name shows in the text box, click on the [Upload] button.

Once we have the reduced version of your database we can take a closer look at your data structure to determine what is going on.

If there is an issue doing this please let us know and we can find another workaround to get me the database for review. I still would not want to have any personal customer or company data.

[ADezii]

I had a chance to revisit this Thread and play around with the Code, taking a different approach. I made a couple of Major as well as Minor changes, and I feel that the Uploaded Attachment is very close to what you are looking for. In it's current state, it will send four EMails (with a *.pdf Attachment) to four specific individuals. Each individual will have their, and only their, transactions listed in the Attached Report. I could only test the Logic so far, the rest I will leave to you.
NOTE: Please be advised that I only build upon/revised what zmbd has already shown you. Should this actually work (???), the credit is all his and not mine. I hope you didn't mind zmdb, that I used your existing Structure and Code to build on. I felt it would be more intuitive to attach a Demo rather than posting the Code.

[zmbd]

@ADezii I hope you didn't mind zmdb, that I used your existing Structure and Code to build on.

Old friend, you are always welcome to use whatever I've posted, indeed, I built upon code that NeoPa taught me.
Thank you for taking time to help kjhyder

[kjhyder]

Database

Thank you again for your assistance. I have uploaded the reduced database below.

[zmbd]

Hello kjhyder ,

So this is the database you're talking about in your other thread...
Sending Unique Emails to Distinct Recipeints

1) Run-time error '3265': Item not found in this collection.
Definitely indicates that ![Path] is not part of the recordset rstEMail - however, in the case revised database that will not matter...

2) IF this is your full table schema then Your database is not normalized - if you do not know what that means, that's ok, in simple terms one is using the database like a flat-workbook instead of taking advantage of the relational-database-design which usually (not always) will make things like you're trying to do a lot easier.

In any case, I've revised your stripped-down database code... works just fine on my PC, give it whirl as is on yours - see what you think.
One small thing say you have some user "John Doe" and Mr. Doe has two different emails in table YTD - then Mr. Doe will receive two emails, possibly with the same report.

If you would like see how I would normalize this database, please start a new thread. I'll be happy to attach a normalized version of the Database6_revis ed.accdb

[kjhyder]

Thank you ZMBD. It works great. Just what I was looking for. With a few cosmetic tweaks the powers that be will love it.

Once I am confident my process works, would there be a change in the code to have outlook send the emails automatically instead of me having to click send on each email that it brings up?

[zmbd]

Small tweak
Comment out, or remove, the .Display
I prefer commenting it out just in-case one needs to troubleshoot.
> and if you haven't done so already, pull that Stop out of the code - it's only there for troubleshooting 🎃

' send the file
          With oMail
             .To = Left$(strEMail, Len(strEMail) - 1)
             .Body = "Please find attached YTD transactions"
             .Subject = MyFileName & " YTD Transactions"
             .Attachments.Add attach
[iCODE]  '            .Display[/iCODE]
          End With

[kjhyder]

The procedure has been working great without any issues. But now our external email provider has put a cap of 1,000 outgoing emails in a 24 hour period. This was not an issue until we have grown and now our agents exceed 1,000. They have set up a noreply@ email which I have access to send from that has a 10,000 email limit. Is there a way in the code to open the outlook emails in the noreply@ email instead of my khyder@ email so I do not run into the limit?