Replies

Hello

I selected the Microsoft Excel 15.0 Object Library….and it did the TRICK

I’m now able to open the report based on filter criteria which I was assigning to the variable zWhere.

I THANK YOU for your efforts, help and support

Hello HTH

I appreciate your prompt response, I tried your suggestion but still the same issue, it prompt for inputbox.

Customer is a text field containing business names only.

Wasijk,

Welcome to the Lounge as a new poster! :cheers:

Try it this way:

Code:

            zWhere = "[Customer] = " &  rst![Customer]  '*** If Customer is a number use Str(rst![Customer])
            
                'Assign filter to the report using variable
            DoCmd.OpenReport zDocname, acViewPreview, ,  zWhere, acWindowNormal

HTH :cheers:

Hello Team

I’m trying to sendan email using your codes, with attachment of XL or PDF, the issue I’m having is when passingvalues using a variable to filter my report or query, it prompt toinput the values!
I have tried it ondifferent machines with different windows and access versions;
Windows 10, Access2013
Windows 7, Access2010
Windows 7, Access2007
Here are the codes after making appropriate changes to work with my db,I tried using docmd with my variable as filter, where “Order_rpt” isreport name, “zWhere” is the criteria/filter, it prompt an inbox toenter value for zWhere!
zWhere is hasstring value (Customer name)

Code:

Public Sub Daily_eml_rpt()

    Dim dbName As Database
    Dim rst As Recordset
    Dim lRecNo As Long
    Dim leMlCnt As Long
    Dim zWhere As String
    Dim zMsgBody As String
    Dim zEmail As String
    Dim zSubject As String
    Dim zDocname As String
    
    
        'Asssign Report Name to variable
    zDocname = "Order_Status_rpt"
    
        'Opens Current database
    
    Set dbName = CurrentDb()
        
        'Opens Recordset/Table
    Set rst = dbName.OpenRecordset("Customer_tbl", dbOpenDynaset)
    rst.MoveFirst


    leMlCnt = 0
  
        'Looping through the recordst to assign the customer name to variable
    Do While Not rst.EOF


        If rst![email] = "" Then
            MsgBox "No Data is available for selected date", vbOKOnly, "Daily Order Status"
        Else
            zWhere = rst![Customer]
            
                'Assign filter to the report using variable
            DoCmd.OpenReport zDocname, acViewPreview, , "[Customer] = " & zWhere, acWindowNormal
            
            
            zEmail = rst![email]
            zSubject = "WPOA Annual Dues Statement: " & rst![Customer]
            zMsgBody = "Hi " & rst![Customer] & vbCrLf & "Please find your Today's Order Status attached."
            DoCmd.SendObject acReport, zDocname, acFormatPDF, zEmail, , , zSubject, zMsgBody, True
            DoCmd.Close acReport, zDocname, acSaveNo
            leMlCnt = leMlCnt + 1  '*** Count Emails Created ***
        End If


        rst.MoveNext        '*** Move to Next Record ***
    Loop


    MsgBox Format(lBillCnt, "#,###") & " Email Messages Created.", , "Customer Emails"
    Set rst = Nothing     '*** Close RecordSet ***
    
End Sub