Access to Outlook

[flynjack]

I have a table named "tblError". The table includes the following columns: (1) Employee ID (2) Error Code (3) Email Address (4) Email Sent (Check box from Yes or No, Yes if checked. I have a second table named "tblErrorCodes" that has two columns (1) Error Code (2) Error description. The tblError is populated by an append query and only pulls new records with error codes. My object is to pull a query that shows Employee ID's with the error code and their email address. I then want the results of this query to send an email to each of the recipients. The title will always be "Error Discrepency" but because each error code can be different, the body of the email will display the error description. These emails would be sent to each employee in the query but separately. I can not just send 1 email to multiple recipients. After the email has been sent, I need the "Email Sent" check box to be automatically checked to show "Yes" meaning sent. If this makes any sense, PLEASE HELP! Thanks.

[MMcCarthy]

You can use a DAO Recordset. I'm setting this up as code behind a command button called cmdEmailErrors as you haven't said how you want this code triggered.

Private Sub cmdEmailErrors_Click()
Dim db As Database
Dim rs As DAO.Recordset
Dim strSQL As String
 
   Set db = CurrentDB
   strSQL = "SELECT [Employee ID], [Error Code], [Error description], [Email Address], [Email Sent] " & _
      "FROM tblError INNER JOIN tblErrorCodes " & _
      "ON tblError.[Error Code] = tblErrorCodes.[Error Code] " & _
      "WHERE [Email Sent] = False;"
   Set rs = db.OpenRecordset(strSQL)
 
   If rs.RecordCount <> 0 Then
      rs.MoveFirst
      Do Until rs.EOF
         DoCmd.SendObject (acSendNoObject, , , rs![Email Address], , , "Error Code: " & rs![Error Code], "Error Description: " & rs![Error description], False)
      On Error GoTo MsgNotSent
      rs.Edit
      rs![Email Sent] = True
      rs.Update
MsgNotSent:
      rs.MoveNext
      Loop
   End If
 
   rs.Close
   Set rs = Nothing
   Set db = Nothing
 
End Sub
 

Mary

[flynjack]

Mary

Thank you for your prompt reply. One more question if you don't mind. How do I place "Error Discrepency" in the Email Subject line? Thanks a million.

[MMcCarthy]

Change the code as follows:

Private Sub cmdEmailErrors_Click()
Dim db As Database
Dim rs As DAO.Recordset
Dim strSQL As String
 
   Set db = CurrentDB
   strSQL = "SELECT [Employee ID], [Error Code], [Error description], [Email Address], [Email Sent] " & _
      "FROM tblError INNER JOIN tblErrorCodes " & _
      "ON tblError.[Error Code] = tblErrorCodes.[Error Code] " & _
      "WHERE [Email Sent] = False;"
   Set rs = db.OpenRecordset(strSQL)
 
   If rs.RecordCount <> 0 Then
      rs.MoveFirst
      Do Until rs.EOF
         DoCmd.SendObject (acSendNoObject, , , rs![Email Address], , , "Error Discrepancy", "Error Code: " & rs![Error Code] & vbCrLf & "Error Description: " & rs![Error description], False)
      On Error GoTo MsgNotSent
      rs.Edit
      rs![Email Sent] = True
      rs.Update
MsgNotSent:
      rs.MoveNext
      Loop
   End If
 
   rs.Close
   Set rs = Nothing
   Set db = Nothing
 
End Sub
 

Mary

[flynjack]

for some reason, I get a compile,syntax error on the follow line.

DoCmd.SendObject (acSendNoObject, , , rs![Email Address], , , "Error Code: " ,,, rs![Error Code], "Error Description: " & rs![Error description], False)

Any idea as to why?

Thanks again.

[MMcCarthy]

for some reason, I get a compile,syntax error on the follow line.

DoCmd.SendObject (acSendNoObject, , , rs![Email Address], , , "Error Code: " ,,, rs![Error Code], "Error Description: " & rs![Error description], False)

Any idea as to why?

Thanks again.

Sorry my fault no brackets required. And you are using too many commas

DoCmd.SendObject acSendNoObject, , , rs![Email Address], , , "Error Discrepancy", "Error Code: " & rs![Error Code] & vbCrLf & "Error Description: " & rs![Error description], False

[flynjack]

I have a query in my dbase that shows shows results in ten columns (let's call the columns a, b, c, d,e,f,g,h,i,j). Column j lists an array of email addresses. I want to email these results through outlook to the individual email addresses in column j. how do I do this.I will not need to add any attachment. I will need to add a subject line called "Employee Errors". Thank you.

[Tanis]

Put the email addresses into a table. Create a query based on this table. In your code, create a recordset of the email addresses. Use SendObject to send the emails. Something like this will do it.

Dim rst As DAO.Recordset
Dim strRecipients As String
Dim strSubject As String
Dim strBody As String

strSubject = "The subject"
strBody = "Body of the email here"

Set rst = CurrentDb.OpenRecordset("qryEmailRecipientsAddress ", dbOpenForwardOnly)
While Not rst.EOF
strRecipients = strRecipients & rst("EmailAddress") & ";"
rst.MoveNext
Wend
rst.Close

DoCmd.SendObject acSendNoObject, , , strRecipients, , , strSubject, strBody

[MMcCarthy]

I have a query in my dbase that shows shows results in ten columns (let's call the columns a, b, c, d,e,f,g,h,i,j). Column j lists an array of email addresses. I want to email these results through outlook to the individual email addresses in column j. how do I do this.I will not need to add any attachment. I will need to add a subject line called "Employee Errors". Thank you.

flynjack

You already have the solution as given by Tanis in your previous thread on this subject. Why have you reposted the question. If you had a problem with the answer you should have posted it on the previous thread.

Mary

[flynjack]

Mmccarthy;

Sorry about that. The info I gave you yesterday was bad info. Turns out the Dbase already had a query built into to so I didn't need the sql join statement. Couldn't figure out how to modify your info to adapt to a query.

[flynjack]

Mmccarthy;

Can you elaborate on the code above and put it into idiot terms for me. Same idea as yesterday, only my query has extracted all of the fields needs to include a column will all email addresses.

[NeoPa]

Mary

Thank you for your prompt reply. One more question if you don't mind. How do I place "Error Discrepency" in the Email Subject line? Thanks a million.

Don't - Use "Error Discrepancy" instead ;).
Seriously though, you don't want spelling mistakes going out like that.
Oops - looks like Mary's sorted that one already (quietly).

[flynjack]

My appologies to all. Instead of the SQL thread, i have devised a query that shows all columns needs for my email (8 columns). Each record needs to be sent individually through outlook. How do I devise the code to use my query. Thanks. (Very New at this!!!!!!!)

[MMcCarthy]

My appologies to all. Instead of the SQL thread, i have devised a query that shows all columns needs for my email (8 columns). Each record needs to be sent individually through outlook. How do I devise the code to use my query. Thanks. (Very New at this!!!!!!!)

Tanis's answer in the other thread is exactly what you need. It uses a predefined query. I am going to merge the two threads.

Mary

[MMcCarthy]

Tanis's answer in the other thread is exactly what you need. It uses a predefined query. I am going to merge the two threads.

Mary

Tanis

A little off topic but I was wondering if you could set up your PM facility. It's great if when I come accross questions I can't answer I like to send a PM to the other forum experts in the hope that they can provide the answer instead.

Mary

[flynjack]

Mary;

I'm sure I have annoyed you enough by now, but please let me ask one more questions. My query "qryReject" pulls the following info.

"Remarks" from SRB Weeklytbl
"Remarks" from SRB Error Code
"Grade" from SRB Weeklytbl
"Name" from SRB Weeklytbl
"SSN" from SRB Weeklytbl
"Reup_Date" from SRB Weeklytbl
"Reject Processed" from SRB Weeklytbl (yes/no check box field)
SYS_USER_ID from RetainUser table
INDIV_LAST_NM from RetainUser table
INDV_FIRST_NAME from RetainUser table
SYS_EMAIL_ADDR_ from RetainUser table

My Object is to email each and every individual record to each of the recipients in the SYS_EMAIL_ADDR fields. If I have a total of 10 records, then that would produce 10 individual emails. Please assist with code one more time. Thank you, sincerely.

[MMcCarthy]

Mary;

I'm sure I have annoyed you enough by now, but please let me ask one more questions. My query "qryReject" pulls the following info.

"Remarks" from SRB Weeklytbl
"Remarks" from SRB Error Code
"Grade" from SRB Weeklytbl
"Name" from SRB Weeklytbl
"SSN" from SRB Weeklytbl
"Reup_Date" from SRB Weeklytbl
"Reject Processed" from SRB Weeklytbl (yes/no check box field)
SYS_USER_ID from RetainUser table
INDIV_LAST_NM from RetainUser table
INDV_FIRST_NAME from RetainUser table
SYS_EMAIL_ADDR_ from RetainUser table

My Object is to email each and every individual record to each of the recipients in the SYS_EMAIL_ADDR fields. If I have a total of 10 records, then that would produce 10 individual emails. Please assist with code one more time. Thank you, sincerely.

Using Tanis's answer ....

Assuming you have an email command button. e.g. cmdEmail

Private Sub cmdEmail_Click()
Dim rs As DAO.Recordset
Dim strRecipients As String
Dim strSubject As String
Dim strBody As String
 
   strSubject = "Error Discrepancy"
 
   Set rs = CurrentDb.OpenRecordset("qryReject", dbOpenForwardOnly)
 
   rs.MoveFirst
   While Not rs.EOF
      strBody = "Error Code: " & rs![Field with Error Code] & vbCrLf & "Error Description: " & rs![Field with Error description]
      strRecipient = rs!SYS_EMAIL_ADDR
      DoCmd.SendObject acSendNoObject, , , strRecipient, , , strSubject, strBody
      rs.MoveNext
   Wend
   rs.Close
   Set rs = Nothing
 
End Sub
 

Mary

[flynjack]

Using Tanis's answer ....

Assuming you have an email command button. e.g. cmdEmail

Private Sub cmdEmail_Click()
Dim rs As DAO.Recordset
Dim strRecipients As String
Dim strSubject As String
Dim strBody As String
 
   strSubject = "Error Discrepancy"
 
   Set rs = CurrentDb.OpenRecordset("qryReject", dbOpenForwardOnly)
 
   rs.MoveFirst
   While Not rs.EOF
      strBody = "Error Code: " & rs![Field with Error Code] & vbCrLf & "Error Description: " & rs![Field with Error description]
      strRecipient = rs!SYS_EMAIL_ADDR
      DoCmd.SendObject acSendNoObject, , , strRecipient, , , strSubject, strBody
      rs.MoveNext
   Wend
   rs.Close
   Set rs = Nothing
 
End Sub
 

Mary

Mary,

Thank you. It worked. Couldn't have done it without your help. Have a happy holidays and hapy new year.

[MMcCarthy]

Mary,

Thank you. It worked. Couldn't have done it without your help. Have a happy holidays and hapy new year.

You're welcome and thank you.

Mary