Send Invoice to Customer in Outlook using VBA

[danz013]

I have a table that holds sales info.

I have a select query that gather the relevant sales info for a particular sale and then uses that info to produce a report in the form of an invoice.

So for example, access will ask me to enter the invoice number and will then produce an invoice for the sale. What I want to do is slightly different though.

Ideally, what I want is a button on each sale form. When I've finished typing in the sale information, I will press the button to produce and automatically send the invoice to the customers email address.

I have no idea how to actually do this? You help would be much appreciated. I don't know VBA fluently however I'm slowly learning. I've brought a few big books to help me out.

 

[nIGHTmAYOR]

in access 2003 its easily done in button creation wizard (Reports operation >> Mail report >> Select report) in access 2007 however they are trying to emphasise macros , so u will find a send mail macro where you will provide report's name, yet also you can use VBA code generated by access 2003 button creation wizard on access 2007 :

    Dim stDocName As String
    stDocName = "Report1"
    DoCmd.SendObject acReport, stDocName

 

[danz013]

I'm with you so far, and I've discovered this already.

What want is for access to automatically send the report to the right person.

So, If I'm looking at Mr Bloggs sale, and I press the button, I want access to produce the Mr Blogs Invoice and send it to Mr Blog Email address.

 

[nIGHTmAYOR]

custom create a report (copy the existing one you are working on) where its source sql statment looks up current client (Mr Bloggs) sale/invoice "key" off your opened form and then use this report as a source of your email code.

further to this if your form was named "sales_form" then we are looking to have a source sql statment with where condition looking like this :

Where [sales_table].[invoice_id] = Forms!sales_form!invoice_id

this'd be just an example since i dont know the real nameings of your fields.

 

[danz013]

Ok I'm with you so far, what I don't understand is how I could use the report as a source of your email code?

When I look at the send object macro, it simply gives me an option to input an email address. There is no 'calculation' button for me to tell it where to find the email address?

I hope I'm making sense.

 

[nIGHTmAYOR]

did u try the vb codes and the methode so far ? its straight forward , also use help on SendObject , you can assign a varriable to it where you fill it with the email retrieved by dlookup or so , all triggered in one go via button click event , here is a quick example :

Dim stMail As String
stMail = Dlookup("[client_email],"clients_table","[client_id]="Forms!MySaleForm!MyClientId)
DoCmd.SendObject acSendReport , "report_invoice_copy_name_here", acFormatSNP , stMail, , ,"subject here", "message body here"

 

[danz013]

I fully understand you now however I'm having a few issues getting the code to work.

I've tried

Dim CSAddress As String

CsAddress = DLookup("[EmailAddress]", "tblSales", "[InvoiceNo]" = Forms!frmSales!InvoiceNo)

As soon as it runs the second line I get the following back :

Run time errorr '94'
Invalid use of string.

I'm really not sure whats wrong. When I hover over the Forms!frmSales!InvoiceNo it tells me the value, from what I can see it picks out the right value for the invoice number. Its just not doing the rest?

 

[danz013]

I changed it too this:

Dim CsAddress As String

CsAddress = DLookup("[EmailAddress]", "tblSales", "[InvoiceNo]=" & Forms!frmSales!InvoiceNo)

However, now I'm getting back this response...

Run-time error '2471':

The expression you entered as a query parameter produced this error:
'HT162'.

HT162 is the correct actually the correct invoice number... I'm not sure what the actual error means though

 

[nIGHTmAYOR]

mr danz013 , you are looking up email address in sales table by invoice number ?? are you saveing a client email by every sale ?

 

[danz013]

Yeah, so I'm trying to find the email address for a particular record. The primary key for each of these records is the invoice number. Every record has an email address.

Is something wrong with the syntax?

Thanks again for your help.

 

[danz013]

Hi Guys,

I managed to work this out in the end. The syntax was a bit dodgy.

Thanks for the help.

 

[fibayne]

Hi danz013...been tyring to work out how to do this and have got as far as looping thru all the email address producing an seperate email for each client but cannot get my head round how to attach the client report very similar to your thread where you wanted to attach an invoice, I wnat to attach the clients statement any chance you could post the code that you did this ...I would be eternally gratefull
cheers Fi

 

[pcride]

I need to know what is the client_ID in this scenario? Table, Form Field??

 

[nIGHTmAYOR]

HT162 is the correct actually the correct invoice number

HT162 is not a number its a string! in which case your lookup parameter should look like this :

CsAddress = DLookup("[EmailAddress]", "tblSales", "[InvoiceNo]=[B][COLOR=red]'[/COLOR][/B]" & Forms!frmSales!InvoiceNo & "[B][COLOR=red]'[/COLOR][/B]")

as for the other questions posted , i would suggest searching forum forreferencing outlook in visual basic as it is more handy for requested approach (bulk sends).

 

[nIGHTmAYOR]

pcride , you can always press F1 over any vague command in vba editor (in this case DLOOkUP) and lookup its syntax formation techniques , its pretty straight forward.
but if you are into take away dinners then the answer is

Dlookup("[field_name_to_find]","table_name","[field_of_where_condition]=" & parameter_here) '---- if where condition was numeric

or

Dlookup("[field_name_to_find]","table_name","[field_of_where_condition]=[B]'[/B]" & parameter_here & "[B]'[/B]") '---- if where condition was a string

 

[pcride]

THanks for the reply, however I am still unclear. I did use the F1 key and nothing came up for the email field.

This is my information

My table is called TableMain
The field in the table containing the email address is called EmailAddress
the form that I have has the email field called EmailAddressFrm
The report field that contains the email is called EmailaddressRpt

I don't understand what I put in place of the following from your example
field_of_where_condition
& parameter_here
'---- if where condition was numeric <-- is this just a comment?

Still pretty fresh at these forms.

Thanks for your help.