Email Listing

[Thompyt]

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

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

Form CCB Record Source:
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

[June7]

Open a recordset object of the query and loop through records and concatenate into a string. This could be a very long string. How many records could be involved?

What do you mean by 'form generated query' - how does form generate query?

[Thompyt]

June it could be quite long For this iteration I can see about 16 lines. It is always subject to change. As for the length of characters it would have to be a long string. I can concantenate the 3 controls to have 1 control for the email to look at. But how would I reference it in the code?

If I concantenate all the records into one, it would be extremely long.

Ostensibly I would like to have it Group by Status then list the CR_Numbers & Change Requested under it.

Is there a way do do a loop until end?

[June7]

Certainly.

Dim strMsg As String
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT [Status], [CR_Numbers], [Change Requested] FROM tablename {WHERE clause optional} ORDER BY Status, CR_Numbers, ChangeRequested")
rs.MoveFirst
While Not rs.EOF
strMsg = strMsg & rs!Status & ' ' & rs!CR_Numbers & ' - ' & rs!ChangeRequested & vbCrLf
rs.MoveNext
Wend
rs.Close

[Thompyt]

I made an extra Query [CCB Email] and set the control record to that in a new Form.[CCB Email].

SELECT Chng_ReqQry.CR_ID, Chng_ReqQry.CR_Numbers, Chng_ReqQry.Levels, Chng_ReqQry.Status, Chng_ReqQry.[Change Requested]
FROM Chng_ReqQry
WHERE (((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.Action_Complete)=False) AND ((Chng_ReqQry.Levelz)<>"Level 1"))
GROUP BY Chng_ReqQry.CR_ID, Chng_ReqQry.CR_Numbers, Chng_ReqQry.Levels, Chng_ReqQry.Status, Chng_ReqQry.[Change Requested]
ORDER BY Chng_ReqQry.CR_ID;

It prints out the minimalistic I need for the text in the email.

[June7]

[Change Requested] has a space.

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

Did you try suggested code?

[Thompyt]

I get an error. Runtime error '3061': Too few parameters. Expected 1. I am trying to get the data from the CCB Email Query, not a table. If I use a table then I get the who kitten kaboodle. I know I shouldn't use spaces, but I tried to change the space to a _ once. The Db gave me fits.

Private Sub Send_Open_CCB_Click()
 On Error GoTo ErrorMsgs
 Dim strMsg As String
 Dim rs As DAO.Recordset
 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)
Set rs = CurrentDb.OpenRecordset("SELECT [Status], [CR_Numbers],[Change Requested] FROM CCB_Email ORDER BY Status, CR_Numbers, ChangeRequested")
With objOutlookMsg
   .Subject = "Tomorrows CCB Open CR's - " & Format(Date + 1, "dd mmm yyyy")
   .Body = "The attached CRs are avilable for the next CCB." & vbCrLf & vbCrLf
rs.MoveFirst
While Not rs.EOF
            strMsg = strMsg & rs!Status & " " & rs!CR_Numbers & " - " & rs![Change Requested] & vbCrLf
  rs.MoveNext
 Wend
 rs.Close
   .Body = vbCrLf & vbCrLf & "V/R" & vbCrLf & vbCrLf & "NAme & Address Block"

[June7]

Look at the ChangeRequested field name in the ORDER BY clause. No space and no [].

[Thompyt]

I took out the spaces and get the above runtime error. Is this because I used a Query instead of a table?

[June7]

Query or table shouldn't matter.

Problem is you need the space because that is how you structured the field name.

And then also need [].

[Thompyt]

June,
I noticed the combine changerequested earlier and fixed that. I do not get any errors now, but I also get no list

Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
Set rs = CurrentDb.OpenRecordset("SELECT [Status],[CR_Numbers],[Change Requested] FROM [CCB_Email] ORDER BY Status,CR_Numbers,[Change Requested]")
With objOutlookMsg
   .Subject = "Tomorrows CCB Open CR's - " & Format(Date + 1, "dd mmm yyyy")
   .Body = "The attached CRs are avilable for the next CCB." & vbCrLf & vbCrLf
rs.MoveFirst
While Not rs.EOF
            strMsg = strMsg & rs!Status & " " & rs!CR_Numbers & " - " & rs![Change Requested] & vbCrLf
  rs.MoveNext
 Wend
 rs.Close
   .Body = vbCrLf & vbCrLf & "V/R" & vbCrLf & vbCrLf & "Name and address"

I don't think I can separate, have two .Body= in there. I combined the .Body for the end of the email, but don't get the list from the CCB_Email.Qry. It seems like the strMsg part is not "loading" per se.

[June7]

Right, .Body only one time:

.Body = "The attached CRs are available for the next CCB." & vbCrLf & vbCrLf & strMsg & vbCrLf & vbCrLf & "V/R" & vbCrLf & vbCrLf & "Name and address"

[Thompyt]

June,
Re-edit of my previous post. I have the .Body fixed and the code runs to the end and sets up an email w/attachment. What I don't get is the strmsg part listing the lines from the CCB_Email Query.

Private Sub Send_Open_CCB_Click()
 On Error GoTo ErrorMsgs
 Dim strMsg As String
 Dim rs As DAO.Recordset
 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)
Set rs = CurrentDb.OpenRecordset("SELECT [Status],[CR_Numbers],[Change Requested] FROM [CCB_Email] ORDER BY Status,CR_Numbers,[Change Requested]")
With objOutlookMsg
   .Subject = "Tomorrows CCB Open CR's - " & Format(Date + 1, "dd mmm yyyy")
  rs.MoveFirst
While Not rs.EOF
            strMsg = strMsg & rs!Status & " " & rs!CR_Numbers & " - " & rs![Change Requested] & vbCrLf
  rs.MoveNext
Wend
 rs.Close
   .Body = "The attached CRs are avilable for the next CCB." & vbCrLf & vbCrLf & _
           vbCrLf & vbCrLf & "V/R" & vbCrLf & vbCrLf & "Signature Block
    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")

[June7]

You haven't included the strMsg variable in the concatenation. Review my previous post.

Surprised it works because missing a " mark after Signature Block.

Also, might want to correct spelling of available.

[Thompyt]

"Surprised it works because missing a " mark after Signature Block." This is because I deleted my Sig block out. and wrote it in. I forgot the "

I have to concantenate each line into @ 16 seperate lines or one big line? I understand how to do the 1 row concantenation of data, but I do not know how to concantenate all the rows needed in the data.

strMsg = strMsg & rs!Status & " " & rs!CR_Numbers & " - " & rs![Change Requested] & vbCrLf

This line tells me that I am pulling out the info from these controls already: Status, VR_Numbers, Change Requested.
I was under the assumption that if they are pulled from the query, that they would be added to the Email body.

Thanks for the heads up on the spelling error.