Run Query B with varible input from query A, export to excel > repeat for all query A (1 Viewer)

FrozenMana · Aug 10, 2015

Run Query B with varible input from query A, export to excel > repeat for all query A

I am looking for the vba to have a query cycle through using records from another query as the parameter run and export for each of the records on the second query.

Table 1 has information with all clients
query 1 has x, y, z being pulled > client used as parameter
query 2 has the list of clients

Currently I have the coding to run the query and export however I am not sure how to get it to repeat for each client.

Code:

Private Sub CmdInternalReports_Click()
Dim xlApp As Excel.Application
    Dim rstDetails As DAO.Recordset
    Dim strTab As String
    Dim strDir As String
    
    Set xlApp = Nothing
    Set rstDetails = Nothing
    
    'Open and display the Network EMC Template
    Set xlApp = New Excel.Application
    'xlApp.Visible = True
    xlApp.Workbooks.Open (SavePath & "Templates\Network EMC Report XCOL Template.xlsx")
    
    'Copy the Order has been scheduled delivered mmddyyyy
    strTab = "EMC"
    Set rstDetails = CurrentDb.OpenRecordset("qryXCLONetworkEMCSchedDelv")
    xlApp.Worksheets(strTab).Select
    xlApp.Worksheets(strTab).Cells(3, 1).CopyFromRecordset rstDetails
    xlApp.Worksheets(strTab).Cells.Select
    xlApp.Worksheets(strTab).Cells.EntireColumn.AutoFit
    xlApp.Worksheets(strTab).Cells(1, 1).Select

    
'    xlApp.ActiveWorkbook.SaveAs strDir & "\Order has been scheduled delivered mmddyyyy.xls"
'    SetAttr strDir & "\Order has been scheduled delivered mmddyyyy.xls", vbReadOnly

    xlApp.Worksheets(1).Select
    
    xlApp.ActiveWorkbook.SaveAs "P:\DSC - Inventory Management\EMC and Open Orders\Reports to Work\Order has been scheduled delivered " & Format(Date, "mmddyyyy")
    SetAttr "P:\DSC - Inventory Management\EMC and Open Orders\Reports to Work\Order has been scheduled delivered " & Format(Date, "mmddyyyy") & ".xlsx", vbReadOnly
    'Close Excel
    xlApp.ActiveWorkbook.Close
    xlApp.Quit
    
    MsgBox "Done!"
End Sub

The coding above runs two different queries and saves them to two seperate tabs on the pre-made template for this report.

Below is the SQL coding I currently have for the first query:

Code:

SELECT 
tblImportNetworkEMC.DeliveryHub, 
tblImportNetworkEMC.HubType, 
tblImportNetworkEMC.Origin, 
tblImportNetworkEMC.Client, 
tblImportNetworkEMC.CurrentLoc, 
tblImportNetworkEMC.Consignee, 
tblImportNetworkEMC.Shipment, 
tblImportNetworkEMC.Order, 
tblImportNetworkEMC.WaitingFor, 
tblImportNetworkEMC.TotalDaysAged, 
tblImportNetworkEMC.IntLastDate, 
tblImportNetworkEMC.IntEnterBy, 
tblImportNetworkEMC.IntLastType, 
tblImportNetworkEMC.InternalAge, 
tblImportNetworkEMC.IntCommentText, 
qryClientNetworkEMC_FilterCHAD.Shipment

FROM 
  ((tblImportNetworkEMC 
 LEFT JOIN 
   qryClientNetworkEMC_Filter ON tblImportNetworkEMC.Shipment = qryClientNetworkEMC_Filter.Shipment) 
 LEFT JOIN 
   qryClientNetworkEMC_FilterCHAD ON tblImportNetworkEMC.Shipment = qryClientNetworkEMC_FilterCHAD.Shipment) 
 INNER JOIN 
   tblClientReport ON tblImportNetworkEMC.Client = tblClientReport.Client

WHERE (((tblImportNetworkEMC.WaitingFor)="Client") 
  AND ((tblImportNetworkEMC.IntLastType)<>"XCLO") 
  AND ((qryClientNetworkEMC_FilterCHAD.Shipment) Is Null) 
  AND ((tblImportNetworkEMC.Status)="Active Delivery" 
        Or (tblImportNetworkEMC.Status)="Active Closed Delivery") 
  AND ((qryClientNetworkEMC_Filter.Shipment) Is Null));

I would like to figure out how cycle through the records in a separate query

PHP:

qryClient-NetworkEMCQuery.Client

as the parameter for

PHP:

tblImportNetworkEMC.Client

There can be upto 70 different clients or as little as 2 depending on the day. So the Query I have takes a list of the clients that receive the report and runs it against the imported table to only give the ones with data day.

PHP:

tblClientReport.Distro

Is where I have the report via client name linked the the distro list that should be pulled.

Any advise for how to look this up would be much appreciated.

Ranman256 · Aug 10, 2015

Re: Run Query B with varible input from query A, export to excel > repeat for all que

Are you using a form for the params?
Q1 = select * from table where [clientID] = forms!frmClient!txtClientID

then run Q2 off Q1.
why do you have all this code? No code is needed.

GinaWhipp · Aug 10, 2015

Re: Run Query B with varible input from query A, export to excel > repeat for all que

You are going to need a Loop, something like (UNTESTED)...

Code:

     With rstDetails
        .MoveFirst
            Do While Not .EOF
                    xlApp.Worksheets(1).Select
    
    xlApp.ActiveWorkbook.SaveAs "P:\DSC - Inventory Management\EMC and Open Orders\Reports to Work\Order has been scheduled delivered " & Format(Date, "mmddyyyy")
    SetAttr "P:\DSC - Inventory Management\EMC and Open Orders\Reports to Work\Order has been scheduled delivered " & Format(Date, "mmddyyyy") & ".xlsx", vbReadOnly
    'Close Excel
    xlApp.ActiveWorkbook.Close
    xlApp.Quit
        
        .MoveNext
        Loop

FrozenMana · Aug 10, 2015

Re: Run Query B with varible input from query A, export to excel > repeat for all que

No this is not off of a form.
The user pushes a button and it runs the query and exports it into an excel template that is already set up and saves the excel file in the specified folder.

However at the moment they have to go through and filter the file for each client and save it for that client and email it out.
So I am trying to get it so that the query runs with the client pulled from a seperate table and saves under that client for each client in the table. (as some of the clients will not always have information and there is no need to send blank report).

GinaWhipp · Aug 10, 2015

Re: Run Query B with varible input from query A, export to excel > repeat for all que

Oh, so maybe you want code to drop it out and automatically eMail it?

FrozenMana · Aug 10, 2015

Re: Run Query B with varible input from query A, export to excel > repeat for all que

Yes, that is the goal.

GinaWhipp · Aug 11, 2015

Re: Run Query B with varible input from query A, export to excel > repeat for all que

Hmm, here we go (UNTESTED) AND you need to merge with your code, as well as, change areas where specified...

Code:

Dim strAttachments As String
 
    With rstDetails
        .MoveFirst
            Do While Not .EOF
            
                xlApp.Worksheets(1).Select
        
                xlApp.ActiveWorkbook.SaveAs "P:\DSC - Inventory Management\EMC and Open Orders\Reports to Work\Order has been scheduled delivered " & Format(Date, "mmddyyyy")
                SetAttr "P:\DSC - Inventory Management\EMC and Open Orders\Reports to Work\Order has been scheduled delivered " & Format(Date, "mmddyyyy") & ".xlsx", vbReadOnly
                'Close Excel
                xlApp.ActiveWorkbook.Close
                xlApp.Quit
        strAttachments = "P:\DSC - Inventory Management\EMC and Open Orders\Reports to Work\Order has been scheduled delivered " & Format(Date, "mmddyyyy")
        
        Set objNewMail = olApp.CreateItem(0)
        With objNewMail
            .To = rstDetails.Fields("eMailAddressFieldInYourRecordsource")
            .Subject = "TypeYourSubjectLineHere"
            .Body = "See attachment..."
            
            If strAttachments <> "" Then
                .Attachments.Add strAttachments
            End If
            'For testing
            '.Display
            .Send
        End With
        .MoveNext
        Loop
        
        If Dir("CleanUpDirectory") <> "" Then
            Kill "CleanUpDirectory"
        End If
        End With
        
        rstDetails.Close
        Set rstDetails = Nothing

FrozenMana · Aug 11, 2015

Re: Run Query B with varible input from query A, export to excel > repeat for all que

I do not think that I am explaining this correctly. My apologies as I am a bit rusty with coding.

The code that I posted currently runs two querys and saves them on two seperate tabs of the template that is created for this report.

I am having difficulity with figuring out how to set the queries to run with a parameter for a client filed, where the client for the parameter is pulled from a different table (in a loop until the query has run for each of the specified clients in the table).

vba set parameter value a as first record from table a,
open template
run query b with parameter value A export to tab a,
run query c with parameter value A export to tab b,
save with name of parameter value close,

repeat for second record in table

GinaWhipp · Aug 12, 2015

Re: Run Query B with varible input from query A, export to excel > repeat for all que

You are going to need a Query that first pulls the Clients but only those that get a report then you can run the Loop that only pulls those who get Reports. So, step one is make a query with the Clients but only those that get the report and make sure to include their eMails in the Query.

FrozenMana · Aug 12, 2015

Re: Run Query B with varible input from query A, export to excel > repeat for all que

My apologies, I have that already. qryClient-NetworkEMCQuery (client, distro)

I have not written a loop before and I am still rusty as it has been awhile since I have worked with VBA other than simple stuff.

Below the is coding that I have after looking at what you sent:

Code:

Private Sub CmdTestLoop_Click()
Dim xlApp As Excel.Application
    Dim rstDetails As DAO.Recordset
    Dim strTab As String
    Dim strDir As String
    Dim rs As DAO.Recordset
    Set rstDetails = CurrentDb.OpenRecordset("SELECT * FROM [qryClient-NetworkEMCQuery]")
    Dim strAttachments As String
    
    With rstDetails
        .MoveFirst
            Do While Not .EOF
            
 Dim sn As Recordset
 strCount = 0
 If [qryClient-NetworkEMCQuery].Client >= 1 Then
If Not IsNull([qryClient-NetworkEMCQuery].Report) Or [qryClient-NetworkEMCQuery].Report <> "" Then
 Set Db = CurrentDb
 Set sn = Db.OpenRecordset("SELECT * FROM Client ORDER BY ID DESC", dbOpenDynaset, dbSeeChanges)

sn!Client = [qryClient-NetworkEMCQuery].Client
sn.Update
sn.Close
End If


    'Open and display the Network EMC Template
    Set xlApp = New Excel.Application
    'xlApp.Visible = True
    xlApp.Workbooks.Open (SavePath & "Templates\Network EMC Report Client Template.xls")
    
    'Copy the Active Returns
    strTab = "EMC Active Returns"
    Set rstDetails = CurrentDb.OpenRecordset("qryClientNetworkEMCActiveReturn-test")
    xlApp.Worksheets(strTab).Select
    xlApp.Worksheets(strTab).Cells(3, 1).CopyFromRecordset rstDetails
    xlApp.Worksheets(strTab).Cells.Select
    xlApp.Worksheets(strTab).Cells.EntireColumn.AutoFit
    xlApp.Worksheets(strTab).Cells(1, 1).Select
  
    'Copy the Active Delivery
    strTab = "EMC Active Deliveries"
    Set rstDetails = CurrentDb.OpenRecordset("qryClientNetworkEMCActiveDelivery")
    xlApp.Worksheets(strTab).Select
    xlApp.Worksheets(strTab).Cells(3, 1).CopyFromRecordset rstDetails
    xlApp.Worksheets(strTab).Cells.Select
    xlApp.Worksheets(strTab).Cells.EntireColumn.AutoFit
    xlApp.Worksheets(strTab).Cells(1, 1).Select
    
    'checks to see if the directory exists named mmddyyyy to mmddyyyy, if it doesn't it makes it
'    strDir = SavePath & "Reports\" & Format(WeekStart(Now()), "mmddyyyy") & " to " & Format(WeekStart(Now()) + 4, "mmddyyyy")
'    If Len(Dir(strDir, vbDirectory)) = 0 Then
'        MkDir strDir
'    End If
'    strDir = SavePath & "Reports\" & Format(WeekStart(Now()), "mmddyyyy") & " to " & Format(WeekStart(Now()) + 4, "mmddyyyy") & "\" & Format(Now(), "mmddyyyy")
'    If Len(Dir(strDir, vbDirectory)) = 0 Then
'        MkDir strDir
'    End If
'
'    xlApp.ActiveWorkbook.SaveAs strDir & "\Network EMC Report.xls"
'    SetAttr strDir & "\Network EMC Report.xls", vbReadOnly

    xlApp.Worksheets(1).Select
    
    xlApp.ActiveWorkbook.SaveAs "P:\DSC - Inventory Management\EMC and Open Orders\Client EMC Report Archive\Client Network EMC Report " & Format(Date, "mmddyyyy")
    SetAttr "P:\DSC - Inventory Management\EMC and Open Orders\Client EMC Report Archive\Client Network EMC Report " & Format(Date, "mmddyyyy") & ".xls", vbReadOnly
    'Close Excel
    xlApp.ActiveWorkbook.Close
    xlApp.Quit
    
    Set objNewMail = oLApp.CreateItem(0)
        With objNewMail
            .To = rstDetails.Fields("qryClient-NetworkEMCQuery.Distro")
            .Subject = [qryClient-NetworkEMCQuery.Report] & " EMC Report"
            .Body = "Please see attached report:"
            
            If strAttachments <> "" Then
                .Attachments.Add strAttachments
            End If
            'For testing
            '.Display
            .Send
        End With
        
        .MoveNext
        Loop
    
    End If
    Set xlApp = Nothing
    Set rstDetails = Nothing
    MsgBox "Done!"
    
End Sub

I keep receing a Compile error: Loop without Do.
If I add a do above

Code:

 'Open and display the Network EMC Template
    Set xlApp = New Excel.Application
    'xlApp.Visible = True
    xlApp.Workbooks.Open (SavePath & "Templates\Network EMC Report Client Template.xls")

Than I get Compile error: Do without Loop.

Thank you for being patient with me and for your help.

FrozenMana · Aug 12, 2015

Re: Run Query B with varible input from query A, export to excel > repeat for all que

I am trying something different mocking another database that we currently have, I'll post the results if this works.

jsdba · Aug 12, 2015

Re: Run Query B with varible input from query A, export to excel > repeat for all que

FrozenMana said:
I am trying something different mocking another database that we currently have, I'll post the results if this works.

Code:

[B]    
      End If

.MoveNext
Loop[/B]
Set xlApp = Nothing
Set rstDetails = Nothing
MsgBox "Done!"
    
End Sub

Try that. I always get this problem myself.

FrozenMana · Aug 13, 2015

Re: Run Query B with varible input from query A, export to excel > repeat for all que

Thanks, I will try that once I get this runing again. I changed the update for the table and this is my current code:

Code:

Private Sub CmdTestLoop_Click()
Dim xlApp As Excel.Application
    Dim rstDetails As DAO.Recordset
    Dim strTab As String
    Dim strDir As String
    Dim rs As DAO.Recordset
    Set rstDetails = CurrentDb.OpenRecordset("SELECT * FROM qryClient_NetworkEMCQuery")
    Dim strAttachments As String
    
    With rstDetails
        .MoveFirst
            Do While Not .EOF
            
       DoCmd.SetWarnings False
       DoCmd.RunSQL ("Delete * from tblxRefReportName")
       Dim objItem As Object
For Each objItem In Me.lstReport.Selected
  objItem = True
Next objItem
       DoCmd.RunSQL ("Insert into tblxRefReportName Values ('" & Me.lstReport.ItemData(intCount) & "')")
       DoCmd.RunSQL ("Insert into tblxRefEmail Values ('" & Me.lstReport.ItemData(intCount) & "')")


    'Open and display the Network EMC Template
    Set xlApp = New Excel.Application
    'xlApp.Visible = True
    xlApp.Workbooks.Open (SavePath & "Templates\Network EMC Report Client Template.xls")
    
    'Copy the Active Returns
    strTab = "EMC Active Returns"
    Set rstDetails = CurrentDb.OpenRecordset("qryClientNetworkEMCActiveReturn-test")
    xlApp.Worksheets(strTab).Select
    xlApp.Worksheets(strTab).Cells(3, 1).CopyFromRecordset rstDetails
    xlApp.Worksheets(strTab).Cells.Select
    xlApp.Worksheets(strTab).Cells.EntireColumn.AutoFit
    xlApp.Worksheets(strTab).Cells(1, 1).Select
  
    'Copy the Active Delivery
    strTab = "EMC Active Deliveries"
    Set rstDetails = CurrentDb.OpenRecordset("qryClientNetworkEMCActiveDelivery")
    xlApp.Worksheets(strTab).Select
    xlApp.Worksheets(strTab).Cells(3, 1).CopyFromRecordset rstDetails
    xlApp.Worksheets(strTab).Cells.Select
    xlApp.Worksheets(strTab).Cells.EntireColumn.AutoFit
    xlApp.Worksheets(strTab).Cells(1, 1).Select
    

'    xlApp.ActiveWorkbook.SaveAs strDir & "\Network EMC Report.xls"
'    SetAttr strDir & "\Network EMC Report.xls", vbReadOnly

    xlApp.Worksheets(1).Select
    
    xlApp.ActiveWorkbook.SaveAs "P:\DSC - Inventory Management\EMC and Open Orders\Client EMC Report Archive\" & strFileName & " EMC Report " & Format(Date, "mmddyyyy")
    SetAttr "P:\DSC - Inventory Management\EMC and Open Orders\Client EMC Report Archive\" & strFileName & " EMC Report " & Format(Date, "mmddyyyy") & ".xls", vbReadOnly
    'Close Excel
    xlApp.ActiveWorkbook.Close
    xlApp.Quit
    
    Set objNewMail = oLApp.CreateItem(0)
        With objNewMail
            .To = rstDetails.Fields("qryClient_NetworkEMCQuery.Distro")
            .Subject = qryClient_NetworkEMCQuery & " EMC Report"
            .Body = "Please see attached report:"
            
            If strAttachments <> "" Then
                .Attachments.Add strAttachments
            End If
            'For testing
            '.Display
            .Send
        End With
        
        .MoveNext
        Loop
    
    Set xlApp = Nothing
    Set rstDetails = Nothing
    MsgBox "Done!"
    End With
    
End Sub

This was working until the email portion yesterday however it must not have saved correctly as I am getting a:

Compile error: Argument not optional

Code:

[B][COLOR="Yellow"]Private Sub CmdTestLoop_Click()[/COLOR][/B]
Dim xlApp As Excel.Application
    Dim rstDetails As DAO.Recordset
    Dim strTab As String
    Dim strDir As String
    Dim rs As DAO.Recordset
    Set rstDetails = CurrentDb.OpenRecordset("SELECT * FROM qryClient_NetworkEMCQuery")
    Dim strAttachments As String
    
    With rstDetails
        .MoveFirst
            Do While Not .EOF
            
       DoCmd.SetWarnings False
       DoCmd.RunSQL ("Delete * from tblxRefReportName")
       Dim objItem As Object
For Each objItem In Me.lstReport.[B][COLOR="Blue"]Selected[/COLOR][/B]
  objItem = True
Next objItem
       DoCmd.RunSQL ("Insert into tblxRefReportName Values ('" & Me.lstReport.ItemData(intCount) & "')")
       DoCmd.RunSQL ("Insert into tblxRefEmail Values ('" & Me.lstReport.ItemData(intCount) & "')")

arrow goes to the
Private Sub CmdTestLoopClick()
and Selected is highlighted in blue on the
For Each objItem In Me.lstReport.Selected

GinaWhipp · Aug 13, 2015

Re: Run Query B with varible input from query A, export to excel > repeat for all que

Umm, did I miss something? When did the List Box come into play? Are you trying to cycle that?

FrozenMana · Aug 13, 2015

Re: Run Query B with varible input from query A, export to excel > repeat for all que

Yes I am trying to cycle through the list box.

Currently I have updated my coding to (which appears to be working properly!):

Code:

Private Sub CmdTestLoop_Click()

    'deletes everything from the email table
    DoCmd.RunSQL ("Delete * from tblxRefEmail")
    
    DoCmd.SetWarnings True
    
            intCount = 0
            Do While intCount < Me.lstReport.ListCount
                Select Case Me.lstReport.Selected(intCount)
                    Case -1
                        DoCmd.SetWarnings False
                        DoCmd.RunSQL ("Delete * from tblxRefReportName")
                        DoCmd.RunSQL ("Insert into tblxRefReportName Values ('" & Me.lstReport.ItemData(intCount) & "')")
                        DoCmd.RunSQL ("Insert into tblxRefEmail Values ('" & Me.lstReport.ItemData(intCount) & "')")
                        
                          Dim rstDetails As DAO.Recordset
    Dim intCurrentRow As Integer
    Dim intCurrentColumn As Integer
    Dim xlApp As Excel.Application
    Dim strFileName As String
    
    'Open the specific Clients Template File
    Set xlApp = New Excel.Application
    xlApp.Visible = True
    xlApp.Workbooks.Open (SavePath & "Templates\Network EMC Report Client Template.xls")
    
 'Copy the Active Returns
    strTab = "EMC Active Returns"
    Set rstDetails = CurrentDb.OpenRecordset("qryClientNetworkEMCActiveReturn-test")
    xlApp.Worksheets(strTab).Select
    xlApp.Worksheets(strTab).Cells(3, 1).CopyFromRecordset rstDetails
    xlApp.Worksheets(strTab).Cells.Select
    xlApp.Worksheets(strTab).Cells.EntireColumn.AutoFit
    xlApp.Worksheets(strTab).Cells(1, 1).Select
  
    'Copy the Active Delivery
    strTab = "EMC Active Deliveries"
    Set rstDetails = CurrentDb.OpenRecordset("qryClientNetworkEMCActiveDelivery-test")
    xlApp.Worksheets(strTab).Select
    xlApp.Worksheets(strTab).Cells(3, 1).CopyFromRecordset rstDetails
    xlApp.Worksheets(strTab).Cells.Select
    xlApp.Worksheets(strTab).Cells.EntireColumn.AutoFit
    xlApp.Worksheets(strTab).Cells(1, 1).Select
    
    
    'save the EMC Report
xlApp.Worksheets(1).Select
    
    xlApp.ActiveWorkbook.SaveAs "P:\DSC - Inventory Management\EMC and Open Orders\Client EMC Report Archive\" & DLookup("Report", "tblxRefReportName") & " Network EMC Report " & Format(Date, "mmddyyyy")
    SetAttr "P:\DSC - Inventory Management\EMC and Open Orders\Client EMC Report Archive\" & DLookup("Report", "tblxRefReportName") & " Network EMC Report " & Format(Date, "mmddyyyy") & ".xls", vbReadOnly
    'Close Excel
    xlApp.ActiveWorkbook.Close
    xlApp.Quit

    
    xlApp.DisplayAlerts = True
    
    Set rstDetails = Nothing
    Set xlApp = Nothing
                        
                        DoCmd.SetWarnings True
                    Case 0
                End Select
                intCount = intCount + 1
            Loop
  
    If Me.chkSendEmail.Value = True Then
        Call SendEmail
    End If
    MsgBox "Done"
End Sub

Below is the email coding:

Code:

Public Sub SendEmail()
    Dim oLApp As Outlook.Application
    Dim oLAppMsg As Outlook.MailItem
    Dim oLAppRecip As Outlook.Recipient
    Dim oLAppAttach As Outlook.Attachment
    Dim rstClients As DAO.Recordset
    Dim rstSpecClient As DAO.Recordset
    
    Set rstClients = CurrentDb.OpenRecordset("qryEmailList")
    rstClients.MoveFirst
    Do While rstClients.EOF = False
        DoCmd.SetWarnings False
        DoCmd.RunSQL ("Delete * from tblxRefSpecEmail")
        DoCmd.RunSQL ("Insert into tblxRefSpecEmail Values ('" & rstClients(0).Value & "')")
        DoCmd.SetWarnings True
        Set rstSpecClient = CurrentDb.OpenRecordset("qryEmailAllSpec")
        rstSpecClient.MoveFirst
        
        'Email Client EMC
        'Create the email for the Client EMC
        Set oLApp = CreateObject("Outlook.Application")
        
        'Create the message.
        Set oLAppMsg = oLApp.CreateItem(olMailItem)
        
        'Add the To recipient(s) to the message.
        Set oLAppRecip = oLAppMsg.Recipients.Add(rstSpecClient(1).Value & " EMC Report")
        oLAppRecip.Type = olTo
        
        'Set the Subject, Body, and Importance of the message.
        oLAppMsg.Subject = rstSpecClient(1).Value & " EMC Report"
        oLAppMsg.Body = "Please see the attached report oLAppMsg.Body"
        oLAppMsg.Importance = olImportanceNormal
        
        'Resolve each Recipient's name.
        For Each oLAppRecip In oLAppMsg.Recipients
            oLAppRecip.Resolve
        Next
        
        Do While rstSpecClient.EOF = False
            Set oLAppAttach = oLAppMsg.Attachments.Add("P:\DSC - Inventory Management\EMC and Open Orders\Client EMC Report Archive\" & rstSpecClient(0).Value & " EMC Report " & Format(Date, "mmddyyyy") & ".xls")
            rstSpecClient.MoveNext
        Loop
        'display the message
         oLAppMsg.Display
        
        'display the message
         oLAppMsg.Display
         
         rstClients.MoveNext
    Loop
    
    Set oLApp = Nothing
    Set oLAppMsg = Nothing
    Set oLAppRecip = Nothing
    Set oLAppAttach = Nothing
     
End Sub

GinaWhipp · Aug 13, 2015

Re: Run Query B with varible input from query A, export to excel > repeat for all que

I am soooooooo glad you got it working because you were starting to confuse me!

Run Query B with varible input from query A, export to excel > repeat for all query A (1 Viewer)

FrozenMana

Registered User.

Ranman256

Well-known member

GinaWhipp

AWF VIP

FrozenMana

Registered User.

GinaWhipp

AWF VIP

FrozenMana

Registered User.

GinaWhipp

AWF VIP

FrozenMana

Registered User.

GinaWhipp

AWF VIP

FrozenMana

Registered User.

FrozenMana

Registered User.

jsdba

Registered User.

FrozenMana

Registered User.

GinaWhipp

AWF VIP

FrozenMana

Registered User.

GinaWhipp

AWF VIP

Similar threads

Users who are viewing this thread