Pls help : Sending a HTML email based on a query (1 Viewer)

[hankallan]

Hi all,

Been racking my brains on this one for age on the below problem .

I'm trying to send a HTML mail to a list of people regarding their unpaid invoices.
The code below does the job perfectly but ...

I can run a query showing all results, however one person could have multiple unpaid invoices hence they show up on numerous lines .As soon as i try and apply a filter in the query criteria via combo box or an unbound field in a form in order to group the person

the code below automatically fails at

rs.Open sqlString, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

with a error no value given for one or more required parameters

Guessing i should run the criteria query in the code ? But honestly not the best with VBA hence looking for any help

Many thanks .
Hank

______________________________________________________________
'This sample uses an ADO
' recordset so make sure you have a reference to your latest
' Microsoft ActiveX Object Library set.
' Again you can just call this function from anywhere passing along
' the name of your table/query e.g.: ?HtmlNoReportEmail("qryCustomers")
' Note: strMsg must be hardcoded and your Table or Query must include all
' fields in strMsg

Public Function HtmlNoReportmail(strTblQryName As String)

Dim olApp As Object ' Outlook.Application
Dim olMail As Object ' Outlook.MailItem
Dim strMsg As String
Dim sqlString As String
Dim i As Integer
Dim rowColor As String

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset


sqlString = "SELECT * FROM " & strTblQryName & ""
rs.Open sqlString, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

strMsg = "<table border='1' cellpadding='3' cellspacing='3' style='border-collapse: collapse' bordercolor='#111111' width='800'>" & _
"<tr>" & _
"<td bgcolor='#7EA7CC'> <b>FullName</b></td>" & _
"<td bgcolor='#7EA7CC'> <b>Amount</b></td>" & _
"<td bgcolor='#7EA7CC'> <b>DaysOutstanding</b></td>" & _
"</tr>"

i = 0

Do While Not rs.EOF

If (i Mod 2 = 0) Then
rowColor = "<td bgcolor='#FFFFFF'> "
Else
rowColor = "<td bgcolor='#E1DFDF'> "
End If

strMsg = strMsg & "<tr>" & _
rowColor & rs.Fields("FullName") & "</td>" & _
rowColor & rs.Fields("Amount") & "</td>" & _
rowColor & rs.Fields("DaysOutstanding") & "</td>" & _
"</tr>"

rs.MoveNext
i = i + 1
Loop
strMsg = strMsg & "</table>"

If isAppThere("Outlook.Application") = False Then
' Outlook is not open, create a new instance
Set olApp = CreateObject("Outlook.Application")
Else
' Outlook is already open--use this method
Set olApp = GetObject(, "Outlook.Application")
End If

' Create the New Email Item
Set olMail = olApp.CreateItem(0) ' olMailitem = 0

With olMail
.BodyFormat = 2 ' olFormatHTML = 2
.HTMLBody = strMsg
.Recipients. Add Forms![Q2]![mail]
.Subject = "Outstanding AR to be collected"
'.Send if you want to send it directly without displaying on screen
.Display
End With

Set olApp = Nothing
Set olMail = Nothing
End Function

 

[sneuberg]

Does the filtered query run ok when you run it directly? With the form open and the combo box, etc filled in double click the query in the navigation pane. What are you getting?

 

[hankallan]

Thanks for the reply

Yep query itself would run no issues with the filter .. it's when you execute the code is when it breaks down

Code will run when you manually type in the filter but not when you reference a combo/unbound box.

Think it has something to do with this kb/209203 (sorry can't post links)

Many thanks again for looking at this

Hank