By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 2,140 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

Putting Recordset Results In E-Mail Body

P: 17
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 (123) 456-7890 (321) 654-0987
Sally Smith Yes (111) 222-3333 (111) 333-4444
Mary Jones No (333) 555-1212 (333) 999-9999
Joe Blow Yes (222) 222-4444 (222) 555-0000

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

John Doe
(123) 456-7890
(321) 654-0987

Sally Smith
(111) 222-3333
(111) 333-4444

Joe Blow
(222) 222-4444
(222) 555-0000

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
Oct 8 '07 #1
Share this Question
Share on Google+
5 Replies


Jim Doherty
Expert 100+
P: 897
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 (123) 456-7890 (321) 654-0987
Sally Smith Yes (111) 222-3333 (111) 333-4444
Mary Jones No (333) 555-1212 (333) 999-9999
Joe Blow Yes (222) 222-4444 (222) 555-0000

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

John Doe
(123) 456-7890
(321) 654-0987

Sally Smith
(111) 222-3333
(111) 333-4444

Joe Blow
(222) 222-4444
(222) 555-0000

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?

Expand|Select|Wrap|Line Numbers
  1.  
  2. Function MyBody()
  3. On Error Resume Next
  4. Dim rst As New ADODB.Recordset
  5. Dim mysql As String, myval As String, r As Integer, i As Integer
  6. Dim mylist()
  7. 'Create an SQL statement that satisfies your the recordset to use
  8. 'you will have to adapt this to suit your environment and circumstance
  9. ' Given you have used a Selected field to determine those to send
  10. ' the below syntax is based around that boolean value
  11. ' You obviously need to amend the table name to suit yours
  12. mysql = "SELECT * FROM tblMyBody Where Selected <> 0 "
  13. rst.Open mysql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
  14. r = 0
  15. While Not rst.EOF
  16.     r = r + 1
  17.     ReDim Preserve mylist(r)
  18.     mylist(r) = rst.Fields("Name") & vbCrLf
  19.     mylist(r) = mylist(r) & rst.Fields("Phone Number").Name & ": " & rst.Fields("Phone Number") & vbCrLf
  20.     mylist(r) = mylist(r) & rst.Fields("Mobile Number").Name & ": " & rst.Fields("Mobile Number") & vbCrLf & vbCrLf
  21.     rst.MoveNext
  22. Wend
  23. rst.Close
  24. If r > 0 Then
  25.     'unload the values
  26.     For i = 0 To UBound(mylist)
  27.     If Len(mylist(i)) > 2 Then
  28.         myval = myval & mylist(i)
  29.     End If
  30.     Next i
  31.     MyBody = myval
  32. Else
  33.     MsgBox "No email body available", vbExclamation, "System Message"
  34.     MyBody = ""
  35. End If
  36. 'If you have pasted this into a module behind your main screen form then
  37. 'if you want to asign the value of the function to an unbound text box on your
  38. 'form so that you can see the result before you send it then un-comment the below line
  39. 'Me!txtBody = MyBody
  40. 'If you wish to assign the value of the function to directly to the email 'body'
  41. 'property of your outlook mail message then simply call the function from your email
  42. 'automation routine at the appropriate part like this (presumes your object is called objEmail)
  43. 'ie: objEmail.TextBody = MyBody
  44. 'or if using html format to send then use this
  45. 'ie: objEmail.HTMLBody = MyBody
  46. Erase mylist
  47. End Function
  48.  
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
Oct 9 '07 #2

P: 17
Jim -

Here's my adjusted code:

Expand|Select|Wrap|Line Numbers
  1.  Function MyBody()
  2. On Error Resume Next
  3. Dim rst As New ADODB.Recordset
  4. Dim mysql As String, myval As String, r As Integer, i As Integer
  5. Dim mylist()
  6. 'Create an SQL statement that satisfies your the recordset to use
  7. 'you will have to adapt this to suit your environment and circumstance
  8. ' Given you have used a Selected field to determine those to send
  9. ' the below syntax is based around that boolean value
  10. ' You obviously need to amend the table name to suit yours
  11. 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))"
  12. rst.Open mysql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
  13. r = 0
  14. While Not rst.EOF
  15.     r = r + 1
  16.     ReDim Preserve mylist(r)
  17.     mylist(r) = rst.Fields("contFullName") & vbCrLf
  18.     mylist(r) = mylist(r) & rst.Fields("Phone Number").Name & ": " & rst.Fields("contPhoneNumber") & vbCrLf
  19.     mylist(r) = mylist(r) & rst.Fields("Mobile Number").Name & ": " & rst.Fields("contMobileNumber") & vbCrLf & vbCrLf
  20.     rst.MoveNext
  21. Wend
  22. rst.Close
  23. If r > 0 Then
  24.     'unload the values
  25.     For i = 0 To UBound(mylist)
  26.     If Len(mylist(i)) > 2 Then
  27.         myval = myval & mylist(i)
  28.     End If
  29.     Next i
  30.     MyBody = myval
  31. Else
  32.     MsgBox "No email body available", vbExclamation, "System Message"
  33.     MyBody = ""
  34. End If
  35. 'If you have pasted this into a module behind your main screen form then
  36. 'if you want to asign the value of the function to an unbound text box on your
  37. 'form so that you can see the result before you send it then un-comment the below line
  38. txtBody = MyBody
  39. 'If you wish to assign the value of the function to directly to the email 'body'
  40. 'property of your outlook mail message then simply call the function from your email
  41. 'automation routine at the appropriate part like this (presumes your object is called objEmail)
  42. 'ie: objEmail.TextBody = MyBody
  43. 'or if using html format to send then use this
  44. 'ie: objEmail.HTMLBody = MyBody
  45. Erase mylist
  46. End Function
  47.  
When I call it, it seems to go into never neverland.
Any suggestions?

Thanks.

Jeff
Oct 9 '07 #3

Jim Doherty
Expert 100+
P: 897
Jim -

Here's my adjusted code:

Expand|Select|Wrap|Line Numbers
  1.  Function MyBody()
  2. On Error Resume Next
  3. Dim rst As New ADODB.Recordset
  4. Dim mysql As String, myval As String, r As Integer, i As Integer
  5. Dim mylist()
  6. 'Create an SQL statement that satisfies your the recordset to use
  7. 'you will have to adapt this to suit your environment and circumstance
  8. ' Given you have used a Selected field to determine those to send
  9. ' the below syntax is based around that boolean value
  10. ' You obviously need to amend the table name to suit yours
  11. 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))"
  12. rst.Open mysql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
  13. r = 0
  14. While Not rst.EOF
  15. r = r + 1
  16. ReDim Preserve mylist(r)
  17. mylist(r) = rst.Fields("contFullName") & vbCrLf
  18. mylist(r) = mylist(r) & rst.Fields("Phone Number").Name & ": " & rst.Fields("contPhoneNumber") & vbCrLf
  19. mylist(r) = mylist(r) & rst.Fields("Mobile Number").Name & ": " & rst.Fields("contMobileNumber") & vbCrLf & vbCrLf
  20. rst.MoveNext
  21. Wend
  22. rst.Close
  23. If r > 0 Then
  24. 'unload the values
  25. For i = 0 To UBound(mylist)
  26. If Len(mylist(i)) > 2 Then
  27. myval = myval & mylist(i)
  28. End If
  29. Next i
  30. MyBody = myval
  31. Else
  32. MsgBox "No email body available", vbExclamation, "System Message"
  33. MyBody = ""
  34. End If
  35. 'If you have pasted this into a module behind your main screen form then
  36. 'if you want to asign the value of the function to an unbound text box on your
  37. 'form so that you can see the result before you send it then un-comment the below line
  38. txtBody = MyBody
  39. 'If you wish to assign the value of the function to directly to the email 'body'
  40. 'property of your outlook mail message then simply call the function from your email
  41. 'automation routine at the appropriate part like this (presumes your object is called objEmail)
  42. 'ie: objEmail.Body = MyBody
  43. 'or if using html format to send then use this
  44. 'ie: objEmail.HTMLBody = MyBody
  45. Erase mylist
  46. End Function
  47.  
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.

Expand|Select|Wrap|Line Numbers
  1. While Not rst.EOF
  2. r = r + 1
  3. ReDim Preserve mylist(r)
  4. mylist(r) = rst.Fields("contFullName") & vbCrLf
  5. mylist(r) = mylist(r) & rst.Fields("Phone Number").Name & ": " & rst.Fields("contPhoneNumber") & vbCrLf
  6. mylist(r) = mylist(r) & rst.Fields("Mobile Number").Name & ": " & rst.Fields("contMobileNumber") & vbCrLf & vbCrLf
  7. rst.MoveNext
  8. 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
Oct 9 '07 #4

P: 17
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
Oct 9 '07 #5

Jim Doherty
Expert 100+
P: 897
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
Oct 9 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.