Email customer invoices from mailing list in table

[Strangeway]

Hi all,

I have a multi-part question here. I have a database into which I import an Excel spreadsheet containing details of all of my day's shipping information. The table this info is stored in has a relationship to my master customer list which has a list of email addresses. Here's what I want to accomplish:

1. After importing the spreadsheet, I would like Access to loop through the customer ID numbers and create/save the invoices for each customer for new records only (ie. for that day's shipments.)
2. I want Access to loop through the customer list and automatically email that customers invoice to them if they had a shipment that day. If they did not, then it should skip that record.

I am pretty confident I can get step 1 completed on my own (the simple generation of the report.) The only part I might need help with is having Access create a unique file name for each report. Step 2 has me stumped. I know how to generate an email for each record in my table easily enough. The tricky bit is how do I get Access to know which file to attach to each email so that the customer receives the proper invoice?

 

[Remou]

Do steps one and two at the same time. You will find a number of posts on this topic. Search the forums and get back with code if you get stuck.

You should read FAQ181-2886.

 

[kjv1611]

Strangeway,

As Remou suggested, you can do both 1 and 2 at the same time.

Basically, you can take care of your emailing in a separate sub/function, and even the saving of the invoices in a separate sub/function if you like for easier to read code, and then your loop that calls them.

So, you'll have something like these in one or more modules:

Private Function SaveInvoice(InvoiceID As String)
'code here
End Sub

Private Sub Email(CustomerID As String)
'code here
End Sub

Then in your form level code, I'm assuming, you would loop through the records, running the appropriate code:

Private Sub cmdDoMyStuff_Click()
  Dim db As DAO.Database
  Dim rs As DAO.Recordset

  Set db = CurrentDb
  Set rs = db.OpenRecordset("MyImportedTable")

  Do While Not rs.EOF
    SaveInvoice(rs.Fields("InvoiceID"))
    Email(rs.Fields("CustomerID")
    rs.MoveNext
  Loop

  rs.Close
  Set rs = Nothing
  db.Close
  Set db = Nothing

End Sub

And, of course, you'll want to add in some error handling code once you've gotten everything running the way you want. The reason I say add the error handling code later is that while debugging, if you have error handling code built-in, then the debugger won't go to the specific problem line (unless there is some way to code for that, which I'd love to see.. It's something I've wanted to be able to add in for a long time)


--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Oops, I meant for Email to be a Function, but it probably doesn't matter, and they both actually probably work better as Sub-Procedures, anyway, as they do not return values... or at least, I wouldn't think you'd want to return a value.

--

"If to err is human, then I must be some kind of human!" -Me

 

[Strangeway]

Awesome, thanks very much for the tips! I'll let you know how I make out.

 

[Strangeway]

Success! I got it working by patching together a few different code examples by Remou (thank you yet again!) I've attached the code in case anyone else is trying to do something similar. I'm sure it's not the most efficient in the world, but it works so I'm a happy fella.

My one last hurdle is that I would like Access to skip any records that didn't have any shipments that day. I'm not sure how easy/hard it would be to set up an IF statement that looked for a NULL value and skipped to the next record? I'll search the forums and see what I can find out.

Thanks again for everyone's help!

Sub shipnotify2()

Dim db As DAO.Database
Dim rsDealers As DAO.Recordset
Dim strReport
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim TheAddress As String

Set db = CurrentDb
Set rsDealers = db.OpenRecordset("Test_Dealer")
rsDealers.MoveFirst

strReport = "Shipment_Summary_Report"
DoCmd.OpenReport strReport, acViewReport

Do While Not rsDealers.EOF
    Reports(strReport).Filter = "[Dealer_Code] =" & Chr(34) & rsDealers!Dealer_Code & Chr(34)
    Reports(strReport).FilterOn = True

    DoCmd.Save acReport, strReport
    DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, "c:\Shipment Details.pdf"
    
    Set objOutlook = CreateObject("Outlook.application")
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    TheAddress = rsDealers![Email]
        With objOutlookMsg
            Set objOutlookRecip = .Recipients.Add(TheAddress)
            objOutlookRecip.Type = olTo
            
            .Subject = "Your order has shipped."
            .Body = "Your recent order has been shipped.  Please see the attached document for details of your shipment.  Thank you."
            
        If Not IsMissing(AttachmentPath) Then
            Set objOutlookAttach = .Attachments.Add("c:\Shipment Details.pdf")
        End If
    For Each objOutlookRecip In .Recipients
        objOutlookRecip.Resolve
        If Not objOutlookRecip.Resolve Then
            objOutlookMsg.Display
        End If
    Next
    .Send
    End With
    rsDealers.MoveNext
    
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing

DoCmd.Close acReport, strReport

End Sub

 

[kjv1611]

It shouldn't be a big deal at all. You're already looping through the Dealers recordset. If it's in that recordset, it'll be really easy. If it's in another, it would still be possible, but it might get a little more complicated.

Either that, or you could build a SQL statement for your recordset to loop through, and have it pull everything you need to compare against, and then you wouldn't need the IF statement for this part, b/c that would already be taken care of in your SQL statement (well, I'm assuming you'd take care of it there, b/c it'd be faster).

--

"If to err is human, then I must be some kind of human!" -Me

 

[Strangeway]

Of course (my luck) the data for the reports is contained in a different recordset. In your opinion, which solution would be easier for a rookie to sort out? The SQL solution, or trying to work with the other recordset?

 

[kjv1611]

Easiest - probably just work in the other recordset.

Best solution, I think, would probably be the SQL solution.

But then again, if you're only dealing possibly with hundreds or less of records instead of 10s of thousands, then the SQL probably won't be faster enough to make a huge difference.

--

"If to err is human, then I must be some kind of human!" -Me

 

[Strangeway]

Awesome, thanks so much again for your help, it is really appreciated! Number of records per day would be 800 or less, so I'll try the recordset option first and let you know how I make out.

Cheers!

 

[Remou]

Move this line:
Set objOutlook = CreateObject("Outlook.application")

Up near "Set db", there is no need to be constantly creating this object.


You can use DCount:

If Dcount("*","NameOfShipmentsTable","[ShipDateFieldNameHere]=#" _
   & Format(Date,"yyyy/mm/dd") "# AND [Dealer Code]=" _
   & Chr(34) & rsDealers!Dealer_Code & Chr(34))>0 Then
'There are records

Or you could build your query, Test_Dealer, to only include dealers with shipments, which would be more efficient.

 

[kjv1611]

I'll Second Remou's suggestions. The DCount method is easy, and if you are already building a query, then why not include the elimination you mentioned in that same query?

The SQL Code would work basically the same as editing the query.

--

"If to err is human, then I must be some kind of human!" -Me

 

[Strangeway]

Thank you.