Bulk Emailing a report using email addresses contained in a table

[peskywinnets]

Not sure how to achieve this... so I seek the help from this 'learned' gathering!

I've a table containing ebay sales information, let's simplify it...

buyer   	email  		    product   quantity    price           total         Receipt Number
A Smith   a.smith@acme.com          gloves     1          $10.00        $10.00            1
P Brown   p.brown@jones.com         hat        2          $15.00        $30.00            2

After a lot of faffing about (& learning about sub reports), I've now got access producing a sales receipt from the data....it's in the form of a report (the report generated is one long continuous report, with each page in the report relating to a sales receipt number)

What I want to do now is email each page of the the report (like I say, each page is essentially an individual sales receipt) to the associated email address in bulk.

Therefore there may be 20 sales receipts in a long continuous report, how would I break them out one by one to a pdf file & send it to the correct email address which is contained against the sales receipt number in the same table?!!!

Many thanks

 

[Anakardian]

You nearly gave the answer yourself.

Since you have a subreport able to generate a specific page on you main report, you should be able to have the subreport generate the page you want by providing it with the required ID.

This you should be able to get from the table where you also store the e-mail address by using a recordset.
Walking through the recordset will provide you with the receipt number you need for teh subreport as well as the e-mail-

What I would do is to save the report as a pdf and then have it attached to a e-mail that is then sent off. If you don't need to keep the files you can delete the file after the e-mail has been sent.
All of this can be done in vba so you only have to press a button to activate it.

 

[peskywinnets]

Thanks....ok, so I can now produce a report containing a single sales receipt

what i'm struggling with now, is how I can get access to cycle down a table, if a nominated field (eg 'email sent) in a row is set to false, then it runs my report, saves the report as a pdf, then emails that particular pdf to the email address contained in the row (then set the 'email sent' field to true)

So in my earlier example A Smith gets his sales receipt in pdf format via email, then P Brown gets his sales receipt via email ....but run automatically (vs me having to conduct all this manually)

 

[Minty]

I'm pushed for time to answer - but if you search for a thread I answered / contributed to a while ago there is code there to do what you require.

 

[Anakardian]

This is how I am sending with an excel file.
It can be used for a single e-mail or called for each of the e-mails you wish to send.

There is a small loop that enables it to go through a query and add all the receipients from a pre-defined list. You can use the same technique as it scales with the task.
If you use the query that runs your full report (the one with everyone in it) as a source of looping, you can call this function and pass it the e-mail and filename.
As the loop progresses it will send each person an e-mail.

Before it starts on the next person you can run an update query to mark the receipt as sent.

As for the source of the file with the receipt, try if this thread will not help you out:
http://www.access-programmers.co.uk/forums/showthread.php?t=245039

Public Function ExportToOutlook()


Dim objOutlook As Object
Dim objEMail As Object

Dim strGetFilePath As String
Dim VesselName As String

'Set the path for the sheet to be attached
strGetFilePath = "YOURPATH" & "EXCEL FILE NAME" & ".xlsx" 'replace YOURPATH and EXCEL FILE NAME with your own
     
' If Outlook is open, use GetObject, otherwise open Outlook
    Set objOutlook = GetObject(, "Outlook.Application")
    If objOutlook Is Nothing Then Set objOutlook = CreateObject("Outlook.Application")
 
' Creates a new e-mail
    Set objEMail = objOutlook.CreateItem(0)
    With objEMail
 
' Adds To Recipient by looping through the pre-defined e-mail adresses
Dim Email As String
Dim strSQL As String

strSQL = "SELECT tblEmail.EMailAdress " & _
            "FROM tblEmail " & _
            "WHERE tblEmail.VesselID=" & [Forms]![frmSelectVessel]![cboSelectedVessel] & " " & _
            "AND tblEmail.UseWhenSending=True;" 'Replace this with something that suits your needs such as a query that selects the e-mail address for the buyer

Set dbEmail = CurrentDb
Set rstEmail = dbEmail.OpenRecordset(strSQL)

    .Recipients.Add rstEmail.Fields(0)
rstEmail.MoveNext

Do While rstEmail.EOF = False
    .Recipients.Add rstEmail.Fields(0)

rstEmail.MoveNext
Loop

Set dbEmail = Nothing
Set rstEmail = Nothing

   
 
' Adds CC recipient Remove if not needed
    If Not IsNull([Forms]![frmSelectVessel]![CCMail]) Then
        .cc = [Forms]![frmSelectVessel]![CCMail]
    End If
    
' Adds BCC recipient Remove if not needed
    If Not IsNull([Forms]![frmSelectVessel]![BCCMail]) Then
        .bcc = [Forms]![frmSelectVessel]![BCCMail]
    End If
     
' Sets the Subject, Customise this to whatever you need
    .Subject = VesselName & " - Performance Leadership Data"
 
' Sets the Body, customise this to whatever you need. Remember you can have it pick the name of the buyer, the invoice number or whatever information you need
    If Not IsNull([Forms]![frmSelectVessel]![MailBody]) Then
        .Body = [Forms]![frmSelectVessel]![MailBody]
    End If
 
' Adds attachment
    .Attachments.Add strGetFilePath, olByValue, , "Service Report"
 
' Embeds attachment
'    .Attachments.Add strGetFilePath, olEmbeddedItem, , "Title of attachment"
 
' Receipt upon delivery
'    .OriginatorDeliveryReportRequested = True
 
' Recipt upon read
'    .ReadReceiptRequested = True
 
' Displays the E-Mail
    .Display
 
' Sends the E-Mail (Utilises a tick box to either send the e-mail directly or just display the e-mail and have it sent manually.
    If [Forms]![frmSelectVessel]![SendNow] = False Then
        .Send
    End If
    
 
' Saves a Draft of the E-Mail
'    .Save
End With
 
End Function