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
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