Question How would you go about this?

[Luchi]

Hi,

I faced with a rather basic question on how to build what I want to buld in the most effective way... thought of several methods, but I'm always supprised of all the littel "tricks" (things I didn't know could be done that simple), I find on here and thoughts I would ask this time prior to spending a lot of time into something that I end up completly changing again.

What I have:
I have a list of orders, with all the usual data and status fields (it's a SharePoint list at it's origin).

The reason why:
Once a month we need to contact the responibles for their orders (there is new responsibles that get added all the time) to provide us updatet information. A responsible is identified by his email. An additional "issue" is that a responsible is only allowed to see the oders that concern him.

What I would like to do:

1. I would like to send a mail, with an Excel attachment of all the orders that concern that responsible (emai). Basically all records that have the same email = 1 Table

My thoughts:
Yes... I could create a query for each mail (use simple macro to send mails, running each query every time). My challenge though is

• that sometimes an "email" will not have any records... I don't want to run that.
• Also, as there are new "emails" all the time, each time there is a new one I Need to create a query and add that to the macro.

I think thats it... What's your thoughts and how would you best go about this?

 

[sonic8]

My challenge though is

• that sometimes an "email" will not have any records... I don't want to run that.
• Also, as there are new "emails" all the time, each time there is a new one I Need to create a query and add that to the macro.

Both can be solved fairly easily.
You create one query grouped by the email of the responsible person and the count of of orders. Only query emails with order-count >0.

Now open that query as a Recordset in VBA. Loop through the records and query/export the orders assigned to each responsible person (email). Then send each exported file to the corresponding email address.

 

[Luchi]

Thank you sonic8

in the mean time I created a query, that pulls all the data that I do need and also need to query. I now added a query that pulls me only a list of names (mails) from that query.

That should work to no? If I take that query now as a Recordset in VBA.

Would you have a link or an example of a VBA that does the Loop through and query/export and generate the mail? I have tried to look a bit but not sure I found something suitable. (still learning in regards to VBA)?

 

[sonic8]

Would you have a link or an example of a VBA that does the Loop through and query/export and generate the mail?

I actually have got an sample on my website:
Send serial email with Microsoft Access, VBA and Outlook
That does explain quite a bit of it.
You did not mention how you are sending the emails. My example uses Outlook, but the main logic will be the same if you just use DoCmd.SendObject to send the email.
That would cover the export part already, which is not included in the Outlook sample.

 

[Luchi]

I actually have got an sample on my website:
That does explain quite a bit of it.
You did not mention how you are sending the emails. My example uses Outlook, but the main logic will be the same if you just use .
That would cover the export part already, which is not included in the Outlook sample.

Danke war auf deiner Webseite, super!
Unfortunatly I guss I'm doing something wrong here... I Keep getting an error message when I try to run it (test run): Laufzeitfehler 3075 (fehlender Operator) in Abfrageausdruck 'Email Ansprechpartner'

The full code ist here: (step one )

Option Compare Database
Option Explicit
Public Sub SendSerialEmail()
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT Email Ansprechpartner, Op# Beschaffer, BIT-Nummer, Status Minitender, Vertragsstatus " & _
" FROM MailQ_Pull Records RechnungsUpdate")
Do Until rs.EOF

rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

Do you know why that is? Also, I don't quite see yet how he will group all the records with the same email address into one exportet file?

PS: Love your site and explaining, very good and thank you!

 

[Luchi]

Hallo sonic8,

vielen Dank! I have been looking at your turtorial and advice, but I seem to be getting something wrong... I keep geting an error message:

Laufzeitfehler 3075: Syntaxfehler (fehlender Operator) in Abfrageausdruck 'Email Ansprechpartner'.

Here is the code I have so far (step 1):
Option Compare Database
Option Explicit
Public Sub SendSerialEmail()
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT Email Ansprechpartner, Op# Beschaffer, BIT-Nummer, Status Minitender, Vertragsstatus " & _
" FROM MailQ_Pull Records RechnungsUpdate")
Do Until rs.EOF

rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

Also, I wanted to ask you, I don't quite see yet how this will manage to pull all the records with the same 'email' and export that as one file / attachment. Will it not send or want to send a mail per record?

PS: Super Webseit! danke der ist wirklich sehr hilfreich!

 

[George Moore]

Sorry it is 40 years since I did any German, but you will get syntax errors if there are blank spaces in your field names. You need to enclose them in brackets

eg SELECT [Email Ansprechpartner],[Op# Beschaffer] etc.

 

[Luchi]

ah perfect! thank you

 

[Minty]

Luchi - have a read here about a naming convention and some really good tips https://access-programmers.co.uk/forums/showthread.php?t=225837

 

[sonic8]

Also, I wanted to ask you, I don't quite see yet how this will manage to pull all the records with the same 'email' and export that as one file / attachment. Will it not send or want to send a mail per record?

You need two queries. You got the first one already. That one should be grouped by the email address.

Set rs = db.OpenRecordset("SELECT [Email Ansprechpartner] " & _
                                " FROM [MailQ_Pull Records RechnungsUpdate] GROUP BY  [Email Ansprechpartner]")
    Do Until rs.EOF

At this position in the code you need another query that returns all records and is filtered by the current email address from the recordset. You can do that by using a TempVar or a global function returning the current email address.
That query is the one you should export. You can combine exporting and sending with DoCmd.SendObject.
If you send the email by Outlook automation, you can export with DoCmd.TransferSpreadsheet and then attach the resulting file to the email. - DoCmd.SendObject is much easier to implement.

PS: Super Webseit! danke der ist wirklich sehr hilfreich!

Danke!