email contents of table to recipients in table

[BR549]

Hello, I am working with a table I need to send an email to the names listed in the table. I have a contact table created and the table with the info I would like to embed in the email. The reason I need to it embedded in the email is I am looking for them to turn around the info with comments. Also, if its possible I would like the database to notify me that the name is not in the database, and to top it off my manager asked me to have the names in the Missed table to have their manager copied with the same email.

Example email:
Hello,

We need a response to this information as quickly as possible, please place
your comments in the PM/IM/CM Response column.

embedded table

BR,


embedded signature

[June7]

Sending emails common topic. Search forum or web. Here is one for a start https://www.accessforums.net/program...ook-21903.html

Or maybe the Collect Data wizard would serve your needs.

[BR549]

ok I think I have the email built. When i run the code the email is created and two address's are used 1 for To and 1 for CC. I am still having issues getting the address's out of the tables an input into the email address. Can you please provide some advice

Private Sub SendEmail()


' Send emails to PM/IM/CM with Missed CT
Dim oOutlook As Outlook.Application
Dim oEmailItem As MailItem
' to Prevent 429 Error, if outlook not open.
On Error Resume Next
Err.Clear
Set oOutlook = GetObject(Outlook.Application)
If Err.Number <> 0 Then
Set oOutlook = New Outlook.Application
End If
Set oEmailItem = oOutlook.CreateItem(olMailItem)
With oEmailItem
.to = "brian.roach@ericsson.com"
.cc = "jonathan.boren@ericsson.com"
.Subject = "test Access email"
.Body = "Hello everyone," & vbCrLf & vbCrLf & _
" Please look at the sites below and provide a response in the PM, IM, or CM Response column and reply to all." & _
" We will remove the sites from the suppliers performance which you have identified as outside the supplier's control." & _
" Thank you in advance for your prompt response, if you have any questions please feel free to contact me." & _
" If I have not contacted the right person for the site's below, please forward as you feel necessary." & vbCrLf & vbCrLf & _
"As part of the Supplier Excellence Program (SEP) we measure Suppliers on 7 areas, Cycle Time, Site Handler Completion " & _
"Notification, COP Submission Percentage, COP Submission Time, CAM Compliance, and Quality. In order to accurately reflect " & _
"the suppliers performance, we have found a few occurrences of Cycle Time which seem excessive and may have not been the" & _
"fault of the Supplier." & vbCrLf & vbCrLf & _
"Please respond with the completed table below ASAP, this information will be used in our meetings with suppliers next week."


.display
End With
End Sub

Sub SendMail()


Dim rsData As Recordset
Dim rsEmails As Recordset
Dim strBody As String
'Dim ...
Set rsEmails = CurrentDb.OpenRecordset("SELECT Contact FROM [tbl CT Missed];")
While Not rsEmails.EOF
Set rsData = CurrentDb.OpenRecordset("SELECT email FROM Contacts WHERE Contact=" & rsEmails!contact & ";")
While Not rsData.EOF
'code to build email body using rsData record
strBody = strBody & rsData!field1 & ", " & rsData!Field2 & ", " & rsData!field3 & vbCrLf
rsData.MoveNext
Wend
'code to send email
'...
rsData.Close
rsEmails.MoveNext
Wend


Set oOutlook = Nothing
Set oEmailItem = Nothing


End Sub

[June7]

What are the issues - error message, wrong results, nothing happens?

Is Contact field a number type?

Why is this code broken into two procedures?

How do you expect the code to build email body to work? The only field in rsData recordset is email. Also, the text behind .Body has no connection to the strBody variable. If you don't need any data from the rsData recordset then remove inner loop.

Not really understanding why there are two recordsets just to pull emails.

The code of the first procedure needs to be within the While Not rsEmails.EOF loop of the second procedure.

[BR549]

I have done some further working with the code and nothing happens now. I did verify that the to addresses are in the sttorecipients and the cc addresses are in the stccrecipients, however it runs through the rest of the code and never creates the email. I put a message at the bottom to tell me the code was done. Please help.

Private Sub SendEmail()


' Send emails to PM/IM/CM with Missed CT
Dim oOutlook As Object
Dim oEmailItem As Object
Dim sttorecipients As String
Dim stccrecipients As String
Dim rsto As DAO.Recordset
Dim rscc As DAO.Recordset


Set db = CurrentDb()
Set rsto = db.OpenRecordset("qry CT Missed Contact Email")
Set rscc = db.OpenRecordset("qry CT Missed Manager Email")


With rsto
Do While Not rsto.EOF
sttorecipients = rsto![cemail] & ";" & sttorecipients
.MoveNext
Loop
End With


With rscc
Do While Not rscc.EOF
stccrecipients = rscc![memail] & ";" & stccrecipients
.MoveNext
Loop
End With


' to Prevent 429 Error, if outlook not open.
On Error Resume Next
Err.Clear
Set oOutlook = CreateObject(Outlook.Application)
If Err.Number <> 0 Then
End If
Set oEmailItem = oOutlook.CreateItem(0)
With oEmailItem
.to = sttorecipients & "brian.roach@ericsson.com"
.cc = stccrecipients & "jonathan.boren@ericsson.com"
.Subject = "test Access email"
.Body = "Hello Everyone," & vbCrLf & vbCrLf & _
" Please look at the sites below and provide a response in the PM, IM, or CM Response column and reply to all." & _
" We will remove the sites from the suppliers performance which you have identified as outside the supplier's control." & _
" Thank you in advance for your prompt response, if you have any questions please feel free to contact me." & _
" If I have not contacted the right person for the site's below, please forward as you feel necessary." & vbCrLf & vbCrLf & _
"As part of the Supplier Excellence Program (SEP) we measure Suppliers on 7 areas, Cycle Time, Site Handler Completion " & _
"Notification, COP Submission Percentage, COP Submission Time, CAM Compliance, and Quality. In order to accurately reflect " & _
"the suppliers performance, we have found a few occurrences of Cycle Time which seem excessive and may have not been the" & _
"fault of the Supplier." & vbCrLf & vbCrLf & _
"Please respond with the completed table below ASAP, this information will be used in our meetings with suppliers next week."
.display
End With
Set rsto = Nothing
Set rscc = Nothing
Set db = Nothing
Set oOutlook = Nothing
Set oEmailItem = Nothing
'Set sttorecipients = Nothing
'Set stccrecipients = Nothing
MsgBox "I am done"


End Sub

[BR549]

Solved, I found the company upgraded to Lync 2013 and now Access 2010 reloads every time I open it. The means the library 12 needed for the access was not loaded, which now I have to do each and every time. So i changed a little of the code and it works.

Set oOutlook = CreateObject(Outlook.Application) changed to Set oOutlook = CreateObject("Outlook.Application")
Set oEmailItem = oOutlook.CreateItem(0) changed to Set oEmailItem = oOutlook.CreateItem(olMailItem)

now I am not dependent on the library 12