Putting Recordset Results In E-Mail Body

[atlbearcat]

Hello all....I have a problem that I can't seem to get a resolution on....

I have a database (Access 2002/2003) that has a form that allows users to select records via a "Select" checkbox. When the user is finished selecting the records they want to e-mail, they click a button that generates a PDF of the desired report and places it in an email. Good so far....

What I want to do is to take the recordset of the generated report and programatically place them in the body of the email. Let me be very clear in that I don't want to send 1 message to multiple email addresses. I simply want to take the data in the report and place it in the body of an email message.

Data
Name Selected Phone Number Mobile Number
John Doe Yes [phone] [phone]
Sally Smith Yes [phone] [phone]
Mary Jones No [phone] [phone]
Joe Blow Yes [phone] [phone]

What the output (in the body of the e-mail) should be:

John Doe
[phone]
[phone]

Sally Smith
[phone]
[phone]

Joe Blow
[phone]
[phone]

Again, this email is only going to one person. I have the e-mail part figured out with the exception of getting the data in the body.

Thanks.

Jeff G

[Jim Doherty]

Hello all....I have a problem that I can't seem to get a resolution on....

I have a database (Access 2002/2003) that has a form that allows users to select records via a "Select" checkbox. When the user is finished selecting the records they want to e-mail, they click a button that generates a PDF of the desired report and places it in an email. Good so far....

What I want to do is to take the recordset of the generated report and programatically place them in the body of the email. Let me be very clear in that I don't want to send 1 message to multiple email addresses. I simply want to take the data in the report and place it in the body of an email message.

Data
Name Selected Phone Number Mobile Number
John Doe Yes [phone] [phone]
Sally Smith Yes [phone] [phone]
Mary Jones No [phone] [phone]
Joe Blow Yes [phone] [phone]

What the output (in the body of the e-mail) should be:

John Doe
[phone]
[phone]

Sally Smith
[phone]
[phone]

Joe Blow
[phone]
[phone]

Again, this email is only going to one person. I have the e-mail part figured out with the exception of getting the data in the body.

Thanks.

Jeff G

Depending where you want this... either paste it into a module behind your form or into a standard module then simply call the function. I'm kind of guessing if you have worked out the emailing vba then you will have no problem sorting this out. It largely depends on your system design as to where you want to call this from.

The function is somewhat specific to your post in that it relies on specific field names etc and my guess at your table name.

I also thought the phone numbers might look at 'little' prettier if they had a textual description ie: the 'field name' before each one so that in a long email body you'd know which one it was either home or mobile?

 
Function MyBody()
On Error Resume Next
Dim rst As New ADODB.Recordset
Dim mysql As String, myval As String, r As Integer, i As Integer
Dim mylist()
'Create an SQL statement that satisfies your the recordset to use
'you will have to adapt this to suit your environment and circumstance
' Given you have used a Selected field to determine those to send
' the below syntax is based around that boolean value
' You obviously need to amend the table name to suit yours
mysql = "SELECT * FROM tblMyBody Where Selected <> 0 "
rst.Open mysql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
r = 0
While Not rst.EOF
    r = r + 1
    ReDim Preserve mylist(r)
    mylist(r) = rst.Fields("Name") & vbCrLf
    mylist(r) = mylist(r) & rst.Fields("Phone Number").Name & ": " & rst.Fields("Phone Number") & vbCrLf
    mylist(r) = mylist(r) & rst.Fields("Mobile Number").Name & ": " & rst.Fields("Mobile Number") & vbCrLf & vbCrLf
    rst.MoveNext
Wend
rst.Close
If r > 0 Then
    'unload the values
    For i = 0 To UBound(mylist)
    If Len(mylist(i)) > 2 Then
        myval = myval & mylist(i)
    End If
    Next i
    MyBody = myval
Else
    MsgBox "No email body available", vbExclamation, "System Message"
    MyBody = ""
End If
'If you have pasted this into a module behind your main screen form then
'if you want to asign the value of the function to an unbound text box on your
'form so that you can see the result before you send it then un-comment the below line
'Me!txtBody = MyBody
'If you wish to assign the value of the function to directly to the email 'body'
'property of your outlook mail message then simply call the function from your email
'automation routine at the appropriate part like this (presumes your object is called objEmail)
'ie: objEmail.TextBody = MyBody
'or if using html format to send then use this
'ie: objEmail.HTMLBody = MyBody
Erase mylist
End Function
 

I leave the error handling to you. As is with the on error resume next flow it will either work and display a list or simply return nothing on failure. msgboxing out where no selected 'records' exist

If you want to see the results on your screen form as indicated simply mount a textbox called txtBody on your screen and in a command button type this into the OnClick property dialog =MyBody()

Hope this helps you

Regards

Jim

[atlbearcat]

Jim -

Here's my adjusted code:

 Function MyBody()
On Error Resume Next
Dim rst As New ADODB.Recordset
Dim mysql As String, myval As String, r As Integer, i As Integer
Dim mylist()
'Create an SQL statement that satisfies your the recordset to use
'you will have to adapt this to suit your environment and circumstance
' Given you have used a Selected field to determine those to send
' the below syntax is based around that boolean value
' You obviously need to amend the table name to suit yours
mysql = "SELECT tblContractorInfo.contFirstName, tblContractorInfo.contLastName, tblContractorInfo.contFullName, tblContractorInfo.contDynamicsVendor, tblContractorInfo.contEMail, tblContractorInfo.contPhoneNumber, tblContractorInfo.contMobileNumber, tblContractorInfo.contDesiredHourly, tblContractorProjects.contractProjectNumber, dbo_tblProjectMain.ProjectName, tblContractorProjects.contractSelected, tblContractorProjects.contractSiteName, tblContractorProjects.contractTravelIncluded FROM (tblContractorProjects INNER JOIN tblContractorInfo ON tblContractorProjects.contractContractor = tblContractorInfo.contContractorID) INNER JOIN dbo_tblProjectMain ON tblContractorProjects.contractProjectNumber = dbo_tblProjectMain.ProjectID WHERE (((tblContractorProjects.contractProjectNumber)= [forms]![frmProjectSubContractors]![ProjectNumber]) AND ((tblContractorProjects.contractSelected)=Yes))"
rst.Open mysql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
r = 0
While Not rst.EOF
    r = r + 1
    ReDim Preserve mylist(r)
    mylist(r) = rst.Fields("contFullName") & vbCrLf
    mylist(r) = mylist(r) & rst.Fields("Phone Number").Name & ": " & rst.Fields("contPhoneNumber") & vbCrLf
    mylist(r) = mylist(r) & rst.Fields("Mobile Number").Name & ": " & rst.Fields("contMobileNumber") & vbCrLf & vbCrLf
    rst.MoveNext
Wend
rst.Close
If r > 0 Then
    'unload the values
    For i = 0 To UBound(mylist)
    If Len(mylist(i)) > 2 Then
        myval = myval & mylist(i)
    End If
    Next i
    MyBody = myval
Else
    MsgBox "No email body available", vbExclamation, "System Message"
    MyBody = ""
End If
'If you have pasted this into a module behind your main screen form then
'if you want to asign the value of the function to an unbound text box on your
'form so that you can see the result before you send it then un-comment the below line
txtBody = MyBody
'If you wish to assign the value of the function to directly to the email 'body'
'property of your outlook mail message then simply call the function from your email
'automation routine at the appropriate part like this (presumes your object is called objEmail)
'ie: objEmail.TextBody = MyBody
'or if using html format to send then use this
'ie: objEmail.HTMLBody = MyBody
Erase mylist
End Function
 

When I call it, it seems to go into never neverland.
Any suggestions?

Thanks.

Jeff

[Jim Doherty]

Jim -

Here's my adjusted code:

 Function MyBody()
On Error Resume Next
Dim rst As New ADODB.Recordset
Dim mysql As String, myval As String, r As Integer, i As Integer
Dim mylist()
'Create an SQL statement that satisfies your the recordset to use
'you will have to adapt this to suit your environment and circumstance
' Given you have used a Selected field to determine those to send
' the below syntax is based around that boolean value
' You obviously need to amend the table name to suit yours
mysql = "SELECT tblContractorInfo.contFirstName, tblContractorInfo.contLastName, tblContractorInfo.contFullName, tblContractorInfo.contDynamicsVendor, tblContractorInfo.contEMail, tblContractorInfo.contPhoneNumber, tblContractorInfo.contMobileNumber, tblContractorInfo.contDesiredHourly, tblContractorProjects.contractProjectNumber, dbo_tblProjectMain.ProjectName, tblContractorProjects.contractSelected, tblContractorProjects.contractSiteName, tblContractorProjects.contractTravelIncluded FROM (tblContractorProjects INNER JOIN tblContractorInfo ON tblContractorProjects.contractContractor = tblContractorInfo.contContractorID) INNER JOIN dbo_tblProjectMain ON tblContractorProjects.contractProjectNumber = dbo_tblProjectMain.ProjectID WHERE (((tblContractorProjects.contractProjectNumber)=[forms]![frmProjectSubContractors]![ProjectNumber]) AND ((tblContractorProjects.contractSelected)=Yes))"
rst.Open mysql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
r = 0
While Not rst.EOF
r = r + 1
ReDim Preserve mylist(r)
mylist(r) = rst.Fields("contFullName") & vbCrLf
mylist(r) = mylist(r) & rst.Fields("Phone Number").Name & ": " & rst.Fields("contPhoneNumber") & vbCrLf
mylist(r) = mylist(r) & rst.Fields("Mobile Number").Name & ": " & rst.Fields("contMobileNumber") & vbCrLf & vbCrLf
rst.MoveNext
Wend
rst.Close
If r > 0 Then
'unload the values
For i = 0 To UBound(mylist)
If Len(mylist(i)) > 2 Then
myval = myval & mylist(i)
End If
Next i
MyBody = myval
Else
MsgBox "No email body available", vbExclamation, "System Message"
MyBody = ""
End If
'If you have pasted this into a module behind your main screen form then
'if you want to asign the value of the function to an unbound text box on your
'form so that you can see the result before you send it then un-comment the below line
txtBody = MyBody
'If you wish to assign the value of the function to directly to the email 'body'
'property of your outlook mail message then simply call the function from your email
'automation routine at the appropriate part like this (presumes your object is called objEmail)
'ie: objEmail.Body = MyBody
'or if using html format to send then use this
'ie: objEmail.HTMLBody = MyBody
Erase mylist
End Function
 

When I call it, it seems to go into never neverland.
Any suggestions?

Thanks.

Jeff

Jeff

The function was built strictly around your post example...had nothing to go on other than what you posted. It does work on the fields as per your posting if they are different then you need to make sure that any amendments to the function totally reflect that.

I can see discrepancies at first glance already with your amendments. Make sure your SQL returns some records before pasting in the syntax by running your SQL as a query first simply to make sure its syntax is correct. Saves me having to replicate your table structure my end in order to test your SQL in the absence of your database.

Remember the on error resume next will make this function return nothing if it encounters an error. The fact that the below loop has to work with fields it cannot recognise when compared with your fieldnames is gonna make it error (If you want to see where it is erroring out then simply comment out the on Error Resume Next line and let it error out in order for it to highlight the error line in the VBA window for you.

While Not rst.EOF
r = r + 1
ReDim Preserve mylist(r)
mylist(r) = rst.Fields("contFullName") & vbCrLf
mylist(r) = mylist(r) & rst.Fields("Phone Number").Name & ": " & rst.Fields("contPhoneNumber") & vbCrLf
mylist(r) = mylist(r) & rst.Fields("Mobile Number").Name & ": " & rst.Fields("contMobileNumber") & vbCrLf & vbCrLf
rst.MoveNext
Wend

Also change the objEmail.TextBody reference i placed in there to objEmail.Body (my mistake on my pasting originally)

If this becomes tedious to resolve then send me an empty copy of your db and I'll insert it for you. This should be easy to sort out and worked perfectly on the small sample mdb I built this end when writing it.

Incidentally.... are you by any chance working with at least one of those tables from SQL server? I say that because of the useage of 'dbo' as a prefix for one of your table names there....just merely interested thats's all, although strict sql server naming convention would be "dbo." not "dbo_" )

Regards

Jim

[atlbearcat]

Jim -

Check your PM....I sent you a note.

The tables are SQL, but I've renamed them to the table names listed.

Thanks.

Jeff

[Jim Doherty]

Jim -

Check your PM....I sent you a note.

The tables are SQL, but I've renamed them to the table names listed.

Thanks.

Jeff

Jeff check your PM's and mail