UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Automate Emails From Query Results, Access 2016    
 
   
cheekybuddha
post May 28 2019, 11:35 AM
Post#21


UtterAccess VIP
Posts: 11,464
Joined: 6-December 03
From: Telegraph Hill


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)

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post May 28 2019, 11:59 AM
Post#22


UtterAccess VIP
Posts: 11,464
Joined: 6-December 03
From: Telegraph Hill


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.

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post May 28 2019, 02:02 PM
Post#23


UtterAccess VIP
Posts: 11,464
Joined: 6-December 03
From: Telegraph Hill


Also, what is the point of this criterion?
CODE
...
  AND c2.DMAREA Like '****'
...

--------------------


Regards,

David Marten
Go to the top of the page
 
MadPiet
post May 28 2019, 02:36 PM
Post#24



Posts: 3,221
Joined: 27-February 09



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.

Go to the top of the page
 
EMDS98
post May 29 2019, 07:44 AM
Post#25



Posts: 14
Joined: 25-April 19



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;

This post has been edited by EMDS98: May 29 2019, 08:34 AM
Go to the top of the page
 
EMDS98
post May 29 2019, 08:04 AM
Post#26



Posts: 14
Joined: 25-April 19



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.
Go to the top of the page
 
cheekybuddha
post May 29 2019, 08:35 AM
Post#27


UtterAccess VIP
Posts: 11,464
Joined: 6-December 03
From: Telegraph Hill


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

--------------------


Regards,

David Marten
Go to the top of the page
 
MadPiet
post May 29 2019, 11:05 AM
Post#28



Posts: 3,221
Joined: 27-February 09



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.
Go to the top of the page
 
EMDS98
post May 29 2019, 11:13 AM
Post#29



Posts: 14
Joined: 25-April 19



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.

This post has been edited by EMDS98: May 29 2019, 11:16 AM
Go to the top of the page
 
cheekybuddha
post May 29 2019, 11:22 AM
Post#30


UtterAccess VIP
Posts: 11,464
Joined: 6-December 03
From: Telegraph Hill


The code I posted is your SQL query from Post#20 which you said worked!

--------------------


Regards,

David Marten
Go to the top of the page
 
MadPiet
post May 29 2019, 11:25 AM
Post#31



Posts: 3,221
Joined: 27-February 09



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?
This post has been edited by MadPiet: May 29 2019, 11:50 AM
Go to the top of the page
 
EMDS98
post May 29 2019, 12:28 PM
Post#32



Posts: 14
Joined: 25-April 19



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 I’m 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





Go to the top of the page
 
MadPiet
post May 29 2019, 01:17 PM
Post#33



Posts: 3,221
Joined: 27-February 09



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?


Go to the top of the page
 
MadPiet
post May 29 2019, 02:51 PM
Post#34



Posts: 3,221
Joined: 27-February 09



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.
Go to the top of the page
 
EMDS98
post May 30 2019, 07:19 AM
Post#35



Posts: 14
Joined: 25-April 19



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.
This post has been edited by EMDS98: May 30 2019, 07:19 AM
Go to the top of the page
 
2 Pages V < 1 2


Custom Search


RSSSearch   Top   Lo-Fi    25th August 2019 - 06:09 AM