Add a list of records to an Email

[TexKiernan]

I'm trying to add a list of records to an mail. My records are training
requirements for a person. The record set pulls expired training requirement
for a specific person. I would like to add a line of text in the body of an
email for each expired training requirement. I can't seem to figure it out.
This is as far I have gotten:

Dim dbs As Database
Dim TrainingDuePersRS As Recordset
Dim TrainingDueRS As Recordset
Dim TrainingList As Collection

'On Error Resume Next 'GoTo ErrorMsgs

Set dbs = CurrentDb()
Set TrainingDueRS = dbs.OpenRecordset("qryTrainingOverdue")
Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)


TrainingDueRS.FindFirst ("[personnelid]=" & 1158)
Do While TrainingDueRS.EOF <> True
TrainingList.Add TrainingDueRS!Trainingtitle & " " &
IIf(IsNull(TrainingDueRS!TrainingMaterial) = True,
TrainingDueRS!traininglink, TrainingDueRS!TrainingMaterial)
Loop

HELP! I'm going insane! Thanks

 

[Alex Dybenko]

Hi,
you can try to make a report and send it in email body, like here:
http://maug.pointltd.com/access/Queries/TipDetail.asp?TipID=71

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[TexKiernan]

My email server is very restrictive and strips attachments

Hi,
you can try to make a report and send it in email body, like here:
http://maug.pointltd.com/access/Queries/TipDetail.asp?TipID=71

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

I'm trying to add a list of records to an mail. My records are training
requirements for a person. The record set pulls expired training
requirement
for a specific person. I would like to add a line of text in the body of
an
email for each expired training requirement. I can't seem to figure it
out.
This is as far I have gotten:

Dim dbs As Database
Dim TrainingDuePersRS As Recordset
Dim TrainingDueRS As Recordset
Dim TrainingList As Collection

'On Error Resume Next 'GoTo ErrorMsgs

Set dbs = CurrentDb()
Set TrainingDueRS = dbs.OpenRecordset("qryTrainingOverdue")
Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)


TrainingDueRS.FindFirst ("[personnelid]=" & 1158)
Do While TrainingDueRS.EOF <> True
TrainingList.Add TrainingDueRS!Trainingtitle & " " &
IIf(IsNull(TrainingDueRS!TrainingMaterial) = True,
TrainingDueRS!traininglink, TrainingDueRS!TrainingMaterial)
Loop

HELP! I'm going insane! Thanks

 

[TexKiernan]

Okay, I get it now, doesn't this seem kind of excessive? Shouldn't I be able
to collect records in a collection or is a collection just for objects?

Hi,
you can try to make a report and send it in email body, like here:
http://maug.pointltd.com/access/Queries/TipDetail.asp?TipID=71

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

I'm trying to add a list of records to an mail. My records are training
requirements for a person. The record set pulls expired training
requirement
for a specific person. I would like to add a line of text in the body of
an
email for each expired training requirement. I can't seem to figure it
out.
This is as far I have gotten:

Dim dbs As Database
Dim TrainingDuePersRS As Recordset
Dim TrainingDueRS As Recordset
Dim TrainingList As Collection

'On Error Resume Next 'GoTo ErrorMsgs

Set dbs = CurrentDb()
Set TrainingDueRS = dbs.OpenRecordset("qryTrainingOverdue")
Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)


TrainingDueRS.FindFirst ("[personnelid]=" & 1158)
Do While TrainingDueRS.EOF <> True
TrainingList.Add TrainingDueRS!Trainingtitle & " " &
IIf(IsNull(TrainingDueRS!TrainingMaterial) = True,
TrainingDueRS!traininglink, TrainingDueRS!TrainingMaterial)
Loop

HELP! I'm going insane! Thanks

 

[Alex Dybenko]

Hi,
I think that instead of using collections you can just collect it in a
string var, and then put it in message body

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Okay, I get it now, doesn't this seem kind of excessive? Shouldn't I be
able
to collect records in a collection or is a collection just for objects?

Hi,
you can try to make a report and send it in email body, like here:
http://maug.pointltd.com/access/Queries/TipDetail.asp?TipID=71

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

I'm trying to add a list of records to an mail. My records are
training
requirements for a person. The record set pulls expired training
requirement
for a specific person. I would like to add a line of text in the body
of
an
email for each expired training requirement. I can't seem to figure it
out.
This is as far I have gotten:

Dim dbs As Database
Dim TrainingDuePersRS As Recordset
Dim TrainingDueRS As Recordset
Dim TrainingList As Collection

'On Error Resume Next 'GoTo ErrorMsgs

Set dbs = CurrentDb()
Set TrainingDueRS = dbs.OpenRecordset("qryTrainingOverdue")
Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)


TrainingDueRS.FindFirst ("[personnelid]=" & 1158)
Do While TrainingDueRS.EOF <> True
TrainingList.Add TrainingDueRS!Trainingtitle & " " &
IIf(IsNull(TrainingDueRS!TrainingMaterial) = True,
TrainingDueRS!traininglink, TrainingDueRS!TrainingMaterial)
Loop

HELP! I'm going insane! Thanks

 

[TexKiernan]

Thats the ticket! Thanks! Could you help me with another problem? My
program is creating an error when it shuts down. i don't get an error number
but the erro happens as part of the shout down but only after using my
treeview control. If I don't use the treeview control, I don't get the error.

My email server is very restrictive and strips attachments

Hi,
you can try to make a report and send it in email body, like here:
http://maug.pointltd.com/access/Queries/TipDetail.asp?TipID=71

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

I'm trying to add a list of records to an mail. My records are training
requirements for a person. The record set pulls expired training
requirement
for a specific person. I would like to add a line of text in the body of
an
email for each expired training requirement. I can't seem to figure it
out.
This is as far I have gotten:

Dim dbs As Database
Dim TrainingDuePersRS As Recordset
Dim TrainingDueRS As Recordset
Dim TrainingList As Collection

'On Error Resume Next 'GoTo ErrorMsgs

Set dbs = CurrentDb()
Set TrainingDueRS = dbs.OpenRecordset("qryTrainingOverdue")
Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)


TrainingDueRS.FindFirst ("[personnelid]=" & 1158)
Do While TrainingDueRS.EOF <> True
TrainingList.Add TrainingDueRS!Trainingtitle & " " &
IIf(IsNull(TrainingDueRS!TrainingMaterial) = True,
TrainingDueRS!traininglink, TrainingDueRS!TrainingMaterial)
Loop

HELP! I'm going insane! Thanks

 

[Tony Toews [MVP]]

I'm trying to add a list of records to an mail. My records are training
requirements for a person. The record set pulls expired training requirement
for a specific person. I would like to add a line of text in the body of an
email for each expired training requirement.

Dim TrainingList As Collection

Changed that to a string

'On Error Resume Next 'GoTo ErrorMsgs

No, no, no. Never use Resume Next.

TrainingList.Add TrainingDueRS!Trainingtitle & " " &
IIf(IsNull(TrainingDueRS!TrainingMaterial) = True,
TrainingDueRS!traininglink, TrainingDueRS!TrainingMaterial)

TrainingList = TrainingList & <various fields> & vbcrlf.

This then produces a string that you use as the body of the email.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/