I am trying to run a query for each of my customer organizations to show them open records in our system. I am then going to input those queries into an email to send out mid month. I know i can throw the queries into an excel spreadsheet and attach that to each email but i want to put the query into the email so they dont have to open a spreadsheet. This is usually a manual thing done every month but we are trying to automate it so that the task can be done with a button click.
Assuming that the query only has a few columns and the same ones each month, if you want the query result in the body of the email you can use vba and a recordset to populate the message. If there are many cols, you may want to consider outputting as a report in PDF format or as text in csv and attaching it as a file. I can provide a code example if you want the query result in the body of an email if you are using outlook.
Set dbCurr = CurrentDb()
Set qdfCurr = dbCurr.QueryDefs("Output Mid-Month List")
Do While rsCustList.EOF = False
myRqmtsList = "Stuff and things too long to type again"
qdfCurr.SQL = myRqmtsList
rqmtstotal = DCount("[CNF ID#]", "[Mid-Month list]", "[Customer Organization] = '" & rsCustList![Customer Organization] & "'")
If rqmtstotal <> "0" Then
myAOList = "SQL to select my email string"
Set rsAOList = CurrentDb.OpenRecordset(myAOList)
Do While rsAOList.EOF = False
myEmlRecip = myEmlRecip & rsAOList!email & ";"
rsAOList.MoveNext
Loop
I also have the email set up on paper I just don't know how to add each query I make into its own email.
In the body of the email I will be using the .HTMLBody method to insert what i want each email to say.
Set dbCurr = CurrentDb()
Set qdfCurr = dbCurr.QueryDefs("Output Mid-Month List")
Do While rsCustList.EOF = False
myRqmtsList = "Stuff and things too long to type again"
qdfCurr.SQL = myRqmtsList
rqmtstotal = DCount("[CNF ID#]", "[Mid-Month list]", "[Customer Organization] = '" & rsCustList![Customer Organization] & "'")
If rqmtstotal <> "0" Then
myAOList = "SQL to select my email string"
Set rsAOList = CurrentDb.OpenRecordset(myAOList)
Do While rsAOList.EOF = False
myEmlRecip = myEmlRecip & rsAOList!email & ";"
rsAOList.MoveNext
Loop
I also have the email set up on paper I just don't know how to add each query I make into its own email.
In the body of the email I will be using the .HTMLBody method to insert what i want each email to say.
Not sure that you really want to "skip" the blanks/nulls. What exactly is happening? This would be one example of how to enter "something" in the array when notes is blank.
OMG thank you that worked perfectly. The records with notes appeared with notes and the ones with nothing just had that None in them awesome. what does the Nz stand for so i can write it down in my note book?
OMG thank you that worked perfectly. The records with notes appeared with notes and the ones with nothing just had that None in them awesome. what does the Nz stand for so i can write it down in my note book?
Ok so now i want to make the table look like our standard table in Outlook. I have no idea how to actually code that but i know it has to go into the following line:
Ok so now i want to make the table look like our standard table in Outlook. I have no idea how to actually code that but i know it has to go into the following line:
I want to choose this style of table and change the font color of the header to white. The table style is called "Grid Table 4 - Accent 1" I just don't know were to input it into my code so the tables show up like this.