Hi All,

I have created an invoices database - thank you UtterAccess & tina for helping me do it.

Now I am trying to create a senario where each invoice will have a checkbox in front of it (list of invoices). Once the invoices have been selected using a checkbox, then pressing the button called "EMAIL INVOICES". will send each invoice to each client.

How do I set it up so it picks up the email address from the table called "tblCustomers" for each client and emails invoice as PDF with some text as email body - it does not matter i I have to use outlook or some other program to do it.


Any ideas on how to go about it?

Hi snehal0909

You would need to loop through the list of contacts with invoices to be sent and who have e-mail addresses,
You could use the SendObject command:
DoCmd.SendObject acReport, "ReportName", acFormatPDF, emailaddress, , , "Subject", "text body", True

Hence code would be e.g.


(Air code - not tested!) -and clearly you will need to change all table and field names and the report name to match your database

This method uses Outlook and should give you a good start however it will display a dialog box for each message to be sent asking you to authorise Outlook to send the message, to avoid the message you would need to use outlook automation, which is rather more complex, If you need that, then search UA for 'Outlook Automation'

hth

Thank you Bernie,

Something tells me that this is going to be tough. But I never know unless I try.
Will post results.

If anyone else has any ideas-examples, please post. I am checking the forum every day.

Thank you!

snehal0909,

The key is to keep things simple. Solve one problem at a time - don't try to do it all in one push.

First, figure out how to select which invoices to send. Do NOT proceed to the next problem until you're satisfied with your solution to the first problem. Next, create your command button, but only have it generate a query that lists the invoices to be sent, and the corresponding email address to send to. Again, don't proceed to the next step until you're happy with the creation of this query when you push the button. And so on.

This sort of strategy will cut down on frustration. Plus, if you get stuck and post a question, it's a simpler question that'll be easier to respond to.

Hope this helps,

Dennis

Hi snehal0909

To expand on Dennis' comment (btw Dennis, thank you for the assist)

Dennis' step1. to select which invoices to send:
This is the query SQL which looks for the selected invoices tblInvoices.SendInvoice = -1
It also checks they have a valid invoice address Len(tblClients.Email & '') >0"

strSQL = "SELECT DISTINCT tblClients.Email, tblInvoices.InvoiceID, tblInvoices.InvoiceNo, tblInvoices.SendInvoice " & _
"FROM tblClients INNER JOIN tblInvoices ON tblClients.ClientID = tblInvoices.ClientID " & _
"WHERE tblInvoices.SendInvoice = -1 AND Len(tblClients.Email & '') >0"

The SQL joins the list of clients (tblClients) with the table of invoices (tblInvoices) using ClientID as the field relating the two tables.

Step 2 is to open the query and loop through each record:

This will loop through the records returned by the query recordset and print the invoice number to the immediate window.

Step 3 Create the Strings for the addressee, title and body of the email


Step 4 open the report
DoCmd.OpenReport "ReportName", acViewPreview, , "InvoiceID=" & rst!tblInvoices.InvoiceID
Do this for one report only - Comment out (temporarily) the While ... Wend comands to give


Step 5 Send the e-mail for the 1 report
by adding
DoCmd.SendObject acReport, "ReportName", acFormatPDF, strEMail, , , strSubject, strtext, true
after the DoCmd.OpenReport line, note the 'True' statement at the end of the line

Step 6
Close the report after the send Object , and re-enable the loop (by removing the comment marks), and move through the recordset with the movenext command

Note
The Open Report has 'acHidden' to prevent the report flashing up each time a new record is processed,
The Sendobject command has 'False' at the end to send the e-mail without opening it for user edit first.
rst.Edit ... rstUpdate is commented out , the code sets the SendInvoice flag to 0 and hence prevents the code resending the invoice the next time it is run, - you don't want the code to do this while you are testing.

Finally step 7
Enable the rst.Edit ... rst.Update code to clear the SendInvoice flag.

hth

Thank you Bernie & Dennis.

I am stuck on step-1.

What I have in my database is a form where I put the date & click on SHOW INVOICES button to see the invoices created on that date.
Which means I already have a query that shows me the invoices for a specific date (the ones I want to email).

Please see my attached database to get better idea.

Hi snehal0909

I afraid I am unable to open some of the objects in your file as I do not have A2010 available at the moment,

If you already have a query available that returns the invoices you want to send then you could replace the
Set rst = CurrentDB.OpenRecordSet(strSQL)
with
Set rst = CurrentDB.OpenRecordSet("qryMakeInvoices")

You would then just need to replace the table, field and report names in the code with names in your database and ensure that all the fields needed for the code were available in the query.

Note that the code sends each record returned by the recordset as separate emails and opens and closes the report each time for each record. If you view the invoice report first it must be closed before you send the individual invoices by e-mail.

hth

Sorry I am still not an intermediate user of Access.

The things that confuse me are the following sentences.

Step 2 is to open the query and loop through each record -- ok how do I do it? does the code in step get attached to VIEW INVOICES BUTTON?

Step 3 Create the Strings for the addressee, title and body of the email - how to go about creating a string? I haven't got to the VBA chapter in my ms access book yet.


---------------
what I found is that I understand the whole thing once i have done it rather than trying to understand it all before I even do it. (in terms of MS Access).
So please bear with me.

He snehal0909

Yes the code is 'attached' to the command button;
1. With the form in design view, select the command button, Open the properties window and select the 'Event' tab.
2. In the On Click property select '[Event Procedure]' and then click the "..." on the right hand side of the property - this opens the VBA window
it will read something like:

3. Copy your code into the window


I've shown error handling code in the above example, which you should use in all your VBA routines.
Also you should add an option explicit statement at the top of the code window it should look like:


>>how to go about creating a string<<
In the code you declare string variables

you then assign the text you want that string variable to have

Where you have e.g. rst!InvoiceNo then the data for the string is taken from whatever is returned from the selected record in the recordset,
Anything in quotation marks ( "Invoice No: " ) is added 'as is'

Once you have assigned data to the string variable you can use the string variables whenever you need to e.g.
DoCmd.SendObject acReport, "ReportName", acFormatPDF, strEMail, , , strSubject, strtext, False

hth

Just did that.
It highlights "qryMakeInvoices" and says COMPILE ERROR - VARIABLE NOT DEFINED.

I googled it but may of the results on first page say I have to go to TOOLS - REFERENCES to enable something. but my REFERENCES menu item is grey-disabled.

Any ideas?

Below is the code I have put in.

Option Compare Database
Option Explicit

Private Sub Command12_Click()

On Error GoTo err_proc
Set rst = CurrentDb.OpenRecordset(qryMakeInvoices)
While rst.EOF = False
Debug.Print rst!InvoiceNo
rst.MoveNext
Wend
Set rst = Nothing

exit_proc:
Exit Sub

err_proc:
MsgBox Err.Description, vbInformation
Resume exit_proc

End Sub

Your query name should be in quotes
Set rst = CurrentDb.OpenRecordset("qryMakeInvoices")

I still get an error "variable not defined" and it highlights rst

I have re-created the database in Access 2003, see if you can open it and get the same error.

open the form frmFilterInvoicesByDate & type in 17/05/2012. then click VIEW INVOICES button.



I appreciate all your help making this database.

Hi snehal0909

You must declare all the variables you use, if you refer to post #2 it states

Dim rst as DAO.Recordset

hth

Too few parameters. Expected 1

What does this error mean - I am typing the date in the form & then clicking on View Invoices button. Isn't the date a parameter in this case?

Hi snehal0909

This error means that the query is not picking up the date parameter correctly from the form,

You could try using the original method I suggested, using an SQL string in the code rather than the query:

hth

Ok, I have been trying for a week - looking for answers but it is looking impossible. with all this code involved, I have lost the track of what I am actually doing.

I had a query that returned the invoices I needed to email. But then it got messy as I tried to use the code instead of using the existing query.

Back to the problem. After a week of trying has taught me exactly what I need to do (but I need your help on how to do it)

1) I already have a query where if I put the date in, it will show me the invoices created on that date.

2) Once I see a datasheet of invoices using the query - I want to exclude some invoices by de-selecting the invoice numbers (some customers want me to send invoices via post office, so i simply filter the invoices by invoice number to exclude the invoices that need to be sent using post office)

3) Now I have a list of invoices that need to be emailed - I have a button called EMAIL INVOICES.

4) What code do I attach to the EMAIL INVOICES button so that each of the invoices in the datasheet get emailed to appropriate email address automatically?


Attached is the database - Access 2010

Hi All,

I have used the "air code" to some degree succesfully. When I click my button to run the code it creates emails correctly according to employee number and email address, but it creates it for the the same amount of lines in the report, so if I have 10 lines in the report I get the report 10 times per individual, I need the email to go out only once to every employee. Here is the code:

Tables:

ControlData = Employee fleet details
ReportData = Data from a create table query where only valid employee records have been populated

Form:

Form1 = Witth button

Report:

RptData = Report on table ReportData

CODE:

Option Compare Database
Option Explicit

Private Sub Command0_Click()

On Error GoTo err_proc

Dim strSQL As String
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strtext As String
Dim strEmail As String

strSQL = " SELECT DISTINCT ControlData.EmployeeNumber, ReportData.Registration, ReportData.Tran_Date, ReportData.Tran_No, ReportData.MERCHANTNAME, ReportData.Tran_Code, ReportData.Tran_Descrip, ReportData.ODO, ReportData.R_Amount, ReportData.Email_Address, ReportData.VEHICLEDRIVER " & _
" FROM ReportData INNER JOIN ControlData ON ControlData.EmployeeNumber = ReportData.Emp_No "

Set rst = CurrentDb.OpenRecordset(strSQL)

While rst.EOF = False

strSubject = "Employee Number: " & rst!EmployeeNumber
strtext = "You invoice " & rst!EmployeeNumber & " is attached" & "whatever other text you want"

strEmail = rst!Email_Address
DoCmd.OpenReport "RptData", acViewPreview, , "Emp_No = " & rst!EmployeeNumber, acHidden
DoCmd.SendObject acReport, "RptData", acFormatHTML, strEmail, , , strSubject, strtext, True
DoCmd.Close acReport, "RptData"

rst.MoveNext
Wend

Set rst = Nothing

exit_proc:
Exit Sub

err_proc:
MsgBox Err.Description, vbInformation
Resume exit_proc
End Sub