Automating E-mail/Attachement sending

A

AJ

I have a registration database that is working well. To send out Class
Schedules I use the following VBA script (see below.
The report mentioned is set to print to Adobe Acrobat PDFwriter, so it
essentially creates a file on the hard drive, ready to be attached. The
script also creates an E-mail, and inserts the persons's E-mail, while the
report creates that person's registration.
My problem is that, for each student, I have to create an E-mail, THEN
attach the report (in PDF), THEN send it. We have over 100 students.
How can I automate this, and have a different report for each student? (Can
I turn this into a mail-gun somehow? Can I have 1 script to send each their
own schedule/confirmation?)

Function SendReg()
Dim strDocName As String
Dim strWhere As String
Dim strEmail As String
Dim strSubject As String
Dim strStudent As String

strDocName = "rptStudentRegConf"
strWhere = "[ID]=" & Forms!frmStudent!ID
strEmail = Forms!frmStudent!ParentEmail
strSubject = Forms!frmStudent!FirstName & "'s ASSI 2005 Registration
Confirmation"
strMessage = Forms!frmStudent!ParentName & "," & vbCrLf & vbCrLf & "I
have attached the confirmation of what we have for " &
Forms!frmStudent!FirstName & ". " & vbCrLf & vbCrLf & "Is this all correct?"

DoCmd.OpenReport strDocName, acViewNormal, , strWhere
DoCmd.SendObject acSendNoObject, strDocName, , strEmail, , , strSubject,
strMessage

End Function
 
D

Douglas J. Steele

Easiest way would be to open a recordset that returns each student.

Change your function so that it accepts arguments:

Function SendReg( _
StudentId As Long, _
StudentFirstName As String, _
ParentEmail As String, _
ParentName As String _
)
Dim strDocName As String
Dim strWhere As String
Dim strEmail As String
Dim strSubject As String
Dim strStudent As String

strDocName = "rptStudentRegConf"
strWhere = "[ID]=" & StudentID
strEmail = ParentEmail
strSubject = StudentFirstName & _
"'s ASSI 2005 Registration Confirmation"
strMessage = ParentName & "," & vbCrLf & vbCrLf & _
"I have attached the confirmation of what we have for " & _
StudentFirstName & ". " & vbCrLf & vbCrLf & _
"Is this all correct?"

DoCmd.OpenReport strDocName, acViewNormal, , strWhere
DoCmd.SendObject acSendNoObject, strDocName, , _
strEmail, , , strSubject, strMessage

End Function

Then, in another routine, open your recordset and call that function for
each student:

Sub SendAllReports

Dim dbCurr As DAO.Database
Dim rsStudent As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT Id, FirstName, ParentEMail, ParentName " & _
"FROM StudentTable"

Set dbCurr = CurrentDb()
Set rsStudent = dbCurr.OpenRecordset(strSQL)
With rsStudent
Do While .EOF = False
If IsNull(!ParentEMail) = False Then
Call SendReg(!Id, _
!FirstName, _
!ParentEmail, _
!ParentName)
End If
.MoveNext
Loop
End With

rsStudent.Close
Set rsStudent = Nothing
Set dbCurr = Nothing

End Sub

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



AJ said:
I have a registration database that is working well. To send out Class
Schedules I use the following VBA script (see below.
The report mentioned is set to print to Adobe Acrobat PDFwriter, so it
essentially creates a file on the hard drive, ready to be attached. The
script also creates an E-mail, and inserts the persons's E-mail, while the
report creates that person's registration.
My problem is that, for each student, I have to create an E-mail, THEN
attach the report (in PDF), THEN send it. We have over 100 students.
How can I automate this, and have a different report for each student?
(Can I turn this into a mail-gun somehow? Can I have 1 script to send each
their own schedule/confirmation?)

Function SendReg()
Dim strDocName As String
Dim strWhere As String
Dim strEmail As String
Dim strSubject As String
Dim strStudent As String

strDocName = "rptStudentRegConf"
strWhere = "[ID]=" & Forms!frmStudent!ID
strEmail = Forms!frmStudent!ParentEmail
strSubject = Forms!frmStudent!FirstName & "'s ASSI 2005 Registration
Confirmation"
strMessage = Forms!frmStudent!ParentName & "," & vbCrLf & vbCrLf & "I
have attached the confirmation of what we have for " &
Forms!frmStudent!FirstName & ". " & vbCrLf & vbCrLf & "Is this all
correct?"

DoCmd.OpenReport strDocName, acViewNormal, , strWhere
DoCmd.SendObject acSendNoObject, strDocName, , strEmail, , ,
strSubject, strMessage

End Function
 
A

AJ

Doug,

That worked great! It cycles through each record and coordinates the right
report with the right E-mail object, etc.

IS there a way to remove the need for user intervention for each step? For
example:
Access creates report & E-mail object
(then user has to click "save" to save it to My Documents)
(then user has to click "replace" on subsequent attachments)
(then user has to click mail object's paper clip to attach the
document)
(then user has to click "send")

I was thinking of something like MS Word's record-macro feature...

If that isn't possible, I will be much better off than I was before. Thank
you for the great script. Thank you for moving us forward.

Easiest way would be to open a recordset that returns each student.

Change your function so that it accepts arguments:

Function SendReg( _
StudentId As Long, _
StudentFirstName As String, _
ParentEmail As String, _
ParentName As String _
)
Dim strDocName As String
Dim strWhere As String
Dim strEmail As String
Dim strSubject As String
Dim strStudent As String

strDocName = "rptStudentRegConf"
strWhere = "[ID]=" & StudentID
strEmail = ParentEmail
strSubject = StudentFirstName & _
"'s ASSI 2005 Registration Confirmation"
strMessage = ParentName & "," & vbCrLf & vbCrLf & _
"I have attached the confirmation of what we have for " & _
StudentFirstName & ". " & vbCrLf & vbCrLf & _
"Is this all correct?"

DoCmd.OpenReport strDocName, acViewNormal, , strWhere
DoCmd.SendObject acSendNoObject, strDocName, , _
strEmail, , , strSubject, strMessage

End Function

Then, in another routine, open your recordset and call that function for
each student:

Sub SendAllReports

Dim dbCurr As DAO.Database
Dim rsStudent As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT Id, FirstName, ParentEMail, ParentName " & _
"FROM StudentTable"

Set dbCurr = CurrentDb()
Set rsStudent = dbCurr.OpenRecordset(strSQL)
With rsStudent
Do While .EOF = False
If IsNull(!ParentEMail) = False Then
Call SendReg(!Id, _
!FirstName, _
!ParentEmail, _
!ParentName)
End If
.MoveNext
Loop
End With

rsStudent.Close
Set rsStudent = Nothing
Set dbCurr = Nothing

End Sub

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



AJ said:
I have a registration database that is working well. To send out Class
Schedules I use the following VBA script (see below.
The report mentioned is set to print to Adobe Acrobat PDFwriter, so it
essentially creates a file on the hard drive, ready to be attached. The
script also creates an E-mail, and inserts the persons's E-mail, while
the report creates that person's registration.
My problem is that, for each student, I have to create an E-mail, THEN
attach the report (in PDF), THEN send it. We have over 100 students.
How can I automate this, and have a different report for each student?
(Can I turn this into a mail-gun somehow? Can I have 1 script to send
each their own schedule/confirmation?)

Function SendReg()
Dim strDocName As String
Dim strWhere As String
Dim strEmail As String
Dim strSubject As String
Dim strStudent As String

strDocName = "rptStudentRegConf"
strWhere = "[ID]=" & Forms!frmStudent!ID
strEmail = Forms!frmStudent!ParentEmail
strSubject = Forms!frmStudent!FirstName & "'s ASSI 2005 Registration
Confirmation"
strMessage = Forms!frmStudent!ParentName & "," & vbCrLf & vbCrLf & "I
have attached the confirmation of what we have for " &
Forms!frmStudent!FirstName & ". " & vbCrLf & vbCrLf & "Is this all
correct?"

DoCmd.OpenReport strDocName, acViewNormal, , strWhere
DoCmd.SendObject acSendNoObject, strDocName, , strEmail, , ,
strSubject, strMessage

End Function
 
A

AJ

Doug,

To answer my own question, couldn't I use that vbOK stuff? and set a
constant to specify a hard location, like
"C:\My Documents\Temp" in which to save the reports...
and then I would put those scripts right after "End If" but before
".MoveNext" in the SendAllReports() sub?

You might coach me on this a bit...

Function SendReg( _
StudentId As Long, _
StudentFirstName As String, _
ParentEmail As String, _
ParentName As String _
)
Dim strDocName As String
Dim strWhere As String
Dim strEmail As String
Dim strSubject As String
Dim strStudent As String

strDocName = "rptStudentRegConf"
strWhere = "[ID]=" & StudentID
strEmail = ParentEmail
strSubject = StudentFirstName & _
"'s ASSI 2005 Registration Confirmation"
strMessage = ParentName & "," & vbCrLf & vbCrLf & _
"I have attached the confirmation of what we have for " & _
StudentFirstName & ". " & vbCrLf & vbCrLf & _
"Is this all correct?"

DoCmd.OpenReport strDocName, acViewNormal, , strWhere
DoCmd.SendObject acSendNoObject, strDocName, , _
strEmail, , , strSubject, strMessage

End Function

Then, in another routine, open your recordset and call that function for
each student:

Sub SendAllReports

Dim dbCurr As DAO.Database
Dim rsStudent As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT Id, FirstName, ParentEMail, ParentName " & _
"FROM StudentTable"

Set dbCurr = CurrentDb()
Set rsStudent = dbCurr.OpenRecordset(strSQL)
With rsStudent
Do While .EOF = False
If IsNull(!ParentEMail) = False Then
Call SendReg(!Id, _
!FirstName, _
!ParentEmail, _
!ParentName)
End If
.MoveNext
Loop
End With

rsStudent.Close
Set rsStudent = Nothing
Set dbCurr = Nothing

End Sub

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



AJ said:
I have a registration database that is working well. To send out Class
Schedules I use the following VBA script (see below.
The report mentioned is set to print to Adobe Acrobat PDFwriter, so it
essentially creates a file on the hard drive, ready to be attached. The
script also creates an E-mail, and inserts the persons's E-mail, while
the report creates that person's registration.
My problem is that, for each student, I have to create an E-mail, THEN
attach the report (in PDF), THEN send it. We have over 100 students.
How can I automate this, and have a different report for each student?
(Can I turn this into a mail-gun somehow? Can I have 1 script to send
each their own schedule/confirmation?)

Function SendReg()
Dim strDocName As String
Dim strWhere As String
Dim strEmail As String
Dim strSubject As String
Dim strStudent As String

strDocName = "rptStudentRegConf"
strWhere = "[ID]=" & Forms!frmStudent!ID
strEmail = Forms!frmStudent!ParentEmail
strSubject = Forms!frmStudent!FirstName & "'s ASSI 2005 Registration
Confirmation"
strMessage = Forms!frmStudent!ParentName & "," & vbCrLf & vbCrLf & "I
have attached the confirmation of what we have for " &
Forms!frmStudent!FirstName & ". " & vbCrLf & vbCrLf & "Is this all
correct?"

DoCmd.OpenReport strDocName, acViewNormal, , strWhere
DoCmd.SendObject acSendNoObject, strDocName, , strEmail, , ,
strSubject, strMessage

End Function
 
D

Douglas J. Steele

Sorry, I don't really follow your issue.

See whether there are any suggestions in the Access Email FAQ Tony Toews has
at http://www.granite.ab.ca/access/email.htm


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



AJ said:
Doug,

To answer my own question, couldn't I use that vbOK stuff? and set a
constant to specify a hard location, like
"C:\My Documents\Temp" in which to save the reports...
and then I would put those scripts right after "End If" but before
".MoveNext" in the SendAllReports() sub?

You might coach me on this a bit...

Function SendReg( _
StudentId As Long, _
StudentFirstName As String, _
ParentEmail As String, _
ParentName As String _
)
Dim strDocName As String
Dim strWhere As String
Dim strEmail As String
Dim strSubject As String
Dim strStudent As String

strDocName = "rptStudentRegConf"
strWhere = "[ID]=" & StudentID
strEmail = ParentEmail
strSubject = StudentFirstName & _
"'s ASSI 2005 Registration Confirmation"
strMessage = ParentName & "," & vbCrLf & vbCrLf & _
"I have attached the confirmation of what we have for " & _
StudentFirstName & ". " & vbCrLf & vbCrLf & _
"Is this all correct?"

DoCmd.OpenReport strDocName, acViewNormal, , strWhere
DoCmd.SendObject acSendNoObject, strDocName, , _
strEmail, , , strSubject, strMessage

End Function

Then, in another routine, open your recordset and call that function for
each student:

Sub SendAllReports

Dim dbCurr As DAO.Database
Dim rsStudent As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT Id, FirstName, ParentEMail, ParentName " & _
"FROM StudentTable"

Set dbCurr = CurrentDb()
Set rsStudent = dbCurr.OpenRecordset(strSQL)
With rsStudent
Do While .EOF = False
If IsNull(!ParentEMail) = False Then
Call SendReg(!Id, _
!FirstName, _
!ParentEmail, _
!ParentName)
End If
.MoveNext
Loop
End With

rsStudent.Close
Set rsStudent = Nothing
Set dbCurr = Nothing

End Sub

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



AJ said:
I have a registration database that is working well. To send out Class
Schedules I use the following VBA script (see below.
The report mentioned is set to print to Adobe Acrobat PDFwriter, so it
essentially creates a file on the hard drive, ready to be attached. The
script also creates an E-mail, and inserts the persons's E-mail, while
the report creates that person's registration.
My problem is that, for each student, I have to create an E-mail, THEN
attach the report (in PDF), THEN send it. We have over 100 students.
How can I automate this, and have a different report for each student?
(Can I turn this into a mail-gun somehow? Can I have 1 script to send
each their own schedule/confirmation?)

Function SendReg()
Dim strDocName As String
Dim strWhere As String
Dim strEmail As String
Dim strSubject As String
Dim strStudent As String

strDocName = "rptStudentRegConf"
strWhere = "[ID]=" & Forms!frmStudent!ID
strEmail = Forms!frmStudent!ParentEmail
strSubject = Forms!frmStudent!FirstName & "'s ASSI 2005 Registration
Confirmation"
strMessage = Forms!frmStudent!ParentName & "," & vbCrLf & vbCrLf & "I
have attached the confirmation of what we have for " &
Forms!frmStudent!FirstName & ". " & vbCrLf & vbCrLf & "Is this all
correct?"

DoCmd.OpenReport strDocName, acViewNormal, , strWhere
DoCmd.SendObject acSendNoObject, strDocName, , strEmail, , ,
strSubject, strMessage

End Function
 
A

AJ

Doug,

I looked up an answer. To totally automate that great script you suggested,
I will need to use something like:

RunCommand acCmdSave 'save the report already set to print to
PDF
RunCommand acCmdReplace 'save on top of previous file by same name
(last record)
RunCommand acCmdInsertFile 'insert file into programatically loaded
mail object
RunCommand acCmdSend ' send!

But this doesn't work, and I can't find anything on the MSDN to tell me the
syntax, how to, for example, specify where to save the file, and again, from
what location to insert the file, and which filename, etc.

Could you shed some light on this? (based on your fantastic script?)
 
G

Guest

AJ,

Did you ever find the solution to your problem? I am in the same situation
where I have approximatley fifity e-mails to send out daily with fields for
each person. This would be a HUGE help. You may need to walk me throught
the code....I am somewhat new to writing modules.

THANKS!

AJ said:
Doug,

To answer my own question, couldn't I use that vbOK stuff? and set a
constant to specify a hard location, like
"C:\My Documents\Temp" in which to save the reports...
and then I would put those scripts right after "End If" but before
".MoveNext" in the SendAllReports() sub?

You might coach me on this a bit...

Function SendReg( _
StudentId As Long, _
StudentFirstName As String, _
ParentEmail As String, _
ParentName As String _
)
Dim strDocName As String
Dim strWhere As String
Dim strEmail As String
Dim strSubject As String
Dim strStudent As String

strDocName = "rptStudentRegConf"
strWhere = "[ID]=" & StudentID
strEmail = ParentEmail
strSubject = StudentFirstName & _
"'s ASSI 2005 Registration Confirmation"
strMessage = ParentName & "," & vbCrLf & vbCrLf & _
"I have attached the confirmation of what we have for " & _
StudentFirstName & ". " & vbCrLf & vbCrLf & _
"Is this all correct?"

DoCmd.OpenReport strDocName, acViewNormal, , strWhere
DoCmd.SendObject acSendNoObject, strDocName, , _
strEmail, , , strSubject, strMessage

End Function

Then, in another routine, open your recordset and call that function for
each student:

Sub SendAllReports

Dim dbCurr As DAO.Database
Dim rsStudent As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT Id, FirstName, ParentEMail, ParentName " & _
"FROM StudentTable"

Set dbCurr = CurrentDb()
Set rsStudent = dbCurr.OpenRecordset(strSQL)
With rsStudent
Do While .EOF = False
If IsNull(!ParentEMail) = False Then
Call SendReg(!Id, _
!FirstName, _
!ParentEmail, _
!ParentName)
End If
.MoveNext
Loop
End With

rsStudent.Close
Set rsStudent = Nothing
Set dbCurr = Nothing

End Sub

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



AJ said:
I have a registration database that is working well. To send out Class
Schedules I use the following VBA script (see below.
The report mentioned is set to print to Adobe Acrobat PDFwriter, so it
essentially creates a file on the hard drive, ready to be attached. The
script also creates an E-mail, and inserts the persons's E-mail, while
the report creates that person's registration.
My problem is that, for each student, I have to create an E-mail, THEN
attach the report (in PDF), THEN send it. We have over 100 students.
How can I automate this, and have a different report for each student?
(Can I turn this into a mail-gun somehow? Can I have 1 script to send
each their own schedule/confirmation?)

Function SendReg()
Dim strDocName As String
Dim strWhere As String
Dim strEmail As String
Dim strSubject As String
Dim strStudent As String

strDocName = "rptStudentRegConf"
strWhere = "[ID]=" & Forms!frmStudent!ID
strEmail = Forms!frmStudent!ParentEmail
strSubject = Forms!frmStudent!FirstName & "'s ASSI 2005 Registration
Confirmation"
strMessage = Forms!frmStudent!ParentName & "," & vbCrLf & vbCrLf & "I
have attached the confirmation of what we have for " &
Forms!frmStudent!FirstName & ". " & vbCrLf & vbCrLf & "Is this all
correct?"

DoCmd.OpenReport strDocName, acViewNormal, , strWhere
DoCmd.SendObject acSendNoObject, strDocName, , strEmail, , ,
strSubject, strMessage

End Function
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top