By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 2,802 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

Access to Outlook

P: 14
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.
Dec 12 '06 #1
Share this Question
Share on Google+
18 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdEmailErrors_Click()
  2. Dim db As Database
  3. Dim rs As DAO.Recordset
  4. Dim strSQL As String
  5.  
  6.    Set db = CurrentDB
  7.    strSQL = "SELECT [Employee ID], [Error Code], [Error description], [Email Address], [Email Sent] " & _
  8.       "FROM tblError INNER JOIN tblErrorCodes " & _
  9.       "ON tblError.[Error Code] = tblErrorCodes.[Error Code] " & _
  10.       "WHERE [Email Sent] = False;"
  11.    Set rs = db.OpenRecordset(strSQL)
  12.  
  13.    If rs.RecordCount <> 0 Then
  14.       rs.MoveFirst
  15.       Do Until rs.EOF
  16.          DoCmd.SendObject (acSendNoObject, , , rs![Email Address], , , "Error Code: " & rs![Error Code], "Error Description: " & rs![Error description], False)
  17.       On Error GoTo MsgNotSent
  18.       rs.Edit
  19.       rs![Email Sent] = True
  20.       rs.Update
  21. MsgNotSent:
  22.       rs.MoveNext
  23.       Loop
  24.    End If
  25.  
  26.    rs.Close
  27.    Set rs = Nothing
  28.    Set db = Nothing
  29.  
  30. End Sub
  31.  
Mary
Dec 12 '06 #2

P: 14
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.
Dec 13 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Change the code as follows:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdEmailErrors_Click()
  2. Dim db As Database
  3. Dim rs As DAO.Recordset
  4. Dim strSQL As String
  5.  
  6.    Set db = CurrentDB
  7.    strSQL = "SELECT [Employee ID], [Error Code], [Error description], [Email Address], [Email Sent] " & _
  8.       "FROM tblError INNER JOIN tblErrorCodes " & _
  9.       "ON tblError.[Error Code] = tblErrorCodes.[Error Code] " & _
  10.       "WHERE [Email Sent] = False;"
  11.    Set rs = db.OpenRecordset(strSQL)
  12.  
  13.    If rs.RecordCount <> 0 Then
  14.       rs.MoveFirst
  15.       Do Until rs.EOF
  16.          DoCmd.SendObject (acSendNoObject, , , rs![Email Address], , , "Error Discrepancy", "Error Code: " & rs![Error Code] & vbCrLf & "Error Description: " & rs![Error description], False)
  17.       On Error GoTo MsgNotSent
  18.       rs.Edit
  19.       rs![Email Sent] = True
  20.       rs.Update
  21. MsgNotSent:
  22.       rs.MoveNext
  23.       Loop
  24.    End If
  25.  
  26.    rs.Close
  27.    Set rs = Nothing
  28.    Set db = Nothing
  29.  
  30. End Sub
  31.  
Mary
Dec 13 '06 #4

P: 14
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.
Dec 13 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Dec 13 '06 #6

P: 14
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.
Dec 14 '06 #7

100+
P: 143
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
Dec 14 '06 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Dec 14 '06 #9

P: 14
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.
Dec 14 '06 #10

P: 14
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.
Dec 14 '06 #11

NeoPa
Expert Mod 15k+
P: 31,273
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).
Dec 14 '06 #12

P: 14
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!!!!!!!)
Dec 14 '06 #13

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Dec 14 '06 #14

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Dec 14 '06 #15

P: 14
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.
Dec 14 '06 #16

MMcCarthy
Expert Mod 10K+
P: 14,534
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdEmail_Click()
  2. Dim rs As DAO.Recordset
  3. Dim strRecipients As String
  4. Dim strSubject As String
  5. Dim strBody As String
  6.  
  7.    strSubject = "Error Discrepancy"
  8.  
  9.    Set rs = CurrentDb.OpenRecordset("qryReject", dbOpenForwardOnly)
  10.  
  11.    rs.MoveFirst
  12.    While Not rs.EOF
  13.       strBody = "Error Code: " & rs![Field with Error Code] & vbCrLf & "Error Description: " & rs![Field with Error description]
  14.       strRecipient = rs!SYS_EMAIL_ADDR
  15.       DoCmd.SendObject acSendNoObject, , , strRecipient, , , strSubject, strBody
  16.       rs.MoveNext
  17.    Wend
  18.    rs.Close
  19.    Set rs = Nothing
  20.  
  21. End Sub
  22.  
Mary
Dec 15 '06 #17

P: 14
Using Tanis's answer ....

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdEmail_Click()
  2. Dim rs As DAO.Recordset
  3. Dim strRecipients As String
  4. Dim strSubject As String
  5. Dim strBody As String
  6.  
  7.    strSubject = "Error Discrepancy"
  8.  
  9.    Set rs = CurrentDb.OpenRecordset("qryReject", dbOpenForwardOnly)
  10.  
  11.    rs.MoveFirst
  12.    While Not rs.EOF
  13.       strBody = "Error Code: " & rs![Field with Error Code] & vbCrLf & "Error Description: " & rs![Field with Error description]
  14.       strRecipient = rs!SYS_EMAIL_ADDR
  15.       DoCmd.SendObject acSendNoObject, , , strRecipient, , , strSubject, strBody
  16.       rs.MoveNext
  17.    Wend
  18.    rs.Close
  19.    Set rs = Nothing
  20.  
  21. End Sub
  22.  
Mary

Mary,

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

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Dec 15 '06 #19

Post your reply

Sign in to post your reply or Sign up for a free account.