Ideas on emailing customer order status report

word 2003 (maybe others? i dont have the older ones installed anymore) can do a mail merge to email (tools -> letters and mailings -> doc type email message). would be the easiest, and doesn't require any special code to do. just a query in access with the data you need for a mailing. you need a mapi email client (eg outlook) configured for use to use mail merge to email. for more info, fire up word and look at help topic: "Create and distribute merged e-mail messages"

i used to use, back in the 'good ol days' -- the microsoft exchange client from windows 9x to do mass emails and faxes. worked real slick.

Hi Dale,
I would probably create a recordset based on your Recipients and the report associate with each recipient. I would then pass these parameters for each record in the recordset to an email Function such as this:

Private Function EmailReports(strReportName As String,  strRecipient As String)
Dim objOL As Outlook.Application
Dim objMail As Outlook.MailItem
Dim strPath As String

On Error GoTo ErrorCheck

Set objOL = New Outlook.Application

'Create email item
Set objMail = objOL.CreateItem(olMailItem)
With objMail
    .To = strRecipient
    .Subject = "Weekly Report"
End With

'Use the dynamic recordset to export report and add attachments to email
    rstReports.MoveFirst
    Do Until rstReports.EOF
        strReportName = rstReports.Fields("ReportName").Value
       
        'Output report as Snapshot
        strPath = CurrentProject.Path
        DoCmd.OutputTo acOutputReport, strReportName, acFormatSNP, strPath & "\" & strReportName & ".snp"
        'Add Attachments to email
        objMail.Attachments.Add strPath & "\" & strReportName & ".snp"
       
        rstReports.MoveNext
    Loop

'Send the email
    objMail.Send
   
'Clean up objects
    Set objOL = Nothing
    Set objMail = Nothing
   
Exit Function

ErrorCheck:
    Debug.Print Err.Number, Err.Description
    Select Case Err.Number
        Case Is = 287
            MsgBox "The sending of the Email was terminated"
    End Select
   
End Function

I pulled this from some of my code so you may need to check it first. If you loop through the recordset and send each record to the function, this should do the trick.

Nathan

Nathan,

I have a question about your code or about the concept. You mention that I would create a recordset based on the recipients and the report associated with each recipient. I do not have a seperate report for each recipient. I have one report that page breaks on each recipient and was hoping there was someway to "filter" the report for each recipient, thereby only sending a recipient his/her information.

I maybe showing my ignorance of coding by asking this question, but want to make sure before I dive into figuring it out.

Thanks, Dale

Hi Dale,
I will have a look at this today and see what I can come up with. If I can get the report to filter before the "DoCmd.OutputTo" then we should be ok.

Nathan

Nathan,

Thanks for all of your hard work on this. I am tied up working on another project right now, but hope to get this one knocked out by the end of the week. I'll give your code a try soon.

As for Outlook wanting to verify an email that is being sent automatically, I have used a little app called ClickYes. You may have heard of it. It simply clicks Yes on any dialog that pops up. The downside of using this is that you may have something clicked Yes when you don't want it. So what I've done is to activate ClickYes in my code when I need, but then make it innactive when I'm finished.

Thanks again and I'll let you know when I get your code going.

Dale

Nathan,

By answering my question, you may have bitten off more than you care to chew. I ran into a problem right off the bat. I am getting a compile error: User defined type not defined. I am sure this is something that most on EE would know how to fix, but not me. There error occurs on the last line below:

Option Compare Database
Option Explicit
Private rstContacts As ADODB.Recordset
Private strRecipient As String
Private strEmail As String
Private strSection As String

Private Sub cmdEmailReports_Click()
'Email weekly reports
    EmailReports
   
End Sub
Private Sub EmailReports()
Dim objOL As Outlook.Application

Dale,

I don't think you have a reference set to the OutLook Library. To do this go to the "Tools" option in your Code window tool bar and then select "References.."

This will open the References dialog box. Scroll through the list of avaliable references until you find "Microsoft Outlook 10.0 Object Library". Tick the CheckBox and then 'OK'.

This will allow you to reference all the objects etc available for Microsoft Outlook.

let me know if you have any other problems.

Nathan

Nathan,

That fixed the error message. I'll try to run the code either tonight or in the morning.

Thanks again, Dale

Nathan,

I was just about to throw in the towel, but figured out how to make it work. This is going to be awesome!

Thanks, Dale

Dale,
Glad I could help

Nathan