Please help (Access 2010) Automated Email generated from multiple reports

[SSgtFranklin]

I’m relatively new to using Access (2010), I’ve used it a few times in the past with very basic intentions. However, I’m trying to make a much more complex database that pulls information from multiple reports and queries to give provide my customers with a polished end product.
My facility supports the calibration and repair of over 15,000 items from more than 260 different Owning Work Centers (OWCs). Each item is assigned a unique alpha-numeric ID number, and every OWC is assigned a unique 5-digit OWC code to their account.
I use an online database to pull information on the status of their equipment using 5 different reports (these reports can be exported in either PDF, XML, HTML, or Rich Text Formats) which I will outline below. Using the information on these reports, I’m required to provide each customer a daily, monthly and quarterly report on various equipment status’.
Report 1 – Customer Contact List
This report provides the following information: OWC Code, Name, Email Address, and whether they are a Primary/Alternate Monitor, Supervisor, or Commander, as well as phone numbers.
Report 2 – Master ID Listing
This report provides the following information: OWC Code, Name of Primary Monitor, Phone Number, Equipment ID Number, Part Number, Serial Number, Date Calibrated, Date Due Calibration, Calibration Interval etc. for every item loaded against their OWC account. (Basically provides them every detailed information on each asset they own) However, it does not provide email addresses for any of the contacts listed for their respective work center. (Quarterly Requirement)
Report 3 – Monthly Schedule Listing
This report is used to provide a monthly forecast to the customer to help them identify items requiring calibration in the upcoming month. It provides most of the same information as the Master ID Listing above, but only shows items coming due for calibration in that monthly window. (Monthly Requirement)
Report 4 – Awaiting Customer Pickup (ACP)
This report identifies items that have been serviced/calibrated by our facility and are ready to be picked up by our customers. This listing provides Equipment ID, Part Number, Serial Number, Date Calibrated, and how many days it has been in ACP status. (Daily Requirement)
Report 5 – Overdue Calibration Listing
This report is used to identify items that have exceeded their calibration due date and must be returned to our facility for proper calibration/repair action. Same information as the above ACP report, however, it lists how many days the particular item has been in Overdue status instead of ACP status. (Daily Requirement)
Items in overdue status for 1-7 days must have the Primary and Alternate Monitors contacted to inform them of the overdue status of their equipment. At 8-14 Days I have to contact the Primary/Alternates as well as their supervisor, and 15+ days I’m required to send notification to all contacts including their commander.
Alright, that is the background information on what information I have to work with, with that being said, I’ll explain what I would like to achieve with my Access Database.
1 – I’ve ran all the reports and exported them via XML format into an Excel file
2 – I would like to create a form with buttons that I can click to automatically query the appropriate data from the imported excel files and automatically generate emails to each individual work center.
A – I would like an ACP button that queries the ACP report and filters the data by individual work center and emails the Primary/Alternate Monitors for every OWC that have ACP items
B – I would also like to create 3 different Overdue Notification buttons that queries data for any item showing overdue status from 1-7 days and emails the information to each OWC’s Primary/Alternate monitor. Another button for 8-14 days that emails the primary/alternate monitors and supervisors. And the final button to notify all contacts of the OWC of items overdue for 15+ days.
C – A button that will email each OWC their individual monthly schedules to just the primary and alternate monitors.
D – A button that will email each OWC their individual Master ID listing to just the primary and alternate monitors.

In the end, I want 6 buttons that will generate individualized emails to each OWC notifying the appropriate individuals and will run automated through all 260+ OWC’s until there are no more customers to notify. I also want to have specific information in the body of the emails without having to type it all out or copy/paste 200 emails a day…
I have no idea how to do this since I don’t really know the programming code for access very well. It seems like a daunting task, but I know to someone here it will probably sound extremely simple…that’s why I came to the experts
Thank you all for reading and for any help/tips you can toss my way...I've tried reading about this online and nothing I can find really fits to my needs.

 

[DavidAtWork]

Hi SSgtFranklin, this is as you say "quite complex" and I'm not suprised you haven't found anything that fits your needs as it's quite bespoke and would require a significant amount of development to achieve what you've outlined.
I think it's fair to say this forum is designed to help those that require help/guidance for a specific problem encountered. The brief you've outlined above would require a significant number of hours of development as there would be many steps to reach your goal. That's not to say there aren't members here willing to help, but it would require a lot of effort on their part.
Good luck

David

 

[pbaldy]

How do you eat an elephant? One bite at a time.

That is certainly a tall order, but we can jump in and see where we get. I was going to point you to a link that would help with looping and sending reports, but it seems to be down right now. I'll check it again in the morning.

 

[GinaWhipp]

Hmm, hopefully, this will get you started...
http://www.access-diva.com/vba16.html

OR you code combine the one above with this one because you might need to specify who gets which report...

Function Email(strTo As String, strSubject _
        As String, Optional varMsg As Variant, Optional varAttachment As Variant)
' ©Arvin Meyer 1999-2004 
' Permission to use is granted if copyright notice is left intact.
' Permisssion is denied for use with unsolicited commercial email
'Set reference to Outlook
On Error GoTo Errhandler
Dim strBCC As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim objOutl As Outlook.Application
'Dim objEml As Outlook.MailItem
Dim I As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("qryContacts", dbOpenSnapshot)
Set objOutl = CreateObject("Outlook.application")
'Set objEml = objOutl.createItem(olMailitem)
With rst
    If .RecordCount > 0 Then
        .MoveLast
        .MoveFirst
    End If
End With
For I = 1 To rst.RecordCount
    If Len(rst!EmailAddress) > 0 Then
        strTo = rst!EmailAddress
    Dim objEml As Outlook.MailItem
    Set objEml = objOutl.createItem(olMailitem)
        With objEml
            .To = strTo
            .Subject = strSubject
            
            If Not IsNull(varMsg) Then
                .Body = varMsg
            End If
' Uncomment for attachment            
'            If Not IsMissing(varAttachment) Then
'                .Attachments.Add varAttachment
'            End If
            
            .Send
        End With
    End If
    Set objEml = Nothing
    rst.MoveNext
Next I
ExitHere:
Set objOutl = Nothing
'Set objEml = Nothing
Set rst = Nothing
Set db = Nothing
    Exit Function
    
Errhandler:
    MsgBox Err.Number & ": " & Err.Description
    Resume ExitHere
End Function

 

[pbaldy]

This is the link I couldn't get to work last night:

http://www.granite.ab.ca/access/email/reporttomultiplerecipients.htm

 

[SSgtFranklin]

David, Paul, Gina...thank you all for the information. I'm going to try the sites out that you've provided and see where it gets me. Unfortunately with the ever increasing work load I'm under, it may be slow going.

Thank you all again, I greatly appreciate your help and support.

By the way Paul...the way you eat an elephant is by sending in a Dragon!

DRAGONS! FEED THE BEAST! - Courtesy of the 379 Equipment Maintenance Squadron - Squadron Chant

 

[pbaldy]

Go Dragons! Post back if you get stuck.