Avatar of Carolyn_
Carolyn_
 asked on

FMS Total Access Emailer Variable Attachment File Name

I send out files each day to predetermined distribution lists. Each day the file names change because they end with the current date. Today's file is "test 02-17-10.doc" and tomorrow it will be "test 02-18-10.doc".

How can I set this up to append today's date to the file name in Total Access Emailer?  I currently send the files with an Outlook macro and have it set up as shown below where the file name, today's date and file extenstion are concatenated. I need to accomplish the same thing in TAE.

olkFileDate = Format(olkTodaysDate, "mm-dd-yy")
"TOMATOES " & olkFileDate & ".rtf"

One of the attachment options in TAE is to reference the path and file name in a table field and I was wondering if this might be where I could set it up to work dynamically based on Today's Date.

Thanking you in advance.

Carolyn
Microsoft Access

Avatar of undefined
Last Comment
Luke Chung

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Luke Chung

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Carolyn_

ASKER
Thank you, Luke. After posting my question I figured out how to do this in a query as you suggest and it works! However, I'm on a trial version so I'm unable to test directly from the query. I make a table from the query and use that which is turning into a bit of a pain for evaluation purposes.

Here is another question, if it's okay. (I can add points to this, or make a separate question if you like, feel free to direct me on Experts-Exchange protocol.) In Access I've set up the following four tables:  (BTW, the report file attachments are outside of Access.)

tblCustomers
CustomerID, CustomerName

tblCustEmployees
CustEmployeeID, CustomerID, Name, Email

tblReports
ReportID, ReportName

tblCustomersReports
CustomersReportsID, CustomerID, ReportID, Send (yes/no), Effective (date field used with Send when cust. temporarily suspends receipt of report)

Then I have a query with tblCustEmployees, tblReports, tblCustomerReports and a field for the report like this:
Report: "c:\documents and settings\" & [ReportName] & " " & Format(Date(),"mm-dd-yy") & ".rtf"

If a person is getting 8 reports they are going to get 8 separate emails. I guess I need a way to put all of the reports into one field separated by semicolons, but I'm not sure how to do this in my query.  Also, the file path is quite long (the one above is only for testing), and if there are 8 or more reports I run the risk of going over the 255 limit for a text field, assuming that is also the limit in a Query field.

If I could get the above approach to work it would be good.  The IDEAL situation though is to send using Bulk email where all employees at the same company get the same email with the same attachments (it's okay if the addresses are in the CC).  (Note: The attachments are based on the Customer, not the individual.) So far though it looks like I'd have to set up separate email blasts for each company to accomplish this and I only want to hit the Send button once each night.

Any suggestions you have are greatly appreciated!

Carolyn
SOLUTION
Luke Chung

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Carolyn_

ASKER
Luke,

I don't have my final solution yet, but I can see that I'll be able to make it work.  I've restructured my Access tables, and also just discovered the option to "Send to multiple lists."  

This would be a lot simpler if the file names did not change, but unfortunately that's not the case.  Also, I looked at the sys tables, and while I won't go that route for this project it's good to know they are there and I might make use of them in the future.  (As a workaround if necessary.)

FYI, because of the way I set up the tables, I can now do this in a query to list multiple attachments in one field.  Report: "c:\documents and settings\" & [Report1] & " " & Format(Date(),"mm-dd-yy") & ".rtf;" & "c:\documents and settings\" & [Report2] & " " & Format(Date(),"mm-dd-yy") & ".rtf;" & "c:\documents and settings\" & [Report3] & " " & Format(Date(),"mm-dd-yy") & ".rtf"

Thanks so much for the pointers.  BTW, this product is really good and I'm already thinking of other ways we'll be able to use it in our business.

Carolyn
Luke Chung

Hi Carolyn,
Thanks for the kind words.  I'm really proud of Total Access Emailer and what it offers the Access community. As you've discovered, it leverages your existing investment in Access and offers you a very powerful way to communicate with your contacts.
Wait till you start using it to send attachments using filtered Access reports rather than files on disk. I think you'll be quite amazed.
Good luck with your development efforts. We'd always welcome a customer testimonial describing your experience with it.  Thanks!
Luke
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck