Access 2000 E-mail Question

[Work]

I have a question. Right now I have two tables ones called Master and the other Addresses. In the Master I have all my data that goes into it. The Addresses table is a list of Principals and there address and now e-mail. I then made a query called Letters+Addresses. I have the Master table and Addresses Linked by Street. I then put in a LIKE condition so that when you run the query it asks you for the street. Type it in and you get all the records for that street and the Principal that goes with it. Now I made a report that looks better to print. I called it Letter. In the letter report in control source I have it pull the query Letters+Addresses.

So then I made a form and a button. Once you click on the button it runs the report Letter and runs the Letters+Address and ask you for the Addresses. That part works great.

Now I want it to do this. All from above plus I want an e-mail box to open ( I use OutLook ) with the principals e-mail address filled in automatically and the snapshot file of the report as an attachment. It never gets that far.

So far this is my code.

Private Sub Command28_Click()
On Error GoTo Err_Command28_Click

Dim stDocName As String

stDocName = "Sent LMC"
DoCmd.OpenReport "letter", acPreview, "", ""
DoCmd.SendObject acReport, "letter", "SnapshotFormat(*.snp)", "DLookUp([E-mail]Addresses,[Addresses]=[Input]![Street])", "", "", "Living Material Cards ", "", True, ""




Exit_Command28_Click:
Exit Sub

Err_Command28_Click:
MsgBox Err.Description
Resume Exit_Command28_Click

End Sub

I thought I used the send object but I'm getting the e-mail box to come up but it has DLookUp([E-mail]Addresses,[Addresses]=[Input]![Street] in it. I have no clue now on what to do.


letter= The report name

E-mail = Is the name of the E-mail's in the Addresess Table

Addresses= The Table that has the pricipals names, address, and now e-mails

Input= The name of the form where I input the data that goes in the Master table

Street= The field name for street address in the Addresses table

If you could help that would be great. Thanks.

Rob

 

[Nero]

Try creating a variable to hold the DLookup value.
ie

Dim EmailStr As String

EmailStr = DLookUp("Email","Addresses","Street = Forms!Input!Street")

DoCmd.SendObject acReport, "letter", "SnapshotFormat(*.snp)", EmailStr, "", "Living Material Cards ", "", True,

 

[Work]

Reposnse

Thanks Nero. But Unfortunetly I get this error.

The Expression you entered as a query parameter produced this error:
'The object doesn't contain the automation object E"

What does that mean?

 

[Work]

Anyone else

I have been messing with this and still can't get it to work. I messed around with Nero's sugestion and then it did not work. So I just used

EmailStr = DLookup("Email", Addresses")

And took the rest off. The good news is that it worked but the bad news is that no matter what address I typed in. It pulled the very first e-mail from the list all the time. So I'm real close and is missing somthing.

Thanks.

 

[Nero]

Without the criteria it will always pick up the same record.
Can you post your DB?

 

[Work]

Mmm. I tried to attach it and said my file was too big. So I zipped it and it was too big. I don't know how else to attach it then.

 

[Work]

Does anyone else have a suggestion on what I should do? I still can't get it to work. I think I'm just missing it and tried everything that I know what to do. Thanks.

As far as posting my DB its too big. I tried the database file itself and even tried to zip it and said that it was too big.

 

[Work]

Hello again. Here is the best I can do to send something along. For some reason I can't attach anything.

Private Sub Command28_Click()
On Error GoTo Err_Command28_Click

Dim EmailStr As String

EmailStr = DLookup("Email", "Addresses", "Street=Street")
DoCmd.OpenReport "letter", acPreview, "", ""
DoCmd.SendObject acReport, "letter", "SnapshotFormat(*.snp)", EmailStr, "", "", "Living Material Cards ", "", True, ""




Exit_Command28_Click:
Exit Sub

Err_Command28_Click:
MsgBox Err.Description
Resume Exit_Command28_Click

End Sub