Hi I get runtime error 3075 with the following sql, trying to open query and then create table in email based on query. Any ideas thanks
error occurs on line Set rst = CurrentDb.OpenRecordset(strSQL)
assuming somehting wrong with sql string
Code:
Sub ReportToOutlookBody()
Dim rst As DAO.Recordset
Dim strSQL As String
Dim olApp As Outlook.Application
Dim objMail As Outlook.MailItem
Dim strTableBeg As String
Dim strTableBody As String
Dim strTableEnd As String
Dim strFntNormal As String
Dim strTableHeader As String
Dim strFntEnd As String
'Define format for output
strTableBeg = "<br><br><table border=1 cellpadding=3 cellspacing=0>"
strTableEnd = "</table><br><br>Sincerely,<br><br>The Big Guy"
strTableHeader = "<font size=3 face=" & Chr(34) & "Arial" & Chr(34) & "><b>" & _
"<tr bgcolor=lightblue>" & _
TD("Section") & _
TD("Document Code") & _
TD("Document Title") & _
TD("Document Type") & _
TD("Reason for Change") & _
TD("Issue Number") & _
TD("Issue Date") & _
"</tr></b></font>"
strFntNormal = "<font color=black face=" & Chr(34) & "Arial" & Chr(34) & " size=2>"
strFntEnd = "</font>"
strSQL = " SELECT tblDocs.Section, tblDocs.[Document Code], tblDocs.[Document Title], tblDocs.[Document Type], qryCurrent.[Reason for Change], qryCurrent.[Issue Number], qryCurrent.[Issue Date], tblDocumentType.[Intranet Prefix], tblDocs.[Intranet Suffix], IIf([tblDocs].[Active]=0,'Withdrawn',IIf([tblDocs].[Section]='Group',DLookUp('[Intranet Prefix]','[tblDocumentType]','[Document Type] = 'Quality Manual'') & [tblDocs].[Document Code] & [Intranet Suffix],IIf([tblDocs].[Section]='HSE',DLookUp('[Intranet Prefix]','[tblDocumentType]','[Document Type] = 'LWI'') & [tblDocs].[Document Code] & [Intranet Suffix],([Intranet Prefix] & [tblDocs].[Document Code] & [tblDocs].[Intranet Suffix])))) AS iLink FROM (tblDocumentType INNER JOIN tblDocs ON tblDocumentType.[Document Type] = tblDocs.[Document Type]) INNER JOIN qryCurrent ON tblDocs.[Document Code] = qryCurrent.[Document Code]"
strSQL = strSQL & " WHERE (((tblDocs.Section) <> [Forms]![frmDocumentRegister]![frmSetup2].[Form]![Group Filter]) And ((tblDocs.Active) = True) And ((IIf(IsNull([Forms]![frmDocumentRegister]![FilterBy]), True, [tblDocs].[Section] = [Forms]![frmDocumentRegister]![FilterBy])) <> False) And ((IIf(IsNull([Forms]![frmDocumentRegister]![FilterBy0]), True, [tblDocs].[Document Type] = [Forms]![frmDocumentRegister]![FilterBy0])) <> False) And ((IIf(IsNull([Forms]![frmDocumentRegister]![Like Doc]), True, [tblDocs].[Document Code] Like [Forms]![frmDocumentRegister]![Like Doc])) <> False) And ((IIf(IsNull([Forms]![frmDocumentRegister]![Like Title]), True, [tblDocs].[Document Title] Like [Forms]![frmDocumentRegister]![Like Title])) <> False))"
Set rst = CurrentDb.OpenRecordset(strSQL)
'Build HTML Output for the DataSet
strTableBody = strTableBeg & strFntNormal & strTableHeader
Do Until rst.EOF
strTableBody = strTableBody & _
"<tr>" & _
TD(rst!Section) & _
TD("<a href =" & (rst!iLink) & ">" & (rst!Document_Code) & "</a>") & _
TD(rst!Document_Title) & _
TD(rst!Document_Type) & _
TD(rst!Reason_for_Change) & _
TD(rst!Issue_Number) & _
TD(rst!Issue_Date) & _
"</tr>"
rst.MoveNext
Loop
rst.Close
'Create e-mail item
Set olApp = Outlook.Application
Set objMail = olApp.CreateItem(olMailItem)
With objMail
'Set body format to HTML
.To = " "
.Subject = "Past Due Item"
.BodyFormat = olFormatHTML
.HTMLBody = "<HTML><BODY>" & strFntNormal & strTableBody & " </BODY></HTML>"
.Display
End With
Clean_Up:
Set rst = Nothing
End Sub
Function TD(strIn As String) As String
TD = "<TD nowrap>" & strIn & "</TD>"
End Functionerror occurs on line Set rst = CurrentDb.OpenRecordset(strSQL)
assuming somehting wrong with sql string
