Send emails of report that lists all open invoices by Vendor

[supportservice]

I've searched and searched and can't seem to really find what I'm looking for.
If there is a code out there to achieve the following please direct me! I apologize for the lack of finding it otherwise :-(

I have a database.
There is a macro that extracts data out that creates a table "Emailing-OpenInvoices".
There's a report "Emailing-OpenInvocies" formatted listing all the open invoices by Vendor. Each page is a new vendor.
Each lists the invoice number, due date, invoice amount then at the bottom the total of all invoices per Vendor.

** What I need assistance with how to write code to send each Vendor their list of open invoices and total amount in the email message.
Details on the report includes the following. It is Grouped on VendorNo. Each page is a different Vendor and lists the Vendor's open invoices.

VendorName Hemet Valley Pipe & Suppl VendorNo 0000098

InvoiceDueDate   InvoiceNo   InvoiceAmt
xx/xx/xxxx       xxxxxxx     $xxx.xx
xx/xx/xxxx       xxxxxxx     $xxx.xx
xx/xx/xxxx       xxxxxxx     $xxx.xx

TOTAL: Sum([InvoiceAmt])

NOTE: If the total for the Vendor is $0 I would like it to ignore / suppress it.

Here's a snippet of the data in the table:

VendorNo	VendorName	InvoiceNo	InvoiceDate	InvoiceDueDate	TermsCode	InvoiceAmt	DiscountAmt	NetInvoice	EmailAddress
0000098	Hemet Valley Pipe & Suppl	145701	8/7/2012	9/10/2012	89	$150.92	$0.00	$150.92	service@aelllissolutions.com
0000098	Hemet Valley Pipe & Suppl	145524	8/9/2012	9/10/2012	89	$258.94	$0.00	$258.94	service@aelllissolutions.com
0000098	Hemet Valley Pipe & Suppl	145525	8/9/2012	9/10/2012	89	$433.40	$0.00	$433.40	service@aelllissolutions.com
0000098	Hemet Valley Pipe & Suppl	145526	8/9/2012	9/10/2012	89	$88.42	$0.00	$88.42	service@aelllissolutions.com
0000098	Hemet Valley Pipe & Suppl	145567	8/3/2012	9/10/2012	89	$131.14	$0.00	$131.14	service@aelllissolutions.com
0000098	Hemet Valley Pipe & Suppl	145633	8/2/2012	9/10/2012	89	$17.49	$0.00	$17.49	service@aelllissolutions.com
0000098	Hemet Valley Pipe & Suppl	145652	8/3/2012	9/10/2012	89	$166.80	$0.00	$166.80	service@aelllissolutions.com
0000098	Hemet Valley Pipe & Suppl	145654	8/3/2012	9/10/2012	89	$36.43	$0.00	$36.43	service@aelllissolutions.com
0000098	Hemet Valley Pipe & Suppl	145657	8/6/2012	9/10/2012	89	$11.69	$0.00	$11.69	service@aelllissolutions.com
0000098	Hemet Valley Pipe & Suppl	145522	8/9/2012	9/10/2012	89	$95.83	$0.00	$95.83	service@aelllissolutions.com
0000098	Hemet Valley Pipe & Suppl	145698	8/8/2012	9/10/2012	89	$504.31	$0.00	$504.31	service@aelllissolutions.com
0000098	Hemet Valley Pipe & Suppl	145837	8/9/2012	9/10/2012	89	$192.71	$0.00	$192.71	service@aelllissolutions.com
0000098	Hemet Valley Pipe & Suppl	145684	8/7/2012	9/10/2012	89	$384.10	$0.00	$384.10	service@aelllissolutions.com
0000198	PETE'S ROAD SERVICE	687588-00	8/14/2012	9/10/2012	88	$145.00	$0.00	$145.00	angelell@yahoo.com
0000199	E.M.W.D. (Dump Fees)	2012JULY	8/3/2012	8/20/2012	89	$63.24	$0.00	$63.24	angel@angel-greg.com
0000199	E.M.W.D. (Dump Fees)	2012JULY'	7/31/2012	7/31/2012	89	($63.24)	$0.00	($63.24)	angel@angel-greg.com

 

[supportservice]

Please see attached.Link

 

[kjv1611]

by the way... you didn't just list your whole REAL database in the attached file did you? Those are purely fictitious records, right? I sure hope so... I don't think it'd be good business practice to be posting REAL business relations, etc on the open web...

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

And another by the way (I cannot download the file, as it's at a site I cannot access via my work PC)..

You say a macro creates a table... Why not just use a query? It would be faster, and you aren't duplicating data just for reports. Just doesn't seem like a good idea to create a table for a report.

So what you should do - whole overall:
1. Create a query that gets all "open" invoices with balance > $0
2. From that query, have other queries.... each following here..
3. For one, get a summary query - it gets the Vendor Name, and the total amount in open invoices
4. For another, get the detail for each vendor.
5. Then in your code to build your emails, loop through the Vendor Summary table, and grab that Vendor Name or ID. Capture the Vendor name and sum(amt) in variables
6. In your Vendor Detail Query, filter it on your current Vendor Name or ID from the summary loop
7. Then build a string variable that contains the vendor name, ID, whatever else, the vendor invoice detail, and then the summary, all with appropriate white-space. Id' probably use vbTab and vbCrLf for the white space.

This way, when looping through your summary query (recordset of that query), you'll end up automatically eliminating any vendors with no open invoices, or with total amt due/owing <= $0

Again, though, that's just an overall look... you'll need to take it a step at a time to make sure you get it correct, and test along the way.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[supportservice]

The db is in DropBox.

It really doesn't matter if it's a query or a table.
The table is better because it doesn't have to go through the entire file to extrapolate the required data.
That can be done once.
Then use the table to run reports or queries against.
It's faster to run it against the table with the said data rather having to recalculate and run through the entire data files that needs to be worked with.

I get the part of handling the $0 with

This way, when looping through your summary query (recordset of that query), you'll end up automatically eliminating any vendors with no open invoices, or with total amt due/owing <= $0[/code]

Thank you.

I still need the major part of this project.
Most of all that I found doesn't show how it can display each invoice details per vendor then email it to the corresponding Vendor with the details.

 

[kjv1611]

The table is better because it doesn't have to go through the entire file to extrapolate the required data.
That can be done once.

I understand how you can think this, but in reality, 99.99999% of the time, that is not correct. Usually a query will be just as fast. Besides that, what if the piece building the new table breaks in the mean time.

Building a table strictly for reporting just really goes against the basic ideas of reporting.

Unless you have millions of rows in this table you're talking about, then any performance gains are totally negated by first building a new table, period. And even then, it's at best arguable.

Have you tested it both ways to know the details?

Here's where that yes it will perform worse: If instead of a QUERY, you are literally going through every record in a recordset. A query works totally different, works TONS faster.

If I get a chance this weekend, I'll download your file and take a look. I'd be REALLY shocked to see a scenario where it is worth building a table just for reports.

Otherwise, what exactly do you have working so far, and what is your next step. You need to work on this a step at a time which is why I put a basic plan of attack above in a numbered list.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[supportservice]

The query has a criteria set to extract specific records.
Then to work with that said record in the table it created.

Either way, I would much appreciate assistance on how to send an email per Vendor of all their open invoices that are in the extracted info of the said Table.

The email body should list, as shown in the sample above and go email each "report" to each Vendor with one click of a command button.

You should see couple of Modules I've got started from what I have found.

NOTE: I don't want it to send it as an attachment if possible.

Private Sub EmailReport_Click()
  ' Dim dbs As DAO Database
   Dim rst As DAO.Recordset
   Dim qdf As DAO.QueryDef
   Dim baseSQL As String
   Dim rptSQL As String
   Set dbs = DBEngine(0)(0)
   Set rst = dbs.OpenRecordset("SELECT VendorNo, EmailAddress FROM Emailing-OpenInvoices")
   Set qdf = dbs.QueryDefs("Emailing-OpenInvoices")
   baseSQL = "SELECT [Emailing-OpenInvoices].VendorNo, [Emailing-OpenInvoices].VendorName, [Emailing-OpenInvoices].InvoiceNo, [Emailing-OpenInvoices].InvoiceDueDate, [Emailing-OpenInvoices].InvoiceAmt, [Emailing-OpenInvoices].DiscountAmt, [Emailing-OpenInvoices].NetInvoice, [Emailing-OpenInvoices].EmailAddress FROM [Emailing-OpenInvoices] ' this is the full SQL statement of the qryAdvisorLetters2010 query, without any trailing semi-colon"
   With rst
      Do Until .EOF
         rptSQL = baseSQL & " WHERE VendorNo = " & !VendorNo  ' This will require an 'AND' clause if the Emailing-OpenInvoices query already includes a 'WHERE' clause
         qdf.SQL = rptSQL
         DoCmd.SendObject acSendReport, "rptEmailing-OpenInvoices", acFormatRTF, !Email, , , "Subject: "Please Charge Credit Card on File for the following invoice(s)", "Message: List of open invoices", False
         .MoveNext
      Loop
      .Close
   End With
   qdf.SQL = baseSQL
   Set qdf = Nothing
   Set rst = Nothing
   Set dbs = Nothing
   
End Sub

Thank you

 

[kjv1611]

Yeah, I will TRY TRY TRY to look at this database this weekend at some point. I'm keenly interested. For one, you say you have a table built by a query, and you're accessing via the table. Looking at this procedure you posted, though, it looks like you're building a recordset off of a query, not a table... which is what I was talking about.

Regardless, I'll look so long as I don't run out of time and don't forget. Have a good weekend!

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[supportservice]

Yes it's a code I found but it needs work.

Ok thank you.

Have a good weekend.

Hope someone can assist on how to do this?

 

[kjv1611]

Sorry.. I did not have time to look at this over the weekend, even with the extra day. I was tied up throughout all of it. And I already know I won't likely have much if any time for a while now.. but if I do, and remember, I'll take a look. Maybe someone else will have time to take a stab at it.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[MitchRandall]

I gave up on trying to code this stuff a long time ago and use Total Access Emailer from FMS instead:

http://www.fmsinc.com/MicrosoftAccess/Email.asp

What makes it powerful is that you can use a table or query to send personalized emails to everyone in that list using fields from the table in the message. You can also attach reports as PDF files filtered for each record, so each person gets their data/pages.

You can use it as a wizard or run email blasts via VBA, and distribute the latter to other users. There's a free demo:

http://www.fmsinc.com/MicrosoftAccess/Email/free-trial.html

It's eliminated a lot of headaches for me and let me implemented a lot of features very quickly to my users. It's really eliminated a lot of report printing and physical distribution hassles. Hope this helps.

 

[kjv1611]

Glad you found a solution. I hope it REALLY is worth something, since it costs so much. Wow, I was expecting to see something like $50 or $100, but then I looked and WOW!

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[supportservice]

MitchRandall,
Thank you but there has to be code to do that without the $300-$500 cost for the program each time you want to use it. :-(

 

[kjv1611]

Wow, I'm just now noticing the name on who used the purchased solution. I thought the OP had purchased it, and said he/she gave up on trying to code it.

By the way, supportservice, I downloaded your db last night at home, and Access said it was in an unrecognizable format. I use Access 2010 at home and work. So I couldn't begin to look at it.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57