Variable Email of a Report

[kylebmorris]

Hey all..

Ok i sure this is probably easier then i think, however i am new to access and i have a little situation. I searched this forum first didnt see this anywhere. My client wants to be able to send a email out which is a report generated invoice of her customers purchase. This part is all set and ready to go.. the query and report creation are taken care of and work great for printing.. now i need to add a cmd button to send a email that pulls the customers email address and fills it in. Sending the email is easy.. however pulling the email adress data from the customer tbl is where i need some assistance.



To summarize. I am sending report out as a .pdf it needs to auto fill the customers email address into out look also add a bcc to a static email address and also allow to me to have a generic topic and email body for this process. And the outlook email window should be displayed and the user will manually click send. Anyone able to help me out on this?

Thank you in advance.

[dsmacs]

Include the email in the underlying query or use a dlookup to return it.

Attached DB sample on how to send email with an attachment.

Cheers
Darren

[kylebmorris]

Thanks darren,,, but all that demo while very nice doest really pull off what i am looking to do.. i guess essentially i need someone to tell me how to autofill the email address box in outlook with data from my [email] field in my [tblorders].

kyle

[dsmacs]

Email me a copy of you DB with some dummy data?

d [dot] mcdonald @ ozemail [dot] com [dot] au

[kylebmorris]

update on my progress... so i have determined that i need to so the following:
on my stToName = i need to do a DLookup from the following:

i need to pull the email address from my "customers" table but it needs to be the email that is applied to the current record of my "Orders" table.

Essentially i need the DLookup code that will allow my rptInvoice to be sent as a pdf to the current customer from "Customer" table [email] that is being applied to the current record of my "Orders" table. Can anyone tell me the proper structure for this DLookup

Tables that apply:

"customers"
"orders"

Controls that apply:
[OrderID]
[email]
[CustomerID]

Queries that apply: (underlying the report being sent)

qryInvoiceReport

That Query also contains the email field.


Appreciate any help!

[kylebmorris]

ok this is the code i have so far...

Dim stDocName As String
Dim StrWhere  As String
Dim stToName As String
Dim stCCName As String
Dim stSubject As String
Dim stMessage As String

stDocName = "rptInvoice"
StrWhere = [OrderID] = [Forms]![Add an Order and Details]![OrderID]

stToName = DLookup("Email", "customers", "Orders.OrderID = " & [OrderID])
stCCName = "ABCD@ABCD.com"
stSubject = "Quotation Test"
stMessage = "Attached is a self generated purchasing quotation. This is a test."
DoCmd.SendObject acSendReport, stDocName, acFormatPDF, stToName, stCCName, stSubject, stMessage

Two things are going wrong for me...

the email field always gets the first record emial addresss and my where condition is not working the pdf attached is getting every record.

Please help!

[kylebmorris]

well i suppose it would be rude to say.. help took to long to come buti fixed it myself.. i would up putting a command button on a report which i know its the correct whay to do it.. but what i did was this:

I put a cmd button on my main form to open the report with the where condition using a macro

The i put a email cmd button on the report itself that is not visible in print mode (screen only) then i attached this code the the send cmd button on my report:

Dim stDocName As String
Dim stToName As String
Dim stCCName As String
Dim stSubject As String
Dim stMessage As String

stDocName = "rptInvoice"
stToName = DLookup("Email", "qryInvoiceReport", "OrderId = " & [orders.orderID])
stCCName = "ABCD@ABCD.com"
stSubject = "Quotation Test"
stMessage = "Attached is a self generated purchasing quotation. This is a test."

DoCmd.SendObject acSendReport, stDocName, acFormatPDF, stToName, stCCName, stSubject, stMessage

and it works like a charge. The only thing i dont like is if i dont send the email with outlook it give the user a runtime error for not sending.. can anyone tell me how to get rid of this?

-Kyle