Including Subform Detail in Email 1

[bjt52]

I am using the code below to send out emails and it works great. The problem I am having is I have a subform on my main form that I would like to include in the body of the email. I have tried using Form!frmMain!subDetail.Form.Account but I get
Run-time error '438':
Object doesn't support this property or method.
I am hope someone out there can give me some assistances.
Thanks


Private Sub cmdSend_Click()
Dim strEmail, strSubject As String, strBody As String
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

strEmail = CUID
strBody = FirstName & ",<BR>" & vbCrLf & _
"In conducting our monthly audits, we have identified discrepancies between what" & _
" Serives are showing on the Weekly Audit Reports and what is in the Billing System for (see list below)" & _
"It would be greatly appreciated if we could contact me by " & returndate & _
"so we can get this issue resolved as quickly as possible." & "<BR>" & "<BR>" & _
"Thank You," & "<BR>" & _
"Corporate Audit Team" & "<BR>" & _
"<BR>" _

strSubject = "Serivces not billing. " & _
"Customer: " & [Customer Name] & " " & _
" " & "Acct: " & [Customer Acct] & " " & _
[currentmonth] _

With objEmail
.To = strEmail
.Subject = strSubject
.HTMLBody = strBody
'.Send 'Will cause warning message
.Display
End With

Set objEmail = Nothing
End Sub

 

[Remou]

You are missing a letter:

Form[red]s[/red]!frmMain!subDetail.Form.Account

Also, make sure that subDetail is the name of the Subform Control, not the form contained by the control.

 

[bjt52]

Remou,
Thank you very much the problem was with the name of the Subform Control. The only problem is that it only prints the Account number, what I need is to show all of the data that is in the subform. Is it possible to have the information displayed the same way as it does when you copy and paste like it is from a spreadsheet..
Thank again for you initial response

 

[Remou]

You can loop through the recordset.

'Needs a Reference to Microsoft DAO Object Library
'Typed, not tested
Dim rs As DAO.Recordset

Set rs = Forms!frmMain!subDetail.Form.RecordsetClone
strTable="<Table>"
Do Until rs.EOF
strTable=strTable & "<TR>"
  For i = 0 To rs.Fields.Count - 1
    strTable=strTable & "<TD>" & rs.Fields(i) & "</TD>" 
  Next
strTable=strTable & "</TR>"
rs.MoveNext
Loop
strTable="</Table>"

It is also possible to build a report with all the data you need, write it to disk and read it into the email body. This example illustrates using RTF, but it works just as well with HTML output: thread705-1114129.

 

[bjt52]

Remou,
Please bear with me as I am just starting to learn VBA. I am getting a Varible not define error on the for i =0, can you tell me what I am missing? I added the code you provide to the end of my strBody.
Thanks

Private Sub Command32_Click()
'References: Oulook Library
Dim strEmail, strSubject As String, strBody As String, strTable As String
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem
Dim rs As DAO.Recordset


Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)
Set rs = Forms!frmMain!Detail.Form.RecordsetClone

'strTable = "<Table>"
strEmail = CUID
strBody = FirstName & ",<BR>" & vbCrLf & _
"In conducting our monthly audits, we have identified discrepancies between what" & _
" Serives are showing on the Weekly Audit Reports and what is in the Billing System for (see list below)" & _
"It would be greatly appreciated if we could contact me by " & returndate & _
"so we can get this issue resolved as quickly as possible." & "<BR>" & "<BR>" & _
"Thank You," & "<BR>" & _
"Corporate Audit Team" & "<BR>" & _
"<BR>" & _
strTable = "<Table>"
Do Until rs.EOF
strTable = strTable & "<TR>"
For i = 0 To rs.Fields.Count - 1
strTable = strTable & "<TD>" & rs.Fields(i) & "</TD>"
Next
strTable = strTable & "</TR>"
rs.MoveNext
Loop
strTable = "</Table>" _

'Forms!frmMain!subDetail.Form.[customeracct] _

strSubject = "Serivces not billing. " & _
"Customer: " & [Customer Name] & " " & _
" " & "Acct: " & [Customer Acct] & " " & _
[currentmonth] _

With objEmail
.To = strEmail
.Subject = strSubject
.HTMLBody = strBody
'.Send 'Will cause warning message
.Display
End With

Set objEmail = Nothing
End Sub

 

[Remou]

You probably have Option Explicit at the begining of the module, which is a good idea, but it means that you need to declare all variables. Just add:

[tt]Dim i As Integer[/tt]

at the start.

I note that you are adding line continuation characters ( _ ) to the end of some statements, this is not a good idea as the code will fall over if the blank line after the continuation character is deleted. For example, the blue [blue]_[/blue] are fine, but the red [red]_[/red] is a bad idea:

[tt]strSubject = "Serivces not billing. " & [blue]_[/blue]
"Customer: " & [Customer Name] & " " & [blue]_[/blue]
" " & "Acct: " & [Customer Acct] & " " & [blue]_[/blue]
[currentmonth] [red]_[/red][/tt]

I also note that you have not added the strTable string to the strBody string. This:

[tt]"<BR>" & _
strTable = "<Table>"[/tt]

Will not work. You need to separate the lines out by removing the continuation character and then add strTable to strBody at an appropriate place.

I also see that you have called your subform Detail, this is not a good idea as Deatil is the name of a form section and, as such, a reserved word:

[tt]List of reserved words in Access 2002 and Access 2003

http://support.microsoft.com/kb/q286335/

[/tt]

You may wish to look at naming conventions:
RVBA Naming Conventions

http://www.xoc.net/standards/rvbanc.asp

Naming Conventions for Microsoft Access : The Leszynski/Reddick Guidelines for Microsoft Access:

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccess/html/msdn_20naming.asp

Scoping and Object-Naming Compatibility [Access 2003 VBA Language Reference]:

http://msdn.microsoft.com/library/d...ngAndObjectNamingCompatibility_HV05188026.asp

Object-naming rules:

http://msdn.microsoft.com/library/d...off2000/html/defStandardNamingConventions.asp

And in help:
Guidelines for naming fields, controls, and objects

 

[Remou]

Oops, Detail is not on the list, but still, not a good idea.

 

[bjt52]

Remou,
Thank you for the links. I have several books and they dont not mention anything about naming convention, but I will study up and start using them.. I have also done the correction that you mention.
I am still having a problem with the code. The body text is now gone and it only prints the word “FALSE” in the body any ideal of what may be wrong?

"Corporate Audit Team" & "<BR>" & _
"<BR>" & strTable = "<Table>"
Do Until rs.EOF
strTable = strTable & "<TR>"
For i = 0 To rs.Fields.Count - 1
strTable = strTable & "<TD>" & rs.Fields(i) & "</TD>"
Next
strTable = strTable & "</TR>"
rs.MoveNext
Loop
strTable = "</Table>" _

'Forms!frmMain!subDetail.Form.[customeracct] _

strSubject = "Serivces not billing. " & _
"Customer: " & [Customer Name] & " " & _
" " & "Acct: " & [Customer Acct] & " " & _
[currentmonth]

 

[Remou]

There is an error in my original post. This
[tt]Loop
strTable = "</Table>" _[/tt]

Should read
[tt]Loop
strTable = strTable & "</Table>"[/tt]

Sorry, that is what comes from (me) just typing. :-(

 

[bjt52]

Remou,
I really hate being such a pest but I really grateful for you help as I have been working or this for over a month.
I am still getting the "False". Let me make sure that I am doing corrct.
I put you code in the last line of my body text:

"Corporate Audit Team" & "<BR>" & _
"<BR>" & strTable = "<Table>"
This is where it should correct? Would it help if you seen a screen shot of my database?
Thanks again for all you help..

 

[Remou]

No problem, we seem to have run into a bit of a misunderstanding. This bit:

[tt]rs.MoveNext
Loop
strTable = strTable & "</Table>"[/tt]

Adds the final "</Table>" to the HTML that is used to build a table in the preceeding lines. Now you have strTable equal to:

[tt]<Table><TR><TD>Blah</TD></TR></Table>[/tt]

Or such like. You now need to add this to the rest of the body:

[tt]"Corporate Audit Team" & "<BR>" & _
"<BR>" & strTable[/tt]

If you include '= "<Table>"' at this point, the answer is 'No, "Corporate Audit Team ..." is not equal to "<Table>"', or, in other words, False.

 

[bjt52]

Remou,
I would appreciate if you would take one more look at my code. I have made all the changes you suggested but I am still doing something wrong.
Thanks again for all you time.

Private Sub cmdEmail_Click()
'References: Oulook Library
Dim strEmail, strSubject As String, strBody As String, strTable As String, i As Integer
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem
Dim rs As DAO.Recordset


Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)
Set rs = Forms!frmMain!subDetail.Form.RecordsetClone

strEmail = CUID
strBody = FirstName & ",<BR>" & vbCrLf & _
"In conducting our monthly audits, we have identified discrepancies between what" & _
" Serives are showing on the Weekly Audit Reports and what is in the Billing System for (see list below)" & _
"It would be greatly appreciated if we could contact me by " & returndate & _
"so we can get this issue resolved as quickly as possible." & "<BR>" & "<BR>" & _
"Thank You," & "<BR>" & _
"Corporate Audit Team" & "<BR>" & _
"<BR>" & _
"<BR>" & strTable & "<Table>"
Do Until rs.EOF
strTable = strTable & "<TR>"
For i = 0 To rs.Fields.Count - 1
strTable = strTable & "<TD>" & rs.Fields(i) & "</TD>"
Next
strTable = strTable & "</TR>"
rs.MoveNext
Loop
strTable = "</Table>"

strSubject = "Feature provisioned but not billing. " & _
"Customer: " & [customer name] & " " & _
" " & "Acct: " & [customer Acct] & " " & _
[currentmonth]

With objEmail
.To = strEmail
.Subject = strSubject
.HTMLBody = strBody
'.Send 'Will cause warning message
.Display
End With

Set objEmail = Nothing
End Sub

 

[Remou]

Try:

Private Sub cmdEmail_Click()
'References: Oulook Library
Dim strEmail, strSubject As String, strBody As String, strTable As String, i As Integer
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem
Dim rs As DAO.Recordset


Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)
Set rs = Forms!frmMain!subDetail.Form.RecordsetClone

strEmail = CUID
strBody = FirstName & ",<BR>" & vbCrLf & _
    "In conducting our monthly audits, we have identified discrepancies between what" & _
    " Serives are showing on the Weekly Audit Reports and what is in the Billing System for (see list below)" & _
    "It would be greatly appreciated if we could contact me by " & returndate & _
    "so we can get this issue resolved     as quickly as possible." & "<BR>" & "<BR>" & _
    "Thank You," & "<BR>" & _
    "Corporate Audit Team" & "<BR>" & _
    "<BR>" & _
    "<BR>" [s]& strTable & "<Table>"[/s]

[blue]strTable = strTable & "<Table>"[/blue]

Do Until rs.EOF
strTable = strTable & "<TR>"
  For i = 0 To rs.Fields.Count - 1
    strTable = strTable & "<TD>" & rs.Fields(i) & "</TD>"
  Next
strTable = strTable & "</TR>"
rs.MoveNext
Loop
strTable = strTable & "</Table>"

[blue]strBody = strBody & "<BR>" & strTable[/blue]
   
strSubject = "Feature provisioned but not billing. " & _
    "Customer: " & [customer name] & " " & _
    " " & "Acct: " & [customer Acct] & " " & _
     [currentmonth]

With objEmail
    .To = strEmail
    .Subject = strSubject
    .HTMLBody = strBody
    '.Send 'Will cause warning message
    .Display
End With

Set objEmail = Nothing
End Sub

I hope that is clearer.

PS Some of the wording in strBody is not quite right.

 

[bjt52]

THANK YOU!
It is working except for two issues. When you open the database and generate the first email it work fine but if you select a different Rep from the combo box it will not show the detail in the email. I added Forms!frmMain!subDetail.Form.Requery thinking if I Requery the subform that it might work but it did not, any ideals?
Also, at the end of each row it adds a column that says “False”.
Thank again
Bill

 

[Remou]

You will need to MoveFirst:

[tt]rs.MoveFirst
Do Until rs.EOF ...[/tt]

You should also close the recordset at the end:
[tt]rs.Close
Set rs = Nothing[/tt]

As for the False at the end of each line, check if there is a boolean field in the recordset. There is no need to output all the fields, you can specify the fields that you want.

 

[bjt52]

Remou,
THANK YOU!!!!!
It works great. I really appreciate all the time that you took with help me.. Sorry it has taking me so long to get back with my gratitude.
Bill