question on emailing a record

[Jason Dinnell]

I have searched but I can’t find what I am looking for. Can someone please point me in the right direction? From a command button I am looking for a way to email the current record from a form using a report as an attachment.

Thanks
Jason Dinnell

 

[John Big Booty]

The following should point you in the right direction.

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

stDocName = "Table, Query or Report Name" [COLOR="Green"]'Below in Docmd insert acSendTable for table, acSendReport for report or acSendQuery for query[/COLOR]
stToName = "Insert to email adresses" [COLOR="Green"]'This could also be a field picked up by your query or report[/COLOR]
stCCName = "Insert cc email adresses" [COLOR="Green"]'This could also be a field picked up by your query or report[/COLOR]
stBCCName= "Insert bcc email adresses" [COLOR="Green"]'This could also be a field picked up by your query or report[/COLOR]
stSubject = "Subject text"
stMessage = "Message" [COLOR="Green"]'This could also be a field picked up by your query or report[/COLOR]


DoCmd.SendObject acSendTable, stDocName, acFormatXLS, stToName,stCCName ,stBCCName , stSubject, stMessage

Additionally if you are using Access '07 and have the appropriate add in you can also send PDF files


To email only the current record in a report create a query that will populate your report. In the criteria for the RecordID put;

Forms!FRM_YourFormName!RecordID

 

[Jason Dinnell]

Thanks for the help. You put me on the right track. My mind was working this problem all wrong. I was trying to filter it in the form and then pass the data to the report, like how it is when I print. This is a little more work since I have to generate more queries but it sure solves allot of issues I was having, and there is practically no additional coding beyond what access generates automatically

Thanks
Jason

 

[Wet_Blanket]

Fantastic thread. I have a problem though - I used the coding provided above, and it attaches the report as an excel file. How can I make it go out as a word doc or pdf?

 

[pbaldy]

Change this argument of SendObject: acFormatXLS. More info in VBA Help. For PDF, I use:

http://www.lebans.com/reporttopdf.htm

 

[Wet_Blanket]

Thanks. I also am having trouble with the email's to address. I want it to populate from a field called "FA_email" from my master table - but I can't get it to recognize it. Any suggestions?

 

[pbaldy]

There's a lot of info here:

http://www.granite.ab.ca/access/email.htm

 

[Wet_Blanket]

There's a lot of info here:

http://www.granite.ab.ca/access/email.htm

Yeah, I've gone through that web site and couldn't find anything addressing this issue. It seems like it involves a simply solution - but I can't find it.

Currently, I tried:

stToName = Master.FA_email

Master = the table's name, and FA_email = the field.

But it doesn't recognize the object.

 

[pbaldy]

The site addresses it with a recordset based on a query. If you have a single record in that table, you can use a DLookup() to get the value:

DLookup("FA_email", "Master")

 

[Wet_Blanket]

The site addresses it with a recordset based on a query. If you have a single record in that table, you can use a DLookup() to get the value:

DLookup("FA_email", "Master")

Worked like a charm with some tweaking for my specific purpose - thanks.

 

[ansentry]

Have a look at my sample posted Here

Go down until you see PrintCurrentRecord_Update.zip. It will show you how to email, print etc the current record.

Any further questions post back.

 

[Wet_Blanket]

The site addresses it with a recordset based on a query. If you have a single record in that table, you can use a DLookup() to get the value:

DLookup("FA_email", "Master")

A follow up question, if you don't mind. There can be upto 3 email addresses to send the report to: FA_email, FA_email2, FA_email3.

Would I then put it as:

stToName = DLookup("FA_email", "Master");DLookup("FA_email2", "Master");DLookup("FA_email3", "Master")

?

 

[pbaldy]

Try

stToName = DLookup("FA_email", "Master") & ";" & DLookup("FA_email2", "Master") & ";" & DLookup("FA_email3", "Master")