How include a variable in a function code? (1 Viewer)

[Sergio]

Hello friends:

I have the following code to send a report. My problem is how to replace the email adress "sepaal@yahoo.com" in the code (line in red), with the email adress inside the report I'm sending in the preliminary view. In the report I'm sending the email adress is in the "[Mail]" field. Thank you in advance for your help.

Function Enviar_Recibo()
On Error GoTo Enviar_Recibo_Err

    Dim oApp As New Outlook.Application
    Dim oEmail As Outlook.MailItem
    
    Const sReport As String = "Recibo"
    Dim sOutput As String
    
    sOutput = Environ("Temp") & "\" & sReport & ".pdf"
    If Len(Dir(sOutput)) > 0 Then Kill sOutput
    
    DoCmd.OutputTo objecttype:=acOutputReport, _
                    objectname:=sReport, _
                    outputformat:=acFormatPDF, _
                    outputfile:=sOutput, _
                    outputquality:=acExportQualityPrint
                    
    Set oEmail = oApp.CreateItem(olMailItem)
        
   [B][COLOR=rgb(226, 80, 65)] oEmail.To = "[email]sepaal@yahoo.com[/email]"[/COLOR][/B]
    oEmail.Subject = "Recibo de pago "
    oEmail.Body = "Estimados padres de familia:" & vbCrLf & _
               " " & vbCrLf & _
               "Enviamos archivo adjunto con su último recibo de pago realizado." & vbCrLf & _
               " " & vbCrLf & _
               "Agradecemos su apoyo para continuar con nuestra labor educativa." & vbCrLf & _
               " " & vbCrLf & _
               "Para cualquier aclaración comuníquense con el área de Control Escolar de la escuela." & vbCrLf & _
               " " & vbCrLf & _
               "Muchas gracias."
    oEmail.Attachments.Add sOutput
    With oEmail
        If Not IsNull(.To) And Not IsNull(.Subject) And Not IsNull(.Body) Then
            .Send
            MsgBox "RECIBO ENVIADO"
        Else
            MsgBox "Indique el correo del destinatarios"
        End If
    End With
    
Enviar_Recibo_Exit:
    Exit Function
    
Enviar_Recibo_Err:
    MsgBox Error$
    Resume Enviar_Recibo_Exit
    
End Function

 

[Uncle Gizmo]

Assuming your Report is based on a table or query then that table or query will contain the email address. That's where you have to get it from, you cannot get it from the report.

 

[Sergio]

That´s correct. My Report is based on a Query. But I don't know the sintax to introduce the variable field in my code.

 

[Gasman]

You could set the email address to a global or TempVar variable, but as stated it would have to be before you run the report.

 

[Sergio]

That's the problem, I don't know how to define a global variable inside my code.

 

[Gasman]

That's the problem, I don't know how to define a global variable inside my code.

Well as it is a global variable, you can do it anywhere before you run that code.?
So look for where you can retrieve it, even if it is the result from a Dlookup().

 

[Pat Hartman]

Where is the email address stored? Is it in a control on the form? If it is, then

oEmail.To = Me.EmailAddress

If it is in a different table, use a DLookup()

oEmail.To = DLookup("emailAddress", "SomeTableORQuery", "some criteria expression")

 

[Sergio]

Thank you all of you folks. Especially Pat to remember me the right sintax for this thread.

Finally the right code line was:

oEmail.To = DLookup("Mail","RpteRecibo")

So easy, but my own programming ignorance didn't let me to move on.

Thank you.

 

[Pat Hartman]

Glad to help. rpterecibo is the name of your query? It looks like a report name?

 

[Sergio]

It looks like report name, but it's a query.

Thank you Pat.

 

[Isaac]

Thank you all of you folks. Especially Pat to remember me the right sintax for this thread.

Finally the right code line was:

oEmail.To = DLookup("Mail","RpteRecibo")

So easy, but my own programming ignorance didn't let me to move on.

Thank you.

Just remember that, that Dlookup only makes sense and is correct, if the table/query "rpterecipbo" only contains one single record.
If it contains multiple records, then you need to add criteria to the Dlookup().