auto email to client before 30 days of expiry of loan

[joshynaresh]

Hello alex
yes i i'm using

DoCmd.SendObject acQuery, "InsuranceTable Query", "Excel97-Excel2003Workbook(*.xls)", "naresh.joshy@gmail.com.np;", "", "", "Insurance Renewal List", "Dear Sir, Plz renewal the attached list Thanking you ", False, ""

and it is working. i'm not using email ID form recordset.


but event there are no any record in query although email have been sending. but i don't want to send email if there is no record in query.
Thanking You

[AlexHedley]

Ah it seemed like you had a list of emails and you want to send a report to each person in this list.

Is your case that you just want to send a query to 1 person?

You could check if the query contains some data before you send the object.

[joshynaresh]

firstly i was tried to send a report to each person i this list but now i want to send list ot 1 person only.
but if there is no record in list email should not be sent.

[AlexHedley]

Check if the following is 0.

Dim lngCount as Long
lngCount = DCount("*", "NameOfTable/Query")

[joshynaresh]

Thank You it is working. i also want to update related record on remarks field as "Yes". what should i have to now?

[AlexHedley]

You could use an UPDATE Query.

http://www.599cd.com/tips/access/upd...?key=AlexForum

[joshynaresh]

Yes i can make update query. and i come to my first question. what should i have to do for sending mail to recordset emailid?

[AlexHedley]

Create a Query that lists the Email Addresses, then loop through that passing the email address into the address part of the send command.

[joshynaresh]

can you provide me code for it.

Create a Query that lists the Email Addresses, then loop through that passing the email address into the address part of the send command.

[ItsMe]

Do you have a query saved? What is its name? What are the names of the fields?

[joshynaresh]

Yes I have Query. Query Name is EmailList and filed Name is EmailID.
Thank You.

[AlexHedley]

You are going to need more info in your Query, required is the EmailAddress or how do you know who you are sending it to, helpful would be Name so you can say Hi <NAME>, ...

If you look into RecordSets and Looping through them, there are probably many on the Forum.

Then you just need to pass in the Email Address to the following code you already have:

Amended

DoCmd.SendObject acQuery, "InsuranceTable Query", "Excel97-Excel2003Workbook(*.xls)", <EMAIL_ADDRESS>, "", "", "Insurance Renewal List", "Dear " & <NAME> & ", Plz renewal the attached list Thanking you ", False, ""