Macro in Access2003. SendObject. Write the Subject with report data?

maybe, if you provide me the instructiones it is fine :)

waiting...

OK, right-click the macro and select "Save as".
On the popup form select for the option "As" the "Module" and press OK and [Convert] on the other popup.
Then go to the Modules tab where you find a module for the macro and double-click that to open the code.
Next past that code here so I can advise further.

Nic;o)

Option Compare Database

'------------------------------------------------------------
' Macro_Enviar_Reporte
'
'------------------------------------------------------------
Function Macro_Enviar_Reporte()
On Error GoTo Macro_Enviar_Reporte_Err

    DoCmd.OpenReport "Reporte_Orden_de_Compra_Proveedor", acViewPreview, "", "", acNormal
    DoCmd.PrintOut acPrintAll, 1, 1, acHigh, 1, True
    DoCmd.SendObject acReport, "Reporte_Orden_de_Compra_Proveedor", "SnapshotFormat(*.snp)", "", "rpino@hatch.cl; afigueroa@hatch.cl", "", "Adjunto ""Orden de Compra""", "", True, ""


Macro_Enviar_Reporte_Exit:
    Exit Function

Macro_Enviar_Reporte_Err:
    MsgBox Error$
    Resume Macro_Enviar_Reporte_Exit

End Function

Translation

"Reporte_Orden_de_Compra_Proveedor" this means Purchase Order provider report

OK, I'll have to assume the keyfield for your Purchase Order so ket's call it: PurchaseOrderID

Then the code can filter the report using:

    DoCmd.OpenReport "Reporte_Orden_de_Compra_Proveedor", acViewPreview, "", "[PurchaseOrderID]=" & Me.PurchaseOrderID, acNormal

For the subject we need then:

DoCmd.SendObject acReport, "Reporte_Orden_de_Compra_Proveedor", "SnapshotFormat(*.snp)", "", "rpino@hatch.cl; afigueroa@hatch.cl", "", "Adjunto ""Orden de Compra"":" & Me.PurchaseOrderID, "", True, ""

This requires the code to be "behind" the button on your form with the Purchase Order as the Me.PurchaseOrderID refers to a field with the ID on the form.
Just copy/paste all code between the Function and Function End in the code for the print button on your form and it will work OK.

Need more info ?

Nic;o)

i am sorry nico, but i do not have a button in the form.

OK, then we need to create a button on the form.
Just select a button from the toolbar on the lefthand side and follow the wizard, chosing a button to trigger a report.
When finished you can select the button with a single click and open the properties popup form.
Under the Events tab you'll see the OnClick event and when you place the cursor in the white field after that a [...] button will appear at the end allowing you to open the code Access generated for the event.
Just replace all code between the "Sub" and "End Sub" commands with your above code.

Clear ?

Nic;o)

hey nico i have created a button in the report (design view), but i just have properties for it, i dont have where to add the code.

are you still available to answer?

Yep, but I stated: "we need to create a button on the form."

Nic;o)

i cannt find this:

[Quote nico]
Just select a button from the toolbar on the lefthand side and follow the wizard, chosing a button to trigger a report.
[/Quote]

what wizard, what button?

i have already inseted a button (commandButton) from the toolbox bar, but no wizard found :(

When you place a buton a wizard should be activated allowing you to chose different options from a menu.
When the wizard doesn't start you need to click the Wizard button in the toolbox so it looks "pressed".
It has a magicians stick and is just below the Arrow button.

Nic;o)

nico i understand what was happening!

i dont have any "Form" (where you can add buttons with a wizard)

i am working with Tables, Reports and Macros (this does everything).

i execute the send mail function through the SendObject in Macro (Design View), see the following:

i have a link (shortcut) in my hard drive wich is pointiing to a Macro wich has the associated funtions "OpenReport" (Print Preview, Normal), PrintOut (page 1, redirected to a PDF printer), SendObject (Report, Report_Name, Snapshot Format, CC myEmail@myDomain.cl, Subject "mySubject")<-----here is the problem i want to add the "Purchase Order" Number (numero_orden)

i expect you can better understand me now.

hope this help.

Not sure or it will work with macro's as I don't use them for their inflexibility.
Try to change:
Subject "mySubject"
into:
Subject "mySubject " & Purchase Order: " & Dlookup("numero_orden","orden","????")
The "numero_orden" must be the fieldname, the "orden" needs to be the tablename, the "????" needs to be the so-called WHERE clause to detect the number asked for.

The best way to "gain control" is however a simple form where the "numero_orden" can be selected before activating the report. A macro can easily be changed into VBA by right-clicking it and use the SaveAs with [Module] as type.

Nic;o)

everything is going fine, but the where clause fails. if i aviod using where it works but the where clause is a little bit complex.

this is the working code for the form (i created a form (frmUltimaOrden) with a label (Label1)), the label is correctly asigning with the last Purchase Order number:

SELECT numero_orden
FROM orden_compra
WHERE (((orden_compra.numero_orden)in (Select max(numero_orden)from orden_compra)));

now, in the module i try to do the following:

Function Macro_Enviar_Reporte()

Dim varX As Variant
'varX = DLookup("[numero_orden]", "orden_compra") 'this works. No 'Where' clause
varX = DLookup("[numero_orden]", "orden_compra", [orden_compra] = " & Forms![frmUltimaOrden]![Label1.Caption]) this is not working!!!

the error displayed when i try to use the form is:

"Microsoft Acces can't find the form 'frmUltimaOrden' referref in a macro expresion or a visual basic code", please note that i copy/paste the bane if the form.

in brief, i need to get the last "purchase order" number generated and put it in 'varX' or any variable.

hope this helps.

Try:
varX = DLookup("[numero_orden]", "orden_compra", [orden_compra] = '" & Forms![frmUltimaOrden]![Label1.Caption] & "'")

Nic;o)

it displays an error:

Compile error: Syntax error

:(

OK, try:

varX = DLookup("[numero_orden]", "orden_compra", [orden_compra] = '" & Forms![frmUltimaOrden]![Label1].[Caption] & "'")
Or when the orden_compra is a number:
varX = DLookup("[numero_orden]", "orden_compra", [orden_compra] = " & Forms![frmUltimaOrden]![Label1].[Caption] )

Nic;o)

finally everything failed, though i made it work with the following workaround so i decide to share it to everybody learns (that is the way we help to build a better world :)

i create a macro with an action RunCode to call a module with a funtion called "Macro_Enviar_Reporte ()", here is the module code:

Function Macro_Enviar_Reporte()
On Error GoTo Macro_Enviar_Reporte_Err

    Dim Rst As DAO.Recordset
    Dim ultima_orden As String
   
        Set Rst = CurrentDb.OpenRecordset("Select numero_orden FROM orden_compra Where  (((orden_compra.numero_orden)in (Select max(numero_orden)from orden_compra)))")
        If Rst.NoMatch Then
            'MsgBox "No hay Registros según criterio"
        Else
            'MsgBox "El nombre de la ultima orden es: " & Rst("numero_orden")
            ultima_orden = Rst("numero_orden")
        End If
        Rst.Close
        Set Rst = Nothing

    DoCmd.OpenReport "Reporte_Orden_de_Compra_Proveedor", acViewPreview, "", "", acNormal
    DoCmd.Maximize
    DoCmd.RunCommand acCmdZoom100
    'pregunta = MsgBox("Desea revisar la Orden de Compra?", vbYesNo, "Revisión Orden de Compra")
    'If pregunta <> 6 Then
        DoCmd.PrintOut acPrintAll, 1, 1, acHigh, 1, True
        DoCmd.SendObject acReport, "Reporte_Orden_de_Compra_Proveedor", "SnapshotFormat(*.snp)", "", "afigueroa@hatch.cl; rpino@hatch.cl", "", "Adjunto Orden de Compra N° " & ultima_orden, "", True, ""
        DoCmd.RunCommand acCmdExit
    'End If

Macro_Enviar_Reporte_Exit:
    Exit Function

Macro_Enviar_Reporte_Err:
    MsgBox Error$
    Resume Macro_Enviar_Reporte_Exit

End Function

hope this help!

so the points will go to Nic;o because of his patiently work with me!

thanks a lot Nic;o, you really help me!

honestly,

Oscar Garin.