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
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