Generate and send by email individual reports based on a query

[pbaldy]

No, there's a problem with the SQL. Either a parameter value not concatenated into the string or something misspelled. Thus may help:

http://www.baldyweb.com/ImmediateWindow.htm

 

[papic1972]

OK, thank you pbaldy. I've fixed the SQL now, that makes sense to me.
However I'm a little confused with this line of code from the previous example:

DoCmd.OpenReport strRptName, acViewPreview, , "[VARBOrder].Invoice_ID='" & ![Invoice_ID] & "'"

I'm unsure what [VARDOrder].Invoice_ID is actually referencing.

Should this be the query that is powering the report (i.e rptInvoice) where filtering occurs by OrderID or does this need to be from a query of email addresses? (Hope this makes sense!)

 

[pbaldy]

Does this help?

http://www.baldyweb.com/wherecondition.htm

 

[papic1972]

Ahh yes, stupid me!! Thanks heaps for that.

I can only make this work for the very first record on my form. It doesn't loop through all of my records, I'm unsure as to why this would be.

 

[pbaldy]

Might help to see your code.

 

[papic1972]

Yes of course! Here is the code that I have adapted from the initial example in this post:

Private Sub MakeReportSendEmail_Click()
Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim strSQL As String
Dim strRptName As String
Dim count As Integer
Dim strFilter As String


Dim imsg As Object
Dim iconf As Object
Dim flds As Object
Dim schema As String

Dim strpath As String
Dim strFilterEmail As String
Dim strFile As String


strRptName = "CompleteLintelsInvoiceBATCHTEST"
strSQL = "SELECT * FROM zzqryTrialBatchInvoiceScreen2SUM2 ORDER BY zzqryTrialBatchInvoiceScreen2SUM2.OrderID"
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)

Set imsg = CreateObject("CDO.Message")
Set iconf = CreateObject("CDO.Configuration")
Set flds = iconf.Fields

schema = "...schemas.microsoft.com/cdo/configuration/"
flds.Item(schema & "sendusing") = 2
flds.Item(schema & "smtpserver") = "smtp"
flds.Item(schema & "smtpserverport") = 25
flds.Item(schema & "smtpauthenticate") = 1
flds.Item(schema & "smtpusessl") = True
flds.Item(schema & "smtpconnectiontimeout") = 60
flds.Item(schema & "sendusername") = "xxxx"
flds.Item(schema & "sendpassword") = "xxxx"
flds.Update

With MyRS

Do While Not MyRS.EOF

DoCmd.OutputTo acOutputReport, strRptName, acFormatPDF, "C:\TempInvoicePDF\" & ![EMailAddress] & ".pdf"
DoCmd.Close acReport, strRptName, acSaveNo

strpath = "C:\TempInvoicePDF\"
strFilterEmail = "*.pdf"
strFile = Dir(strpath & strFilterEmail)

With imsg
.To = MyRS.Fields("EmailAddress")
.From = "some_email"
.Subject = "Test Subject:"
.HTMLBody = "Test Body"
.AddAttachment strpath & strFile
Set .Configuration = iconf
.Send

End With

.MoveNext
Loop
End With
MyRS.Close
Set MyRS = Nothing

End Sub

 

[pbaldy]

It should be looping the query, not the form. Also, I don't see you opening the report filtered, so unless it filters from within you'll get the same report for everybody.

 

[papic1972]

Apologies for my the delay in my response, I've been unwell for the past couple of days.

zzqryTrialBatchInvoiceScreen2SUM2 is a query & my report does in fact open filtered (it's filtered by OrderID)

DoCmd.OutputTo acOutputReport, strRptName, acFormatPDF, "C:\TempInvoicePDF\" & ![OrderID] & ".pdf"

After the report is output, I receive the following error:

Runtime error '-2147220960 (80040220)':
The "SendUsing" configuration value is invalid

The code breaks and .Send is highlighted.

I'm testing this on a laptop that isn't connected to a network (only to the internet) and with Ms Outlook installed. Is the following schema incorrect? I'm unsure as to what I would need to change here.

schema = "...schemas.microsoft.com/cdo/configuration/"
flds.Item(schema & "sendusing") = 2
flds.Item(schema & "smtpserver") = "smtp"
flds.Item(schema & "smtpserverport") = 25
flds.Item(schema & "smtpauthenticate") = 1
flds.Item(schema & "smtpusessl") = True
flds.Item(schema & "smtpconnectiontimeout") = 60
flds.Item(schema & "sendusername") = "xxxx"
flds.Item(schema & "sendpassword") = "xxxx"
flds.Update

 

[pbaldy]

I've only used CDO from inside a network where the "smtpserver" argument was the address of the Exchange server. I might guess that in your case, it would need to be the address of your email provider, like would be in the Outlook account setup. That's a total shot in the dark. You could switch to Outlook automation, though that runs into the Outlook warnings issue, which may be why you used CDO to begin with.

 

[papic1972]

OK, thanks heaps! i'll give that a shot and let you know what happens. Yes I've used CDO so I don't encounter the security warnings.

I've just checked the report filtering based on 2 tests records in the query. You were right, even though 2 PDF's are created with 2 unique OrderID's, the content is exactly the same. Does this mean that my query is not looping OR is it that the report is not being filtered when opened?

 

[pbaldy]

How does the report filter? If its source refers to a form or something, you'd need to copy the current value from the loop there.

 

[papic1972]

I've tried filtering the report with from a separate query "zzqryTrialInvoiceBATCHTESTCOPY"

With MyRS

Do While Not MyRS.EOF

DoCmd.OpenReport strRptName, , , "[zzqryTrialInvoiceBATCHTESTCOPY].[OrderID]='" & ![OrderID] & "'"
'Debug.Print ("OrderID: " & MyRS!OrderID & " Qty " & MyRS!Qty)
DoCmd.OutputTo acOutputReport, strRptName, acFormatPDF, "C:\TempInvoicePDF\" & ![OrderID] & ".pdf"
DoCmd.Close acReport, strRptName, acSaveNo


However, when it runs it keeps asking for the [zzqryTrialInvoiceBATCHTESTCOPY].[OrderID] value. I would have thought that as each OrderID is looped that the OrderID would automatically filter the opening of the report....it doesn't seem to be the case. I must be doing something wrong here.

 

[pbaldy]

Have tried simply

DoCmd.OpenReport strRptName, , , "[OrderID]='" & ![OrderID] & "'"

 

[papic1972]

Thank you, yes I gave that a shot yesterday, however it returns the following error:

"Data Type mismatch in criteria expression"

This doesn't make any sense to me as the OrderID is an int type in SQL Server. Why would this error occur?

 

[pbaldy]

You're treating it as text. Try

DoCmd.OpenReport strRptName, , , "[OrderID]=" & ![OrderID]

 

[papic1972]

Yes you are correct, that stops the error, thanks heaps.

The test query I'm running has 2 records. The PDF's are produced with the correct OrderID names however both PDF's have the identical information, so it's not actually filtering. I'm unsure what could be causing this.

 

[pbaldy]

Ah, you have to open the report in preview mode. You're printing, so it's not available to the OutputTo line.

 

[papic1972]

Yes definitely, that works!! Thanks heaps for that!

The only problem I have still is trying to get the email configuration to work. I tried your suggestion of inserting my email details but that didn't work. I don't want to not use CDO, it's most effective. This will be used in a network environment with Exchange Server so i'll see if I can test it in that environment today & i'll let you know the outcome.

 

[pbaldy]

No problem! CDO should be fine in that environment, with the server name or address there.

 

[papic1972]

OK, i'll give it a shot!!

I have one more question if you can!
If I wanted to filter by OrderID and RevisionNo, what would the syntax be? I tried this but I get a syntax error:

DoCmd.OpenReport strRptName, acViewPreview, , "[OrderID]=" & ![OrderID] & "[RevisionNo]=" & ![RevisionNo]