run multiple reports and email to correct person

[craig1988]

Hi there



I apologise in advance as I have already asked this question on this forum but I have had no luck in completing this function for my database.

Perhaps, I am asking the wrong questions or not explaining it properly.

Here goes...
THE ASK
I would like to have a command button that will create a report and email that report TO EACH INDIVIDUAL MEMBER of my team. (Currently I have 30 team members so I need to generate 30 reports and send each report to the "owner of that report")
THE STRUCTURE
TErrorLog - This is the table that contains the data that will make up the report.
TStaffList - This is the table that contains the staffID's and email address to be sent to

I am fairly sure that my previous posts have explained that. Maybe the next part is where the miscommunication is happening. I am going to break it down and I am not trying to insulting anyone by explaining this (after all, I am the one looking for help)

THE PROCESS
Upon clicking the command button...
Access looks at TStaffList and picks up the first StaffID. It then uses this staffID as a parameter in a query to generate a report and then email that report to that staffID (getting the email address from the table TStaffList).
This process would then reeat itself until it reaches the last StaffID in the table TStaffList.

Hopefully someone can help me with this. It is very frustrating as it should be very much possible.

[ranman256]

Set your values in list accordingly...

Sub SendListEmails()
Dim vTo, vBody, vSubj, vRpt

For i = 0 To lstBox.ListCount - 1
   lstBox = lstBox.ItemData(i)
   vRpt = lstBox
   vSubj = "Subject:" & lstBox.Column(1)
   vTo = lstBox.Column(2)
   vBody = "message body"
  
  DoCmd.SendObject acSendReport, vRpt, acFormatPDF, vTo, , , vSubj, vBody
Next
End Sub

[craig1988]

Hi Ranman

I think I have now set up as you have said.

On a form I have a list box (lstBox). It has two columns StaffID, EMail.

I have not done anything to the code as I want to test it before I start making changes.

Private Sub Command14_Click()

    Dim lstBox As ListBox
    Dim vTo, vBody, vSubj, vRpt
    For i = 0 To lstBox.ListCount - 1
       lstBox = lstBox.ItemData(i)
       vRpt = lstBox
       vSubj = "Subject: Test for " & lstBox.Column(1)
       vTo = lstBox.Column(2)
       vBody = "message body"
      
      DoCmd.SendObject acSendReport, vRpt, acFormatPDF, vTo, , , vSubj, vBody
    Next

End Sub

When I click the button I get an error.
Run-time error '91':
Pbject variable or With block variable not set

Any idea what is going wrong?

[ranman256]

is your list box called: lstBox (properties, other tab, name)

REMOVE : dim lstBOX
(sorry that was for me)

[RayMilhon]

Looking at your original post I don't see the purpose of the list box. You're sending the report and e-mail to all 30 team members. So the code behind you're command button would need to do the following:

Sub mailreport
    Dim rs as new adodb.recordset, sql as string
    '  Although any type of recordset should work dao or adodb.  I prefer adodb
    sql = "Select staffid, emailaddress from tstafflist"
   rs.open sql,currentproject.connection,adopenforwardonly, adlockreadonly
   Do until rs.eof
        'this part will take a little research on your part in my code this takes the query and exports it to excel
        DoCmd.TransferSpreadsheet acExport, 8, strquery, strpath
  
        'Then I would call a function that actually does the email        
        Call masteremail(rs!emailaddress)


        rs.movenext
   Loop

rs.close
set rs = nothing
End Sub

Public Sub Masteremail(ByRef emailaddress as string)
    Dim objoutlook As Outlook.Application
    Dim objoutlookmsg As Outlook.MailItem
    Dim objoutlookrecip As Outlook.recipient
    Dim objoutlookattach As Outlook.Attachment
    Dim sfile As String
    Dim fs As Object
    Set fs = CreateObject("scripting.filesystemobject")
    Set objoutlook = CreateObject("outlook.application")
    Set objoutlookmsg = objoutlook.CreateItem(olMailItem)
    
    objoutlookmsg.Recipients.Add (emailaddress)
    With objoutlookmsg
        .Subject = "email subject "
        .Body = ""
    End With
    sfile = path to your file & filename    
    objoutlookmsg.Attachments.Add (sfile)
    'if multiple files you can continue to add
    'sfile = "\\HVVSHARE\HVVHOMEFOLDERS\rmilhon\Production_vvezrep\dme\CPAP_EXPIRING_AUTHORIZATIONS_" & Format(Date, "mmm_dd_yyyy") & ".xls"
    'objoutlookmsg.Attachments.Add (sfile)
    objoutlookmsg.Send
    Set objoutlookmsg = Nothing
    Set objoutlook = Nothing
    
    
End Sub