Loop through query to send emails with attachments (1 Viewer)

[Drand]

Hi

I have 134 users from different countries who send in Excel files with data for a survey. Some of the files may not be complete and have missing data.

To date, I have managed to:

1. Automate the import of all 134 files into a table.
2. Create a query to identify those files that have missing data.
3. Export a new excel file for each country with missing data to a directory. File names are "missing data for country code 1" etc.
4. I then import the file names from the directory into a table "tblMissingDataForEmails". Relevant fields are "Fname" (File Name) and "Fpath" (The directory)
5. I have a function which extracts the country code number form the file name.
6. I have a table which contains email details (Recipient and Email Adress) "tblEmailDetails" with fields "CountryCode", "Recipient" and "Email".
7. I have a query which links the email address to the country code extracted from the file name. "qryMissingDataEmailsIncCountryCode"

I want my next step to loop through the "tblMissingDataForEmails", look up the email address from "qryMissingDataEmailsIncCountryCode", fields are "CountryCode", "Recipient" and "Email" and send an email to each country with the relevant attachment from the directory, using Outlook.

I know how to output the files to email individually, but I want to be able to automate the process by looping through the affected counties and have no idea how to do this.

I hope this is enough information but please let me know if anything further is needed.

Many thanks

 

[Ranman256]

save the report, then email via email.
put a list box on the form, this will cycle thru the names in the list,
create their report,
then send it from the email in the list.

'------------
Public Sub ScanAndEmail()
'------------
Dim vTo, vSubj, vBody, vRpt
Dim vFilePath
dim i as integer


vRpt = "rReport1"
vBody = "body of email"
vSubj = vRpt
vFilePath = "\\server\pdf reports\"        'path of the snapshot or XL or PdF


     'scan the list box
For i = 0 To lstEAddrs.ListCount - 1
   vTo = lstEAddrs.Column(2)
     
   'DoCmd.SendObject acSendReport, vRpt, acFormatPDF, vTO, , , vSubj, vBody
Next
End Sub

then email it using outlook:

Public Function Send1Email(ByVal pvTo, ByVal pvSubj, ByVal pvBody, Optional ByVal pvFile) As Boolean
Dim oApp As Outlook.Application
Dim oMail As Outlook.MailItem

On Error GoTo ErrMail

'NOTE : YOU MUST HAVE THE OUTLOOK REFERENCE CHECKED IN VBE; Alt-F11, menu,tools, references, Microsoft Outlook XX Object library

Set oApp = GetApplication("Outlook.Application")  'it may be open already so use this
'Set oApp = CreateObject("Outlook.Application")  'not this

Set oMail = oApp.CreateItem(olMailItem)
With oMail
    .To = pvTo
    .Subject = pvSubj
    If Not IsMissing(pvFile) Then .Attachments.Add pvFile, olByValue, 1
   
    .HTMLBody = pvBody
    'If Not IsNull(pvBody) Then .Body = pvBody
   
    .Display True   'show user but dont send yet
    '.Send          'send now
End With

Send1Email = True
endit:
Set oMail = Nothing
Set oApp = Nothing
Exit Function

ErrMail:
MsgBox Err.Description, vbCritical, Err
Resume endit
DoCmd.OutputTo acOutputReport, "rMyReport", acFormatPDF, vFile

End Function

Function GetApplication(className As String) As Object
' function to encapsulate the instantiation of an application object
Dim theApp As Object
On Error Resume Next
Set theApp = GetObject(, className)
If Err.Number <> 0 Then
    MsgBox "Unable to Get" & className & ", attempting to CreateObject"
    Set theApp = CreateObject(className)
End If

If theApp Is Nothing Then
    Err.Raise Err.Number, Err.Source, "Unable to Get or Create the " & className & "!"
    Set GetApplication = Nothing
End If

'MsgBox "Successfully got a handle on Outlook Application, returning to caller"
Set GetApplication = theApp
End Function

 

[Drand]

Hi and thank you for your response.

I am just looking through the code and I should say that I am an absolute beginner when it comes to VBA. My questions may therefore seem to be fairly simple!

How do I change "'DoCmd.SendObject acSendReport, vRpt, acFormatPDF, vTO, , , vSubj, vBody" to send an Excel file rather than a pdf. The user needs to populate the missing data which will then be updated in the main consolidation table.

Where in your code does it link the email to the correct file? What I mean here is that each country has a different spreadsheet to update. They are not all the same. I just don't understand where that happens.

Thanks for your help and patience.

 

[Gasman]

Look at the Similar threads at the bottom of this thread.
Also search the site as this question has been asked numerous time.

That should give you code that you can adapt to your system.

I would do it with Outlook automation and attaching the file via that.
Basically I would create a recordset that idetifies those that supplied missing data.
Then walk that recordset, creating and populating the email with that data. Hopefully that will also tell you which file to attach to send back.

I'll attach a file for the code I used just to send emails with that recordset loop. It does NOT attach any files, as I had no need to do so, but ti will give you the bulk of what you need. I am attaching the whole sub, not removing anything, so you need to understand it and take what you need.

HTH

 

[GinaWhipp]

This might help...
https://www.access-diva.com/vba20.html