473,289 Members | 1,917 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

Access Report to Send to Multiple Recipients

15 Byte
I have a report in Access that is grouped by customer with a page break after each customer so only their data is shown on the page. I would like to email the report to each customer with their individual data(page). I can get Access to email to each customer, but it emails the entire report and does not email only the specific customer. I attempted to create a Module to handle this and while it does email, it sends an email for each line of data even if it is the same customer and only sends the email text and not the details from the report. Below is the code:
Expand|Select|Wrap|Line Numbers
  1. Public Sub SendSerialEmail()
  2.  
  3.  Dim db As DAO.Database
  4.  Dim rs As DAO.Recordset
  5.  
  6.  Dim emailTo As String
  7.  Dim emailSubject As String
  8.  Dim emailText As String
  9.  
  10.  Dim outApp As Outlook.Application
  11.  Dim outMail As Outlook.MailItem
  12.  Dim outlookStarted As Boolean
  13.  
  14.  On Error Resume Next
  15.  Set outApp = GetObject(, "Outlook.Application")
  16.  On Error GoTo 0
  17.  If outApp Is Nothing Then
  18.      Set outApp = CreateObject("Outlook.Application")
  19.      outlookStarted = True
  20.  End If
  21.  Set db = CurrentDb
  22.  Set rs = db.OpenRecordset("SELECT Pay, Bill, Datepaid, Totalamount, Email FROM Query1")
  23.  Do Until rs.EOF
  24.      emailTo = rs.Fields("Email").Value
  25.  
  26.      emailSubject = "YTD Transactions"
  27.  
  28.      emailText = emailText & _
  29.                  "Below is your year to date transactions."
  30.  
  31.  
  32.      Set outMail = outApp.CreateItem(olMailItem)
  33.      outMail.To = emailTo
  34.      outMail.Subject = emailSubject
  35.      outMail.Body = emailText
  36.      outMail.Send
  37.  
  38.      rs.MoveNext
  39.  Loop
  40.  
  41.  rs.Close
  42.  Set rs = Nothing
  43.  Set db = Nothing
  44.  
  45.    If outlookStarted Then
  46.      outApp.Quit
  47.  End If
  48.  
  49.  Set outMail = Nothing
  50.  Set outApp = Nothing
  51. End Sub
I have a table, query and report with Pay (customer), Bill (invoice number), Datepaid, Totalamount, Email columns.

Any help would be greatly appreciated.

Thanks
May 13 '22 #1

✓ answered by 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_revised.accdb

23 28353
zmbd
5,501 Expert Mod 4TB
Hello kjhyde
Welcome to Bytes.
I've moved your thread to the Access forum as your question applies more directly to Access and VBA instead of VB6

You have the basics down and I think you will find the following information to also be helpfull:
Application Automation.

Now for your particular situation:
Line22 has your recordset - impossible to tell what it is actually returning to your code; however, from your post I have a fairly good idea that it's just a raw record dump of all your customers.
You will need an outer loop with your customer base and then a filtered query to run the individualized emails.

There is a thread where I've answered a very similar question with the attached database
Open the form in design view
Show the properties for the [Send the Emails] button
Show the code for the [On Click] event of the button
Scroll down to the Private Sub z_send_single_email_to_each_person_with_all_querie s() script which will walk you thru one method of accomplishing what you are after.
+ What I do in this code is create an Excel workbook from the query and then add records to the workbook for the person of interest in the loop
+ The workbook is then attached to an email and then sent.
++ This is only one method - please don't focus too much on the workbook aspect.
++ Instead, you could pull the information form the query and build a string that is then inserted into the body of of the email
++ I have code somewhere for a dynamic report that's emailed too... I'll have to find that database, it's been a long time since I've used it as most of my staff simply view the reports online
Attached Files
File Type: zip Bytes_944237.zip (105.8 KB, 75 views)
May 14 '22 #2
kjhyder
15 Byte
Thank you for your response. I did not make my question clear in my original post. I have a report in Access that is grouped by customer listing out each invoice, payment, and payment date over the course of the year. The report is subtotaled by customer. I have also added page breaks after each customer. If I were to print the report, I would get an individual pages showing only one customer's data on each page. I would like the ability to create a "Send Email" button on the report that would generate emails to each customer either attaching their unique page of the report or inserting their unique data into the email body. I do have a table with each customer and customer email. I would also like the process from the "Send Email" button to send to each customer automatically without me having to send to each customer individually.
May 19 '22 #3
zmbd
5,501 Expert Mod 4TB
🌟 Reports should be exactly that - a report.
In an ideal world, reports should never have "command buttons" instituting an action therefrom, actions are the province of the user-form.
Splitting your current report along the page/groupings, AFAIK, isn't possible, even within the on_page event.
Your base query should set the data that you want to have sent to the customer to receive in the report.

🏁 You need to decide if you want to send the report and/or create text within the body of the email

📝📩If you goal is to simply send the information from within the email then my example database loop is all you need to get you started with steping thru the data set.
For each customer in the outer loop, use the current customer primary key to filter/create the record set from the data table(s) based on the related foreign key
Pull from the record set to create your string for the body text. The exact code will vary based upon your data, business model, and desired output.

✉📓If however, you wish to send the report that is a slightly different ball of wax. You will need to create a core-report that shows the information with your totals etc... from there we would need to set the parameters for your query that feeds the report, use a "WhereCondition" in a DoCmd.OpenReport, (even OpenArgs is a possibility) (so many ways to do this), and then do the fancy email automation.
♻More than likely we can actually use the report that you have using the "WhereCondition" in the DoCmd.OpenReport method.
May 19 '22 #4
kjhyder
15 Byte
Thank you zmbd.

My goal is to simply send the information from within the email. So if my table consists of the following data:
Pay Bill Date Datepaid Totalamount Email
Ali 123 Street 3/18/22 3/21/22 100.00 a@abc.com
Ali 456 Street 3/14/22 200.00 a@abc.com
Brent 789 Street 3/11/22 3/13/22 100.00 b@abc.com
Brittany 123 S Street 2/21/22 2/23/22 100.00 b2@abc.com
Brittany 456 S Street 1/15/22 1/16/22 200.00 b2@abc.com
Bryan 789 S Street 2/25/22 200.00 b3@abc.com

I would like to email Ali at a@abc.com (in one email) their data of 123 Street and 456 Street (with 300.00 subtotal if possible)
email Brent at b@abc.om their data
email Brittany at b1@abc.com their data (in one email)
email Bryan at 2b@abc.com their data

And accomplish this without having to manually select each agent and send their data

Is that possible with my original code and adding your Private Sub z_send_single_email_to_each_person_with_all_querie s() code?

Thank you
May 23 '22 #5
ADezii
8,834 Expert 8TB
Forgive me for jumping in late, but the following Code, based on your Data (tblData), will send (Display) four unique EMails to your Customers. The Body of the EMail will contain the Data unique to each as well as a Total for TotalAmount. It was literally thrown together, is crude, and can definitely be improved upon. For the sake of brevity, no Error Checking, Resetting of Variables, etc. is performed.
Expand|Select|Wrap|Line Numbers
  1. Dim objOut As Object
  2. Dim objMail As Object
  3. Dim olns As Object
  4. Dim strSQL1 As String
  5. Dim strSQL2 As String
  6. Dim MyDB As DAO.Database
  7. Dim rstUnique As DAO.Recordset
  8. Dim rstData As DAO.Recordset
  9. Dim curTotal As Currency
  10. Dim strBuild As String
  11. Dim strBody As String
  12. Dim strCustomer As String
  13. Dim strTO As String
  14.  
  15. Set objOut = CreateObject("Outlook.Application")
  16. Set olns = objOut.GetNamespace("MAPI")
  17.  
  18. Set MyDB = CurrentDb
  19.  
  20. strSQL1 = "SELECT DISTINCT [Pay] FROM tblData ORDER BY [Pay]"       'Unique individuals
  21.  
  22. Set rstUnique = MyDB.OpenRecordset(strSQL1, dbOpenForwardOnly)
  23.  
  24. With rstUnique
  25.   Do While Not .EOF
  26.     Set rstData = MyDB.OpenRecordset("SELECT * FROM tblData WHERE [Pay] ='" & ![Pay] & "'", dbOpenSnapshot)
  27.       strCustomer = rstData![Pay]
  28.       strTO = rstData![email]
  29.         Do While Not rstData.EOF
  30.           Set objMail = objOut.CreateItem(0)
  31.           curTotal = curTotal + rstData![TotalAmount]
  32.           strBuild = strBuild & rstData![Bill] & ", "
  33.             rstData.MoveNext
  34.         Loop
  35.           objMail.To = strTO
  36.           objMail.Subject = "My Demo"
  37.           objMail.Body = "Data for Customer " & strCustomer & ", " & Left(strBuild, Len(strBuild)-2) & _
  38.                          "for a Total of " & Format(curTotal, "Currency")
  39.             objMail.Display
  40.           curTotal = 0    'RESET for next Customer
  41.           strBuild = ""   'RESET Address
  42.             .MoveNext
  43.   Loop
  44. End With
  45.  

Attached Images
File Type: jpg Capture.JPG (47.0 KB, 515 views)
May 23 '22 #6
zmbd
5,501 Expert Mod 4TB
@kjhyder
+ ADezii has bodged a fair bit of code for you
+ More than likely the routine I've put together in the first attachment will work as you've asked; HOWEVER, If instead you would like a report attachment let us know and I'll post a second database that does this for you using the "WhereCondition" in the DoCmd.OpenReport method later this evening

@ADezii
You're never too late to the party, if you don't come, how are you supposed to have cake and icecream?!
May 24 '22 #7
ADezii
8,834 Expert 8TB
Thanks for the invite, zmbd, NeoPa and I go way back as 'Party Animals'! (LOL)
May 24 '22 #8
ADezii
8,834 Expert 8TB
@kjhyder:
You can actually have a single Code Segment that will cover both Options for you (EMail/Report). This Code would exist in a Sub-Routine with a single Argument indicating whether to place the Data in the Body of the EMail, or attach a Report with the Data instead. The Declaration would look like:
Expand|Select|Wrap|Line Numbers
  1. Public Sub SendEMails(blnAttachReport As Boolean)
  2.   'Main Code segment here
  3. End Sub
  4.  
  5. Call SendEMails(True)         'Attach Report to EMail
  6. Call SendEMails(False)        'Put Data in Body of EMail
  7.  
May 24 '22 #9
kjhyder
15 Byte
Thank you both zmbd and ADezii.

Outside of some formatting on the email body, I cam very close here.

In case the powers that be want to change their mind and have the information dispersed in an attached PDF instead of the email body, could you post the second database you mentioned with the "WhereCondition" in the DoCmd.OpenReport method?

Thanks
May 24 '22 #10
zmbd
5,501 Expert Mod 4TB
In case the powers that be want to change their mind and have the information dispersed in an attached PDF instead of the email body, could you post the second database you mentioned with the "WhereCondition" in the DoCmd.OpenReport method?
Oh... that NEVER happens (ROTFL)
See attached....

Open the form, click the second button... first button just rehashes what the first database so that you have it all in one place.
Of course, design view the form, view the event code...

The report isn't pretty, it's just to show one way to make things happen, I have much fancier ones for work; however, those go out to the corporate staff and one does need to put on the Ritz.
Attached Files
File Type: zip Bytes_977301.zip (150.5 KB, 64 views)
May 25 '22 #11
kjhyder
15 Byte
Just as you might have guessed, they would like the report as a pdf attachment. I have used your code and updated accordingly. I am receiving the following error.

Select VBNO
Error Number: 3075
Error Detail: Syntax error (missing operator) in query expression 'Brent Kendrick'.

Brent is the first name in my table. I run the debug and nothing comes up. Below is the code I have adapted from yours.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4.  
  5.  
  6. Private Sub Command2_Click()
  7. '
  8.  
  9. Dim zdb As DAO.Database
  10. Dim zq As DAO.QueryDef
  11. Dim zrs_email As DAO.Recordset
  12. Dim zvbyesno As Long, znumber As Long
  13. Dim zsql As String, zfname As String, zlname As String, zemail As String
  14. Dim zmessage As String, zmessagelead As String, zsubject As String
  15. Dim zWhere As String
  16. '
  17. On Error GoTo z_error_trap
  18. '
  19.  
  20. zvbyesno = MsgBox("[Yes] You are about to send out a lot of" & vbCrLf & _
  21.                   "      emails to people in the list." & vbCrLf & _
  22.                   "      I understand:" & vbCrLf & _
  23.                   "      This may result in my people flogging me" & vbCrLf & _
  24.                   "      and may also result in the ISP tagging me" & vbCrLf & _
  25.                   "      as a SPAM-BOT thus shuting me down!" & vbCrLf & _
  26.                   vbCrLf & _
  27.                   "[NO] Please do not do anything.", vbDefaultButton2 + vbYesNo, _
  28.                   "Please Confirm the Intent To Flood the Internet...")
  29. '
  30. If zvbyesno = vbYes Then
  31.  
  32.     Set zdb = CurrentDb
  33.     '
  34.  
  35.     zsql = "SELECT YTD.Pay," & _
  36.            " YTD.email" & _
  37.            " FROM YTD" & _
  38.            " WHERE (((YTD.email) Is Not Null));"
  39.     Set zrs_email = zdb.OpenRecordset(zsql, dbOpenDynaset)
  40.     '
  41.  
  42.     If zrs_email.RecordCount > 0 Then
  43.  
  44.         zrs_email.MoveLast
  45.         zrs_email.MoveFirst
  46.         '
  47.  
  48.         Do
  49.  
  50.             zfname = zrs_email![Pay]
  51.             zemail = zrs_email![email]
  52.             zsubject = "YTD Transactions"
  53.             '
  54.  
  55.             zmessage = "Dear " & zfname & ":" & vbCrLf & _
  56.                 "Attched is your YTD transactions"
  57.             '
  58.  
  59.             zWhere = zrs_email![Pay]
  60.             DoCmd.OpenReport ReportName:="RYTD", View:=acViewPreview, WhereCondition:=zWhere
  61.             Reports("RYTD").Visible = False
  62.             '
  63.  
  64.             DoCmd.SendObject objecttype:=acSendReport, objectname:="RYTD", Outputformat:=acFormatPDF, _
  65.               To:=zemail, Subject:=zsubject, messagetext:=zmessage, EditMessage:=True
  66.             '
  67.  
  68.             DoCmd.Close objecttype:=acReport, objectname:="RYTD", Save:=acSaveNo
  69.             '
  70.  
  71.             zrs_email.MoveNext
  72.  
  73.         Loop Until zrs_email.EOF
  74.     Else
  75.  
  76.         Err.Raise Number:=vbObjectError + 3297, Description:="No Records Returned With emails"
  77.     End If
  78. Else
  79.     Exit Sub
  80. End If
  81. '
  82.  
  83. z_return_from_error:
  84. If zdb Is Nothing Then
  85.     Exit Sub
  86. Else
  87.  
  88.     If Not zrs_email Is Nothing Then
  89.       zrs_email.Close
  90.       Set zrs_email = Nothing
  91.     End If
  92.  
  93.     Set zdb = Nothing
  94. End If
  95. '
  96. Exit Sub
  97. z_error_trap:
  98.  
  99. If Err.Number = 2501 Then Resume Next
  100. '
  101. MsgBox "Please report the following error to the DBA:" _
  102.  & vbCrLf & "Select VBNO" _
  103.  & vbCrLf & "Error Number: " & Err.Number _
  104.  & vbCrLf & "Error Detail: " & Err.Description
  105. Resume z_return_from_error
  106.  
  107. End Sub
  108.  
  109.  
  110.  


Any idea where the error is coming from?

Thanks,
May 26 '22 #12
zmbd
5,501 Expert Mod 4TB
I suspect that you're using a string value where a numeric value is expected

Please alter the code as follows and then run
Expand|Select|Wrap|Line Numbers
  1. Comment out Line 17 by inserting an single quote " ' "
  2. Line 17            'On Error GoTo z_error_trap
  3. (...)
  4. insert Line 34)>             Stop
  5. (...)
  6. Line 35            zsql = "SELECT YTD.Pay," & _
  7. Line 36                    " YTD.email" & _
  8. Line 37                    " FROM YTD" & _
  9. Line 38                    " WHERE*(((YTD.email) Is Not Null));"
  10.  
  11. insert (38A)>             Debug.Print "zsql = " & zsql
  12.  
  13. Line 39           Set zrs_email = zdb.OpenRecordset(zsql, dbOpenDynaset)
  14. (...)
  15. Line 59            zWhere = zrs_email![Pay]
  16.  
  17. insert (59A)>             Debug.Print "zWhere= " & zWhere 
  18.  
  19. Line 60            DoCmd.OpenReport ReportName:="RYTD", View:=acViewPreview, WhereCondition:=zWhere
  20. Line 61            Reports("RYTD").Visible = False
  21. (...)
The code execution will "break" at the new stop at Line 34 at this point I would like for you to do three things:
1) press <Ctrl><G> to open the "Immediate" window.
2) press function key <F8> slowly to step thru the remaining lines of code. Note the line at the point where the VBA error triggers when you press <F8> and report that information along with...
3) Copy and paste into this thread zsql = TheSQLThatResolves AND any zWhere= SomeTextWillBeHere text that prints to the "Immediate window"
Please note, if the text-strings that resolve in the immediate window have confidential information please PM/DM me instead of posting to the public thread - this is a one-time exception to the no direct message rule.
May 27 '22 #13
kjhyder
15 Byte
The following show in the "Immediate" window

Line 39 - zsql = SELECT YTD.Pay, YTD.email FROM YTD WHERE (((YTD.email) Is Not Null));

Line 60 - zWhere= Brent Kendrick

Thanks again
May 27 '22 #14
zmbd
5,501 Expert Mod 4TB
I take it that the error occured when you pressed <F8> on: DoCmd.OpenReport (...)

So what we need to do is wrap the resulting text from zWhere = zrs_email![Pay] in double=quotes so that the resolved string looks like "Brent Kendrick". If you will look at the demo database I was using the numerical primary key to initiate the filters.

I'm going to borrow one of NeoPa's tricks (Thank You NeoPa!) using the Replace() function to insert the double-quotes. Cannot count the number of times this trick has fixed problems with SQL strings.

>Refering to the line numbers in the code in Post#12
Expand|Select|Wrap|Line Numbers
  1. (...)
  2. Change (59)     zWhere = "%1" & zrs_email![Pay] & "%1"
  3. Insert (59A)      zWhere=Replace(zWhere,"%1",Chr(34))  
  4. insert (59B)      Debug.Print "zWhere= " & zWhere 
  5. (...)
I do the Replace() in two steps because I like to be able to troubleshoot the strings at each step - that is to say, that you could take Line59 and Line59A and combine in to one command: zWhere = Replace( "%1" & zrs_email![Pay] & "%1", "%1",Chr(34))
as a personal preference, I don't like to do that because it's harder to detect when a string goes funky.

Here again, once you make the changes, execute the code, <F8> to step thru, report back any errors, line/code, and the resolved strings from the immediate window.
HOWEVER,
If this all goes to plan, and everything works, you can then take out the Stop and Debug.Print lines and return Line 17 back to normal by removing the single-quote so that the error trap catches any user aborts on emails.

Please post back to let us know how things are working - or not working.👍👊
May 27 '22 #15
kjhyder
15 Byte
After making the suggested changes (and removing the "Stop" and "Debug.Print", 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,
May 27 '22 #16
zmbd
5,501 Expert Mod 4TB
> 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.
May 27 '22 #17
ADezii
8,834 Expert 8TB
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.
Attached Files
File Type: zip Bytes_977301_2.zip (174.5 KB, 64 views)
May 28 '22 #18
zmbd
5,501 Expert Mod 4TB
@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
May 28 '22 #19
kjhyder
15 Byte
Thank you again for your assistance. I have uploaded the reduced database below.
Attached Files
File Type: zip Database6.zip (47.9 KB, 63 views)
May 31 '22 #20
zmbd
5,501 Expert Mod 4TB
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_revised.accdb
Attached Files
File Type: zip Database6_Revised.zip (105.4 KB, 61 views)
Jun 1 '22 #21
kjhyder
15 Byte
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?
Jun 1 '22 #22
zmbd
5,501 Expert Mod 4TB
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 🎃
Expand|Select|Wrap|Line Numbers
  1. ' send the file
  2.           With oMail
  3.              .To = Left$(strEMail, Len(strEMail) - 1)
  4.              .Body = "Please find attached YTD transactions"
  5.              .Subject = MyFileName & " YTD Transactions"
  6.              .Attachments.Add attach
  7.   '            .Display
  8.           End With
  9.  
Jun 1 '22 #23
kjhyder
15 Byte
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?
Jan 17 '23 #24

Sign in to post your reply or Sign up for a free account.

Similar topics

15
by: news.microsoft.com | last post by:
HI, ASPEmail to send to multiple recipients? I tried to use comma and semicolon but all the time give this message error: Error: 6 - 501 Bad address syntax nor :...
2
by: .Net Newbie | last post by:
Hello, I am currently coding my ASP.Net pages in c# and have run into a question concerning Emails. I have four objects on a page (six including 2 buttons). The first is a subject line...
3
by: greatsky | last post by:
Hello- I need to create multiple PDF files from one access report that breaks on client for approx. 20 clients. Any suggestions? Thanks!
4
by: Michelle | last post by:
Hi Is it possible/anyone know how to send a document (.pdf) to multiple recipients using POP where the email is not just a matter of throwing 1000 email addresses in the BCC list, but each email...
1
by: duncanmcl | last post by:
How can I refer to each subreport when one to many exist on a report. Need to add a field from each to a totals by the main from. Seems I can't use the sum function on a grouping break. So thing I...
2
by: npbaker1 | last post by:
I am not able to get an e-mail sent to multiple recipients. I am using C# in VS 2003. Here is the code: MailMessage mail = new MailMessage(); mail.To =...
9
by: tucsonhanny | last post by:
I have code for outputting an Access report into multiple PDF's, but I am having trouble with the filtering. The code below does create multiple pdf files but it doesn't filter the results. All...
3
by: Reva Harrison | last post by:
I have a database that tracks drawings issued to the contractor. I have created a report listing all drawings issued per consultant per contract. This report includes a Dlookup formula that...
14
by: mazintech | last post by:
i have multiple reports (Report1, 2 , 3 ... 8) auto generated and saved to a file C:\Reports. can someone assist with code on one Click to a button to send these to multiple recipients in...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.