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

[Denburt]

Sounds like you have some work cut out for you. I have a vbs script that will generate a questionaire and they can fill it in and send me the results... Sounds like this might be a prt of something you might use to get started on. The code is indepth and you will need a fair amount of coding to accomplish this. I will be willing ot help if I can but you will have to be patient. Let me know if I might be on the right track.

[developing]

sounds like Denburt got your back; but if that gets too complicated, try the new command button wizard...it has built in report/form funtions that allow you to email form/report

[Denburt]

I am so out of touch with the wizards I guess I should work with them more. :)

[jayme]

developing - im not sure i will be able to go that route (button wizard) or not...we have ms access 2000 so i don't know if my version has that option or not? thanks for your help!!

denburt - thank you so much! i really appreciate your help. i found a code yesterday that i thought might be sending me on the right track but i am not familiar with ms access coding at all so i don't know if it will work or not.
this is what i found -

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


'This forces the record to be saved.

DoCmd.RunCommand acCmdSaveRecord


'There is no need to check if the form is blank (as with report and preview) as the cmdEmailReport button will
'only be enabled if there is and e-mail address in CustomerEMailAddress field.

    

'If there is nothing in the subject control (CustomerEMailSubject) then the subject field in the e-mail client
'will display the name of the report.

If IsNull(Me!CustomerEMailSubject) Or Me!CustomerEMailSubject = "" Then


    strMailto = Me.CustomerEMailAddress
    strDocName = "Rpt_Customer"
    StrCriterion = " [CustomerID]=" & Forms![Frm_Customer].[CustomerID]

'This will hide the customer form


Me.Visible = False


 ' This will open the report with the same primary key as the form on the screen.In preview mode
   
    DoCmd.OpenReport "Rpt_Customer", acPreview, , StrCriterion
    
    
    
'This will minimize the report, whilst the e-mail in being prepared, I have tried not to have it open in preview but
'cannot get it to work.
    
    DoCmd.Minimize
    




'This will create the e-mail

DoCmd.SendObject acReport, strDocName, "RichTextFormat(*.rtf)", strMailto, "", "", strDocName, , True, ""



   'Tbis explains all the section of the e-mail see SendObject in help
      'Explanation DoCmd.SendObject [objecttype][, objectname][, outputformat][, to][, cc][, bcc][, subject][, messagetext][, editmessage][, templatefile]



Else

'If there is something in the subject control (CustomerEMailSubject) then the subject field in the e-mail client
'will display that data.

    strMailto = Me.CustomerEMailAddress
    strSubject = Me.CustomerEMailSubject
    strDocName = "Rpt_Customer"
    StrCriterion = " [CustomerID]=" & Forms![Frm_Customer].[CustomerID]


'This will hide the customer form


Me.Visible = False


 
   
 DoCmd.OpenReport "Rpt_Customer", acPreview, , StrCriterion
    
    
    
'This will minimize the report, whilst the e-mail in being prepared, I have tried not to have it open in preview but
'cannot get it to work.
    
    DoCmd.Minimize
    




'This will create the e-mail

DoCmd.SendObject acReport, strDocName, "RichTextFormat(*.rtf)", strMailto, "", "", strSubject, , True, ""

   'Tbis explains all the section of the e-mail see SendObject in help
      'Explanation DoCmd.SendObject [objecttype][, objectname][, outputformat][, to][, cc][, bcc][, subject][, messagetext][, editmessage][, templatefile]
End If

'This will close the report when you either cancel or send the e-mail. The Frm_Customer will open
DoCmd.Close acReport, "Rpt_Customer"


cmdEMailReport_Click_Exit:
    Exit Sub

cmdEMailReport_Click_Err:
    MsgBox Error$
    Resume cmdEMailReport_Click_Exit
    
    End Sub

i obviously need to change it a little to fit what my report/form is called but i thought maybe this might help a little. but i dont know...
they have given me a month or two to get this complete so i do have some time...well-let me know what you think!!
thanks again!

[Denburt]

The code looks good give it a try and let us know if you have any problems. A lot of the commands in the code can be highlighted then you hit F1 and you will see help on that topic.

[jayme]

i have been playing with it and was erasing the parts i didnt need and came up with 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


'This forces the record to be saved.

DoCmd.RunCommand acCmdSaveRecord

'If there is something in the subject control (CustomerEMailSubject) then the subject field in the e-mail client
'will display that data.

    strMailto = "jayme.kuenkel@viox-services.com"
    strSubject = ":: NEW INQUIRY ::"
    strDocName = "JOB TRACKING"
    StrCriterion = " [Inquiry No]=" & Forms![JOB TRACKING form].[Inquiry No]


'This will hide the customer form


Me.Visible = False


 
   
 DoCmd.OpenReport "JOB TRACKING", acPreview, , StrCriterion
    
    
    
'This will minimize the report, whilst the e-mail in being prepared, I have tried not to have it open in preview but
'cannot get it to work.
    
    DoCmd.Minimize
    




'This will create the e-mail

DoCmd.SendObject acReport, strDocName, "RichTextFormat(*.rtf)", strMailto, "", "", strSubject, , True, ""

   'Tbis explains all the section of the e-mail see SendObject in help
      'Explanation DoCmd.SendObject [objecttype][, objectname][, outputformat][, to][, cc][, bcc][, subject][, messagetext][, editmessage][, templatefile]


'This will close the report when you either cancel or send the e-mail. The Frm_Customer will open
DoCmd.Close acReport, "JOB TRACKING"


cmdEMailReport_Click_Exit:
Exit Sub

cmdEMailReport_Click_Err:
    MsgBox Error$
    Resume cmdEMailReport_Click_Exit
    
End Sub

it works as far as creating an email for me with that certain record on it - the thing is now-i had put in my email address(as a test to see what it would do), but i am going to need it to send it to several different emails and they won't be the same each time.
like i said before-there are checkboxes for the trade groups that get involved in the jobs. so if the electrical box is checked i want that record to go to the electrical manager-if the plumbing is checked then send it to the plumbing manager-and there could be when there are 2 or 3 boxes checked at one time. so is there a way to tell it that if that box is checked send it to this email address?
also-when it creates the file to put in the email, i opened it up to see what it looks like, and the checkboxes do not transfer over to the document it creates(opens up in word) so it has the lists of where the checkboxes are but there are no checks to see what information is checked off-not sure if there is a way around that?

[NeoPa]

Jayme,
Clearly you've put some work into this project, but please remember this forum is for answering relatively small, self-contained questions. Not about getting someone to take you through a whole project. If you are getting stuck, it's down to you to break it down to a level which is answerable by people with more experience than yourself, rather than expecting others to take you through the whole complicated process.

MODERATOR.

[Denburt]

Curious, what is your experience with VBA? I don't mind helping out, but one step at a time. You posted a lot of information about a lot of issues but we have to find a place to start.

[Denburt]

it works as far as creating an email for me with that certain record on it - the thing is now-i had put in my email address(as a test to see what it would do), but i am going to need it to send it to several different emails and they won't be the same each time.
like i said before-there are checkboxes for the trade groups that get involved in the jobs. so if the electrical box is checked i want that record to go to the electrical manager-if the plumbing is checked then send it to the plumbing manager-and there could be when there are 2 or 3 boxes checked at one time. so is there a way to tell it that if that box is checked send it to this email address?

rs =Select MyEmailField from MyTable where myCheckBox = true
'Then just loop through the recordset
if not rs.eof then
rs.movefirst
Do until rs.eof
if len(strMailto) >0 then 
strMailto = strMailto & ";" &  rs!MyEmailField
else
strMailto = rs!MyEmailField
rs.movenext
loop
end if

also-when it creates the file to put in the email, i opened it up to see what it looks like, and the checkboxes do not transfer over to the document it creates(opens up in word) so it has the lists of where the checkboxes are but there are no checks to see what information is checked off-not sure if there is a way around that?

Interesting, I will try and check this out when I get a chance. If anyone else wants to post on this matter feel free.

[jayme]

I do not have much experience with VBA-pretty much I am learning as I go here..so I'm just trying to find things here and there that will help me along the way...you are helping out a lot-and I do appreciate it. Sorry if I went into information overload here-breaking this down into steps sounds like a good way of going about this...thanks.
I will work with the code you posted before about emailing to the different email addresses today..and I'll see what I come up with.

I do have one quick question for anyone that may know what the deal is...
I was testing the "Email Report" button I have with the code I posted before and all of a sudden out of nowhere it starts creating an email and making the attachement an xls attachement and opening in excel instead of a rtf attachment and opening in word. The code is the same I was using before and it was working-just wasn't sure what might have changed...??

[Denburt]

Verify the fact that this line has not changed:

DoCmd.SendObject acReport, strDocName, "RichTextFormat(*.rtf)", strMailto, "", "", strSubject, , True, ""

[jayme]

i saw that line-where is tells it to create the rtf file..but it hasn't changed-that is still the same..that is why i was so confused that all of a sudden it changed. I dont remember doing anything else to it either..so if the code is exactly the same as before i wasn't sure what else it could be that was making it create an xls file??

[Denburt]

Try the following:

DoCmd.SendObject acReport, strDocName, acFormatRTF, strMailto, "", "", strSubject, , True, ""

[jayme]

still attaches as an xls...so weird!!