Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 319
  • Last Modified:

Syntax is wrong - Emailing object via Outlook.

I am trying to set the record source of a report and based on this value I want the option to email the report to the appropriate invidividuals.  what am I doing wrong?

code behind the button that activates the code:

Private Sub Command80_Click()
Dim strsql As String

     strsql = "SELECT DISTINCTROW TA_SR.MeasNo, TA_SR.FTEMNomenclature, TA_SR.NomenclatureModel, TA_SR.Equipment_ID, TA_SR.Multiple_ID, TA_SR.Job_Group," & _
                    " TA_SR.Project, TA_SR.Complete_By_Date, TA_SR.Requestor_ID, TA_SR.Service_Lab, TA_SR.Work_Code, TA_SR.Charge_Number, TA_SR.Disposition, TA_SR.RC1A," & _
                    " TA_SR.RC1B, TA_SR.RC2A, TA_SR.RC2B, TA_SR.Requestor_Comment_3, TA_SR.SRCtr, QS_TT_GeneralInfo.StableEmail, TA_SR.Requestor_Comment_1, TA_SR.Requestor_Comment_2, TA_SR.WSNo, TL_Labs.Contact" & _
                " FROM TL_Labs INNER JOIN (TA_SR INNER JOIN QS_TT_GeneralInfo ON" & _
                    " TA_SR.Requestor_ID = QS_TT_GeneralInfo.RequestorId) ON TL_Labs.LabCtr = TA_SR.Service_Lab" & _
                " WHERE (((TA_SR.Job_Group) = [Forms]![FS_SelectSr]![Job_Group]))" & _
                " ORDER BY TA_SR.Job_Group, TA_SR.SRCtr"
    DoCmd.OpenReport "RS_EmailSR", acViewPreview
        Me.RecordSource = strsql
End Sub


Private Sub Report_Close()
Dim strsql As String
Dim curdb As DAO.Database
Dim rs As Recordset
Dim nTo As String
Dim nCC As String

Select Case MsgBox("Do you wish to email this Service Request?", vbYesNo Or vbQuestion Or vbSystemModal Or vbDefaultButton1, "Sending Email")

    Case vbYes
        Set nTo = "Schaefer, Karen F.;" '"Smith, John, J.'" Green, Dave M; Moe, MaryE; Jones, Steven S;"
        Set rs.OpenRecordset = strsql
            Do Until rs.EOF = True
                nCC = rs.Fields("Service_Lab")
               DoCmd.SendObject acSendReport, "RS_EMailSR", acFormatRTF, _
                    nTo, nCC, , _
                    "Instrumentation Needs", "Please ship the following items to the appropriate labs."
             rs.MoveNext
           Loop
        rs.Close
    Case vbNo
    Exit Sub
        Set nTo = Nothing
        Set nCC = Nothing
        Set rs = Nothing
End Select
End Sub

Thanks,

Karen
0
Karen Schaefer
Asked:
Karen Schaefer
  • 23
  • 15
  • 9
2 Solutions
 
Rey Obrero (Capricorn1)Commented:

try

 " WHERE (((TA_SR.Job_Group) = " & [Forms]![FS_SelectSr]![Job_Group] ))" & _

if Job_Group is text


 " WHERE (((TA_SR.Job_Group) = '" & [Forms]![FS_SelectSr]![Job_Group] &"'"))" & _
0
 
Karen SchaeferAuthor Commented:
thanks for the suggestion, however, the sql statement works fine - it dies when the nTo is trying to be set.

I tried the nTo as receipent and nCC as receipent and as strings - what should I be using to pass the names to those to email to?  The nto is fixed based on the list I provide where as nCC is based on the Service Lab field in the sql string.

K
0
 
rockiroadsCommented:
if u want to set the recordsource of as report

then this line wont do it surely

Me.RecordSource = strsql

this is setting the recordsource of the form that u arein

try this

Reports("RS_EmailSR").Recordsource = strsql

0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
Karen SchaeferAuthor Commented:
ok what about passing the receipents names in the email export of the report.

k
0
 
rockiroadsCommented:
another thing is to base your report on this sql

SELECT DISTINCTROW TA_SR.MeasNo, TA_SR.FTEMNomenclature, TA_SR.NomenclatureModel, TA_SR.Equipment_ID, TA_SR.Multiple_ID, TA_SR.Job_Group, TA_SR.Project, TA_SR.Complete_By_Date, TA_SR.Requestor_ID, TA_SR.Service_Lab, TA_SR.Work_Code, TA_SR.Charge_Number, TA_SR.Disposition, TA_SR.RC1A, TA_SR.RC1B, TA_SR.RC2A, TA_SR.RC2B, TA_SR.Requestor_Comment_3, TA_SR.SRCtr, QS_TT_GeneralInfo.StableEmail, TA_SR.Requestor_Comment_1, TA_SR.Requestor_Comment_2, TA_SR.WSNo, TL_Labs.Contact
FROM TL_Labs INNER JOIN (TA_SR INNER JOIN QS_TT_GeneralInfo ON TA_SR.Requestor_ID = QS_TT_GeneralInfo.RequestorId) ON TL_Labs.LabCtr = TA_SR.Service_Lab
ORDER BY TA_SR.Job_Group, TA_SR.SRCtr

So its basically your query without the filters

then u can open the report and set the filter
e.g.

    Dim sReport as String

    sReport = "RS_EmailSR"

    DoCmd.OpenReport sReport, acViewDesign
    Reports(sReport).Filter = "TA_SR.Job_Group = " & Forms]![FS_SelectSr]![Job_Group]
    Reports(sReport).FilterOn = True
    DoCmd.Close acReport, sReport, acSaveYes
    DoCmd.OpenReport sReport, acViewPreview

0
 
rockiroadsCommented:
urm., forgot opening [ in Forms

note if this code running on form FS_SelectSr then u can just do

 Reports(sReport).Filter = "TA_SR.Job_Group = " & Me.Job_Group


0
 
Karen SchaeferAuthor Commented:
I do not having any issues with the recordsource of the report - my currrent issue is setting the email values for the receipients of the email and the proper syntax for  passing these values.

As i stated that the nTo = a fixed set of names, whereas the nCC = the varialbe from the query results hence the rs.fields("Service lab")

What am I doing wrong here my current code to stopping of the object variable for the nTo?

Karen
0
 
Rey Obrero (Capricorn1)Commented:
you don't use set to assign a string to variable string


        nTo = "Schaefer, Karen F.;Smith, John, J.; Green, Dave M; Moe, MaryE; Jones, Steven S;"
0
 
rockiroadsCommented:
ok, I see

well for a start, u define strsql

and then u do this inside Report_Close

Set rs.OpenRecordset = strsql


but what is strsql set to ?
0
 
Karen SchaeferAuthor Commented:
I need to be able to send the report as an email and the cc receipents will be varied based on the record select and the value in the service lab field.

K
0
 
Karen SchaeferAuthor Commented:
the value of the cc recepient = the service lab field and the strsql was previously set to the recordsource of the report where I do not close the strsql.

K
0
 
Rey Obrero (Capricorn1)Commented:


        Set rs.OpenRecordset = strsql
            Do Until rs.EOF = True
                if nCC="" then
                       nCC = rs.Fields("Service_Lab")
                 else
                   nCC = nCC & ";" & rs.Fields("Service_Lab")
                end if
             rs.MoveNext
           Loop
                          DoCmd.SendObject acSendReport, "RS_EMailSR", acFormatRTF, _
                    nTo, nCC, , _
                    "Instrumentation Needs", "Please ship the following items to the appropriate labs."
0
 
Rey Obrero (Capricorn1)Commented:
summarizing

       nTo = "Schaefer, Karen F.;Smith, John, J.; Green, Dave M; Moe, MaryE; Jones, Steven S;"

       Set rs.OpenRecordset = strsql
            Do Until rs.EOF = True
                if nCC="" then
                       nCC = rs.Fields("Service_Lab")
                 else
                   nCC = nCC & ";" & rs.Fields("Service_Lab")
                end if
             rs.MoveNext
           Loop
                          DoCmd.SendObject acSendReport, "RS_EMailSR", acFormatRTF, _
                    nTo, nCC, , _
                    "Instrumentation Needs", "Please ship the following items to the appropriate labs."
0
 
Karen SchaeferAuthor Commented:
should the nTo and nCC be set as strings or receipients?

k
0
 
Karen SchaeferAuthor Commented:
I am still having issues with the setting of variables on the nTo and nCC.  

and

I am also having issues with the revised open report code.

    Reports("RS_EmailSR").RecordSource = strsql
    DoCmd.OpenReport "RS_EmailSR", acViewPreview

It does not recognize the report name even though i verified teh report name.

K.
0
 
rockiroadsCommented:
karen, looking at your code, I cant see where u initialise strsql

u define it

dim strsql as string

then next time I see it in use its

 Set rs.OpenRecordset = strsql


so I dont understand when u say u havent closed it
0
 
Karen SchaeferAuthor Commented:
see my initial question above for my orginal code this is where I set the sql is on opening of the report and I do not close the sql out until I close the report.

Karen
0
 
Karen SchaeferAuthor Commented:
Maybe i am approaching this wong -

I need to create the recordsource for a report, be able to preview the report and then on close of this report I need to give the use the option to email the report to a certain group of individuals based on the Service lab listed in the report.

the email will always be addressed to a the same people and the cc will always change.

I need code that will allow the cc to be fleixible.

Karen

0
 
Rey Obrero (Capricorn1)Commented:
you need to move these codes to the form module

Dim strsql As String
Dim curdb As DAO.Database
Dim rs As Recordset
Dim nTo As String
Dim nCC As String

Select Case MsgBox("Do you wish to email this Service Request?", vbYesNo Or vbQuestion Or vbSystemModal Or vbDefaultButton1, "Sending Email")

    Case vbYes
        nTo = "Schaefer, Karen F.;Smith, John, J.; Green, Dave M; Moe, MaryE; Jones, Steven S;"

       Set rs.OpenRecordset = strsql
            Do Until rs.EOF = True
                if nCC="" then
                       nCC = rs.Fields("Service_Lab")
                 else
                   nCC = nCC & ";" & rs.Fields("Service_Lab")
                end if
             rs.MoveNext
           Loop
                          DoCmd.SendObject acSendReport, "RS_EMailSR", acFormatRTF, _
                    nTo, nCC, , _
                    "Instrumentation Needs", "Please ship the following items to the appropriate labs."
    Case vbNo
    Exit Sub
        Set nTo = Nothing
        Set nCC = Nothing
        Set rs = Nothing
End Select
0
 
Karen SchaeferAuthor Commented:
why a form when it is on close of the report?

Karen
0
 
Karen SchaeferAuthor Commented:
I revert back to a actual query as the recordsoure for the on open of the report.

and

I set the code to on close of the report.

Private Sub Report_Close()
Dim strsql As String
Dim curdb As DAO.Database
Dim rs As Recordset
'Dim nTo As String
Dim nCC As String

Select Case MsgBox("Do you wish to email this Service Request?", vbYesNo Or vbQuestion Or vbSystemModal Or vbDefaultButton1, "Sending Email")

    Case vbYes
       strsql = "SELECT DISTINCTROW TA_SR.MeasNo, TA_SR.FTEMNomenclature, TA_SR.NomenclatureModel, TA_SR.Equipment_ID, TA_SR.Multiple_ID, TA_SR.Job_Group," & _
                    " TA_SR.Project, TA_SR.Complete_By_Date, TA_SR.Requestor_ID, TA_SR.Service_Lab, TA_SR.Work_Code, TA_SR.Charge_Number, TA_SR.Disposition, TA_SR.RC1A," & _
                    " TA_SR.RC1B, TA_SR.RC2A, TA_SR.RC2B, TA_SR.Requestor_Comment_3, TA_SR.SRCtr, QS_TT_GeneralInfo.StableEmail, TA_SR.Requestor_Comment_1, TA_SR.Requestor_Comment_2, TA_SR.WSNo, TL_Labs.Contact" & _
                " FROM TL_Labs INNER JOIN (TA_SR INNER JOIN QS_TT_GeneralInfo ON" & _
                    " TA_SR.Requestor_ID = QS_TT_GeneralInfo.RequestorId) ON TL_Labs.LabCtr = TA_SR.Service_Lab" & _
                " WHERE (((TA_SR.Job_Group) = [Forms]![FS_SelectSr]![Job_Group]))" & _
                " ORDER BY TA_SR.Job_Group, TA_SR.SRCtr"
      ' Set nTo = "EXT-Schaefer, Karen F.;" '"Brenner, Clayton J; Green, Dave M; Moe, Valerie E; Broadhurst, Steven S;"
       Set rs.OpenRecordset = strsql
            Do Until rs.EOF = True
                If nCC = "" Then
                       nCC = rs.Fields("TL_Labs.Contact")
                 Else
                   nCC = nCC & ";" & rs.Fields("TL_Labs.Contact")
                End If
             rs.MoveNext
           Loop
                DoCmd.SendObject acSendReport, "RS_EMailSR", acFormatRTF, _
                    "Brenner, Clayton J; Green, Dave M; Moe, Valerie E; Broadhurst, Steven S;", nCC, , _
                    "Instrumentation Needs", "Please ship the following items to the appropriate labs."
        rs.Close
    Case vbNo
    Exit Sub
        'Set nTo = Nothing
        Set nCC = Nothing
        Set rs = Nothing
End Select
End Sub

Still does not list the rs.openrecordset?

k
0
 
Rey Obrero (Capricorn1)Commented:
if you want it on the close event of the report you have to repeat the strSql. or you need to create a new one with the list of  Service Labs

Dim strsql As String
Dim curdb As DAO.Database
Dim rs As Recordset
Dim nTo As String
Dim nCC As String

    strsql = "SELECT DISTINCTROW TA_SR.MeasNo, TA_SR.FTEMNomenclature, TA_SR.NomenclatureModel, TA_SR.Equipment_ID, TA_SR.Multiple_ID, TA_SR.Job_Group," & _
                    " TA_SR.Project, TA_SR.Complete_By_Date, TA_SR.Requestor_ID, TA_SR.Service_Lab, TA_SR.Work_Code, TA_SR.Charge_Number, TA_SR.Disposition, TA_SR.RC1A," & _
                    " TA_SR.RC1B, TA_SR.RC2A, TA_SR.RC2B, TA_SR.Requestor_Comment_3, TA_SR.SRCtr, QS_TT_GeneralInfo.StableEmail, TA_SR.Requestor_Comment_1, TA_SR.Requestor_Comment_2, TA_SR.WSNo, TL_Labs.Contact" & _
                " FROM TL_Labs INNER JOIN (TA_SR INNER JOIN QS_TT_GeneralInfo ON" & _
                    " TA_SR.Requestor_ID = QS_TT_GeneralInfo.RequestorId) ON TL_Labs.LabCtr = TA_SR.Service_Lab" & _
                " WHERE (((TA_SR.Job_Group) = [Forms]![FS_SelectSr]![Job_Group]))" & _
                " ORDER BY TA_SR.Job_Group, TA_SR.SRCtr"



Select Case MsgBox("Do you wish to email this Service Request?", vbYesNo Or vbQuestion Or vbSystemModal Or vbDefaultButton1, "Sending Email")

    Case vbYes
        nTo = "Schaefer, Karen F.;Smith, John, J.; Green, Dave M; Moe, MaryE; Jones, Steven S;"

       Set rs.OpenRecordset = strsql
            Do Until rs.EOF = True
                if nCC="" then
                       nCC = rs.Fields("Service_Lab")
                 else
                   nCC = nCC & ";" & rs.Fields("Service_Lab")
                end if
             rs.MoveNext
           Loop
                          DoCmd.SendObject acSendReport, "RS_EMailSR", acFormatRTF, _
                    nTo, nCC, , _
                    "Instrumentation Needs", "Please ship the following items to the appropriate labs."
    Case vbNo
    Exit Sub
        Set nTo = Nothing
        Set nCC = Nothing
        Set rs = Nothing
End Select
0
 
Karen SchaeferAuthor Commented:
I was doing the same thing you were, unfortunately I still having issues with the recordset?

K
0
 
Rey Obrero (Capricorn1)Commented:
ok try this

       Set rs.OpenRecordset = strsql
            if rs.eof then
                 msgbox "No Records"
                 exit sub
            end if
            Do Until rs.EOF = True
                If nCC = "" Then
                       nCC = rs("Contact")
                 Else
                   nCC = nCC & ";" & rs("Contact")
                End If
             rs.MoveNext
           Loop
0
 
Rey Obrero (Capricorn1)Commented:
sorry, use this instead

    Set rs=currentdb.OpenRecordset(strsql)
            if rs.eof then
                 msgbox "No Records"
                 exit sub
            end if
            Do Until rs.EOF = True
                If nCC = "" Then
                       nCC = rs("Contact")
                 Else
                   nCC = nCC & ";" & rs("Contact")
                End If
             rs.MoveNext
           Loop
0
 
Karen SchaeferAuthor Commented:
I am getting Too few parameters on the openrecordset?

k
0
 
Rey Obrero (Capricorn1)Commented:


try the where clause i posted above.
is the form FS_SelectSr still open?
0
 
rockiroadsCommented:
Karen, the report's recordsource has been successfully set, correct?

so why not do this

Set rs = CurrentDB.OpenRecordset (Me.RecordSource)

then loop thru. u need to build nCC as cap as said

Also I defined Recordset as DAO

here is your modified code



Private Sub Report_Close()
   
    Dim rs As DAO.Recordset
    Dim nTo As String
    Dim nCC As String
    Dim sSubject As String
    Dim sMsg As String
   
    Select Case MsgBox("Do you wish to email this Service Request?", vbYesNo Or vbQuestion Or vbSystemModal Or vbDefaultButton1, "Sending Email")
   
        Case vbYes
       
            'Set variables here
            nTo = "EXT-Schaefer, Karen F.;Brenner, Clayton J; Green, Dave M; Moe, Valerie E; Broadhurst, Steven S;"
            sSubject = "Instrumentation Needs"
            sMsg = "Please ship the following items to the appropriate labs."
           
            'Loop thru and build CC list based on report's current recordsource
            Set rs = CurrentDb.OpenRecordset(Me.RecordSource)
            nCC = ""
            Do While rs.EOF = False
                nCC = nCC & ";" & rs!Contact   'or is it rs!Service_Lab
                rs.MoveNext
            Loop
            If Len(nCC) > 0 Then nCC = Mid$(nCC, 2)
            DoCmd.SendObject acSendReport, "RS_EMailSR", acFormatRTF, nTo, nCC, , sSubject, sMsg
            rs.Close
            Set rs = Nothing
       
        Case Else
    End Select
End Sub
   


What I recommend though is once u get this working, is to store email address (for nTo) in a table and never do any kind of hardcoding
0
 
Karen SchaeferAuthor Commented:
I tried your example, unforunately I am still getting the Too Few parameters on the openrecordset.

I even tried moving the code on open of the report instead of onclose.

what am I missing.  do I need to seed a global variable or public varialbe that will hold the service lab info.

K
0
 
Karen SchaeferAuthor Commented:
Private Sub Report_close(Cancel As Integer)
    Dim rs As DAO.Recordset
    Dim nTo As String
    Dim nCC As String
    Dim sSubject As String
    Dim sMsg As String
   
    Select Case MsgBox("Do you wish to email this Service Request?", vbYesNo Or vbQuestion Or vbSystemModal Or vbDefaultButton1, "Sending Email")
   
        Case vbYes
       
            'Set variables here
            nTo = "EXT-Schaefer, Karen F.;" 'Brenner, Clayton J; Green, Dave M; Moe, Valerie E; Broadhurst, Steven S;"
            sSubject = "Instrumentation Needs"
            sMsg = "Please ship the following items to the appropriate labs."
           
            'Loop thru and build CC list based on report's current recordsource
            Set rs = CurrentDb.OpenRecordset(Me.RecordSource)'---------------------------It dies here!!!!!
            nCC = ""
            Do While rs.EOF = False
                nCC = nCC & ";" & rs!Service_Lab
                rs.MoveNext
            Loop
            If Len(nCC) > 0 Then nCC = Mid$(nCC, 2)
            DoCmd.SendObject acSendReport, "RS_EMailSR", acFormatRTF, nTo, nCC, , sSubject, sMsg
            rs.Close
            Set rs = Nothing
       
        Case Else
    End Select

End Sub
0
 
Rey Obrero (Capricorn1)Commented:
K,  try this first

Private Sub Command80_Click()
Dim strsql As String, rs as DAO.recordset

     strsql = "SELECT DISTINCTROW TA_SR.MeasNo, TA_SR.FTEMNomenclature, TA_SR.NomenclatureModel, TA_SR.Equipment_ID, TA_SR.Multiple_ID, TA_SR.Job_Group," & _
                    " TA_SR.Project, TA_SR.Complete_By_Date, TA_SR.Requestor_ID, TA_SR.Service_Lab, TA_SR.Work_Code, TA_SR.Charge_Number, TA_SR.Disposition, TA_SR.RC1A," & _
                    " TA_SR.RC1B, TA_SR.RC2A, TA_SR.RC2B, TA_SR.Requestor_Comment_3, TA_SR.SRCtr, QS_TT_GeneralInfo.StableEmail, TA_SR.Requestor_Comment_1, TA_SR.Requestor_Comment_2, TA_SR.WSNo, TL_Labs.Contact" & _
                " FROM TL_Labs INNER JOIN (TA_SR INNER JOIN QS_TT_GeneralInfo ON" & _
                    " TA_SR.Requestor_ID = QS_TT_GeneralInfo.RequestorId) ON TL_Labs.LabCtr = TA_SR.Service_Lab" & _
                " WHERE (((TA_SR.Job_Group) = [Forms]![FS_SelectSr]![Job_Group]))" & _
                " ORDER BY TA_SR.Job_Group, TA_SR.SRCtr"
'    DoCmd.OpenReport "RS_EmailSR", acViewPreview
'        Me.RecordSource = strsql

set rs=currentdb.openrecordset(strsql)

if rs.eof then
   msgbox "No records'
   exit sub
end if



End Sub


see if you can open the recordset without error




0
 
rockiroadsCommented:
ok, then the SQL u have defined in elsewhere, why cant u just use that and use it there


strsql = "SELECT DISTINCTROW TA_SR.MeasNo, TA_SR.FTEMNomenclature, TA_SR.NomenclatureModel, TA_SR.Equipment_ID, TA_SR.Multiple_ID, TA_SR.Job_Group," & _
                    " TA_SR.Project, TA_SR.Complete_By_Date, TA_SR.Requestor_ID, TA_SR.Service_Lab, TA_SR.Work_Code, TA_SR.Charge_Number, TA_SR.Disposition, TA_SR.RC1A," & _
                    " TA_SR.RC1B, TA_SR.RC2A, TA_SR.RC2B, TA_SR.Requestor_Comment_3, TA_SR.SRCtr, QS_TT_GeneralInfo.StableEmail, TA_SR.Requestor_Comment_1, TA_SR.Requestor_Comment_2, TA_SR.WSNo, TL_Labs.Contact" & _
                " FROM TL_Labs INNER JOIN (TA_SR INNER JOIN QS_TT_GeneralInfo ON" & _
                    " TA_SR.Requestor_ID = QS_TT_GeneralInfo.RequestorId) ON TL_Labs.LabCtr = TA_SR.Service_Lab" & _
                " WHERE TA_SR.Job_Group = " & [Forms]![FS_SelectSr]![Job_Group] " & _
                " ORDER BY TA_SR.Job_Group, TA_SR.SRCtr"

Set rs = CurrentDb.OpenRecordset(strsql)



0
 
Rey Obrero (Capricorn1)Commented:
rocki, i already suggested that to K,  don't know if she did it or not.
0
 
Karen SchaeferAuthor Commented:
I did change the recordsource for the report back to the original qry and add the SQL string to the code on the onclose of the report - unforunately I am still getting a too few parameter issue I am still trying to work out.

K
0
 
rockiroadsCommented:
Cap, just a suttle difference

I placed [Forms]![FS_SelectSr]![Job_Group]  outside quotes


correction to be made, got rid of rogue " on 2nd last line. Here it is corrected



so its now


    Dim rs As DAO.Recordset
    Dim nTo As String
    Dim nCC As String
    Dim sSubject As String
    Dim sMsg As String
    Dim strSql as String
   
    Select Case MsgBox("Do you wish to email this Service Request?", vbYesNo Or vbQuestion Or vbSystemModal Or vbDefaultButton1, "Sending Email")
   
        Case vbYes
       
            'Set variables here
            nTo = "EXT-Schaefer, Karen F.;" 'Brenner, Clayton J; Green, Dave M; Moe, Valerie E; Broadhurst, Steven S;"
            sSubject = "Instrumentation Needs"
            sMsg = "Please ship the following items to the appropriate labs."
           
            strSql = "SELECT DISTINCTROW TA_SR.MeasNo, TA_SR.FTEMNomenclature, TA_SR.NomenclatureModel, TA_SR.Equipment_ID, TA_SR.Multiple_ID, TA_SR.Job_Group," & _
                    " TA_SR.Project, TA_SR.Complete_By_Date, TA_SR.Requestor_ID, TA_SR.Service_Lab, TA_SR.Work_Code, TA_SR.Charge_Number, TA_SR.Disposition, TA_SR.RC1A," & _
                    " TA_SR.RC1B, TA_SR.RC2A, TA_SR.RC2B, TA_SR.Requestor_Comment_3, TA_SR.SRCtr, QS_TT_GeneralInfo.StableEmail, TA_SR.Requestor_Comment_1, TA_SR.Requestor_Comment_2, TA_SR.WSNo, TL_Labs.Contact" & _
                " FROM TL_Labs INNER JOIN (TA_SR INNER JOIN QS_TT_GeneralInfo ON" & _
                    " TA_SR.Requestor_ID = QS_TT_GeneralInfo.RequestorId) ON TL_Labs.LabCtr = TA_SR.Service_Lab" & _
                " WHERE TA_SR.Job_Group = " & [Forms]![FS_SelectSr]![Job_Group] & _
                " ORDER BY TA_SR.Job_Group, TA_SR.SRCtr"

            'Loop thru and build CC list based on report's current recordsource
            Set rs = CurrentDb.OpenRecordset(strSql)
            nCC = ""
            Do While rs.EOF = False
                nCC = nCC & ";" & rs!Service_Lab
                rs.MoveNext
            Loop
            If Len(nCC) > 0 Then nCC = Mid$(nCC, 2)
            DoCmd.SendObject acSendReport, "RS_EMailSR", acFormatRTF, nTo, nCC, , sSubject, sMsg
            rs.Close
            Set rs = Nothing
       
        Case Else
    End Select


0
 
Rey Obrero (Capricorn1)Commented:

K,
did you try this


Private Sub Command80_Click()
Dim strsql As String, rs as DAO.recordset

     strsql = "SELECT DISTINCTROW TA_SR.MeasNo, TA_SR.FTEMNomenclature, TA_SR.NomenclatureModel, TA_SR.Equipment_ID, TA_SR.Multiple_ID, TA_SR.Job_Group," & _
                    " TA_SR.Project, TA_SR.Complete_By_Date, TA_SR.Requestor_ID, TA_SR.Service_Lab, TA_SR.Work_Code, TA_SR.Charge_Number, TA_SR.Disposition, TA_SR.RC1A," & _
                    " TA_SR.RC1B, TA_SR.RC2A, TA_SR.RC2B, TA_SR.Requestor_Comment_3, TA_SR.SRCtr, QS_TT_GeneralInfo.StableEmail, TA_SR.Requestor_Comment_1, TA_SR.Requestor_Comment_2, TA_SR.WSNo, TL_Labs.Contact" & _
                " FROM TL_Labs INNER JOIN (TA_SR INNER JOIN QS_TT_GeneralInfo ON" & _
                    " TA_SR.Requestor_ID = QS_TT_GeneralInfo.RequestorId) ON TL_Labs.LabCtr = TA_SR.Service_Lab" & _
                " WHERE (((TA_SR.Job_Group) = [Forms]![FS_SelectSr]![Job_Group]))" & _
                " ORDER BY TA_SR.Job_Group, TA_SR.SRCtr"
'    DoCmd.OpenReport "RS_EmailSR", acViewPreview
'        Me.RecordSource = strsql

set rs=currentdb.openrecordset(strsql)

if rs.eof then
   msgbox "No records'
   exit sub
end if



End Sub


see if you can open the recordset without error



0
 
Karen SchaeferAuthor Commented:
Rock,

I tried your revision - it seems to get me almost there - need to modiy the where statement to force it to read as a number.

               " WHERE (((TA_SR.Job_Group) = [Forms]![FS_SelectSr]![Job_Group]))"  Help with the proper syntax - to include the single quote around the variable, please.

k
0
 
Karen SchaeferAuthor Commented:
nevermind I got the correct syntax.

thanks it is almost there - unforunately I can not launch this portion of the code from Onclose of the report - so I will have to rethink how to handle this issue.

I would like to be able to preview the report, then close the report then fire off this code to email the report.  what would you recommend - two seperate buttons or is there a way to use the same button and code it that on close of the report it executes the second half of the code?

K
0
 
Karen SchaeferAuthor Commented:
it is the sendreport portion that can not be execute while the report is still open.

k
0
 
Karen SchaeferAuthor Commented:
How do I modify the send report to automatically send the email without actual open Outlook and the usiing selecting send within outlook?

K
0
 
Rey Obrero (Capricorn1)Commented:
didn't i tell you to move the codes  to the form module.
create another button for emailing the report
0
 
Karen SchaeferAuthor Commented:
I did move it to a separate button on the main form - I was trying to simplify the steps the user takes to sending the email -

I am still having a minor issue with the passing of the TO names -

It wants to put the ";" in the wrong places - what do you recommend?

Karen
0
 
Rey Obrero (Capricorn1)Commented:
<It wants to put the ";" in the wrong places - what do you recommend? >

where is the data coming from?
0
 
Karen SchaeferAuthor Commented:
I copied the names from Outlook to box.

k
0
 
Rey Obrero (Capricorn1)Commented:
will the TO names be changing from time to time?
if it is, create a table of names, then open it as recordset like what we did for nCC
0
 
Karen SchaeferAuthor Commented:
thanks I was thinking the same thing thanks to both of you for your assistance.

As usual you come to my rescue.

Thanks,

Karen
0
 
rockiroadsCommented:
Hi Karen, after my last post, I went to bed.
Great stuff your sorted now done, :)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 23
  • 15
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now