Email Single Record in Form (Hayley Baxter help plzz)

[g@v]

Hi,

I have searched this forum and found a few examples but cannot get them to work in Access 97, what i would like to do is email a single record by using a query for Repair No..

So i would like it to be a button, it's possible to do this via a sendOject command but that emails ALL records not give a option to type in your repair number and email that...

Thanks for any one who can help, im a complete noob an dneed some help

 

[Lightwave]

g@v

From what I can tell, you are firstly trying to find a record from the repair no then you are wanting to actually get the e-mail to boot up and take the e-mail address from that record and put it into the address for the outlook address. As such it’s a two stage process that could be converted into one process. I've done something similar but never combined it together before…

What I would try is something along these lines.

Firstly place a button on your form that has all the list of repairs in it.. This form is going to have the following code behind the on_click event.

Private Sub FindRepairRec_Click()
On Error GoTo Err_FindRepairRec_Click

Dim strDocName As String
Dim intID As Integer
Dim strLinkCriteria As String

intID = InputBox("Please enter the Housing Site ID", "Find distinct Housing Site")

strDocName = "01SITENAMETBL"
strLinkCriteria = "[ID]=" & intID
DoCmd.OpenForm strDocName, , , strLinkCriteria

Exit_FindRepairRec_Click:
Exit Sub

Err_FindRepairRec_Click:
Resume Exit_FindRepairRec_Click

End Sub

You will need to replace 01SITENAMETBL with the name of the form which shows details of the repair record - if this does not exist then you could just move the current record to the record you are interested in. You will also need to change the ID to equal the name of your Repair no field. As the above effectively opens up the 01sitenametbl form based on the ID - what you are wanting to do is open your Repair Record based on your Repair No.

Next step I did in my database was to put a really simple button within your equivalent repair record that on click had the following code…. Note you will need to change Me!Email to Me!Whatever the name of your field is which contains customer e-mail address within the repair record.


Private Sub cmdEmail_Click()
On Error GoTo HandleErr

'Save the record
If Me.Dirty Then DoCmd.RunCommand acCmdSaveRecord

'Make sure the hyperlink property is current
EnableEmail

'We don't actually have to do anything here. The button uses its own
'hyperlink property to send mail, as specified in the Form_Current

ExitHere:
Exit Sub

HandleErr:
Select Case Err
Case Else
MsgBox Err & ":" & Err.Description, _
, "Form_01PEOPLE.cmdEmail_Click"
End Select
Resume ExitHere

End Sub
Private Sub EnableEmail()
On Error Resume Next
cmdEmail.HyperlinkAddress = "mailto: " & Me!Email
End Sub

 

[g@v]

thanks for the reply, you got the first part right I'm wanting to find a Repair no then open up the report and attach to email now SendObject does this but does all of the records and attaches all records I only want to do this on 1 record


Thanks again

 

[Lightwave]

Hi g@v

Right understand now - never done second part b4 - the first part will certainly get you to the repair you're looking for and from there should be easy to run the report - I'll have a look to see about attaching a report to an e-mail.

M

 

[Lightwave]

g@v

The following code will take the information of a record for a particular ID and send that information to the report named R02PEOPLE - So if you replaced ID with the Repair Number field name and R02PEOPLE with the name of your repair report you should be able to get the repair information for one particular repair to come up in the report you are wanting to send.

Thus once that has been achieved you would then need to combine this code with the attach to e-mail code -

At the moment can you get the information related to one repair into the relevant report????
It looks like you have tried to do all the stages in one go - I usually have to break them down and then combine them up to get it working well


Private Sub Command37_Click()

On Error GoTo HandleErr

If IsNull(Me![ID]) Then
MsgBox "Nae Luck Pal no data for report. . . . . Cancelling"
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenReport "R02PEOPLE", acPreview, , "[ID]=" & [ID]
End If

ExitHere:
Exit Sub

HandleErr:

Select Case Err
Case Else
MsgBox Err & ":" & Err.Description, , _
"Form_HouseholdInventory.Preview_Click"
End Select
Resume ExitHere
Resume
End Sub

 

[Lightwave]

Used the search command and found a thread called

"creating PDF's from Access 2003" which deals with the intricacies of attaching reports to e-mails.

Although it looks like you've managed to actually attach the pdf to e-mails its just the report is wrong and / or the number of e-mails it is attached to is wrong.

Try the above and see how you get on.

 

[g@v]

Lightwave...Thanks for your help...

I've managed to get it to work and email selected repair number using..

Private Sub Command28_Click()
On Error GoTo HandleErr

If IsNull(Me![Repair No]) Then
MsgBox "No Repair No Entered. . . . . Cancelling"
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenReport "Customer Returns", acPreview, , "[Repair No]=" & [Repair No]
DoCmd.SendObject acReport, "Customer Returns", "RichTextFormat(*.rtf)", "", "", "", "", "", False, ""

End If

ExitHere:
Exit Sub

HandleErr:

Select Case Err
Case Else
MsgBox Err & ":" & Err.Description, , _
"Form_HouseholdInventory.Preview_Click"
End Select
Resume ExitHere
Resume
End Sub

Only question i have is whats this?

"Form_HouseholdInventory.Preview_Click"

I';ve added this to email me just the current record...

DoCmd.SendObject acReport, "Customer Returns", "RichTextFormat(*.rtf)", "", "", "", "", "", False, ""

Although this doesnt copy over the image shall search for why it doesnt

Thanks again buddie

 

[Lightwave]

Sounds like you're almost there -

The HouseholdInventory item is error trapping reference from my database and would probably appear if you had an error in your database where the report was being produced. Guessing you might want to change it to something more relevant to your application chances are you'll never see it though.

Good luck with the next stages..