I am attempting to get a list from a form generated Query. How do I get it to list all the information in that query into an email in text form? The 3 controls are the ones I want from the forms Record Source
Form CCB Record Source:Code:Private Sub Send_Open_CCB_Click() On Error GoTo ErrorMsgs Dim objOutlook As Outlook.Application Dim objOutlookMsg As Outlook.MailItem Dim objOutlookAttach As Outlook.Attachment Dim objOutlookRecip As Outlook.Recipient Dim strBody, strAddresses, strSubject As String Set objOutlook = CreateObject("Outlook.Application") Set objOutlookMsg = objOutlook.CreateItem(olMailItem) With objOutlookMsg .Subject = "Tomorrows CCB Open CR's - " & Format(Date + 1, "dd mmm yyyy") .Body = "The below CRs are avilable for the next CCB." & vbCrLf & vbCrLf & ![Status] & Chr(9) & ![CR_Numbers] & " - " & ![Change Requested] DoCmd.OutputTo 3, "CCB Open Changes", acFormatPDF, "C:\Temp\CCB Open Changes - " & Format(Date + 1, "dd mmm yyyy") & ".pdf", , 0 .Attachments.Add ("C:\Temp\CCB Open Changes - " & Format(Date + 1, "dd mmm yyyy") & ".pdf") .Display Kill "C:\Temp\CCB Open Changes - " & Format(Date + 1, "dd mmm yyyy") & ".pdf" DoCmd.Close acReport, "CCB Open Changes" End With Set objOutlookMsg = Nothing Set objOutlook = Nothing Set objOutlookAttach = Nothing Exit Sub ErrorMsgs: If Err.Number = "287" Then MsgBox "You clicked No to the Outlook security warning. " & _ "Rerun the procedure and click Yes to access e-mail " & _ "addresses to send your message." Else MsgBox Err.Number & " " & Err.Description End If End Sub
SELECT Chng_ReqQry.CR_ID, Chng_ReqQry.CR_Number, Chng_ReqQry.CR_Numbers, Chng_ReqQry.Levels, IIf(([Chng_ReqQRY].[Sub_No]=0),Format([Chng_ReqQRY].[Date_ID],"dd mmm yy"),"") AS Date_IDs, Chng_ReqQry.Status, Chng_ReqQry.Change_Type, Chng_ReqQry.[HB Vers], Chng_ReqQry.Units, Chng_ReqQry.[MTOE Paras], Chng_ReqQry.People, Chng_ReqQry.[Change Requested], Chng_ReqQry.Rationale, Chng_ReqQry.NOTES, Chng_ReqQry.Action_Items, Chng_ReqQry.NIE, Chng_ReqQry.Hr, Chng_ReqQry.AO_Vote
FROM Chng_ReqQry
WHERE (((Chng_ReqQry.Action_Complete)=False) AND ((Chng_ReqQry.AO_Vote)<>"Hold" And (Chng_ReqQry.AO_Vote)<>"Defer" And (Chng_ReqQry.AO_Vote)<>"Withdraw" And (Chng_ReqQry.AO_Vote)<>"Open") AND ((Chng_ReqQry.CR_Numbers)<>"") AND ((Chng_ReqQry.Levelz)<>"Level 1"))
GROUP BY Chng_ReqQry.CR_ID, Chng_ReqQry.CR_Number, Chng_ReqQry.CR_Numbers, Chng_ReqQry.Levels, IIf(([Chng_ReqQRY].[Sub_No]=0),Format([Chng_ReqQRY].[Date_ID],"dd mmm yy"),""), Chng_ReqQry.Status, Chng_ReqQry.Change_Type, Chng_ReqQry.[HB Vers], Chng_ReqQry.Units, Chng_ReqQry.[MTOE Paras], Chng_ReqQry.People, Chng_ReqQry.[Change Requested], Chng_ReqQry.Rationale, Chng_ReqQry.NOTES, Chng_ReqQry.Action_Items, Chng_ReqQry.NIE, Chng_ReqQry.Hr, Chng_ReqQry.AO_Vote
ORDER BY Chng_ReqQry.CR_ID, Chng_ReqQry.Levels DESC;
Example:
Defer CR1 - Open email
Approve CR2 - List all items
Deny CR3 - Close Email
Thanks