×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

run-time error 2585 - action can't be carried out - while using DoCmd.SendObject acSendReport

run-time error 2585 - action can't be carried out - while using DoCmd.SendObject acSendReport

run-time error 2585 - action can't be carried out - while using DoCmd.SendObject acSendReport

(OP)
Dear All,

Please help.

I have been trying to test if a field in my report is EMAIL and then if so send that report to the referenced email address.

But I get the error:

Quote (Run-time error 2585:)


This action can't be carried out while processing a form or report event.

Fields
CusConMeth ( used for the contact type i.e. EMAIL )
CusConDet ( used for the storing of the contact type detail, i.e. the actual email address )


Report name: LdParkingSPDAdoptLett

Below is the code I am using on the On Load Event

CODE --> vb

Private Sub Report_Load()

    If CusConMeth = "EMAIL" Then
        
        DoCmd.SendObject acSendReport, "LdParkingSPDAdoptLett", acFormatPDF, CustConDet, , , "TEST"
            
    End If

End Sub 
Please help sadeyes
Thanks.

Thank you,

Kind regards

Triacona

RE: run-time error 2585 - action can't be carried out - while using DoCmd.SendObject acSendReport

I would first try adding DoEvents before calling the code. If that does not work try moving the code further down the event chain.
Open > Load > Resize > Activate > Current

RE: run-time error 2585 - action can't be carried out - while using DoCmd.SendObject acSendReport

(OP)
Thanks for your help.
How would I call do events, I tried moving it down, but it will not work.

Thank you,

Kind regards

Triacona

RE: run-time error 2585 - action can't be carried out - while using DoCmd.SendObject acSendReport

Simply put the line of code

CODE -->

DoEvents 
This gives control back to the processor to complete queued events.
So if there is a form event that needs to be processed it will process it first before the next line of code

CODE -->

DoEvents
If CusConMeth = "EMAIL" Then
        DoCmd.SendObject acSendReport, "LdParkingSPDAdoptLett", acFormatPDF, CustConDet, , , "TEST"
End If 

RE: run-time error 2585 - action can't be carried out - while using DoCmd.SendObject acSendReport

(OP)
Thanks MajP smile
I tried entering DoEnvents and it still gives me the same error.

CODE --> vb

Option Compare Database

Private Sub Report_Load()
DoEvents
    If CusConMeth = "EMAIL" Then
            
        DoCmd.SendObject acSendReport, "LdParkingSPDAdoptLettEmail", acFormatPDF, CustConDet, , , "Parking Standards Supplementary Planning Document", " Dear Sir/Madam, Please see attached. Yours sincerely Ernest Amoako"
            
    End If


End Sub 
Thanks for all your help

Thank you,

Kind regards

Triacona

RE: run-time error 2585 - action can't be carried out - while using DoCmd.SendObject acSendReport

(OP)
Hi MajP,

I have wrote a Function to send the report to email.
That seems to run.
How would I do the following.
1. In function code, reference the field names I need to check?
2. Only send the 2 pages relevant to the customer to that customer?
This is the Function I have so far:

CODE --> Function

Public Function EmailReport(stDocName As String)
On Error Resume Next
    
    'DoCmd.OpenReport stDocName
    'DoCmd.OutputTo acOutputReport, stDocName, acFormatPDF, , True, , , acExportQualityPrint
    'DoCmd.OutputTo acOutputReport, stDocName, acFormatPDF, , True
    DoCmd.SendObject acSendReport, "LdParkingSPDAdoptLettEmail", acFormatPDF, "etienne.pienaar@woking.gov.uk", , , "Parking Standards Supplementary Planning Document", " Dear Sir/Madam, Please see attached. Yours sincerely Ernest Amoako"

End Function 
If I run this it opens an Outlook message with the attachment.
How do I prevent that?
And as per no. 1 how do I then loop through each customer ( 6000 ) and send each of them a separate email with the section of the report, only relevant to them?

Thanks again for all your help thumbsup

Thank you,

Kind regards

Triacona

RE: run-time error 2585 - action can't be carried out - while using DoCmd.SendObject acSendReport

You need to loop the primary keys and emails addresses from some query that contains all the names in your report. Then you open a filtered report and send the report. Close the report once you send it and on to the next user.

Here is an example where I pull each distinct order ID from a report and send it as an email. You would do the same with the user PK.

CODE -->

Public Sub OpenSend()
  Dim rptName As String
  Dim emailAddress As String
  Dim rs As DAO.Recordset
  Dim ID As Long
  Dim recordSource As String
  Dim strSql As String
  
  rptName = "order summary"
  'Get the distinct list of things to send
  strSql = "Select Distinct [Order ID] from [Order Summary]"
  
  Set rs = CurrentDb.OpenRecordset(strSql)
  Do While Not rs.EOF
    'open a filtered report
    ID = rs![Order ID]
    'EmailAddress = RS!EmailAddress 
    DoCmd.OpenReport rptName, acViewPreview, , "[order ID] = " & ID  ' in your case you would be filtering on a persons PK
    EmailReport rptName, emailAddress
    DoCmd.Close acReport, rptName
    rs.MoveNext
  Loop
End Sub 

CODE -->

Public Function EmailReport(stDocName As String, emailAddress As String)
On Error Resume Next
        DoCmd.SendObject acSendReport, stDocName, acFormatPDF, emailAddress, , , "Test", "Hi There"
End Function 

RE: run-time error 2585 - action can't be carried out - while using DoCmd.SendObject acSendReport

However, the above will give you a prompt. I do not think there is a way using outlook even with outlook automation you will not get a prompt. You can ask around on the VBA forum about this. I do not do a lot of this, so not an authority. From a quick read the solution may be to use CDO.
https://www.rondebruin.nl/win/s1/cdo.htm.
However, then the code I provided cannot use the sendobject method.
I think instead you would have to open and save the report into a folder. Then use CDO to send the report as an attachment.

So you would do the same thing of looping the distinct customers, and save their report. Then you would use CDO to attach that report and send it using the name and path you save it as.

Here is an example of saving parts of a report to individual PDFs.

CODE -->

Public Sub LoopCustomers()
  Dim rs As DAO.Recordset
  Dim strSql As String
  Dim companyName As String
  Const qryName = "qryCustomers"
  Const fieldName = "Company Name"
  
  strSql = "SELECT DISTINCT [" & fieldName & "] FROM " & qryName
  Debug.Print strSql
  Set rs = CurrentDb.OpenRecordset(strSql)
  Do While Not rs.EOF
    companyName = rs.Fields(fieldName)
    CreateCustomerReports (companyName)
    ' Now that report is saved use CDO to attach the report and send it
    'https://www.rondebruin.nl/win/s1/cdo.htm.
    'to add attachment imsg.AddAttachment "file path and name" from below code
    rs.MoveNext
  Loop
  
End Sub

Public Sub CreateCustomerReports(companyName As String)
  Const rptName = "rptCustomers"
  Const strPath = "C:\"
  Const fieldName = "Company Name"
  Dim pathAndFile As String
  companyName = Replace(companyName, "'", "''")
  DoCmd.OpenReport rptName, acViewPreview, , "[" & fieldName & "] = '" & companyName & "'"
  companyName = Replace(companyName, " ", "_")
  pathAndFile = strPath & companyName & ".pdf"
  Debug.Print pathAndFile
  DoCmd.OutputTo acOutputReport, rptName, acFormatPDF, pathAndFile
  DoCmd.Close acReport, rptName
End Sub 

RE: run-time error 2585 - action can't be carried out - while using DoCmd.SendObject acSendReport

I tried the CDO code and got the message "The transport failed to connect to the server".

RE: run-time error 2585 - action can't be carried out - while using DoCmd.SendObject acSendReport

(OP)
Thanks so much for all your help bigsmile
I will try these solutions and let you know how I get on.
I have tried the following code, I don't get an error, but it seems to freeze.

CODE --> VB

Public Function OpenSend()
  Dim rptName As String
  Dim emailAddress As String
  Dim rs As DAO.Recordset
  Dim ID As Long
  Dim recordSource As String
  Dim strSql As String
  
  rptName = "LdParkingSPDAdoptLettEmail"
  'Get the distinct list of things to send
  strSql = "SELECT UNI7LIVE_LDCUS.CUSREF AS CusRef, " & _
            "UNI7LIVE_LDCUS.CUSNAME AS CustomerName, " & _
            "UNI7LIVE_LDCUS.DEPT AS CustomerDept, " & _
            "UNI7LIVE_LDCUS.CUSALIAS AS CusAlias, " & _
            "Carriage([UNI7LIVE_LDCUS].[ADDRESS]) AS CusAddress, " & _
            "UNI7LIVE_LDCUS.ADDRESS, " & _
            "UNI7LIVE_LDCUSCON.LDCONMETH AS CusConMeth, " & _
            "UNI7LIVE_LDCUSCON.CONVAL AS CustConDet, " & _
            "UNI7LIVE_LDCUSCON.PREFERRED AS CusConPreffered, " & _
            "UNI7LIVE_LDCUSAP.LDAPTYPE AS ApType, " & _
            "UNI7LIVE_LDCUSAP.DEPT AS ApDept, UNI7LIVE_LDCUSAP.ORG AS ApOrg, " & _
            "UNI7LIVE_LDCUSAP.ADDRESS AS ApAdd, " & _
            "UNI7LIVE_LDCUSAPCON.LDCONMETH AS ApConMeth, " & _
            "UNI7LIVE_LDCUSAPCON.CONVAL AS ApConDet, " & _
            "UNI7LIVE_LDCUSAPCON.PREFERRED AS ApPref " & _
            "FROM ((UNI7LIVE_LDCUS LEFT JOIN UNI7LIVE_LDCUSAP ON UNI7LIVE_LDCUS.KEYVAL = UNI7LIVE_LDCUSAP.PKEYVAL) " & _
            "LEFT JOIN UNI7LIVE_LDCUSCON ON UNI7LIVE_LDCUS.KEYVAL = UNI7LIVE_LDCUSCON.PKEYVAL) " & _
            "LEFT JOIN UNI7LIVE_LDCUSAPCON ON UNI7LIVE_LDCUSAP.KEYVAL = UNI7LIVE_LDCUSAPCON.PKEYVAL " & _
            "WHERE (((UNI7LIVE_LDCUS.CUSNAME) Like 'Etienne*') AND ((UNI7LIVE_LDCUS.ADDRESS)='Unknown' Or (UNI7LIVE_LDCUS.ADDRESS) Is Null) AND ((UNI7LIVE_LDCUSCON.PREFERRED)='T')) " & _
            "ORDER BY UNI7LIVE_LDCUS.CUSREF "
  
  Set rs = CurrentDb.OpenRecordset(strSql)
  Do While Not rs.EOF
    'open a filtered report
    ID = rs!CusRef
    emailAddress = rs!CustConDet
    DoCmd.OpenReport rptName, acViewPreview ' in your case you would be filtering on a persons PK
    EmailReport rptName, emailAddress
    DoCmd.Close acReport, rptName
    rs.MoveNext
  Loop
End Function

Public Function EmailReport(stDocName As String, emailAddress As String)
On Error Resume Next
    
  DoCmd.SendObject acSendReport, stDocName, acFormatPDF, emailAddress, , , "Parking Standards Supplementary Planning Document", " Dear Sir/Madam, " & _
  "Please see attached, which refers to the Parking Standards Supplementary Planning Document." & _
  "Yours sincerely " & _
  "Ernest Amoako"
    
  
End Function
 

Thank you,

Kind regards

Triacona

RE: run-time error 2585 - action can't be carried out - while using DoCmd.SendObject acSendReport

I would make it easier. Your query to loop should only contain the customer ID (cusRef) and the email address fields that are contained in that report. No need for any other fields and all of those other joins, because you are looping that query just to filter the report. It should have a distinct clause if necessary so you only get one record per customer. I would save that as stored query. Assume I save this query as "qryCustomersAndEmail". You code is a lot simpler to check because you can validate the query. I have no idea if that unnecessary sql string is correct. Instead.
strSql = "qryCustomersAndEmail"

RE: run-time error 2585 - action can't be carried out - while using DoCmd.SendObject acSendReport

(OP)
Hi MajP,
Thanks again for all your help smile
I have got it not to hand with the following code:

CODE --> VB

Public Function OpenSend()
  Dim rptName As String
  Dim emailAddress As String
  Dim rs As DAO.Recordset
  Dim ID As Long
  Dim recordSource As String
  Dim strSql As String
  
  rptName = "LdParkingSPDAdoptLettEmail"
  'Get the distinct list of things to send
  strSql = "SELECT UNI7LIVE_LDCUS.CUSREF AS CusRef, " & _
            "UNI7LIVE_LDCUS.CUSNAME AS CustomerName, " & _
            "UNI7LIVE_LDCUS.DEPT AS CustomerDept, " & _
            "UNI7LIVE_LDCUS.CUSALIAS AS CusAlias, " & _
            "Carriage([UNI7LIVE_LDCUS].[ADDRESS]) AS CusAddress, " & _
            "UNI7LIVE_LDCUS.ADDRESS, " & _
            "UNI7LIVE_LDCUSCON.LDCONMETH AS CusConMeth, " & _
            "UNI7LIVE_LDCUSCON.CONVAL AS CustConDet, " & _
            "UNI7LIVE_LDCUSCON.PREFERRED AS CusConPreffered, " & _
            "UNI7LIVE_LDCUSAP.LDAPTYPE AS ApType, " & _
            "UNI7LIVE_LDCUSAP.DEPT AS ApDept, UNI7LIVE_LDCUSAP.ORG AS ApOrg, " & _
            "UNI7LIVE_LDCUSAP.ADDRESS AS ApAdd, " & _
            "UNI7LIVE_LDCUSAPCON.LDCONMETH AS ApConMeth, " & _
            "UNI7LIVE_LDCUSAPCON.CONVAL AS ApConDet, " & _
            "UNI7LIVE_LDCUSAPCON.PREFERRED AS ApPref " & _
            "FROM ((UNI7LIVE_LDCUS LEFT JOIN UNI7LIVE_LDCUSAP ON UNI7LIVE_LDCUS.KEYVAL = UNI7LIVE_LDCUSAP.PKEYVAL) " & _
            "LEFT JOIN UNI7LIVE_LDCUSCON ON UNI7LIVE_LDCUS.KEYVAL = UNI7LIVE_LDCUSCON.PKEYVAL) " & _
            "LEFT JOIN UNI7LIVE_LDCUSAPCON ON UNI7LIVE_LDCUSAP.KEYVAL = UNI7LIVE_LDCUSAPCON.PKEYVAL " & _
            "WHERE (((UNI7LIVE_LDCUS.CUSNAME) Like 'Etienne*') AND ((UNI7LIVE_LDCUS.ADDRESS)='Unknown' Or (UNI7LIVE_LDCUS.ADDRESS) Is Null) AND ((UNI7LIVE_LDCUSCON.PREFERRED)='T')) " & _
            "ORDER BY UNI7LIVE_LDCUS.CUSREF "
  
  Set rs = CurrentDb.OpenRecordset(strSql)
  Do While Not rs.EOF
    'open a filtered report
    ID = rs!CusRef
    emailAddress = rs!CustConDet
    DoCmd.OpenReport rptName, acViewPreview, , CusRef = ID ' in your case you would be filtering on a persons PK
    EmailReport rptName, emailAddress
    DoCmd.Close acReport, rptName
    rs.MoveNext
  Loop
End Function

Public Function EmailReport(stDocName As String, emailAddress As String)
On Error Resume Next
    
  DoCmd.SendObject acSendReport, stDocName, acFormatPDF, emailAddress, , , "Parking Standards Supplementary Planning Document", " Dear Sir/Madam, Chr(13) " & _
  "Please see attached, which refers to the Parking Standards Supplementary Planning Document. Chr(13)" & _
  "Yours sincerely Chr(13)" & _
  "Ernest Amoako"
    
  
End Function
 
But it still opens a dialogue box.
I will endeavour to checkout the other info you have provided.
Thanks again bigsmile

Thank you,

Kind regards

Triacona

RE: run-time error 2585 - action can't be carried out - while using DoCmd.SendObject acSendReport

(OP)
Hi MajP,
It does not seem to work completely.
What I mean by this is that when it generates email with the attachment of the report, the email address is correct, but there is no correlation between that and the report.
So if I have customer a, customer a's details are not appearing on the report.
the OpenSend function, where does that need to be?
it seems the WHERE condition in the DoCmd.OpenReport, does not seem to be linking the 2.
Thanks for all you help on this thumbsup

Thank you,

Kind regards

Triacona

RE: run-time error 2585 - action can't be carried out - while using DoCmd.SendObject acSendReport

Not sure that code would even run. Your where statement is incorrect

CODE -->

DoCmd.OpenReport rptName, acViewPreview, , CusRef = ID 
should be

CODE -->

DoCmd.OpenReport rptName, acViewPreview, , "CusRef = " & ID 

That should resolve to a string something like "CusRef = 1234"
I think how you wrote it may resolve to a value of False because it assumes CusRef is a variable and not equal to ID.
Make sure you use "Option Explicit" at the top of all code modules. You can google this.
Also if ID was text and not numeric the proper form is

CODE -->

DoCmd.OpenReport rptName, acViewPreview, , "CusRef = '" & ID & "'" 
which resolves to a string like "CusRef = 'Abc123'"

RE: run-time error 2585 - action can't be carried out - while using DoCmd.SendObject acSendReport

(OP)
Dear MajP,
Thanks for your reply smile
I am using Option Compare Database
I currently have the following code:

CODE --> VB

Public Function OpenSend()
  Dim rptName As String
  Dim emailAddress As String
  Dim rs As DAO.Recordset
  Dim ID As String
  Dim recordSource As String
  Dim strSql As String
  
  rptName = "LdParkingSPDAdoptLettEmail"
  'Get the distinct list of things to send
  strSql = "SELECT DISTINCT UNI7LIVE_LDCUS.CUSREF AS CusRef, " & _
            "UNI7LIVE_LDCUS.CUSNAME AS CustomerName, " & _
            "UNI7LIVE_LDCUS.DEPT AS CustomerDept, " & _
            "UNI7LIVE_LDCUS.CUSALIAS AS CusAlias, " & _
            "UNI7LIVE_LDCUS.ADDRESS, " & _
            "UNI7LIVE_LDCUSCON.LDCONMETH AS CusConMeth, " & _
            "UNI7LIVE_LDCUSCON.CONVAL AS CustConDet, " & _
            "UNI7LIVE_LDCUSCON.PREFERRED AS CusConPreffered, " & _
            "UNI7LIVE_LDCUSAP.LDAPTYPE AS ApType, " & _
            "UNI7LIVE_LDCUSAP.DEPT AS ApDept, UNI7LIVE_LDCUSAP.ORG AS ApOrg, " & _
            "UNI7LIVE_LDCUSAP.ADDRESS AS ApAdd, " & _
            "UNI7LIVE_LDCUSAPCON.LDCONMETH AS ApConMeth, " & _
            "UNI7LIVE_LDCUSAPCON.CONVAL AS ApConDet, " & _
            "UNI7LIVE_LDCUSAPCON.PREFERRED AS ApPref " & _
            "FROM ((UNI7LIVE_LDCUS LEFT JOIN UNI7LIVE_LDCUSAP ON UNI7LIVE_LDCUS.KEYVAL = UNI7LIVE_LDCUSAP.PKEYVAL) " & _
            "LEFT JOIN UNI7LIVE_LDCUSCON ON UNI7LIVE_LDCUS.KEYVAL = UNI7LIVE_LDCUSCON.PKEYVAL) " & _
            "LEFT JOIN UNI7LIVE_LDCUSAPCON ON UNI7LIVE_LDCUSAP.KEYVAL = UNI7LIVE_LDCUSAPCON.PKEYVAL " & _
            "WHERE (((UNI7LIVE_LDCUS.CUSNAME) Not Like 'Etienne*') AND ((UNI7LIVE_LDCUS.ADDRESS)='Unknown' Or (UNI7LIVE_LDCUS.ADDRESS) Is Null) AND ((UNI7LIVE_LDCUSCON.PREFERRED)='T')) " & _
            "ORDER BY UNI7LIVE_LDCUS.CUSREF "
  
  Set rs = CurrentDb.OpenRecordset(strSql)
  Do While Not rs.EOF
    'open a filtered report
    ID = rs!CusRef
    emailAddress = rs!CustConDet
    DoCmd.OpenReport rptName, acViewPreview, , rs!CusRef = ID  ' in your case you would be filtering on a persons PK"
    EmailReport rptName, emailAddress
    DoCmd.Close acReport, rptName
    rs.MoveNext
  Loop
End Function 
The part rs!CusRef = ID seems to work, in part, as it for each email recipient, sends the entire report with 6000 letters, each with a customer name and address ( all of )
Am I right in assuming rs is record set?
Therefore rs!CusRef is calling the field generated in the above sql and then matching that to the report's CusRef?
Or should I link the report directly, rather than use my own sql?
This is the only frustrating part, as I would like it linked to each recipient linked to only their letter.
If I set it "CusRef = " & ID ( as CusRef is a text )

Thanks again for all your help.

Thank you,

Kind regards

Triacona

RE: run-time error 2585 - action can't be carried out - while using DoCmd.SendObject acSendReport

(OP)
Hi MajP,

Your instruction on the code was spot on !! 2thumbsup

CODE --> VB

"CusRef = '" & ID & "'" 
Is this then from the report that the CusRef value is found?
Also why is the entire thing in string mode?
How do I add an exit to the loop, for example I want to cancel the outputting action?
Thanks for all your help you are a star! smile

Thank you,

Kind regards

Triacona

RE: run-time error 2585 - action can't be carried out - while using DoCmd.SendObject acSendReport

Quote:

Also why is the entire thing in string mode?

When you open a form or report you can pass in a "where" clause to tell it what records to open. Basically you are providing part of the SQL string. It looks like a SQL Where clause without the word "where". So if you had a field "firstName" and you wanted it to open to all of your Mikes then you would pass in the string "FirstName = 'Mike'". If you wanted it to open to the record with a numeric UserID of 1234 you pass in the string "UserID = 1234". Any legit SQL where clause will work as long as those fields are included in the recordsource of the report or form. "UnitCost > 5", "[Motor Code] in ('A','F','WW')", "Sex = 'M' or MaritalStatus = 'Married'"

CODE -->

ID = rs!CusRef
' ID is some text field value. Assume it is ABC123
emailAddress = rs!CustConDet
DoCmd.OpenReport rptName, acViewPreview, , "CusRef = '" & ID & "'" 
'This then would resolve to something like
'DoCmd.OpenReport "LdParkingSPDAdoptLettEmail", acViewPreview, , "CusRef = 'ABC123'" 

You can add a prompt after each email, write before you loop

CODE -->

DoCmd.Close acReport, rptName
    rs.MoveNext
    If MsgBox("Do you want to continue?", vbYesNo, "Continue?") = vbNo Then Exit Do
  Loop 

However, although this will cycle all of your customers and send them their pages you still are going to have to click outlook send 6000 times and the continue button 6000 times. As I said, you may want to try the CDO code. I however could not get a test message to work. Are all 6k customers getting emails or only a portion? Assuming you could get out 30 a minute that is still a couple of hours of clicking. If that is the case I would think in this code you add some could to write to a table that the email was sent. Then the query for the loop should only include those who have not been sent an email. That way you can do a bunch and take a break. Then restart the code where you left off. Now if the code locked up you do not have an easy way to resume. Again I strongly recommend the query that you loop is a stored query and you do not build it in code.

Quote:

I would make it easier. Your query to loop should only contain the customer ID (cusRef) and the email address fields that are contained in that report. No need for any other fields and all of those other joins, because you are looping that query just to filter the report and tell it where to send the filtered report. It should have a distinct clause if necessary so you only get one record per customer. I would save that as stored query. Assume I save this query as "qryCustomersAndEmail". Your code is a lot simpler to check because you can validate the query.
strSql = "qryCustomersAndEmail"
That way you can simply modify the query and use the code to send to a specific list. All you need is a list of CusRef and customer emails that you want to send to in the query.

so take this and store it as a query. Or better yet simplify this to return just CusRef and CustConDet.

CODE -->

strSql = "SELECT DISTINCT UNI7LIVE_LDCUS.CUSREF AS CusRef, " & _
            "UNI7LIVE_LDCUS.CUSNAME AS CustomerName, " & _
            "UNI7LIVE_LDCUS.DEPT AS CustomerDept, " & _
            "UNI7LIVE_LDCUS.CUSALIAS AS CusAlias, " & _
            "UNI7LIVE_LDCUS.ADDRESS, " & _
            "UNI7LIVE_LDCUSCON.LDCONMETH AS CusConMeth, " & _
            "UNI7LIVE_LDCUSCON.CONVAL AS CustConDet, " & _
            "UNI7LIVE_LDCUSCON.PREFERRED AS CusConPreffered, " & _
            "UNI7LIVE_LDCUSAP.LDAPTYPE AS ApType, " & _
            "UNI7LIVE_LDCUSAP.DEPT AS ApDept, UNI7LIVE_LDCUSAP.ORG AS ApOrg, " & _
            "UNI7LIVE_LDCUSAP.ADDRESS AS ApAdd, " & _
            "UNI7LIVE_LDCUSAPCON.LDCONMETH AS ApConMeth, " & _
            "UNI7LIVE_LDCUSAPCON.CONVAL AS ApConDet, " & _
            "UNI7LIVE_LDCUSAPCON.PREFERRED AS ApPref " & _
            "FROM ((UNI7LIVE_LDCUS LEFT JOIN UNI7LIVE_LDCUSAP ON UNI7LIVE_LDCUS.KEYVAL = UNI7LIVE_LDCUSAP.PKEYVAL) " & _
            "LEFT JOIN UNI7LIVE_LDCUSCON ON UNI7LIVE_LDCUS.KEYVAL = UNI7LIVE_LDCUSCON.PKEYVAL) " & _
            "LEFT JOIN UNI7LIVE_LDCUSAPCON ON UNI7LIVE_LDCUSAP.KEYVAL = UNI7LIVE_LDCUSAPCON.PKEYVAL " & _
            "WHERE (((UNI7LIVE_LDCUS.CUSNAME) Not Like 'Etienne*') AND ((UNI7LIVE_LDCUS.ADDRESS)='Unknown' Or (UNI7LIVE_LDCUS.ADDRESS) Is Null) AND ((UNI7LIVE_LDCUSCON.PREFERRED)='T')) " & _
            "ORDER BY UNI7LIVE_LDCUS.CUSREF " 
Lets assume you call this query "qryCustomers_Emails"
Then the above code simply becomes
strSql = "qryCustomers_Emails"

RE: run-time error 2585 - action can't be carried out - while using DoCmd.SendObject acSendReport

(OP)
Dear MajP,

I have found a way to automate it in the code.
I was looking all over and found a MS Article.

CODE --> VB

DoCmd.SendObject acSendReport, stDocName, acFormatPDF, emailAddress, , , "Parking Standards Supplementary Planning Document", "Dear " & CustomerName & Chr(13) & Chr(13) & _
  "Please see attached, which refers to the Parking Standards Supplementary Planning Document. " & Chr(13) & Chr(13) & _
  "Yours sincerely  " & Chr(13) & Chr(13) & _
  "Ernest Amoako", False 
The last part in bold allows me to send the emails without the outlook popup and send automatically.
Thanks again for all your help! smile smile Thumbsup

Thank you,

Kind regards

Triacona

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close