Email filtered report by Supplier from a list of email address in Supplier table

[chip0105]

Here is an outline of my process:

1. I submit score cards to suppliers on a monthly basis.

2. I have a table with supplier names and email address. If the supplier is currently active the “Inactive” check box field in that record in not checked. Additional suppliers could be added at any time. Existing suppliers could become inactive then reactivated at a later date. Only active suppliers (those without the “Inactive” check-box field checked) should ever be listed.

3. I have a form with a combo box to filter and display the score card data in a subform for that selected supplier.

4. I use a VBA process that emails the filtered data to the supplier selected in the combo box. As there are numerous suppliers, I am looking to automate this process so I do not have to manually select suppliers individually and email them their individually filtered report.

I found some code online that enables me, with one-click, to send an email to individual suppliers using the email address as listed in my supplier table. The code allows me to add a subject line and message body for each supplier by name.

Here is what I now need:

1. I want to use VBA to filter the data for the report for each supplier within the supplier table.

2. Attach the filtered report to the email being sent to that supplier. Filter and email only the data for the selected supplier and not all data to all suppliers.

3. Loop this process for each active supplier in the supplier table; inactive check-box field is not checked in the supplier table.

I hope I explained this well enough to get some haelp.

Any assistance would be GREATLY appreciated!!

Here is the code I am currently working with:

Private Sub cmdOneClickMassEmail_Click()
On Error GoTo ErrorMessage
Set OutApp = CreateObject("Outlook.Application")
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("_tmpSuppliers")
Dim rp = CurrentReportingPeriod
With rs
 
    If .EOF And .BOF Then
    MsgBox "No emails will be sent becuase there are no records assigned from the list", vbInformation
    Else
    Do Until .EOF
    stremail = ![SupplierToEmailAdderss]
    stremailcc = ![SupplierCCEmailAdderss]
    strsubject = "Score Card for " & ![SupplierName] & " - " & DLookup("[Current Period]", "[qrySelect_CurrentReportingPeriodMonthAndYear]", "[Current Period]")
    strbody = "Dear " & ![SupplierName] & "," & vbCrLf & _
              "Email message body goes here."
    Set OutMail = OutApp.CreateItem(olMailItem)
    With OutMail
    .To = stremail
    .CC = stremailcc
    .BCC = ""
    .Subject = strsubject
    .Body = strbody
    .Send
    End With
    .MoveNext
    Loop
    End If
End With
Exit_cmdOneClickMassEmail_Click:
Exit Sub
ErrorMessage:
    MsgBox Err.Description
    Resume Exit_cmdOneClickMassEmail_Click
End Sub
 

[jimatqsi]

Chip,
I do this routinely every day. However, I don't use Outlook because it requires a response to a security pop-up every time an email is sent. It's possible the later versions of Outlook don't require that. I use CDO email instead. You can learn all you need to know about CDO email by searching the web with the following:
site:bytes.com CDO email

Now, your challenge includes several key areas.
1. Launch a report and limit it dynamically to a given supplier
2. Create a .pdf file from that report
3. Send an email and attach the .pdf file

Let's say for the moment you are willing to tolerate the security pop-ups. That way you can use the code you already have for sending emails. Below, I'll put a couple relevant threads at Bytes where you can see discussions about forming emails with attachments. This stuff is often version sensitive, so just be aware of that.

The way I do my emailing of reports is to have a loop that processes suppliers and creates a .pdf file and then calls a common function for sending emails. I pass to the function the address, subject, file name(s) to attach and so on. In your case, to use the code you have you might do the reverse, call a function to create the report and return the name of the .pdf file created.

How you create the .pdf will depend in part on what version of MS Office you are using. Later versions make that simpler than in the past. Using version 2003, I rely on the free Bullzip .pdf printer driver to create files for me. But that requires me to automatically set the default printer, which could be another area of study for you. A lot depends on your version.

You've got a lot of challenges ahead. I don't want to overwhelm you with too much information in one reply. Probably you should start by editing your code to automatically attach a given file. That'll be simple. Then, tackle making your report print to a .pdf file. At that point, you'll know better which parts you need more help with. Post back with your results, questions and specific version information.

Jim

http://bytes.com/topic/access/answer...k-address-book
http://bytes.com/topic/access/answer...-automatically