Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Macros _ Automate Emails From Query Results

Posted by: EMDS98 Apr 25 2019, 10:27 AM

OVERVIEW: We have many companies providing us service on more than 1 work order in many cases. Each work order is a row of DATA in a master table (tblMajorRepairData). I've created a Query "qryAutomateEmails" that will filter the records that need to be emailed. I need to send emails for updates on these work orders to each company daily, but I would like to send 1 email to each company containing an excel spreadsheet with all the work orders the currently have open in our system. I also need to update a table, tblMajorRepairData with a new "fldStatus" ("Automated Email Sent") and "fldNextUpdate" with the current time ( Now() ).

I haven't found code that does what I need. Any help will be greatly appreciated.

Posted by: Minty Apr 25 2019, 10:37 AM

Google is your friend https://www.devhut.net/2016/07/07/vba-automating-excel/

This will be a good start for you. If you get stuck ask back here Daniel is a member of this forum.

Posted by: jleach Apr 25 2019, 11:19 AM

Also check in our Code Archive here for an entry by CheekyBuddha (David) for Sending Emails with CDO - this can be used to send emails without relying on Outlook automation (which is the uninitiated's usual first choice, but the poorest one there is, IMO).

Posted by: EMDS98 Apr 25 2019, 12:13 PM

I appreciate you speedy responses. I guess the main thing I need help with is how to send one email to each company (that includes all work orders) rather than an email for each record (work order). Below is my current code.

CODE
Private Sub cmdAutomateEmails_Click()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    Dim emailTo As String
    Dim emailSubject As String
    Dim emailText As String


    Dim outApp As Outlook.Application
    Dim outMail As Outlook.MailItem
    Dim outlookStarted As Boolean
        
    
    On Error Resume Next
    Set outApp = GetObject(, "Outlook.Application")
    On Error GoTo 0
    If outApp Is Nothing Then
        Set outApp = CreateObject("Outlook.Application")
        outlookStarted = True
    End If

    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT District, ROnum, UnitNum, SPName,EmailAddr " & _
                                " FROM qryAutomateEmails")
    Do Until rs.EOF

        emailTo = "<" & rs.Fields("EmailAddr").Value & ">"
                    
        emailSubject = "Test Subject"
        
        emailText = rs.Fields("ROnum").Value & vbCrLf

                    
        Set outMail = outApp.CreateItem(olMailItem)
        outMail.To = emailTo
        outMail.Subject = emailSubject
        outMail.Body = emailText
        outMail.send

        rs.MoveNext
    Loop
    
    rs.Close
    Set rs = Nothing
    Set db = Nothing

    
    If outlookStarted Then
        outApp.Quit
    End If
    
    Set outMail = Nothing
    Set outApp = Nothing
    
End Sub

Posted by: MadPiet Apr 25 2019, 01:27 PM

You need one recordset for recipients, and another for the work orders belonging to that recipient.

Here's some nice airy air code for you Basically, you have an outer Recipients loop and then an inner WorkOrders loop.

while not rsRecipients.EOF
'create a new email
set rsWorkOrders = CurrentDB.OpenRecordset "SELECT... FROM WorkOrders WHERE CompanyID = " & rsRecipients.Fields("CompanyID").Value
'process work Orders...
' add them to e-mail or something...
while not EOF rsWorkOrders
'add the info from work orders to the e-mail
rsWorkOrders.MoveNext
loop 'Work orders
rsWorkOrders.Close
rsRecipients.MoveNext
Loop '--Recipients

Posted by: EMDS98 Apr 25 2019, 02:06 PM

Ok, that is starting to make sense but I'm not quite sure how to add it to my code. Are you able to show me how it should look?

Posted by: EMDS98 May 3 2019, 07:27 AM

Can anyone help with editing the above code so that 1 email is sent to each company containing all records with the same company name and the records are added to the body of the email?

Posted by: cheekybuddha May 3 2019, 07:55 AM

What is the SQL of qryAutomateEmails ?

Please also describe the tables involved in qryAutomateEmails (field names and datatypes, which field is Primary Key, etc)

Posted by: EMDS98 May 3 2019, 12:22 PM



qryAutomateEmails

CODE
SELECT MajorRepairData.District, MajorRepairData.[RO #] AS ROnum, MajorRepairData.[Unit #] AS UnitNum, MajorRepairData.Status, MajorRepairData.WorkOrderNumber, MajorRepairData.RepairNotes, MajorRepairData.ETC, SPListandRates.[ID #], SPListandRates.[Service Provider Name] AS SPName, SPListandRates.[Email Address] AS EmailAddr
FROM MajorRepairData INNER JOIN SPListandRatesON MajorRepairData.[Map #] = SPListandRates.[ID #]
WHERE (((MajorRepairData.NextUpdate)>=Now()))
GROUP BY MajorRepairData.District, MajorRepairData.[RO #], MajorRepairData.[Unit #], MajorRepairData.Status, MajorRepairData.WorkOrderNumber, MajorRepairData.RepairNotes, MajorRepairData.ETC, SPListandRates.[ID #], SPListandRates.[Service Provider Name], SPListandRates.[Email Address], MajorRepairData.[RO #], MajorRepairData.[Unit #], SPListandRates.[Service Provider Name], SPListandRates.[Email Address]
ORDER BY SPListandRates.[Service Provider Name], SPListandRates.[Email Address];


Main Data Table: MajorRepairData (Primary Key [WorkOrderNumber](Short Text))
Required Fields/Data Type: [RO #](Number), [Unit #](Short Text), [District](Short Text), [ETC](Short Text), [RepairNotes](Short Text), [NextUpdate](Date/Time),[Map #](Number)

Table: SPListandRates (Primary Key [ID #](AutoNumber))
Required Fields/Data Type: [ID #], [Service Provider Name](Short Text), [EmailAddress](Short Text)

I'm trying to send all the records (with the Required Fields listed above) from "MajorRepairData" in the body of an email where [MajorRepairData].[Map #] matches [SPListandRates].[ID #]

Posted by: MadPiet May 3 2019, 12:43 PM

QUOTE
I'm trying to send all the records (with the Required Fields listed above) from "MajorRepairData" in the body of an email where [MajorRepairData].[Map #] matches [SPListandRates].[ID #]


So get that information and return it as something like a Word table? I have done it before, but not in a long time... but I'm sure there's code out there for it. In a nutshell, you'd have to loop through a table of Recipients, and then pass the RecipientID to the "MajorRepairData" query (as a filter) and then that will return a bunch of records, which you then format like a table and drop into your e-mail.

Posted by: MadPiet May 3 2019, 02:00 PM

if you could post a database with DUMMY data, I could probably bang this together in 15 mins. I used to work in healthcare, so please, just FAKE data.

In a nutshell, you get a list of unique recipients. (Say you're looking for all Customers with outstanding invoices... then it's something like

SELECT c.CustomerID, c.CustomerName, c.EMail
FROM Customer c
WHERE EXISTS (SELECT 1 FROM Invoice I WHERE i.CustomerID = c.CustomerID AND i.InvoicePaidDate IS NULL);

Then you would have a list of customers that have outstanding invoices (Yeah, the query is kinda lame, but I wanted it to be simple). Then you open a recordset of those emailaddresses/CustomerIDs and get the unpaid Invoices that belong to them.

SELECT
FROM Invoice I
WHERE i.CustomerID = rsCustomers.Fields("CustomerID")
AND i.InvoicePaidDate IS NULL

Then you convert that to a table and drop it into your email. Simple, right?

Posted by: EMDS98 May 6 2019, 08:48 AM

Yes the concept is simple but I'm having a hard time getting this code to do what I want. I actually want to list the DATA in the body of the email, for some reason many of the people we email to are not familiar with excel and it actually prevents us from getting updates from them. I really appreciate the help on this. I attached a sample database with fake DATA.

 AutomateEmailsTest.zip ( 23.84K ): 19
 

Posted by: cheekybuddha May 7 2019, 09:21 AM

Hi,

You seem to have the following fields duplicated in your GROUP BY clause:

CODE
MajorRepairData.[RO #], MajorRepairData.[Unit #], SPListandRates.[Service Provider Name], SPListandRates.[Email Address]


Piet will probably come up with a good suggestion, but I think you an get a list of customers to email with a query like:
CODE
SELECT DISTINCT
  r.[Email Address] AS EmailAddr
FROM MajorRepairData d
INNER JOIN SPListandRates r
        ON d.[Map #] = r.[ID #]
WHERE d.NextUpdate >= Now();

Then, you can use code like:
CODE
Private Sub cmdAutomateEmails_Click()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset, rsDets As DAO.Recordset
    Dim strSQL As String
    
    Dim emailTo As String
    Dim emailSubject As String
    Dim emailText As String

    Dim outApp As Outlook.Application
    Dim outMail As Outlook.MailItem
    Dim outlookStarted As Boolean
    
    On Error Resume Next
    Set outApp = GetObject(, "Outlook.Application")
    On Error GoTo 0
    If outApp Is Nothing Then
        Set outApp = CreateObject("Outlook.Application")
        outlookStarted = True
    End If

    Set db = CurrentDb
    strSQL = "SELECT DISTINCT " & _
             "r.[Email Address] AS EmailAddr " & _
             "FROM MajorRepairData d " & _
             "INNER JOIN SPListandRates r " & _
             "ON d.[Map #] = r.[ID #] " & _
             "WHERE d.NextUpdate >= Now();"
    Set rs = db.OpenRecordset(strSQL)
    Do Until rs.EOF
        emailTo = "<" & .Fields("EmailAddr") & ">"
        emailSubject = "Test Subject"
        emailText = vbNullString
        strSQL = "SELECT District, ROnum, UnitNum, SPName FROM qryAutomateEmails WHERE EmailAddr = '" & emailTo & "';"
        Set rsDets = db.OpenRecordset(strSQL)
        With rsDets
            Do Until .EOF
                emailText = emailText & .Fields("District") & ", " & .Fields("ROnum") & ", " & .Fields("UnitNum") & ", " & .Fields("SPName") & vbNewLine
                .MoveNext
            Loop
            .Close
        End With
        Set outMail = outApp.CreateItem(olMailItem)
        outMail.To = emailTo
        outMail.Subject = emailSubject
        outMail.Body = emailText
        outMail.send
        Set outMail = Nothing
        .MoveNext
    Loop
    
    rs.Close
    Set rs = Nothing
    Set db = Nothing

    If outlookStarted Then
        outApp.Quit
    End If
    
    Set outMail = Nothing
    Set outApp = Nothing
    
End Sub

(Untested, of course!!!)

hth,

d

Posted by: EMDS98 May 7 2019, 03:29 PM

Thanks David! I have it working where it will only send 1 email per email address but I'm not getting anything in the body of the email. I do have the necessary fields added to the query. Below is the current code I'm using:

CODE
Set rsDets = db.OpenRecordset(strSQL)
        With rsDets
            Do Until .EOF
                emailText = emailText & rsDets.Fields("District") & ", " & rsDets.Fields("RO #") & ", " & rsDets.Fields("Unit #") & ", " & rsDets.Fields("Service Provider Name") & vbNewLine
                .MoveNext
            Loop
            .Close
        End With
        Set outMail = outApp.CreateItem(olMailItem)
        outMail.To = emailTo
        outMail.Subject = emailSubject
        outMail.Body = emailText & "Testing"
        outMail.send
        Set outMail = Nothing
        rs.MoveNext
    Loop

Posted by: cheekybuddha May 7 2019, 03:57 PM

As a test, try running with this:

CODE
' ...
        With rsDets
            Do Until .EOF
                emailText = emailText & rsDets.Fields("District") & ", " & rsDets.Fields("RO #") & ", " & rsDets.Fields("Unit #") & ", " & rsDets.Fields("Service Provider Name") & vbNewLine
                .MoveNext
            Loop
            .Close
           Debug.Print emailTo, "Recs: " & .RecordCount
        End With
' ...

Then check the Immediate Window (Ctrl+G) and report back whether you are getting any records for each email address.

... Of course, I have just realised that you added '<...>' around each email address, so there won't be any matches in the recordset!!!

Revise your code to:
CODE
Private Sub cmdAutomateEmails_Click()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset, rsDets As DAO.Recordset
    Dim strSQL As String
    
    Dim emailTo As String
    Dim emailSubject As String
    Dim emailText As String

    Dim outApp As Outlook.Application
    Dim outMail As Outlook.MailItem
    Dim outlookStarted As Boolean
    
    On Error Resume Next
    Set outApp = GetObject(, "Outlook.Application")
    On Error GoTo 0
    If outApp Is Nothing Then
        Set outApp = CreateObject("Outlook.Application")
        outlookStarted = True
    End If

    Set db = CurrentDb
    strSQL = "SELECT DISTINCT " & _
             "r.[Email Address] AS EmailAddr " & _
             "FROM MajorRepairData d " & _
             "INNER JOIN SPListandRates r " & _
             "ON d.[Map #] = r.[ID #] " & _
             "WHERE d.NextUpdate >= Now();"
    Set rs = db.OpenRecordset(strSQL)
    Do Until rs.EOF
        emailTo = .Fields("EmailAddr")
        emailSubject = "Test Subject"
        emailText = vbNullString
        strSQL = "SELECT District, ROnum, UnitNum, SPName FROM qryAutomateEmails WHERE EmailAddr = '" & emailTo & "';"
        Set rsDets = db.OpenRecordset(strSQL)
        With rsDets
            Do Until .EOF
                emailText = emailText & rsDets.Fields("District") & ", " & rsDets.Fields("RO #") & ", " & rsDets.Fields("Unit #") & ", " & rsDets.Fields("Service Provider Name") & vbNewLine
                .MoveNext
            Loop
            .Close
        End With
        Set outMail = outApp.CreateItem(olMailItem)
        outMail.To = "<" & emailTo & ">"
        outMail.Subject = emailSubject
        outMail.Body = emailText
        outMail.send
        Set outMail = Nothing
        .MoveNext
    Loop
    
    rs.Close
    Set rs = Nothing
    Set db = Nothing

    If outlookStarted Then
        outApp.Quit
    End If
    
    Set outMail = Nothing
    Set outApp = Nothing
    
End Sub


Hopefully you should be good to go now! thumbup.gif

d

Posted by: EMDS98 May 8 2019, 12:20 PM

Good catch... I totally missed that. It is working perfect now. I really appreciate your help on this. Below is the final code I'm using:

CODE
Private Sub cmdAutomateEmails_Click()

    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset, rs2 As DAO.Recordset
    Dim strSQL As String
    
    Dim emailTo As String
    Dim emailSubject As String
    Dim emailText As String
    Dim xSPName As String
    Dim xIntro As String
    Dim xSignature As String
    Dim xArea As String

    Dim outApp As Outlook.Application
    Dim outMail As Outlook.MailItem
    Dim outlookStarted As Boolean
    
    On Error Resume Next
    Set outApp = GetObject(, "Outlook.Application")
    On Error GoTo 0
    If outApp Is Nothing Then
        Set outApp = CreateObject("Outlook.Application")
        outlookStarted = True
    End If

    Set db = CurrentDb
    'strSQL = "SELECT DISTINCT r.[Email Address] AS EmailAddr, d.[RO #], d.[Unit #], r.[Service Provider Name], d.District" & _
    '"FROM MajorRepairData AS d INNER JOIN SPListandRatesTESTING AS r ON d.[Map #] = r.[ID #]" & _
    '"WHERE (((d.nextupdate) >= Now()))" & _
    '"GROUP BY r.[Email Address], d.[RO #], d.[Unit #], r.[Service Provider Name], d.District;"
    
      strSQL = "SELECT DISTINCT " & _
             "r.[Email Address] AS EmailAddr " & _
             "FROM MajorRepairData d " & _
             "INNER JOIN SPListandRatesTESTING r " & _
             "ON d.[Map #] = r.[ID #] " & _
             "WHERE d.NextUpdate >= Now();"
            
    Set rs1 = db.OpenRecordset(strSQL)
    Do Until rs1.EOF
        emailTo = rs1.Fields("EmailAddr")
        emailSubject = "Update Request " & Now()
        emailText = vbNullString
        strSQL = "SELECT District, [RO #], [UnitNum], [Service Provider Name], [VIN],[Area] FROM qryAutomateEmails WHERE EmailAddr = '" & emailTo & "';"
        Set rs2 = db.OpenRecordset(strSQL)
        With rs2
            Do Until .EOF
                emailText = emailText & "Unit #: " & rs2.Fields("UnitNum") & ", " & "VIN: " & rs2.Fields("VIN") & ", " & "District #: " & rs2.Fields("District") & ", " & "RO #: " & rs2.Fields("RO #") & vbNewLine & "Detailed Update:" & vbNewLine & "Estimated Completion Time:" & vbNewLine & vbNewLine
                xSPName = rs2.Fields("Service Provider Name") & "," & vbNewLine & vbNewLine
                xIntro =  "Please reply with a ""Detailed Update"" and ""Estimated Completion Time"" for each unit." & vbNewLine & vbNewLine
                xSignature = "Thank you," & vbNewLine & vbNewLine & "Company Name"
                xArea = rs2.Fields("Area")
                
                .MoveNext
            Loop
            .Close
        End With
        Set outMail = outApp.CreateItem(olMailItem)
        outMail.SentOnBehalfOfName = xArea & "Test@Testing.com"
        outMail.To = "<" & emailTo & ">"
        outMail.Subject = emailSubject
        outMail.body = xSPName & xIntro & emailText & xSignature
        outMail.send
        Set outMail = Nothing
        rs1.MoveNext
    Loop
    
    rs1.Close
    Set rs1 = Nothing
    Set db = Nothing

    If outlookStarted Then
        outApp.Quit
    End If
    
    Set outMail = Nothing
    Set outApp = Nothing

Posted by: cheekybuddha May 8 2019, 01:23 PM

>> xSignature = "Thank you," & vbNewLine & vbNewLine & "Company Name" <<

I guess Company Name is replaced with your actual company name.

This line might effectively be moved outside the loop since it doesn't depend on any fields of rs2 - no need to keep setting it repeatedly!

Glad, you've got it working!

thumbup.gif

d

Posted by: NICKRICKARDS May 18 2019, 08:50 AM

Hi all,

so will this code also work with access 2013, as i have some users on 2016 and some on the older version?

Posted by: MadPiet May 21 2019, 04:58 PM

Should work fine. I can't remember, but I think the filetype for 2013 and 2016 is almost exactly the same.

Posted by: EMDS98 May 28 2019, 11:06 AM

So I had this working but I had to add in a bunch of criteria and after rewriting the SQL statement in the code, I'm getting duplicate emails and emails without data. My query is correct and excludes what I want but when I run the code it doesn't match my query.

This is the SQL from my query:

CODE
SELECT DISTINCTROW r.[ID #], Trim([Email Address]) AS EmailAddr, d.WorkOrderNumber, d.[RO #], Trim([Unit #]) AS UnitNum, r.[Service Provider Name], d.District AS Dist, Trim(Nz([VFVIN],"Not Available")) AS VIN, d.Area, CORP_LDSTMST.DMNAME, Trim([DMADD1]) AS DistAddr, Trim([DMCITY]) AS DistCity, CORP_LDSTMST.DMDIST, CORP_LDSTMST.DMLOC
FROM ((SERVICE_WORKORDERV RIGHT JOIN ((MajorRepairData AS d INNER JOIN SPListandRates AS r ON d.[Map #] = r.[ID #]) LEFT JOIN CORP_LVEHFIL ON d.[Unit #] = CORP_LVEHFIL.VFUNIT) ON SERVICE_WORKORDERV.WORK_ORD = d.WorkOrderNumber) LEFT JOIN CORP_LDSTMST ON (SERVICE_WORKORDERV.LOCATION = CORP_LDSTMST.DMLOC) AND (SERVICE_WORKORDERV.DISTRICT = CORP_LDSTMST.DMDIST)) INNER JOIN Users ON d.User = Users.EmployeeName
WHERE (((CORP_LDSTMST.DMADD1)<>"                        " And (CORP_LDSTMST.DMADD1)<>"UNKNOWN                 ") AND ((Users.Title)<>"MRS") AND ((d.CurrentEmailUpdates)<2) AND ((d.NextUpdate)<=Now()) AND ((CORP_LDSTMST.DMAREA) Like "****") AND ((d.Status)="Repairs in Progress" Or (d.Status)="Waiting on Diagnosis" Or (d.Status)="Waiting on Estimate" Or (d.Status)="Waiting on Parts" Or (d.Status)="Waiting on Service Provider Response" Or (d.Status)="Repairs Pending" Or (d.Status)="Waiting on Revised Estimate") AND ((d.WOStatus)<>"F" And (d.WOStatus)<>"V" And (d.WOStatus)<>"R") AND ((Trim([Email Address])) Is Not Null))
GROUP BY r.[ID #], Trim([Email Address]), d.WorkOrderNumber, d.[RO #], Trim([Unit #]), r.[Service Provider Name], d.District, Trim(Nz([VFVIN],"Not Available")), d.Area, CORP_LDSTMST.DMNAME, Trim([DMADD1]), Trim([DMCITY]), CORP_LDSTMST.DMDIST, CORP_LDSTMST.DMLOC;


This is the SQL I added to my code:
CODE
    strSQL = "SELECT DISTINCT r.[ID #], Trim([Email Address]) AS EmailAddr, d.WorkOrderNumber, d.[RO #], Trim([Unit #]) AS UnitNum, r.[Service Provider Name], d.District AS Dist, Trim(Nz([VFVIN],""Not Available"")) AS VIN, d.Area, CORP_LDSTMST.DMNAME, Trim([DMADD1]) AS DistAddr, Trim([DMCITY]) AS DistCity, CORP_LDSTMST.DMDIST, CORP_LDSTMST.DMLOC " & _
    "FROM ((SERVICE_WORKORDERV RIGHT JOIN ((MajorRepairData AS d LEFT JOIN SPListandRates AS r ON d.[Map #] = r.[ID #]) LEFT JOIN CORP_LVEHFIL ON d.[Unit #] = CORP_LVEHFIL.VFUNIT) ON SERVICE_WORKORDERV.WORK_ORD = d.WorkOrderNumber) LEFT JOIN CORP_LDSTMST ON (SERVICE_WORKORDERV.LOCATION = CORP_LDSTMST.DMLOC) AND (SERVICE_WORKORDERV.DISTRICT = CORP_LDSTMST.DMDIST)) LEFT JOIN Users ON d.Status = Users.EmployeeName " & _
    "WHERE (((CORP_LDSTMST.DMADD1)<>""                        "" And (CORP_LDSTMST.DMADD1)<>""UNKNOWN                 "") AND ((Users.Title)<>""MRS"") AND ((d.CurrentEmailUpdates)<2) AND ((d.NextUpdate)<=Now()) AND ((CORP_LDSTMST.DMAREA) Like ""****"") AND ((d.Status)=""Repairs in Progress"" Or (d.Status)=""Waiting on Diagnosis"" Or (d.Status)=""Waiting on Estimate"" Or (d.Status)=""Waiting on Parts"" Or (d.Status)=""Waiting on Service Provider Response"" Or (d.Status)=""Repairs Pending"" Or (d.Status)=""Waiting on Revised Estimate"") AND ((d.WOStatus)<>""F"" And (d.WOStatus)<>""V"" And (d.WOStatus)<>""R"") AND ((Trim([Email Address])) Is Not Null))"

Posted by: cheekybuddha May 28 2019, 11:35 AM

What an effin' mess!

What tables do the following fields come from?
[Email Address]
[Unit #]
[VFVIN] (my guess: CORP_LVEHFIL)
[DMADD1] (my guess: CORP_LDSTMST)
[DMCITY] (my guess: CORP_LDSTMST)

Posted by: cheekybuddha May 28 2019, 11:59 AM

The SQL of your query in the previous post looks like it oughtn't to work!!

But since you say it does work, can you try and see if the following SQL will return the same results:

CODE
SELECT DISTINCTROW
  r.[ID #],
  Trim([Email Address]) AS EmailAddr,
  d.WorkOrderNumber,
  d.[RO #],
  Trim([Unit #]) AS UnitNum,
  r.[Service Provider Name],
  d.District AS Dist,
  Trim(Nz(VFVIN,'Not Available')) AS VIN,
  d.Area,
  c2.DMNAME,
  Trim(DMADD1) AS DistAddr,
  Trim(DMCITY) AS DistCity,
  c2.DMDIST,
  c2.DMLOC
FROM (
  (
    SERVICE_WORKORDERV wo
    RIGHT JOIN (
      (
        MajorRepairData AS d
        INNER JOIN SPListandRates AS r
                ON d.[Map #] = r.[ID #]
      )
      LEFT JOIN CORP_LVEHFIL c1
             ON d.[Unit #] = c1.VFUNIT
    )
             ON wo.WORK_ORD = d.WorkOrderNumber
  )
  LEFT JOIN CORP_LDSTMST c2
         ON (wo.LOCATION = c2.DMLOC)
        AND (wo.DISTRICT = c2.DMDIST)
)
INNER JOIN Users u
        ON d.User = u.EmployeeName
WHERE Trim(c2.DMADD1) NOT IN ('', 'UNKNOWN')
  AND u.Title <> 'MRS'
  AND d.CurrentEmailUpdates < 2
  AND d.NextUpdate <=Now()
  AND c2.DMAREA Like '****'
  AND d.Status IN (
    'Repairs in Progress',
    'Waiting on Diagnosis',
    'Waiting on Estimate',
    'Waiting on Parts',
    'Waiting on Service Provider Response',
    'Repairs Pending',
    'Waiting on Revised Estimate'
  )
  AND d.WOStatus NOT IN ('F', 'V', 'R')
  AND [Email Address] IS NOT NULL
GROUP BY
  r.[ID #],
  Trim([Email Address]),
  d.WorkOrderNumber,
  d.[RO #],
  Trim([Unit #]),
  r.[Service Provider Name],
  d.District,
  Trim(Nz(VFVIN,'Not Available')),
  d.Area,
  c2.DMNAME,
  Trim(DMADD1),
  Trim(DMCITY),
  c2.DMDIST,
  c2.DMLOC
;


If it does, then it should be easier to convert into a VBA string.

Posted by: cheekybuddha May 28 2019, 02:02 PM

Also, what is the point of this criterion?

CODE
...
  AND c2.DMAREA Like '****'
...

Posted by: MadPiet May 28 2019, 02:36 PM

I have a stupid question.

Why not loop through the Recipients (from spListAndRates) that are in the Repairs (MajorRepairData) using EXISTS to weed out any recipients without related repairs)
and then another loop to get all the Tow Tickets or whatever and append those to the Email Message as a table?

Seems like this should be a pretty straightforward problem to solve.


Posted by: EMDS98 May 29 2019, 07:44 AM

I tried this code and it created an email that grouped the correct cases for the correct service but duplicated the same email for each case.

For example: for JohnsService@email.com there are 3 work orders
Email # 2: Workorder 09888, 09887, 09886
Email # 2: Workorder 09888, 09887, 09886
Email # 2: Workorder 09888, 09887, 09886

I have this criteria (AND c2.DMAREA Like '****') because the area's that i want are 4 numbers, but the corporate table I'm pulling from contains billing areas that i want to exclude and they never 4 characters.

[Email Address] is from the SPListansRates
[Unit #] is from MajorRepairData

You are correct on your guess for these tables.
[VFVIN] (my guess: CORP_LVEHFIL)
[DMADD1] (my guess: CORP_LDSTMST)
[DMCITY] (my guess: CORP_LDSTMST)

Also, this is the GROUP BY SQL. The code has the same issue with it added so I left it out. Not sure if that has anything to do with it.

CODE
GROUP BY r.[ID #], Trim([Email Address]), d.WorkOrderNumber, d.[RO #], Trim([Unit #]),
r.[Service Provider Name], d.District, Trim(Nz([VFVIN],"Not Available")), d.Area, CORP_LDSTMST.DMNAME,
Trim([DMADD1]), Trim([DMCITY]), CORP_LDSTMST.DMDIST, CORP_LDSTMST.DMLOC;

Posted by: EMDS98 May 29 2019, 08:04 AM

If the current code doesn't work out, I will research EXISTS and give it a try. I'm just not understanding why my query gives me the results I want but when I add the SQL to VBA it doesn't.

Posted by: cheekybuddha May 29 2019, 08:35 AM

If your current SQL works, then try this conversion to VBA:

CODE
strSQL = "SELECT DISTINCTROW r.[ID #], Trim([Email Address]) AS EmailAddr, " & _
         "d.WorkOrderNumber, d.[RO #], Trim([Unit #]) AS UnitNum, " & _
         "r.[Service Provider Name], d.District AS Dist, " & _
         "Trim(Nz([VFVIN],'Not Available')) AS VIN, d.Area, CORP_LDSTMST.DMNAME, " & _
         "Trim([DMADD1]) AS DistAddr, Trim([DMCITY]) AS DistCity, " & _
         "CORP_LDSTMST.DMDIST, CORP_LDSTMST.DMLOC "
strSQL = strSQL & _
         "FROM ((SERVICE_WORKORDERV RIGHT JOIN " & _
         "((MajorRepairData AS d INNER JOIN SPListandRates AS r ON d.[Map #] = r.[ID #]) " & _
         "LEFT JOIN CORP_LVEHFIL ON d.[Unit #] = CORP_LVEHFIL.VFUNIT) " & _
         "ON SERVICE_WORKORDERV.WORK_ORD = d.WorkOrderNumber) " & _
         "LEFT JOIN CORP_LDSTMST ON (SERVICE_WORKORDERV.LOCATION = CORP_LDSTMST.DMLOC) " & _
         "AND (SERVICE_WORKORDERV.DISTRICT = CORP_LDSTMST.DMDIST)) " & _
         "INNER JOIN Users ON d.User = Users.EmployeeName "
strSQL = strSQL & _
         "WHERE (((CORP_LDSTMST.DMADD1)<>'                        ' " & _
         "And (CORP_LDSTMST.DMADD1)<>'UNKNOWN                 ') " & _
         "AND ((Users.Title)<>'MRS') AND ((d.CurrentEmailUpdates)<2) " & _
         "AND ((d.NextUpdate)<=Now()) AND ((CORP_LDSTMST.DMAREA) Like '****') " & _
         "AND ((d.Status)='Repairs in Progress' Or (d.Status)='Waiting on Diagnosis' " & _
         "Or (d.Status)='Waiting on Estimate' Or (d.Status)='Waiting on Parts' " & _
         "Or (d.Status)='Waiting on Service Provider Response' " & _
         "Or (d.Status)='Repairs Pending' " & _
         "Or (d.Status)='Waiting on Revised Estimate') " & _
         "AND ((d.WOStatus)<>'F' And (d.WOStatus)<>'V' " & _
         "And (d.WOStatus)<>'R') AND ((Trim([Email Address])) Is Not Null)) "
strSQL = strSQL & _
         "GROUP BY r.[ID #], Trim([Email Address]), d.WorkOrderNumber, " & _
         "d.[RO #], Trim([Unit #]), r.[Service Provider Name], d.District, " & _
         "Trim(Nz([VFVIN],'Not Available')), d.Area, " & _
         "CORP_LDSTMST.DMNAME, Trim([DMADD1]), Trim([DMCITY]), " & _
         "CORP_LDSTMST.DMDIST, CORP_LDSTMST.DMLOC;"


hth,

d

Posted by: MadPiet May 29 2019, 11:05 AM

If your goal is one e-mail per customer with open work orders or whatever, then I would absolutely do something like this:

1. return the EMail Addresses etc for all customers with active Repairs.

CODE
SELECT [ID#] AS id, [Service Provider Name] AS provider, [EMail Address] AS email
FROM spListAndRates LR
WHERE EXISTS (SELECT 1 FROM MajorRepairData mrd WHERE mrd.[Map#] = LR.[ID#])


<-- the join in the WHERE clause (and the aliasing) forces a filter of the MajorRepairData table, so that only customers with active repairs are returned.

Once you have that, you can pass that ID to a query to retrieve the Repairs for each Service Provider. then just convert that data into something you can slap into your e-mail body. (Maybe convert the returned results to a delimited string and convert it to a table in your e-mail).

2. Generate the child records and maybe convert to a table and drop into the Customer's e-mail. You could loop and pass the ServiceProviderID to your query to return the related repairs, and then convert that result set to a table.

3. Send.

Might be me, but I think trying to get all this in a single query is both waaaaay complicating the issue and completely unnecessary. If you join the one side and the many side of a one-to-many relationship in a query, you're going to return a record for each record in the child/many side table. And that's not what you want.

Posted by: EMDS98 May 29 2019, 11:13 AM

cheekybuddha,

I tried that code and it still is producing duplicates.


Madpiet,

I will try to rebuild and see what I can come up with.

Posted by: cheekybuddha May 29 2019, 11:22 AM

The code I posted is your SQL query from https://www.UtterAccess.com/forum/index.php?showtopic=2053542&view=findpost&p=2720109 which you said worked!

Posted by: MadPiet May 29 2019, 11:25 AM

If you join the List table and the Repairs table in a query, it's going to return duplicates from the List table. That's the way SQL works. That's why I suggested using EXISTS. Using DISTINCT is not going to solve your problem if you join the two tables. A query with a join between two tables will by definition return one row for each record on the many side of the relationship. If you want this to work, process the SPList data and then inside that loop process the RepairData.

If someone else can make it work another way, I'd love to hear it, but I don't think it's possible.

Why not just create a report with all the data, maybe with outer report based on Customer and inner report based on jobs or whatever, and then just send that?

Posted by: EMDS98 May 29 2019, 12:28 PM

Sorry Cheeky, I meant the Query worked perfectly as it doesn't show any duplicates but when I added the SQL to my code, it starts producing duplicates. I know when you helped previously I had this working but I kept finding minor issues and had to add more criteria, that's when this problem started.

Madpiet, I understand what you're saying but as of now Im not totally sure how to approach this. Also, we have found that we get better responses from the emails when it is in the email body where they can just type in the updates that they have or I would definitely send a report.

I'll post the newest code that I have, I've made some changes from when I first posted here. I really appreciate the help from you guys.

CODE
Function AutoUpdateRequest()

'************************************************
'Update Request
'************************************************
    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset, rs2 As DAO.Recordset
    Dim strSQL As String
    
    Dim emailTo As String
    Dim emailSubject As String
    Dim emailText As String
    Dim xSPName As String
    Dim xIntro As String
    Dim xSignature As String
    Dim xArea As String

    Dim outApp As Outlook.Application
    Dim outMail As Outlook.MailItem
    Dim outlookStarted As Boolean
    
    DoCmd.SetWarnings False
    
    On Error Resume Next
    Set outApp = GetObject(, "Outlook.Application")
    On Error GoTo 0
    If outApp Is Nothing Then
        Set outApp = CreateObject("Outlook.Application")
        outlookStarted = True
    End If

    Set db = CurrentDb
    
'SQL from qryAutomateEmail
strSQL = "SELECT DISTINCTROW r.[ID #], Trim([Email Address]) AS EmailAddr, " & _
         "d.WorkOrderNumber, d.[RO #], Trim([Unit #]) AS UnitNum, " & _
         "r.[Service Provider Name], d.District AS Dist, " & _
         "Trim(Nz([VFVIN],'Not Available')) AS VIN, d.Area, CORP_LDSTMST.DMNAME, " & _
         "Trim([DMADD1]) AS DistAddr, Trim([DMCITY]) AS DistCity, " & _
         "CORP_LDSTMST.DMDIST, CORP_LDSTMST.DMLOC "
strSQL = strSQL & _
         "FROM ((SERVICE_WORKORDERV RIGHT JOIN " & _
         "((MajorRepairData AS d INNER JOIN SPListandRates AS r ON d.[Map #] = r.[ID #]) " & _
         "LEFT JOIN CORP_LVEHFIL ON d.[Unit #] = CORP_LVEHFIL.VFUNIT) " & _
         "ON SERVICE_WORKORDERV.WORK_ORD = d.WorkOrderNumber) " & _
         "LEFT JOIN CORP_LDSTMST ON (SERVICE_WORKORDERV.LOCATION = CORP_LDSTMST.DMLOC) " & _
         "AND (SERVICE_WORKORDERV.DISTRICT = CORP_LDSTMST.DMDIST)) " & _
         "INNER JOIN Users ON d.User = Users.EmployeeName "
strSQL = strSQL & _
         "WHERE (((CORP_LDSTMST.DMADD1)<>'                        ' " & _
         "And (CORP_LDSTMST.DMADD1)<>'UNKNOWN                 ') " & _
         "AND ((Users.Title)<>'MRS') AND ((d.CurrentEmailUpdates)<2) " & _
         "AND ((d.NextUpdate)<=Now()) AND ((CORP_LDSTMST.DMAREA) Like '****') " & _
         "AND ((d.Status)='Repairs in Progress' Or (d.Status)='Waiting on Diagnosis' " & _
         "Or (d.Status)='Waiting on Estimate' Or (d.Status)='Waiting on Parts' " & _
         "Or (d.Status)='Waiting on Service Provider Response' " & _
         "Or (d.Status)='Repairs Pending' " & _
         "Or (d.Status)='Waiting on Revised Estimate') " & _
         "AND ((d.WOStatus)<>'F' And (d.WOStatus)<>'V' " & _
         "And (d.WOStatus)<>'R') AND ((Trim([Email Address])) Is Not Null)) "
strSQL = strSQL & _
         "GROUP BY r.[ID #], Trim([Email Address]), d.WorkOrderNumber, " & _
         "d.[RO #], Trim([Unit #]), r.[Service Provider Name], d.District, " & _
         "Trim(Nz([VFVIN],'Not Available')), d.Area, " & _
         "CORP_LDSTMST.DMNAME, Trim([DMADD1]), Trim([DMCITY]), " & _
         "CORP_LDSTMST.DMDIST, CORP_LDSTMST.DMLOC;"

      
    Set rs1 = db.OpenRecordset(strSQL)
    Do Until rs1.EOF
        emailTo = rs1.Fields("EmailAddr")
        emailSubject = "Company Name - Update Request " & Now()
        emailText = vbNullString
        strSQL = "SELECT [Dist], [RO #], [UnitNum], [Service Provider Name], [VIN],[Area],[DistAddr], [DistCity] FROM qryAutomateEmails WHERE EmailAddr = '" & emailTo & "'"
        Set rs2 = db.OpenRecordset(strSQL)
        With rs2
            Do Until .EOF
                emailText = emailText & "<p><strong>Unit #:</strong> " & rs2.Fields("UnitNum") & ", " & "<strong>VIN:</strong> " & rs2.Fields("VIN") & ", " & _
                "<strong>RO #:</strong> " & rs2.Fields("RO #") & "<br><strong>Location:</strong> " & rs2.Fields("DistAddr") & " <em>(" & rs2.Fields("DistCity") & " - " & rs2.Fields("Dist") & ")</em>" & _
                "<br>" & "<strong>Detailed Update:</strong><br>" & "<strong>Estimated Completion Time:</strong><br>" & "</p>"
                xSPName = rs2.Fields("Service Provider Name") & "," & vbNewLine & vbNewLine
                xArea = rs2.Fields("Area")
                
                .MoveNext
            Loop
            .Close
        End With
        
        xIntro = "<p>Below are the units that are currently receiving repairs at your location.  Please reply with a ""Detailed Update"" and ""Estimated Completion Time"" for each unit.</p>" & vbNewLine & vbNewLine
        xSignature = "<br>Thank you," & "<br><br>" & "<strong>Company Name</strong><br>" & "<em><strong>Department</strong></em><br>" & "<a href='" & xArea & "CompanyName@Testing.com" & "'>" & xArea & "CompanyName@Testing.com</a>"
              
        Set outMail = outApp.CreateItem(olMailItem)
        outMail.SentOnBehalfOfName = xArea & "CompanyName@Testing.com"
        outMail.To = emailTo
        outMail.Subject = emailSubject
        outMail.BodyFormat = olFormatHTML
        outMail.HTMLBody = xSPName & xIntro & emailText & xSignature
        outMail.display
        Set outMail = Nothing
        rs1.MoveNext
    Loop
    
    rs1.Close
    Set rs1 = Nothing
    Set db = Nothing

    If outlookStarted Then
        outApp.Quit
    End If
    
    Set outMail = Nothing
    Set outApp = Nothing






Posted by: MadPiet May 29 2019, 01:17 PM

Okay, let me break it down for you. Joining the Rates and the Repairs table is a dead end. It's not going to get you where you want to go, because doing that will cause duplicates in the ServiceProvider column. So that's out.

The other option is to basically ask "Show me every Provider that has records in the Repairs table". (You can filter down that set if you like, say by date or something else... "WHERE NextUpdate >= #5/15/2019#", for example. Once you have your set of Repairs that you're interested in (and you include the Map# in that set (think virtual table or whatever), then you can get the set of Service Providers with "open" Repairs. (I'm using "open" as an example - it's whatever filter you apply to the Repairs table). It could return something like {1,3,4}. Then if you use EXISTS, you can return only the Providers that have one or more Repairs in the set you defined (or "open" repairs or whatever it is).

CODE
SELECT sp.*
FROM SpListAndRates sp
WHERE EXISTS (SELECT 1 FROM MajorRepairData mrd WHERE mrd.[Map #] = sp.[ID #])


the first two lines say "show me all the columns from SpListAndRates."
the 3rd line says "compare the values in MajorRepairData.Map# and SpListAndRates.ID and remove any values from ListAndRates that's not in both tables. So now you have something that looks like a mailing list. (CustomerID, EMail)

So if you loop through that set (some records), then you can retrieve their related Repair data records. Think of it like another query... SELECT <field list> FROM MajorrepairData AS mrd WHERE mrd.Map# matches the values in ListANdRates.ID#. And that query is the basis for a table or something that you put in the body of your e-mail to that customer. (Something like "these are all your interesting/outstanding Repairs")

The way you get that to work is that you open a recordset of ListAndRates customers (filtered as above). and then for each one of those, you open another recordset (based on the query I just described) of that customer's Repairs. Then you do something with that. (Create a Word table?) and drop that into your e-mail.

If you think of this in terms of a relationship... ListAndRates--(1,M)--Repairs (one Customer, one or more Repairs). Then you retrieve all the Customers that have repairs, and then you get the repairs for each individual customer one at a time. (because we're stuffing them into an e-mail, and we don't want anyone else's repair info in another customer's mail message.

Make sense?



Posted by: MadPiet May 29 2019, 02:51 PM

Okay, so you want to export each Customer's data to Excel first. Use TransferSpreadsheet to do that. Then if you use a variable to name the file, you can just pass that to the Outlook code to attach the file. Then you can use KILL filename to delete the file after it's attached.

Posted by: EMDS98 May 30 2019, 07:19 AM

I have everything working the way I want now. I created a make table query that brings in all of the cases that need to be emailed and changed my SQL statement to:

CODE
    strSQL = "SELECT DISTINCT " & _
             "[EmailAddr] " & _
             "FROM AutomateEmails"


I didn't realize that you couldn't refer to 2 tables in a vba SQL statement. Thanks for explaining that to me.