Sending Batch Email to Field Agents

[charlie weaver]

I work for a large insurance company, and Im looking for a way to send emails with variable data to about 5,000 agents out in the field. (Not spam, I promise.)
Id like to use Access to pull info from various excel lists, and then send the results via email to our various agents.

I found some vb code online shown below, but Im a complete novice.
Im trying to find a way to change the code so it sends from an excel email list.

Will modifing the code show below serve my purposes, or should I look in some other direction?

Charlie
------------------------------------------

Public Sub SendMail()
'Provides the Send Mail automation
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailAddress As String
Dim strEMailMsg As String
Dim ingCounter As Integer
Dim intCount As Integer

strSubject = "Latest Job Outcomes"
strEmailAddress = "[e-mail]"
'strEmailAddress,replace [Mail Addresses Go Here] above with valid
'e-mail addresses
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("qrySendMail")


'Count of unsent e-mails
intCount = DCount("[lngJobOutcome]", "[tblJobOutcomes]" _
, "[ysnSentByMailToStaff]=0")
'If count of unsent e-mails is zero then the procedure will not run
'If count of unsent e-mails is greater than zero, msgbox will prompt
'to send mail.

If intCount = 0 Then
MsgBox ("You have " & intCount & " new job outcome e-mails to send.") _
, vbInformation, "System Information"
Exit Sub
Else

rst.MoveFirst
Do Until rst.EOF

strEMailMsg = rst![strStudentFirstName] & " " & rst![strStudentLastName] _
& " - " & rst![strStudentNumber] & " - " & " on the " & rst![strCourse] _
& " course" & " has informed us of a new job." & Chr(10) & Chr(10) _
& "Below are the details that have been submitted by the agent:" _
& Chr(10) & Chr(10) & rst![memNewJobDescription] & Chr(10) & Chr(10) _
& "Charlie"

'EMAIL USER DETAILS & ATT REPORT
DoCmd.SendObject , , acFormatRTF, strEmailAddress, _
, , strSubject, strEMailMsg, False, False


rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

'Run update to update the sent mail check box
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblJobOutcomes SET tblJobOutcomes.ysnSentByMailToStaff = -1 WHERE (((tblJobOutcomes.ysnSentByMailToStaff)=0))"
DoCmd.SetWarnings True
MsgBox "All new Job Outcomes have been sent", vbInformation, "Thank You"
End If
End Sub

[ADezii]

I work for a large insurance company, and Im looking for a way to send emails with variable data to about 5,000 agents out in the field. (Not spam, I promise.)
Id like to use Access to pull info from various excel lists, and then send the results via email to our various agents.

I found some vb code online shown below, but Im a complete novice.
Im trying to find a way to change the code so it sends from an excel email list.

Will modifing the code show below serve my purposes, or should I look in some other direction?

Charlie
------------------------------------------

Public Sub SendMail()
'Provides the Send Mail automation
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strEmailAddress As String
Dim strEMailMsg As String
Dim ingCounter As Integer
Dim intCount As Integer

strSubject = "Latest Job Outcomes"
strEmailAddress = "[e-mail]"
'strEmailAddress,replace [Mail Addresses Go Here] above with valid
'e-mail addresses
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("qrySendMail")


'Count of unsent e-mails
intCount = DCount("[lngJobOutcome]", "[tblJobOutcomes]" _
, "[ysnSentByMailToStaff]=0")
'If count of unsent e-mails is zero then the procedure will not run
'If count of unsent e-mails is greater than zero, msgbox will prompt
'to send mail.

If intCount = 0 Then
MsgBox ("You have " & intCount & " new job outcome e-mails to send.") _
, vbInformation, "System Information"
Exit Sub
Else

rst.MoveFirst
Do Until rst.EOF

strEMailMsg = rst![strStudentFirstName] & " " & rst![strStudentLastName] _
& " - " & rst![strStudentNumber] & " - " & " on the " & rst![strCourse] _
& " course" & " has informed us of a new job." & Chr(10) & Chr(10) _
& "Below are the details that have been submitted by the agent:" _
& Chr(10) & Chr(10) & rst![memNewJobDescription] & Chr(10) & Chr(10) _
& "Charlie"

'EMAIL USER DETAILS & ATT REPORT
DoCmd.SendObject , , acFormatRTF, strEmailAddress, _
, , strSubject, strEMailMsg, False, False


rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

'Run update to update the sent mail check box
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblJobOutcomes SET tblJobOutcomes.ysnSentByMailToStaff = -1 WHERE (((tblJobOutcomes.ysnSentByMailToStaff)=0))"
DoCmd.SetWarnings True
MsgBox "All new Job Outcomes have been sent", vbInformation, "Thank You"
End If
End Sub

Will this Excel E-Mail list consist of Recipient Names and comprise strEmailList?

[charlie weaver]

Will this Excel E-Mail list consist of Recipient Names and comprise strEmailList?

Yes.

What I would ultimately like to do is be able to extract email addresses and other data from and excel database in access, and then send emails to all the agents from that excel list using an access form with buttons.

I was thinking I just need to find a variable to use in place of the static email address after the strEmailAddress as shown below, but there is probably much more I need to change.

strSubject = "Latest Job Outcomes"
strEmailAddress = "[e-mail]"
'strEmailAddress,replace [Mail Addresses Go Here] above with valid
'e-mail addresses
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("qrySendMail")

[ADezii]

Yes.

What I would ultimately like to do is be able to extract email addresses and other data from and excel database in access, and then send emails to all the agents from that excel list using an access form with buttons.

I was thinking I just need to find a variable to use in place of the static email address after the strEmailAddress as shown below, but there is probably much more I need to change.

strSubject = "Latest Job Outcomes"
strEmailAddress = "[e-mail]"
'strEmailAddress,replace [Mail Addresses Go Here] above with valid
'e-mail addresses
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("qrySendMail")

What you need to do first is to Import a sample Excel List into Access and display the structure of it. Hopefully, we can advance from that point on.

[charlie weaver]

I imported the excel email list as a table. I named the table tblEmailAddresses.
Then I replaced the static email address shown below to "[tblEmailAddresses]" and that didnt work. Next I tried "strEmailAddresses" after naming the column header in my excel file strEmailAddresses, and that didnt work.

Im just grabbing in the dark because Im brand spankin new. Im trying to think this through logically, but Im not getting very far yet. I really didnt think this was going to be very hard, and it probably isnt once I get headed in the right direction.

I appreciate your help,

Charlie

strEmailAddress = "[e-mail]"