Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.
Triacona

Triacona

Technical User
Jun 11, 2009
462
GB
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:
Run-time error 2585: said:
This action can't be carried out while processing a form or report event.
Click to expand...

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: 
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
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Thanks MajP [smile]
I tried entering DoEnvents and it still gives me the same error.

Code: 
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
 
Upvote 0 Downvote
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: 
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
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
    '[URL unfurl="true"]https://www.rondebruin.nl/win/s1/cdo.htm.[/URL]
    '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
 
Upvote 0 Downvote
I tried the CDO code and got the message "The transport failed to connect to the server".
 
Upvote 0 Downvote
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: 
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
[s][/s]

Thank you,

Kind regards

Triacona
 
Upvote 0 Downvote
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"
 
Upvote 0 Downvote
Hi MajP,
Thanks again for all your help [smile]
I have got it not to hand with the following code:
Code: 
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
[s][/s]
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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'
 
Upvote 0 Downvote
Dear MajP,
Thanks for your reply [smile]
I am using Option Compare Database
I currently have the following code:
Code: 
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
 
Upvote 0 Downvote
Hi MajP,

Your instruction on the code was spot on !! [2thumbsup]
Code: 
"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
 
Upvote 0 Downvote
Also why is the entire thing in string mode?
Click to expand...

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.

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"
Click to expand...
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
 
Upvote 0 Downvote
Dear MajP,

I have found a way to automate it in the code.
I was looking all over and found a MS Article.
Code: 
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"[b], False[/b]
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

breezett93
  • Locked
  • Question
Report often takes multiple minutes to load, lots of formatting time between pages
Replies
5
Views
284
dhookom
dhookom
VicM
  • Locked
  • Question
Curious behavior using Chr(10) & Chr(13)
Replies
4
Views
434
SoftwareRT
SoftwareRT
breezett93
  • Locked
  • Question
What should be multiple reports is becoming one report with multiple pages 1
Replies
13
Views
248
dhookom
dhookom
soloracer
  • Locked
  • Question
Problem using report recordsource values within report events. Error 2427 1
Replies
17
Views
251
soloracer
soloracer
breezett93
  • Locked
  • Question
Sending report via email only works when Outlook is closed
Replies
1
Views
219
breezett93
breezett93

Part and Inventory Search

Sponsor

Back
Top