emailing a form

[jayme]

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!!

[jayme]

ok-i got it to compile-so thats good. one step closer! :-)

[Denburt]

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?

Set rs1 = db.openrecordset("Select ELECTRIC from EmailAddresses where " & [Forms]![JOB TRACKING form]![001] & " = true")
If Not rs1.EOF Then
rs1.MoveFirst
Do Until rs1.EOF
 

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.

If Not IsNull(rs1!Electric) And Len(rs1!Electric) > 0 Then
strMailto = strMailto & rs1!Electric & ";"
End If
rs1.MoveNext
Loop
End If
rs1.Close
Set rs1 = Nothing
DoCmd.SendObject acReport, strDocName, acFormatRTF, strMailto, "", "", strSubject, , True, ""
 

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.

[jayme]

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"

[Denburt]

No option to debug when you get this message?

Try this just to verify:

Set rs1 = db.openrecordset("Select ELECTRIC from EmailAddresses where Electric = true")
 

[jayme]

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:

Set rs1 = db.openrecordset("Select ELECTRIC from EmailAddresses where Electric = true")
 

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...

[Denburt]

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 [e-mail];[e-mail] 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?

[jayme]

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?

[Denburt]

This is probably what you want:

if me!001 = true then
Set rs1 = db.openrecordset("Select ELECTRIC from EmailAddresses")
elseif me!002 then
Set rs1 = db.openrecordset("Select Plumbing from EmailAddresses")
elseif me!003 then
 
etc.
end if
 
 

[jayme]

cool-thanks.
do i still keep the other syntax from before?

If Me![001] = True Then
Set rs1 = db.openrecordset("Select Electric from EmailAddresses")
If Not rs1.EOF Then
rs1.MoveFirst
Do Until rs1.EOF
ElseIf Me![002] = True Then
Set rs2 = db.openrecordset("Select Plumbing from EmailAddresses")
End If
If Not IsNull(rs1!Electric) And Len(rs1!Electric) > 0 Then
strMailto = strMailto & rs1!Electric & ";"
End If
If Not IsNull(rs2!Plumbing) And Len(rs2!Plumbing) > 0 Then
strMailto = strMailto & rs2!Plumbing & ";"
End If
rs1.MoveNext
Loop
rs1.Close
Set rs1 = Nothing
 

[jayme]

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:

Private Sub cmdEmailReport_Click()
On Error GoTo cmdEMailReport_Click_Err
Dim strMsg As String, strTitle As String
Dim intStyle As Integer
Dim StrCriterion As String
Dim strMailto As String
Dim strSubject As String
Dim strDocName As String
Dim db As database
Dim rs As Recordset
Dim rs1 As Recordset
Dim rs2 As Recordset
Set db = CurrentDb
DoCmd.RunCommand acCmdSaveRecord
 
If Me![001] = True Then
Set rs1 = db.openrecordset("Select Electric from EmailAddresses")
    If Not rs1.EOF Then
    rs1.MoveFirst
    End If
End If
Do Until rs1.EOF
Loop
If Me![002] = True Then
Set rs2 = db.openrecordset("Select Plumbing from EmailAddresses")
    rs2.Requery
    If Not IsNull(rs1!Electric) And Len(rs1!Electric) > 0 Then
    strMailto = strMailto & rs1!Electric & ";"
End If
If Not IsNull(rs2!Plumbing) And Len(rs2!Plumbing) > 0 Then
strMailto = strMailto & rs2!Plumbing & ";"
End If
rs1.MoveNext
End If
rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
DoCmd.SendObject acReport, strDocName, acFormatRTF, strMailto, "", "", strSubject, , True, ""
 
    strSubject = ":: INQUIRY NO. " & [Inquiry No] & " ::"
    strDocName = "JOB TRACKING"
    StrCriterion = " [Inquiry No]=" & [Forms]![JOB TRACKING form].[Inquiry No]
 
Me.Visible = False
 
DoCmd.OpenReport "JOB TRACKING", acPreview, , StrCriterion
 
 
DoCmd.Minimize
 
DoCmd.SendObject acReport, strDocName, acFormatRTF, strMailto, "", "", strSubject, , True, ""
 
DoCmd.Close acReport, "JOB TRACKING"
 
cmdEMailReport_Click_Exit:
Exit Sub
cmdEMailReport_Click_Err:
    MsgBox Error$
    Resume cmdEMailReport_Click_Exit
 
End Sub
 

thank you again!

[Denburt]

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.

Dim rs1 As Recordset
Dim myField as string
Set db = CurrentDb
DoCmd.RunCommand acCmdSaveRecord
if me!001 = true then
Set rs1 = db.openrecordset("Select ELECTRIC from EmailAddresses")
myField ="Electric"
elseif me!002 then
Set rs1 = db.openrecordset("Select Plumbing from EmailAddresses")
myField ="Plumbing"
elseif me!003 then
Set rs1 = db.openrecordset("Select Plumbing from EmailAddresses")
myField ="Plumbing"
end if
If not rs1.eof then
rs1.movefirst
Do Until rs1.EOF
strMailto = strMailto & rs1!Electric & ";"
rts1.movenext
Loop
End if
    strSubject = ":: INQUIRY NO. " & [Inquiry No] & " ::"
    strDocName = "JOB TRACKING"
    StrCriterion = " [Inquiry No]=" & [Forms]![JOB TRACKING form].[Inquiry No]
 
Me.Visible = False
 
DoCmd.OpenReport "JOB TRACKING", acPreview, , StrCriterion
 
DoCmd.Minimize
 
DoCmd.SendObject acReport, strDocName, acFormatRTF, strMailto, "", "", strSubject, , True, ""
 
DoCmd.Close acReport, "JOB TRACKING"
 
cmdEMailReport_Click_Exit:
Exit Sub
cmdEMailReport_Click_Err:
    MsgBox Error$
    Resume cmdEMailReport_Click_Exit
 
End Sub
end if
 

[jayme]

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! :-)

[jayme]

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..

Private Sub cmdEmailReport_Click()
On Error GoTo cmdEMailReport_Click_Err
Dim strMsg As String, strTitle As String
Dim intStyle As Integer
Dim StrCriterion As String
Dim strMailto As String
Dim strSubject As String
Dim strDocName As String
Dim db As database
Dim rs1 As Recordset
Dim myField As String
Set db = CurrentDb
DoCmd.RunCommand acCmdSaveRecord
If Me![001] = True Then
Set rs1 = db.openrecordset("Select Electric from EmailAddresses")
myField = "Electric"
ElseIf Me![002] = True Then
Set rs1 = db.openrecordset("Select Plumbing from EmailAddresses")
myField = "Plumbing"
ElseIf Me![003] = True Then
Set rs1 = db.openrecordset("Select Plumbing from EmailAddresses")
myField = "Plumbing"
End If
If Not rs1.EOF Then
rs1.MoveFirst
Do Until rs1.EOF
strMailto = strMailto & rs1!Electric & ";"
rs1.MoveNext
Loop
End If
DoCmd.SendObject acReport, strDocName, acFormatRTF, strMailto, "", "", strSubject, , True, ""
 
    strSubject = ":: INQUIRY NO. " & [Inquiry No] & " ::"
    strDocName = "JOB TRACKING"
    StrCriterion = " [Inquiry No]=" & [Forms]![JOB TRACKING form].[Inquiry No]
 
Me.Visible = False
 
DoCmd.OpenReport "JOB TRACKING", acPreview, , StrCriterion
 
 
DoCmd.Minimize
 
DoCmd.SendObject acReport, strDocName, acFormatRTF, strMailto, "", "", strSubject, , True, ""
 
DoCmd.Close acReport, "JOB TRACKING"
 
cmdEMailReport_Click_Exit:
Exit Sub
cmdEMailReport_Click_Err:
    MsgBox Error$
    Resume cmdEMailReport_Click_Exit
 
End Sub
 

[Denburt]

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, ""

[jayme]

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?

Private Sub cmdEmailReport_Click()
On Error GoTo cmdEMailReport_Click_Err
Dim strMsg As String, strTitle As String
Dim intStyle As Integer
Dim StrCriterion As String
Dim strMailto As String
Dim strSubject As String
Dim strDocName As String
Dim db As database
Dim rs1 As Recordset
Dim myField As String
Set db = CurrentDb
DoCmd.RunCommand acCmdSaveRecord
DoCmd.SendObject acReport, strDocName, acFormatRTF, strMailto, "", "", strSubject, , True, ""
'On Error GoTo cmdEMailReport_Click_Err
If Me![001] = True Then
Set rs1 = db.openrecordset("Select Electric from EmailAddresses")
myField = "Electric"
ElseIf Me![002] = True Then
Set rs1 = db.openrecordset("Select Plumbing from EmailAddresses")
myField = "Plumbing"
ElseIf Me![003] = True Then
Set rs1 = db.openrecordset("Select Plumbing from EmailAddresses")
myField = "Plumbing"
End If
If Not rs1.EOF Then
rs1.MoveFirst
Do Until rs1.EOF
strMailto = strMailto & rs1!myField & ";"
rs1.MoveNext
Loop
End If
 
    strSubject = ":: INQUIRY NO. " & [Inquiry No] & " ::"
    strDocName = "JOB TRACKING"
    StrCriterion = " [Inquiry No]=" & [Forms]![JOB TRACKING form].[Inquiry No]
 
Me.Visible = False
 
DoCmd.OpenReport "JOB TRACKING", acPreview, , StrCriterion
 
 
DoCmd.Minimize
 
DoCmd.SendObject acReport, strDocName, acFormatRTF, strMailto, "", "", strSubject, , True, ""
 
DoCmd.Close acReport, "JOB TRACKING"
 
cmdEMailReport_Click_Exit:
Exit Sub
cmdEMailReport_Click_Err:
    MsgBox Error$
    Resume cmdEMailReport_Click_Exit
 
End Sub
 

[Denburt]

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.

[jayme]

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...??

[Denburt]

Before the sendobject command make sure you check all your variables.

If  len(strDocName) >0 and len(strMailto)>0 and len(strSubject) >0 then
DoCmd.SendObject acReport, strDocName, acFormatRTF, strMailto, , , strSubject, , True
else
Msgbox "Document Name: " & StrDocname & vbcrlf & " Mail Recipient: " & strMailto & vbcrlf & "Subject: " & strSubject
end if
 

vbcrlf is simply a carriage return to separate each item on a separate line.

[jayme]

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...

Dim strMsg As String, strTitle As String
Dim intStyle As Integer
Dim StrCriterion As String
Dim strMailto As String
Dim strSubject As String
Dim strDocName As String
Dim db As database
Dim rs1 As Recordset
Dim myField As String
Set db = CurrentDb
 

If len(strDocName) >0 and len(strMailto)>0 and len(strSubject) >0 then
DoCmd.SendObject acReport, strDocName, acFormatRTF, strMailto, , , strSubject, , True
else
Msgbox "Document Name: " & StrDocname & vbcrlf & " Mail Recipient: " & strMailto & vbcrlf & "Subject: " & strSubject
end if
 

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?

[Denburt]

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.

[jayme]

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..

Private Sub cmdEmailReport_Click()
On Error GoTo cmdEMailReport_Click_Err
Dim strMsg As String, strTitle As String
Dim intStyle As Integer
Dim StrCriterion As String
Dim strMailto As String
Dim strSubject As String
Dim strDocName As String
Dim db As database
Dim rs1 As Recordset
Dim myField As String
Set db = CurrentDb
DoCmd.RunCommand acCmdSaveRecord
If Len(strDocName) > 0 And Len(strMailto) > 0 And Len(strSubject) > 0 Then
DoCmd.SendObject acReport, strDocName, acFormatRTF, strMailto, , , strSubject, , True
Else
MsgBox "Document Name: " & strDocName & vbCrLf & " Mail Recipient: " & strMailto & vbCrLf & "Subject: " & strSubject
End If
'On Error GoTo cmdEMailReport_Click_Err
If Me![001] = True Then
Set rs1 = db.openrecordset("Select Electric from EmailAddresses")
myField = "Electric"
ElseIf Me![002] = True Then
Set rs1 = db.openrecordset("Select Plumbing from EmailAddresses")
myField = "Plumbing"
ElseIf Me![003] = True Then
Set rs1 = db.openrecordset("Select Plumbing from EmailAddresses")
myField = "Plumbing"
End If
If Not rs1.EOF Then
rs1.MoveFirst
Do Until rs1.EOF
strMailto = strMailto & rs1(myField) & ";"
rs1.MoveNext
Loop
End If
    strSubject = ":: INQUIRY NO. " & [Inquiry No] & " ::"
    strDocName = "JOB TRACKING"
    StrCriterion = " [Inquiry No]=" & [Forms]![JOB TRACKING form].[Inquiry No]
 
Me.Visible = False
 
DoCmd.OpenReport "JOB TRACKING", acPreview, , StrCriterion
 
 
DoCmd.Minimize
 
DoCmd.Close acReport, "JOB TRACKING"
 
cmdEMailReport_Click_Exit:
Exit Sub
cmdEMailReport_Click_Err:
    MsgBox Error$
    Resume cmdEMailReport_Click_Exit
 
End Sub
 

[Denburt]

I don't say this to often but hey I am feeling generous. Copy paste this:

Private Sub cmdEmailReport_Click()
'On Error GoTo cmdEMailReport_Click_Err
Dim strMsg As String, strTitle As String
Dim intStyle As Integer
Dim StrCriterion As String
Dim strMailto As String
Dim strSubject As String
Dim strDocName As String
Dim db As database
Dim rs1 As Recordset
Dim myField As String
Set db = CurrentDb
DoCmd.RunCommand acCmdSaveRecord
If Me![001] = True Then
Set rs1 = db.openrecordset("Select Electric from EmailAddresses")
myField = "Electric"
ElseIf Me![002] = True Then
Set rs1 = db.openrecordset("Select Plumbing from EmailAddresses")
myField = "Plumbing"
ElseIf Me![003] = True Then
Set rs1 = db.openrecordset("Select Plumbing from EmailAddresses")
myField = "Plumbing"
End If
If Not rs1.EOF Then
rs1.MoveFirst
Do Until rs1.EOF
strMailto = strMailto & rs1(myField) & ";"
rs1.MoveNext
Loop
End If
'Hopefully your inquiry no. is an integer if it is a string your criteria should look lik the following
'    StrCriterion = " [Inquiry No]='" & Me![Inquiry No] & "'"
 
    strSubject = ":: INQUIRY NO. " & Me![Inquiry No] & " ::"
    strDocName = "JOB TRACKING"
    StrCriterion = " [Inquiry No]=" & Me![Inquiry No]
 
Me.Visible = False
 
DoCmd.OpenReport "JOB TRACKING", acPreview, , StrCriterion
 
 
DoCmd.Minimize
 
If Len(strDocName) > 0 And Len(strMailto) > 0 And Len(strSubject) > 0 Then
DoCmd.SendObject acReport, strDocName, acFormatRTF, strMailto, , , strSubject, , True
Else
MsgBox "Document Name: " & strDocName & vbCrLf & " Mail Recipient: " & strMailto & vbCrLf & "Subject: " & strSubject
End If
 
DoCmd.Close acReport, "JOB TRACKING"
 
cmdEMailReport_Click_Exit:
Exit Sub
cmdEMailReport_Click_Err:
    MsgBox Error$
    Resume cmdEMailReport_Click_Exit
 
End Sub
 

[jayme]

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?!

[jayme]

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!!

[jayme]

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!!

Private Sub cmdEmailReport_Click()
'On Error GoTo cmdEMailReport_Click_Err
Dim strMsg As String, strTitle As String
Dim intStyle As Integer
Dim StrCriterion As String
Dim strMailto As String
Dim strSubject As String
Dim strDocName As String
Dim db As database
Dim rs1 As Recordset
Dim myField As String
Set db = CurrentDb
DoCmd.RunCommand acCmdSaveRecord
Select Case (Me![001])
Case Is = True
    Set rs1 = db.openrecordset("Select Electric from EmailAddresses")
    myField = "Electric"
    strMailto = strMailto & rs1(myField) & ";"
Case rs1.EOF
    rs1.MoveFirst
    Do Until rs1.EOF
    rs1.MoveNext
    Loop
End Select
Select Case (Me![002])
Case Is = True
    Set rs1 = db.openrecordset("Select Plumbing from EmailAddresses")
    myField = "Plumbing"
    strMailto = strMailto & rs1(myField) & ";"
Case rs1.EOF
    rs1.MoveFirst
    Do Until rs1.EOF
    rs1.MoveNext
    Loop
End Select
Select Case (Me![012])
Case Is = True
    Set rs1 = db.openrecordset("Select Hvac from EmailAddresses")
    myField = "Hvac"
    strMailto = strMailto & rs1(myField) & ";"
Case rs1.EOF
    rs1.MoveFirst
    Do Until rs1.EOF
    rs1.MoveNext
    Loop
End Select
    strSubject = ":: INQUIRY NO. " & Me![Inquiry No] & " ::"
    strDocName = "JOB TRACKING"
    StrCriterion = " [Inquiry No]=" & Me![Inquiry No]
    strText = "You have just received a new Inquiry!"
 
Me.Visible = False
 
DoCmd.OpenReport "JOB TRACKING", acPreview, , StrCriterion
 
 
DoCmd.Minimize
If Len(strDocName) > 0 And Len(strMailto) > 0 And Len(strSubject) > 0 Then
DoCmd.SendObject acReport, strDocName, acFormatRTF, strMailto, , , strSubject, , True
Else
MsgBox "Document Name: " & strDocName & vbCrLf & " Mail Recipient: " & strMailto & vbCrLf & "Subject: " & strSubject
End If
DoCmd.Close acReport, "JOB TRACKING"
 
cmdEMailReport_Click_Exit:
Exit Sub
cmdEMailReport_Click_Err:
    MsgBox Error$
    Resume cmdEMailReport_Click_Exit
 
End Sub
 

[Denburt]

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.

If Me![001] = True Then
Set rs1 = db.openrecordset("Select Electric from EmailAddresses")
myField = "Electric"
ElseIf Me![002] = True Then
Set rs1 = db.openrecordset("Select Plumbing from EmailAddresses")
myField = "Plumbing"
ElseIf Me![003] = True Then
Set rs1 = db.openrecordset("Select Plumbing from EmailAddresses")
myField = "Plumbing"
End If
If Not rs1.EOF Then
rs1.MoveFirst
Do Until rs1.EOF
strMailto = strMailto & rs1(myField) & ";"
rs1.MoveNext
Loop
End If

Replace that with this.

If Me![001] = True Then
Set rs1 = db.openrecordset("Select Electric from EmailAddresses")
myField = "Electric"
If Not rs1.EOF Then
rs1.MoveFirst
Do Until rs1.EOF
strMailto = strMailto & rs1(myField) & ";"
rs1.MoveNext
Loop
end if
rs1.close
If Me![002] = True Then
Set rs1 = db.openrecordset("Select Plumbing from EmailAddresses")
myField = "Plumbing"
If Not rs1.EOF Then
rs1.MoveFirst
Do Until rs1.EOF
strMailto = strMailto & rs1(myField) & ";"
rs1.MoveNext
Loop
end if
rs1.close
If Me![003] = True Then
Set rs1 = db.openrecordset("Select Plumbing from EmailAddresses")
myField = "Plumbing"
If Not rs1.EOF Then
rs1.MoveFirst
Do Until rs1.EOF
strMailto = strMailto & rs1(myField) & ";"
rs1.MoveNext
Loop
end if
rs1.close
End If
set rs1 = nothing
 

:) Have a nice day.

[jayme]

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!

[NeoPa]

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.

[jayme]

Here is the link to my new post about the checkboxes..
- missing checkboxes?? - rtf file -

THANK YOU!!

[Denburt]

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. :)

[jayme]

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!