email to a cleint email address

[colkas]

Hi
I want to email a quotation report based on a survey type I have this code.....

Private Sub Command103_Click()
Dim StDocName As String
If MsgBox("This will send Quote to your Client. Continue?", _
vbExclamation + vbYesNoCancel) = vbYes Then
StDocName = "quotedem"
DoCmd.SendObject acSendReport, Combo38.Column(2), acFormatPDF, , "type = '" & Combo38.Column(1) & "' AND IDquote = " & idquote
End If
Set rs = Nothing
End Sub

This selects the correct survey type and creates the correct document, it also goes to email. My problem is I want it to insert the email address of the client in the TO: part.

I have the email address in a table called tblcleint.clientemail and this is linked to the quote.
Any ideas how I can change the code so it can get the correct email address and it appear in the TO:
Thanks

 

[CEH]

If the email address is visible in the form... (if not make a textbox for it drawing off your table) then just insert the textbox after your format....
DoCmd.SendObject acSendReport, Combo38.Column(2), acFormatPDF,Me.EmailTO, "type = '" & Combo38.Column(1) & "' AND IDquote = " & idquote

"EmailTo " being the name of your textbox.

 

[colkas]

Hi CEH

Thanks for the reply. I have made the field part of the combo box relating to the clients information.... It is called cboclientid and the field name is clientemail. I have tried adding in code into the area where the TO: should be to draw the information from the client information in the combo box, but I just cant get it to work.... I ahve played with the coding but tend to get debug errors on that line..... have you any ideas how it should be doded for the TO: bit Thanks


Private Sub Command103_Click()
Dim StDocName As String
If MsgBox("This will send Quote to your Client. Continue?", _
vbExclamation + vbYesNoCancel) = vbYes Then
StDocName = "quotedem"
DoCmd.SendObject acSendReport, Combo38.Column(2), acFormatPDF, cboclientid.Column(3), , "type = '" & Combo38.Column(1) & "' AND IDquote = " & idquote
End If
Set rs = Nothing
End Sub

 

[ted.martin]

You could also refer to the Post I just made in the Macros section of this forum.

http://www.access-programmers.co.uk/forums/showthread.php?t=208462

 

[colkas]

Hi

Thanks for the replies, Ted not wuite what I am loking for but thanks. Ok this is where i ahve got to

If I preview the report I use this code and it brings just the one report with the correct quote information.

StDocName = "quotedem"
DoCmd.OpenReport Combo38.Column(2), acPreview, , "type = '" & Combo38.Column(1) & "' AND IDquote = " & idquote

I have made the email address visible in the cboclientid combo box and adjusted my email code as follows


StDocName = "quotedem"
DoCmd.SendObject acSendReport, Combo38.Column(2), acFormatPDF, cboQclientid.Column(3), , , "Asbestos Surveyors UK Quotation", , , "type = '" & Combo38.Column(1) & "' AND IDquote = " & idquote
This now works EXCEPT, it now makes the report for all quotes with that type of survey type, so for example we have 2 quotes with survey type Combination, so it creates the PDF attachment and then it as 2 full reports (as one). I only need the quote it relates to on the screen (I am mailing by button on the screen)...

Any ideas on what is incorrect on the code....


Thanks

 

[ted.martin]

When the SendObject run it essentially 'reprint' the report. Therefore, when you need to have a Filter in the Open Event of the report. Something like this where lngNo is your Quotation number and has been 'set' before your run the SendObject routine as you cannot Filter within the SendObject function (as you would do with a Form or Report)

Private Sub Report_Open(Cancel As Integer)

        Me.Filter = "QuoteNo = " & lngNo
        Me.FilterOn = True

End Sub

 

[colkas]

Hi Ted

Sorrry dont quite follow....

I ahve added this to the report

Private Sub Report_Open(Cancel As Integer)
Me.Filter = "idquote = " & idquote
Me.FilterOn = True
End Sub


But I am getting a debug error message on the Line Me.Filter = "idquote = " & idquote

The field for the quote is idquote

Any ideas thanks

 

[ted.martin]

idQuote cannot be a control and a variable.

Something like this is better where lngidQuote is a (Long) Public Variable

Me.Filter = "idquote.value = " & lngidquote

 

[colkas]

Hi Ted

I now have thiscode in the open event

Private Sub Report_Open(Cancel As Integer)
Me.Filter = "idquote.value = " & lngidquote
Me.FilterOn = True
End Sub

However when i try and run the report now from the quote form I am getitng this message

Run time error 3075
Extra ) in qury expression '(idquote.value=)'

Any ideas

Thanks

 

[ted.martin]

Why are you using brackets - you don't need them.

My examplar was Me.Filter = "idquote.value = " & lngidquote

 

[colkas]

Hi Ted

I dont know what you mean using brackets.. this is my exact code in the Open event of the report....................

Private Sub Report_Open(Cancel As Integer)
Me.Filter = "idquote.value = " & Ingidquote
Me.FilterOn = True
End Sub

When i run the report from the Quote form I then get the
Run time error 3075
Extra ) in qury expression '(idquote.value=)'

Any ideas please

 

[ted.martin]

is your field idquote.value an integer or string?

 

[colkas]

Hi

The field idquote is an integer.

Thanks

 

[colkas]

Hi

Just to let you all know I finally solved this issue..... What I did was go into the report and then go into the record Source and then highlight the criteria section for Quoteid.
I then right mouse clicked and went to build, and drilled through to the formquote and selected IDquote

I then ran the report worked ok and then emailed it and it selects the quote on the screen an no others...

Phewww finally got there and thanks to all for your replies and help