469,315 Members | 2,141 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,315 developers. It's quick & easy.

emailing a form

jayme
83
I am not an advanced access user so I am in need of some suggestions here...
I work for a facility maintenance company that does a variety of jobs for many commercial companies with a several different trade groups we have (electrical, hvac, plumbing, painting, masonry, etc...) Our Trade Group Managers submit estimates and budgets for the jobs they are bidding to. I created a database to store all those jobs so we can track what manager has bid to any certain job, what trades are involved, if it has been accepted-rejected-in progress-and such. As of now the managers turn a written (paper) form into me to submit an estimating inquiry request and I put it in the database and print them out a receipt of all the information they submitted to me along with an Inquiry Number (which is the primary key in the database)-then that is how we keep track of that particular bid-with that unique Inquiry Number assigned to it.
Now we are wanting to be able to have this all electronically. So the managers can submit an inquiry request electronically and get a receipt back electronically-either by email or online or whatever. On the form they fill out there are several different trades that are able to be checked off by check boxes. After they are finished filling out the form I would like for there to be a button at the bottom to email that form to all the checked off trades. How do I go about doing that?


..any suggestions would be greatly appreciated.
Thank you so much!!
Mar 29 '07
80 4905
jayme
83
ok-i got it to compile-so thats good. one step closer! :-)
Apr 5 '07 #51
Denburt
1,356 Expert 1GB
I took out a couple of the groups and just left in the Electrical group to see if I could get this one working before I start adding in other groups.
I have gone through it and can not find what is up.
It is giving me the error where it cant find the field "|" in my expression. I don't know what that means-I can't find anywhere where there is a "|". SO I just want to make sure I have this right...

ELECTRIC the field in the EmailAddresses table where the electrical managers email address is.
JOB TRACKING form is the form we are on
001=the checkbox we are using

Do I have them all in the correct places of the syntax?

Expand|Select|Wrap|Line Numbers
  1. Set rs1 = db.openrecordset("Select ELECTRIC from EmailAddresses where " & [Forms]![JOB TRACKING form]![001] & " = true")
  2. If Not rs1.EOF Then
  3. rs1.MoveFirst
  4. Do Until rs1.EOF
  5.  
Yes looks good.



Also-where there is rs1!Electric-what is the "Electric" word to represent? Just wanted to make sure I have that labeled correctly as well.
Expand|Select|Wrap|Line Numbers
  1. If Not IsNull(rs1!Electric) And Len(rs1!Electric) > 0 Then
  2. strMailto = strMailto & rs1!Electric & ";"
  3. End If
  4. rs1.MoveNext
  5. Loop
  6. End If
  7. rs1.Close
  8. Set rs1 = Nothing
  9. DoCmd.SendObject acReport, strDocName, acFormatRTF, strMailto, "", "", strSubject, , True, ""
  10.  
rs1!ELECTRIC is reffering to the field ELECTRIC in the EmailAddresses table. You are essentially communicating with the recordset you created.
EXAMPLE ONLY:
rs1!YourFieldInTheRecordset
In your case you are using an SQL statement with one field ELECTRIC.
Apr 5 '07 #52
jayme
83
rs1!ELECTRIC is reffering to the field ELECTRIC in the EmailAddresses table. You are essentially communicating with the recordset you created.
EXAMPLE ONLY:
rs1!YourFieldInTheRecordset
In your case you are using an SQL statement with one field ELECTRIC.
thats what i thought-but just wanted to make sure i had it right. i dont know what else it could be. i have checked and double checked. that doesnt mean i didnt miss anything but even when i used the "Find" function it didn't come up with anywhere that has a "|". So that error is still coming up-driving me nuts! ahh!
Obviously I have something wrong here-I just have no clue what...is there anywhere else you can think of that I should look for this error?
"job tracking can't find the field '|' referred to in your expression"
Apr 5 '07 #53
Denburt
1,356 Expert 1GB
No option to debug when you get this message?

Try this just to verify:
Expand|Select|Wrap|Line Numbers
  1. Set rs1 = db.openrecordset("Select ELECTRIC from EmailAddresses where Electric = true")
  2.  
Apr 5 '07 #54
jayme
83
No option to debug when you get this message?
No-you can just push OK then that's it-nothing else happens.

Try this just to verify:
Expand|Select|Wrap|Line Numbers
  1. Set rs1 = db.openrecordset("Select ELECTRIC from EmailAddresses where Electric = true")
  2.  
Tried this one too-same thing..
so weird!
Do you think I have my EmailAddresses table set up the wrong way or something set up wrong outside of the actual syntax?
My EmailAddresses table has
Electric Plumbing etc.. -going across the top
then I have the email addreses under those-some have more than one email address in it-others will just have one.
(just want to make sure thats not causing issues)
I am not really sure what else it could be...
Apr 5 '07 #55
Denburt
1,356 Expert 1GB
My EmailAddresses table has
Electric Plumbing etc.. -going across the top
then I have the email addreses under those-some have more than one email address in it-others will just have one.
Sounds like this is the problem. I thought you said that "Electric" was a yes/no field.

I would also like to make sure that if you are storing more than one address in a field that they are separated by a colon 1@2.com;2@3.com etc.

Now we need to determine which records to pull and how to pull them.

I have reread some of the posts and I think what needs to be done is to clarify what is happening here. On your form you choose whether you want to send it to Electric, plumbing etc. right so far? Now what does the check box tell us, you didn't mention it in your table so I am only guessing that it only exists on the form correct?
Apr 5 '07 #56
jayme
83
In my table with Electric, Plumbing, etc going across the top I have the email addresses under those, but I have each email address in a seperate row-same column, but seperate row.

As far as my form-
There are checkboxes on there labeled as 001 - Electric, 002 - Plumbing, etc (the 001, 002, etc is the group number we use here) Anyways-so those are the labels but I went back and named them just with the number so there aren't spaces and dashes and all that. So we will just be using the 001, 002, 003, etc for the checkboxes-yes/no fields. These checkboxes do only exist on the form.
So I want to be able to check the 001 box on the form then it go to the EmailAddresses table and find Elecric then pull the email address(es) under it.
Does that make sense?
Apr 5 '07 #57
Denburt
1,356 Expert 1GB
This is probably what you want:

Expand|Select|Wrap|Line Numbers
  1. if me!001 = true then
  2. Set rs1 = db.openrecordset("Select ELECTRIC from EmailAddresses")
  3. elseif me!002 then
  4. Set rs1 = db.openrecordset("Select Plumbing from EmailAddresses")
  5. elseif me!003 then
  6.  
  7. etc.
  8. end if
  9.  
  10.  
Apr 5 '07 #58
jayme
83
cool-thanks.
do i still keep the other syntax from before?

Expand|Select|Wrap|Line Numbers
  1. If Me![001] = True Then
  2. Set rs1 = db.openrecordset("Select Electric from EmailAddresses")
  3. If Not rs1.EOF Then
  4. rs1.MoveFirst
  5. Do Until rs1.EOF
  6. ElseIf Me![002] = True Then
  7. Set rs2 = db.openrecordset("Select Plumbing from EmailAddresses")
  8. End If
  9. If Not IsNull(rs1!Electric) And Len(rs1!Electric) > 0 Then
  10. strMailto = strMailto & rs1!Electric & ";"
  11. End If
  12. If Not IsNull(rs2!Plumbing) And Len(rs2!Plumbing) > 0 Then
  13. strMailto = strMailto & rs2!Plumbing & ";"
  14. End If
  15. rs1.MoveNext
  16. Loop
  17. rs1.Close
  18. Set rs1 = Nothing
  19.  
Apr 6 '07 #59
jayme
83
i have been searching around to see what this error means but am unable to figure out why it is saying this...
"object doesn't support this property or method"
here is the code i have in the VBA:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdEmailReport_Click()
  2. On Error GoTo cmdEMailReport_Click_Err
  3. Dim strMsg As String, strTitle As String
  4. Dim intStyle As Integer
  5. Dim StrCriterion As String
  6. Dim strMailto As String
  7. Dim strSubject As String
  8. Dim strDocName As String
  9. Dim db As database
  10. Dim rs As Recordset
  11. Dim rs1 As Recordset
  12. Dim rs2 As Recordset
  13. Set db = CurrentDb
  14. DoCmd.RunCommand acCmdSaveRecord
  15.  
  16. If Me![001] = True Then
  17. Set rs1 = db.openrecordset("Select Electric from EmailAddresses")
  18.     If Not rs1.EOF Then
  19.     rs1.MoveFirst
  20.     End If
  21. End If
  22. Do Until rs1.EOF
  23. Loop
  24. If Me![002] = True Then
  25. Set rs2 = db.openrecordset("Select Plumbing from EmailAddresses")
  26.     rs2.Requery
  27.     If Not IsNull(rs1!Electric) And Len(rs1!Electric) > 0 Then
  28.     strMailto = strMailto & rs1!Electric & ";"
  29. End If
  30. If Not IsNull(rs2!Plumbing) And Len(rs2!Plumbing) > 0 Then
  31. strMailto = strMailto & rs2!Plumbing & ";"
  32. End If
  33. rs1.MoveNext
  34. End If
  35. rs1.Close
  36. rs2.Close
  37. Set rs1 = Nothing
  38. Set rs2 = Nothing
  39. DoCmd.SendObject acReport, strDocName, acFormatRTF, strMailto, "", "", strSubject, , True, ""
  40.  
  41.     strSubject = ":: INQUIRY NO. " & [Inquiry No] & " ::"
  42.     strDocName = "JOB TRACKING"
  43.     StrCriterion = " [Inquiry No]=" & [Forms]![JOB TRACKING form].[Inquiry No]
  44.  
  45. Me.Visible = False
  46.  
  47. DoCmd.OpenReport "JOB TRACKING", acPreview, , StrCriterion
  48.  
  49.  
  50. DoCmd.Minimize
  51.  
  52. DoCmd.SendObject acReport, strDocName, acFormatRTF, strMailto, "", "", strSubject, , True, ""
  53.  
  54. DoCmd.Close acReport, "JOB TRACKING"
  55.  
  56. cmdEMailReport_Click_Exit:
  57. Exit Sub
  58. cmdEMailReport_Click_Err:
  59.     MsgBox Error$
  60.     Resume cmdEMailReport_Click_Exit
  61.  
  62. End Sub
  63.  
thank you again!
Apr 6 '07 #60
Denburt
1,356 Expert 1GB
Take real close look at your previous post, your syntax for various controls etc. seem to need a bit of clean up. The following still needs some clean up but hopefully you will have some more understanding.

I was trying to stating my last post.

Expand|Select|Wrap|Line Numbers
  1. Dim rs1 As Recordset
  2. Dim myField as string
  3. Set db = CurrentDb
  4. DoCmd.RunCommand acCmdSaveRecord
  5. if me!001 = true then
  6. Set rs1 = db.openrecordset("Select ELECTRIC from EmailAddresses")
  7. myField ="Electric"
  8. elseif me!002 then
  9. Set rs1 = db.openrecordset("Select Plumbing from EmailAddresses")
  10. myField ="Plumbing"
  11. elseif me!003 then
  12. Set rs1 = db.openrecordset("Select Plumbing from EmailAddresses")
  13. myField ="Plumbing"
  14. end if
  15. If not rs1.eof then
  16. rs1.movefirst
  17. Do Until rs1.EOF
  18. strMailto = strMailto & rs1!Electric & ";"
  19. rts1.movenext
  20. Loop
  21. End if
  22.     strSubject = ":: INQUIRY NO. " & [Inquiry No] & " ::"
  23.     strDocName = "JOB TRACKING"
  24.     StrCriterion = " [Inquiry No]=" & [Forms]![JOB TRACKING form].[Inquiry No]
  25.  
  26. Me.Visible = False
  27.  
  28. DoCmd.OpenReport "JOB TRACKING", acPreview, , StrCriterion
  29.  
  30. DoCmd.Minimize
  31.  
  32. DoCmd.SendObject acReport, strDocName, acFormatRTF, strMailto, "", "", strSubject, , True, ""
  33.  
  34. DoCmd.Close acReport, "JOB TRACKING"
  35.  
  36. cmdEMailReport_Click_Exit:
  37. Exit Sub
  38. cmdEMailReport_Click_Err:
  39.     MsgBox Error$
  40.     Resume cmdEMailReport_Click_Exit
  41.  
  42. End Sub
  43. end if
  44.  
Apr 7 '07 #61
jayme
83
thanks...i was out of the office yesterday-but i've been playing around with the code. thank you for your help. ill see what i can do with this! :-)
Apr 10 '07 #62
jayme
83
ok-im stumped. i keep getting the error "object doesn't support this property or method" (sorry-i just dont understand why it keeps coming up-as much as i have tried..)
this is the code i am using..
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdEmailReport_Click()
  2. On Error GoTo cmdEMailReport_Click_Err
  3. Dim strMsg As String, strTitle As String
  4. Dim intStyle As Integer
  5. Dim StrCriterion As String
  6. Dim strMailto As String
  7. Dim strSubject As String
  8. Dim strDocName As String
  9. Dim db As database
  10. Dim rs1 As Recordset
  11. Dim myField As String
  12. Set db = CurrentDb
  13. DoCmd.RunCommand acCmdSaveRecord
  14. If Me![001] = True Then
  15. Set rs1 = db.openrecordset("Select Electric from EmailAddresses")
  16. myField = "Electric"
  17. ElseIf Me![002] = True Then
  18. Set rs1 = db.openrecordset("Select Plumbing from EmailAddresses")
  19. myField = "Plumbing"
  20. ElseIf Me![003] = True Then
  21. Set rs1 = db.openrecordset("Select Plumbing from EmailAddresses")
  22. myField = "Plumbing"
  23. End If
  24. If Not rs1.EOF Then
  25. rs1.MoveFirst
  26. Do Until rs1.EOF
  27. strMailto = strMailto & rs1!Electric & ";"
  28. rs1.MoveNext
  29. Loop
  30. End If
  31. DoCmd.SendObject acReport, strDocName, acFormatRTF, strMailto, "", "", strSubject, , True, ""
  32.  
  33.     strSubject = ":: INQUIRY NO. " & [Inquiry No] & " ::"
  34.     strDocName = "JOB TRACKING"
  35.     StrCriterion = " [Inquiry No]=" & [Forms]![JOB TRACKING form].[Inquiry No]
  36.  
  37. Me.Visible = False
  38.  
  39. DoCmd.OpenReport "JOB TRACKING", acPreview, , StrCriterion
  40.  
  41.  
  42. DoCmd.Minimize
  43.  
  44. DoCmd.SendObject acReport, strDocName, acFormatRTF, strMailto, "", "", strSubject, , True, ""
  45.  
  46. DoCmd.Close acReport, "JOB TRACKING"
  47.  
  48. cmdEMailReport_Click_Exit:
  49. Exit Sub
  50. cmdEMailReport_Click_Err:
  51.     MsgBox Error$
  52.     Resume cmdEMailReport_Click_Exit
  53.  
  54. End Sub
  55.  
Apr 11 '07 #63
Denburt
1,356 Expert 1GB
Place a comment before the errror trapping as such:
'On Error GoTo cmdEMailReport_Click_Err

Then you should get an error message that has a button on it called debug. press that and it should highlight the line in question.

Just reviewing the code i think your problem may lie in this line.

strMailto = strMailto & rs1!Electric & ";"

It should be:

strMailto = strMailto & rs1(MyField) & ";"

Also you have this line before you have any of the variables established.

DoCmd.SendObject acReport, strDocName, acFormatRTF, strMailto, "", "", strSubject, , True, ""
Apr 11 '07 #64
jayme
83
i made those changes..

i am now getting the error-
"the object type argument for the action or method is blank or invalid"
here is the updated code-did i make the changes you noted correctly?
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdEmailReport_Click()
  2. On Error GoTo cmdEMailReport_Click_Err
  3. Dim strMsg As String, strTitle As String
  4. Dim intStyle As Integer
  5. Dim StrCriterion As String
  6. Dim strMailto As String
  7. Dim strSubject As String
  8. Dim strDocName As String
  9. Dim db As database
  10. Dim rs1 As Recordset
  11. Dim myField As String
  12. Set db = CurrentDb
  13. DoCmd.RunCommand acCmdSaveRecord
  14. DoCmd.SendObject acReport, strDocName, acFormatRTF, strMailto, "", "", strSubject, , True, ""
  15. 'On Error GoTo cmdEMailReport_Click_Err
  16. If Me![001] = True Then
  17. Set rs1 = db.openrecordset("Select Electric from EmailAddresses")
  18. myField = "Electric"
  19. ElseIf Me![002] = True Then
  20. Set rs1 = db.openrecordset("Select Plumbing from EmailAddresses")
  21. myField = "Plumbing"
  22. ElseIf Me![003] = True Then
  23. Set rs1 = db.openrecordset("Select Plumbing from EmailAddresses")
  24. myField = "Plumbing"
  25. End If
  26. If Not rs1.EOF Then
  27. rs1.MoveFirst
  28. Do Until rs1.EOF
  29. strMailto = strMailto & rs1!myField & ";"
  30. rs1.MoveNext
  31. Loop
  32. End If
  33.  
  34.     strSubject = ":: INQUIRY NO. " & [Inquiry No] & " ::"
  35.     strDocName = "JOB TRACKING"
  36.     StrCriterion = " [Inquiry No]=" & [Forms]![JOB TRACKING form].[Inquiry No]
  37.  
  38. Me.Visible = False
  39.  
  40. DoCmd.OpenReport "JOB TRACKING", acPreview, , StrCriterion
  41.  
  42.  
  43. DoCmd.Minimize
  44.  
  45. DoCmd.SendObject acReport, strDocName, acFormatRTF, strMailto, "", "", strSubject, , True, ""
  46.  
  47. DoCmd.Close acReport, "JOB TRACKING"
  48.  
  49. cmdEMailReport_Click_Exit:
  50. Exit Sub
  51. cmdEMailReport_Click_Err:
  52.     MsgBox Error$
  53.     Resume cmdEMailReport_Click_Exit
  54.  
  55. End Sub
  56.  
Apr 11 '07 #65
Denburt
1,356 Expert 1GB
rs1!myField

Should be

rs1(myField)

Myfield is a variable that contains a field name the field name could change depending on which check box is checked. Since you are using incorrect syntax the variable "strMailto" is empty and gives you the error you are encountering.
Apr 11 '07 #66
jayme
83
rs1!myField

Should be

rs1(myField)

Myfield is a variable that contains a field name the field name could change depending on which check box is checked. Since you are using incorrect syntax the variable "strMailto" is empty and gives you the error you are encountering.
i made that change but i still get the error...??
Apr 11 '07 #67
Denburt
1,356 Expert 1GB
Before the sendobject command make sure you check all your variables.

Expand|Select|Wrap|Line Numbers
  1. If  len(strDocName) >0 and len(strMailto)>0 and len(strSubject) >0 then
  2. DoCmd.SendObject acReport, strDocName, acFormatRTF, strMailto, , , strSubject, , True
  3. else
  4. Msgbox "Document Name: " & StrDocname & vbcrlf & " Mail Recipient: " & strMailto & vbcrlf & "Subject: " & strSubject
  5. end if
  6.  
vbcrlf is simply a carriage return to separate each item on a separate line.
Apr 11 '07 #68
jayme
83
Before the sendobject command make sure you check all your variables.
so youre saying something is wrong with the variables?? i looked at those and they all seem to match up with what is in the rest of the syntax...
Expand|Select|Wrap|Line Numbers
  1. Dim strMsg As String, strTitle As String
  2. Dim intStyle As Integer
  3. Dim StrCriterion As String
  4. Dim strMailto As String
  5. Dim strSubject As String
  6. Dim strDocName As String
  7. Dim db As database
  8. Dim rs1 As Recordset
  9. Dim myField As String
  10. Set db = CurrentDb
  11.  
Expand|Select|Wrap|Line Numbers
  1. If len(strDocName) >0 and len(strMailto)>0 and len(strSubject) >0 then
  2. DoCmd.SendObject acReport, strDocName, acFormatRTF, strMailto, , , strSubject, , True
  3. else
  4. Msgbox "Document Name: " & StrDocname & vbcrlf & " Mail Recipient: " & strMailto & vbcrlf & "Subject: " & strSubject
  5. end if
  6.  
vbcrlf is simply a carriage return to separate each item on a separate line.
i added this in-when i put it before the "If Me![001] = True Then...." i get a message box that appears with Document Name: Mail Rec, etc then i click ok and it says "Object doesnt support this property or method"

so you think something is wrong with my variables or do you think it is something else?
Apr 11 '07 #69
Denburt
1,356 Expert 1GB
If you recieved the message box then one of the variables is empty so you will get an error if you try to send it.

Your message box should look similar to this except that one of these will not have the appropriatte information next to it.

"Document Name: " What ever your report name is.

"Mail Recipient: " Your complete list of recipients

"Subject: " Your subject line

After you click O.K. you say your still getting an error, so what line is giving you an error?

Per my earlier post:
Place a comment before the error trapping as such:
'On Error GoTo cmdEMailReport_Click_Err

Then you should get an error message that has a button on it called debug. press that and it should highlight the line in question.
Apr 11 '07 #70
jayme
83
If you recieved the message box then one of the variables is empty so you will get an error if you try to send it.

Your message box should look similar to this except that one of these will not have the appropriatte information next to it.

"Document Name: " What ever your report name is.

"Mail Recipient: " Your complete list of recipients

"Subject: " Your subject line
The message box just looks like this -
Document Name:
Mail Recipient:
Subject:
there is no other information in it...
After you click O.K. you say your still getting an error, so what line is giving you an error?
and yes-so then i just click ok then the error still comes up

i put that line in there-per your earlier post, but i dont know if i did it correctly-it never highlighted a specific line after the error came up. so i am still unsure of what line is giving issues here..

just in case it helps to see what i have here-dont think its any different from the last time but just in case..
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdEmailReport_Click()
  2. On Error GoTo cmdEMailReport_Click_Err
  3. Dim strMsg As String, strTitle As String
  4. Dim intStyle As Integer
  5. Dim StrCriterion As String
  6. Dim strMailto As String
  7. Dim strSubject As String
  8. Dim strDocName As String
  9. Dim db As database
  10. Dim rs1 As Recordset
  11. Dim myField As String
  12. Set db = CurrentDb
  13. DoCmd.RunCommand acCmdSaveRecord
  14. If Len(strDocName) > 0 And Len(strMailto) > 0 And Len(strSubject) > 0 Then
  15. DoCmd.SendObject acReport, strDocName, acFormatRTF, strMailto, , , strSubject, , True
  16. Else
  17. MsgBox "Document Name: " & strDocName & vbCrLf & " Mail Recipient: " & strMailto & vbCrLf & "Subject: " & strSubject
  18. End If
  19. 'On Error GoTo cmdEMailReport_Click_Err
  20. If Me![001] = True Then
  21. Set rs1 = db.openrecordset("Select Electric from EmailAddresses")
  22. myField = "Electric"
  23. ElseIf Me![002] = True Then
  24. Set rs1 = db.openrecordset("Select Plumbing from EmailAddresses")
  25. myField = "Plumbing"
  26. ElseIf Me![003] = True Then
  27. Set rs1 = db.openrecordset("Select Plumbing from EmailAddresses")
  28. myField = "Plumbing"
  29. End If
  30. If Not rs1.EOF Then
  31. rs1.MoveFirst
  32. Do Until rs1.EOF
  33. strMailto = strMailto & rs1(myField) & ";"
  34. rs1.MoveNext
  35. Loop
  36. End If
  37.     strSubject = ":: INQUIRY NO. " & [Inquiry No] & " ::"
  38.     strDocName = "JOB TRACKING"
  39.     StrCriterion = " [Inquiry No]=" & [Forms]![JOB TRACKING form].[Inquiry No]
  40.  
  41. Me.Visible = False
  42.  
  43. DoCmd.OpenReport "JOB TRACKING", acPreview, , StrCriterion
  44.  
  45.  
  46. DoCmd.Minimize
  47.  
  48. DoCmd.Close acReport, "JOB TRACKING"
  49.  
  50. cmdEMailReport_Click_Exit:
  51. Exit Sub
  52. cmdEMailReport_Click_Err:
  53.     MsgBox Error$
  54.     Resume cmdEMailReport_Click_Exit
  55.  
  56. End Sub
  57.  
Apr 11 '07 #71
Denburt
1,356 Expert 1GB
I don't say this to often but hey I am feeling generous. Copy paste this:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdEmailReport_Click()
  2. 'On Error GoTo cmdEMailReport_Click_Err
  3. Dim strMsg As String, strTitle As String
  4. Dim intStyle As Integer
  5. Dim StrCriterion As String
  6. Dim strMailto As String
  7. Dim strSubject As String
  8. Dim strDocName As String
  9. Dim db As database
  10. Dim rs1 As Recordset
  11. Dim myField As String
  12. Set db = CurrentDb
  13. DoCmd.RunCommand acCmdSaveRecord
  14. If Me![001] = True Then
  15. Set rs1 = db.openrecordset("Select Electric from EmailAddresses")
  16. myField = "Electric"
  17. ElseIf Me![002] = True Then
  18. Set rs1 = db.openrecordset("Select Plumbing from EmailAddresses")
  19. myField = "Plumbing"
  20. ElseIf Me![003] = True Then
  21. Set rs1 = db.openrecordset("Select Plumbing from EmailAddresses")
  22. myField = "Plumbing"
  23. End If
  24. If Not rs1.EOF Then
  25. rs1.MoveFirst
  26. Do Until rs1.EOF
  27. strMailto = strMailto & rs1(myField) & ";"
  28. rs1.MoveNext
  29. Loop
  30. End If
  31. 'Hopefully your inquiry no. is an integer if it is a string your criteria should look lik the following
  32. '    StrCriterion = " [Inquiry No]='" & Me![Inquiry No] & "'"
  33.  
  34.     strSubject = ":: INQUIRY NO. " & Me![Inquiry No] & " ::"
  35.     strDocName = "JOB TRACKING"
  36.     StrCriterion = " [Inquiry No]=" & Me![Inquiry No]
  37.  
  38. Me.Visible = False
  39.  
  40. DoCmd.OpenReport "JOB TRACKING", acPreview, , StrCriterion
  41.  
  42.  
  43. DoCmd.Minimize
  44.  
  45. If Len(strDocName) > 0 And Len(strMailto) > 0 And Len(strSubject) > 0 Then
  46. DoCmd.SendObject acReport, strDocName, acFormatRTF, strMailto, , , strSubject, , True
  47. Else
  48. MsgBox "Document Name: " & strDocName & vbCrLf & " Mail Recipient: " & strMailto & vbCrLf & "Subject: " & strSubject
  49. End If
  50.  
  51. DoCmd.Close acReport, "JOB TRACKING"
  52.  
  53. cmdEMailReport_Click_Exit:
  54. Exit Sub
  55. cmdEMailReport_Click_Err:
  56.     MsgBox Error$
  57.     Resume cmdEMailReport_Click_Exit
  58.  
  59. End Sub
  60.  
Apr 12 '07 #72
jayme
83
well thank you so much!! :-) i actually got a message box that has an error in it but then it did show me the line that it is tripping up on this time...

error:OBJECT DOESNT SUPPORT THIS PROPERTY OR METHOD

then when i click to debug it highlights this line:
If Me![001] = True Then

i tried changing it several different ways-but each time it still comes back with an error. not sure what is wrong with that line?!
Apr 12 '07 #73
jayme
83
SO CLOSE!!!
ok-forget that last post-i guess i got a little ancy-but i was messing around with it some more and it actually worked...well just about worked! :-)
i actually did NOT get any errors-so that is definitely a plus.
as i was testing it i was checking off different boxes and it seems that if i check 001 and 002 then it will only get the email addresses for the 001 checkbox and not for the 002 checkbox. is there something i can add in there to allow it to gather all the email addresses based on all the checkboxes checked?
i guess now since [001] = true then it doesnt go on to see if [002] = true and so on.
thank you SO much for your help-i really appreciate it!!
Apr 12 '07 #74
jayme
83
CLOSER...
ok this time i think i am officially stuck! :-) sorry...
i was looking into the part about emailing to more than one email address when more than one box is checked and I came up with this...i am not sure how correct this is but it seems to work-all but one detail. so when i use the code below it allows me to email to several email addresses based on the different checkboxes marked off..BUT - for instance - if there is more than one email address under Electric then it just gets the first address-not all the ones in the list...so is there a way to ask it to capture all the address in the list? maybe this isnt the way to go about it-but it got me a little closer so i thought maybe i was on the right track-let me know what you think! :-)
thank you!!
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdEmailReport_Click()
  2. 'On Error GoTo cmdEMailReport_Click_Err
  3. Dim strMsg As String, strTitle As String
  4. Dim intStyle As Integer
  5. Dim StrCriterion As String
  6. Dim strMailto As String
  7. Dim strSubject As String
  8. Dim strDocName As String
  9. Dim db As database
  10. Dim rs1 As Recordset
  11. Dim myField As String
  12. Set db = CurrentDb
  13. DoCmd.RunCommand acCmdSaveRecord
  14. Select Case (Me![001])
  15. Case Is = True
  16.     Set rs1 = db.openrecordset("Select Electric from EmailAddresses")
  17.     myField = "Electric"
  18.     strMailto = strMailto & rs1(myField) & ";"
  19. Case rs1.EOF
  20.     rs1.MoveFirst
  21.     Do Until rs1.EOF
  22.     rs1.MoveNext
  23.     Loop
  24. End Select
  25. Select Case (Me![002])
  26. Case Is = True
  27.     Set rs1 = db.openrecordset("Select Plumbing from EmailAddresses")
  28.     myField = "Plumbing"
  29.     strMailto = strMailto & rs1(myField) & ";"
  30. Case rs1.EOF
  31.     rs1.MoveFirst
  32.     Do Until rs1.EOF
  33.     rs1.MoveNext
  34.     Loop
  35. End Select
  36. Select Case (Me![012])
  37. Case Is = True
  38.     Set rs1 = db.openrecordset("Select Hvac from EmailAddresses")
  39.     myField = "Hvac"
  40.     strMailto = strMailto & rs1(myField) & ";"
  41. Case rs1.EOF
  42.     rs1.MoveFirst
  43.     Do Until rs1.EOF
  44.     rs1.MoveNext
  45.     Loop
  46. End Select
  47.     strSubject = ":: INQUIRY NO. " & Me![Inquiry No] & " ::"
  48.     strDocName = "JOB TRACKING"
  49.     StrCriterion = " [Inquiry No]=" & Me![Inquiry No]
  50.     strText = "You have just received a new Inquiry!"
  51.  
  52. Me.Visible = False
  53.  
  54. DoCmd.OpenReport "JOB TRACKING", acPreview, , StrCriterion
  55.  
  56.  
  57. DoCmd.Minimize
  58. If Len(strDocName) > 0 And Len(strMailto) > 0 And Len(strSubject) > 0 Then
  59. DoCmd.SendObject acReport, strDocName, acFormatRTF, strMailto, , , strSubject, , True
  60. Else
  61. MsgBox "Document Name: " & strDocName & vbCrLf & " Mail Recipient: " & strMailto & vbCrLf & "Subject: " & strSubject
  62. End If
  63. DoCmd.Close acReport, "JOB TRACKING"
  64.  
  65. cmdEMailReport_Click_Exit:
  66. Exit Sub
  67. cmdEMailReport_Click_Err:
  68.     MsgBox Error$
  69.     Resume cmdEMailReport_Click_Exit
  70.  
  71. End Sub
  72.  
Apr 12 '07 #75
Denburt
1,356 Expert 1GB
O.K. I was thinking that the check boxes were in an option group and they would only be able to select one option. in my original copy/paste code replace the following.

Expand|Select|Wrap|Line Numbers
  1. If Me![001] = True Then
  2. Set rs1 = db.openrecordset("Select Electric from EmailAddresses")
  3. myField = "Electric"
  4. ElseIf Me![002] = True Then
  5. Set rs1 = db.openrecordset("Select Plumbing from EmailAddresses")
  6. myField = "Plumbing"
  7. ElseIf Me![003] = True Then
  8. Set rs1 = db.openrecordset("Select Plumbing from EmailAddresses")
  9. myField = "Plumbing"
  10. End If
  11. If Not rs1.EOF Then
  12. rs1.MoveFirst
  13. Do Until rs1.EOF
  14. strMailto = strMailto & rs1(myField) & ";"
  15. rs1.MoveNext
  16. Loop
  17. End If

Replace that with this.

Expand|Select|Wrap|Line Numbers
  1. If Me![001] = True Then
  2. Set rs1 = db.openrecordset("Select Electric from EmailAddresses")
  3. myField = "Electric"
  4. If Not rs1.EOF Then
  5. rs1.MoveFirst
  6. Do Until rs1.EOF
  7. strMailto = strMailto & rs1(myField) & ";"
  8. rs1.MoveNext
  9. Loop
  10. end if
  11. rs1.close
  12. If Me![002] = True Then
  13. Set rs1 = db.openrecordset("Select Plumbing from EmailAddresses")
  14. myField = "Plumbing"
  15. If Not rs1.EOF Then
  16. rs1.MoveFirst
  17. Do Until rs1.EOF
  18. strMailto = strMailto & rs1(myField) & ";"
  19. rs1.MoveNext
  20. Loop
  21. end if
  22. rs1.close
  23. If Me![003] = True Then
  24. Set rs1 = db.openrecordset("Select Plumbing from EmailAddresses")
  25. myField = "Plumbing"
  26. If Not rs1.EOF Then
  27. rs1.MoveFirst
  28. Do Until rs1.EOF
  29. strMailto = strMailto & rs1(myField) & ";"
  30. rs1.MoveNext
  31. Loop
  32. end if
  33. rs1.close
  34. End If
  35. set rs1 = nothing
  36.  
:) Have a nice day.
Apr 12 '07 #76
jayme
83
awesome!! thank you so much! that works great! i appreciate your help so much!!
im going to start another post about the checkboxes not showing up whent the file opens up from the email- so if you want to join in you can-but i thought i would start another post for that - just so you dont feel obligated to keep helping me and so other people can throw in their opinions...
thanks again! youre the best!!
have a great day!
Apr 12 '07 #77
NeoPa
32,173 Expert Mod 16PB
Good idea Jayme, and I agree he's a pretty awesome guy to have around helping.
Can I suggest that you post a link in here to your new thread when you've done it. If you're not sure how, just let me know when it's ready and I'll do it for you.
Apr 12 '07 #78
jayme
83
Here is the link to my new post about the checkboxes..
- missing checkboxes?? - rtf file -

THANK YOU!!
Apr 12 '07 #79
Denburt
1,356 Expert 1GB
awesome!! thank you so much! that works great! i appreciate your help so much!!
im going to start another post about the checkboxes not showing up whent the file opens up from the email- so if you want to join in you can-but i thought i would start another post for that - just so you dont feel obligated to keep helping me and so other people can throw in their opinions...
thanks again! youre the best!!
have a great day!
You are quite welcome, I just love it when it all comes together. Glad it is working. When i get a chance i will check in on your other post. :)
Apr 12 '07 #80
jayme
83
You are quite welcome, I just love it when it all comes together. Glad it is working. When i get a chance i will check in on your other post. :)
me too-its a good feeling to finally get something accomplished...couldnt have done it without you for sure!
thanks!
Apr 12 '07 #81

Post your reply

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

Similar topics

reply views Thread by Steven Scaife | last post: by
9 posts views Thread by Brendan MAther | last post: by
2 posts views Thread by Chuck | last post: by
1 post views Thread by Alfie | last post: by
2 posts views Thread by Paul Wilson | last post: by
4 posts views Thread by Mike Moore | last post: by
reply views Thread by paulhux174 | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.