Using SendObject and capturing text from fields to add to outlook.

[mulchgirl]

I have a form with a Command button set up to email the current record/invoice as a report, and as an attached document. It works well. However, I have in the underlying invoices table, and email address field (which is also on the form) and I would like Access to automatically add that email address for that specific invoice to Outlook's "To:" field, just like it attaches the current invoice.
This is my current VBA code.

Private Sub Command55_Click()
 
    DoCmd.RunCommand acCmdSaveRecord
 
    DoCmd.OpenReport "rptINVOICES", acPreview, , "[ORDER ID]=Forms!frmQUOTES![ORDER ID]"
    DoCmd.SendObject acReport, "rptINVOICES", "PDFFormat(*.pdf)", "", "", "", "", "", True, ""
 
 
 
End Sub

Thanks ahead of time for your help.

[zmbd]

You would use the send object; however, select the email...
While in your VBE you can press [F1] and search, or follow this link:
>> DoCmd.SendObject Method (Access) Office 2010

As for your email, you will need to use either dlookup() and find the email that way, or, depending on your form you may be able to pull that information from your form controls


There is also outlook automation code which you may find more flexable.
Once again, you can find this information in the insights articles - go to the insight site map at the bottom of the page and take a look at #159

[mulchgirl]

I'm stumped, I'll apologize ahead of time if I should be getting this.

I'd like to return the Customer email field from the tblOrders table for the record that is currently being displayed in the frmInvoices form to my "To" portion of my SendObject. It looks like below. Currently, I am only getting the code in the parenthesis to appear in the To field of the email.

DoCmd.SendObject acReport, "rptINVOICES", "PDFFormat(*.pdf)", "[Customer Email]=Forms!frmQUOTES![ORDER ID]", "", "", "Mulch Quote", "Attached is a copy of your Zeager Quote or Order.  Thank you for contacting us.", True, ""

[zmbd]

In this case, I'm going to have to see much more of the code than you posted. Having just that single line doesn't let me how you're accessing the data from your form.

[mulchgirl]

Private Sub Command55_Click()
 
    DoCmd.RunCommand acCmdSaveRecord
 
    DoCmd.OpenReport "rptINVOICES", acPreview, , "[ORDER ID]=Forms!frmQUOTES![ORDER ID]"
    DoCmd.SendObject acReport, "rptINVOICES", "PDFFormat(*.pdf)", "[Customer Email]=Forms!frmQUOTES![ORDER ID]", "", "", "Mulch Quote", "Attached is a copy of your Zeager Quote or Order.  Thank you for contacting us.", True, ""
 
 
 
End Sub

Let me know if you need more information = Thanks zmbd for helping me numerous times - I feel like I'm learning a lot.

[zmbd]

I don't normally do this... however, I'm feeling particularly nice today:

PetPeeve: 1st, MG this is not your fault -
A large number of texts and etc show to use the commands while building the string inline with them (as you attempted).
HOWEVER, one cannot easily trouble shoot the resolved string.

In your case, it as I thought, you are not retrieving the value from your form. You are in fact sending:
"[ORDER ID]=Forms!frmQUOTES![ORDER ID]"
as your value instead of (say you're on ID # 42:
"[ORDER ID]=42"

So... take a look at how I would write your code:

(* Warning: This is "air" code, in that I've written this from memory! *)

Private Sub Command55_Click()
    Dim zStrReport As String
    Dim zStrSQL As String
    Dim zStrSendTo As String
    Dim zStrSubject As String
    Dim zStrMessage As String
    '
    'Set error trap:
    On Error GoTo zErrorTrap
    '
    'Make sure that any edits to the current record have been saved.
    DoCmd.RunCommand acCmdSaveRecord
    '
    'I would like to open the following report:
    zStrReport = "rptInvoices"
    zStrSQL = "[ORDER ID] = " & Forms!frmQUOTES![ORDER ID].value
    '
    '
    DoCmd.OpenReport ReportName:=zStrReport, AcView:=acNormal, WhereCondition:=zSQL
    '
    'Now let's send that in an email as a pdf
    zStrSendTo = "[e-mail]"
    zStrSubject = "Mulch Quote"
    zStrMessage = "Attached is a copy of your Zeager Quote or Order.  Thank you for contacting us."
    '
    DoCmd.SendObject ObjectType:=acSendReport, ObjectName:=zStrReport, OutputFormat:=acFormatPDF, TO:=zStrSendTo, Subject:=zStrSubject, Messagetext:=zStrMessage, EditMessage:=True
    '
    '
zExitFromError:
    exitsub
zErrorTrap:
'
'uncomment the Stop line for troubleshooting.
'Stop
    MsgBox "The following error occured while attempting to send: " & zStrReport & vbCrLf & _
        "TO: " & zStrSendTo & vbCrLf & _
        "Error Number: " & Err.Number & vbCrLf & _
        "Error Detail: " & Err.Description, vbCritical + vbOKOnly, "Send Invoice Error"
Resume zExitFromError
End Sub

If I want to see how that zStrSQL is resolving I can place a debug.print zStrSQL on a line right below it and then after running, or during if I get an error and have the stop uncommented.

Now I can't debug and compile this on my system as I don't have your form and database so there may be a syntax error...
What you should do is comment out your code, copy and paste mine in there and then do a debug compile.

You should take note of a few things... The TO and Message can be customized based on values from your customer table and so much more... (the how to will either be a dlookup against the customer table, or pulled from the form's record set depening on how you have things setup) AND you can debug.print these strings to just in case you're not getting what you expect!

I've been able to include your report name in three places by defining it once. If you want to change the report name you only need to change it in one place and a few other items.