Emailing Query (1 Viewer)

V

Valentine

Member
Local time
Today, 08:10
Joined
Oct 1, 2021
Messages
261
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.
 
S

sxschech

Registered User.
Local time
Today, 05:10
Joined
Mar 2, 2010
Messages
792
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.
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 13:10
Joined
Sep 21, 2011
Messages
14,238
I would create a report and attached that as a pdf file?
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 05:10
Joined
Oct 29, 2018
Messages
21,455
One other approach, if you're using HTML formatted email, is to generate the records as an HTML table in the body of the email.
 
S

sxschech

Registered User.
Local time
Today, 05:10
Joined
Mar 2, 2010
Messages
792
theDBguy said:
One other approach, if you're using HTML formatted email, is to generate the records as an HTML table in the body of the email.
Click to expand...
That was what the first part my suggestion was, but guess I didn't use the right terminology by leaving out mention of the word <HTML> in my post
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 05:10
Joined
Oct 29, 2018
Messages
21,455
sxschech said:
That was what the first part my suggestion was, but guess I didn't use the right terminology by leaving out mention of the word <HTML> in my post
Click to expand...
Oh, sorry for duplicating your post then. Actually, the technique is the same even if the user only wants to send plain text messages. Cheers!
 
V

Valentine

Member
Local time
Today, 08:10
Joined
Oct 1, 2021
Messages
261
Yeah im going the HTML route. So far i have
Code: 
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.
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 05:10
Joined
Oct 29, 2018
Messages
21,455
Valentine said:
Yeah im going the HTML route. So far i have
Code: 
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.
Click to expand...
Take a look at this...

Generate an HTML Table String from a Query's Recordset | DEVelopers HUT (devhut.net)
 
V

Valentine

Member
Local time
Today, 08:10
Joined
Oct 1, 2021
Messages
261
ok I did that code string but dont know how to pull it into my email.
My email code looks like this :
Code: 
Set myOutlApp = CreateObject("Outlook.Application")
Set myMail = myOutlApp.CreateItem(0)
With myMail
     .To = myEmlRecip
     .CC = "myEml"
     .Subject "mySubject"
     .HTMLBody = "<HTML><BODY style=font-family:Calibri>" & rsCustList![Customer Organization] & " AOs, " "<br>" & "<br>" _
                            & "open scrubs blah blah blah." & "<br>" & "<br>"

I want to add in the table after the comments
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 05:10
Joined
Oct 29, 2018
Messages
21,455
Valentine said:
ok I did that code string but dont know how to pull it into my email.
My email code looks like this :
Code: 
Set myOutlApp = CreateObject("Outlook.Application")
Set myMail = myOutlApp.CreateItem(0)
With myMail
     .To = myEmlRecip
     .CC = "myEml"
     .Subject "mySubject"
     .HTMLBody = "<HTML><BODY style=font-family:Calibri>" & rsCustList![Customer Organization] & " AOs, " "<br>" & "<br>" _
                            & "open scrubs blah blah blah." & "<br>" & "<br>"

I want to add in the table after the comments
Click to expand...
Have you tried?
Code: 
.HTMLBody = "<HTML><BODY style=font-family:Calibri>" & rsCustList![Customer Organization] & " AOs, " "<br>" & "<br>" _
                            & "open scrubs blah blah blah." & "<br>" & "<br>" _
                            & GenHTMLTable("YourQueryName")
 
V

Valentine

Member
Local time
Today, 08:10
Joined
Oct 1, 2021
Messages
261
Never mond that question i figured out how to do the table.
Code: 
Dim aHead(1 To 6) As String
Dim aRow(1 To 6) As String
Dim aBody() As String
Dim 1Cnt As Long

    aHead(1) = "Customer Org"
    aHead(2) = "ID"
    aHead(3) = "Title"
    aHead(4) = "Status"
    aHead(5) = "Type"
    aHead(6) = "Notes"
    
    1Cnt = 1
    ReDim aBody(1 To 1Cnt)
    aBody(1Cnt) = "<HTML><body><table border='1';'border-collapse:collapse'><thead><tr style='font-family:Calibri'><th>" _
    & Join(aHead, "</th><th>") _
    & "</th></tr></thread>"
    
    Set rec = CurrentDb.OpenRecordset(myRqmtsList)
    
    If Not (rec.BOF And rec.EOF) Then
        Do While Not rec.EOF
            1Cnt = 1Cnt + 1
            ReDim Preserve aBody(1 to 1Cnt)
            aRow(1) = rec("Customer Org")
            aRow(2) = rec("ID")
            aRow(3) = rec("Title")
            aRow(4) = rec("Status")
            aRow(5) = rec ("Type")
            aRow(6) = rec("Notes")
            aBody(1Cnt) = "<tr style='font-family:Calibri'><td>" & Join(aRow, "</td><td>") & "</td></tr>"
            rec.MoveNext
        Loop
    End IF
    
    aBody(1Cnt) = aBody(1Cnt) & "</table></body></html>"

Then I just put:
Join(aBody) & "<br>" & "</BODY>"

where I want the table.

Now i have another issue, not all the records will have data in the "Notes" field and that is stopping the whole thing.
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 05:10
Joined
Oct 29, 2018
Messages
21,455
Valentine said:
Never mond that question i figured out how to do the table.
Code: 
Dim aHead(1 To 6) As String
Dim aRow(1 To 6) As String
Dim aBody() As String
Dim 1Cnt As Long

    aHead(1) = "Customer Org"
    aHead(2) = "ID"
    aHead(3) = "Title"
    aHead(4) = "Status"
    aHead(5) = "Type"
    aHead(6) = "Notes"
   
    1Cnt = 1
    ReDim aBody(1 To 1Cnt)
    aBody(1Cnt) = "<HTML><body><table border='1';'border-collapse:collapse'><thead><tr style='font-family:Calibri'><th>" _
    & Join(aHead, "</th><th>") _
    & "</th></tr></thread>"
   
    Set rec = CurrentDb.OpenRecordset(myRqmtsList)
   
    If Not (rec.BOF And rec.EOF) Then
        Do While Not rec.EOF
            1Cnt = 1Cnt + 1
            ReDim Preserve aBody(1 to 1Cnt)
            aRow(1) = rec("Customer Org")
            aRow(2) = rec("ID")
            aRow(3) = rec("Title")
            aRow(4) = rec("Status")
            aRow(5) = rec ("Type")
            aRow(6) = rec("Notes")
            aBody(1Cnt) = "<tr style='font-family:Calibri'><td>" & Join(aRow, "</td><td>") & "</td></tr>"
            rec.MoveNext
        Loop
    End IF
   
    aBody(1Cnt) = aBody(1Cnt) & "</table></body></html>"

Then I just put:
Join(aBody) & "<br>" & "</BODY>"

where I want the table.

Now i have another issue, not all the records will have data in the "Notes" field and that is stopping the whole thing.
Click to expand...
Hi. Glad to hear you're making good progress. As for empty notes, can you maybe check it first before assigning something to the array?
 
V

Valentine

Member
Local time
Today, 08:10
Joined
Oct 1, 2021
Messages
261
is there a way to code in a null statement into there so it skips the "blanks"?
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 05:10
Joined
Oct 29, 2018
Messages
21,455
Valentine said:
is there a way to code in a null statement into there so it skips the "blanks"?
Click to expand...
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.
Code: 
aRow(6) = Nz(rec("Notes"),"None")
 
V

Valentine

Member
Local time
Today, 08:10
Joined
Oct 1, 2021
Messages
261
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?
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 05:10
Joined
Oct 29, 2018
Messages
21,455
Valentine said:
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?
Click to expand...
Hi. Congratulations! Glad to hear you got it sorted out. Nz() is a function to replace null values with a default one you specify.

Good luck with your project.
 
V

Valentine

Member
Local time
Today, 08:10
Joined
Oct 1, 2021
Messages
261
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:
Code: 
aBody(1Cnt) = "<HTML><body><table border='1';'border-collapse:collapse'><thread><tr style='font-family:Calibri'><th>" _
& Join(aHead, "</th><th>") _
& "</th></tr></thead>"

but i dont know how to put the design specifics in there other than choosing the border width and font.
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 05:10
Joined
Oct 29, 2018
Messages
21,455
Valentine said:
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:
Code: 
aBody(1Cnt) = "<HTML><body><table border='1';'border-collapse:collapse'><thread><tr style='font-family:Calibri'><th>" _
& Join(aHead, "</th><th>") _
& "</th></tr></thead>"

but i dont know how to put the design specifics in there other than choosing the border width and font.
Click to expand...
Can you post an image of what you're trying to duplicate?
 
V

Valentine

Member
Local time
Today, 08:10
Joined
Oct 1, 2021
Messages
261
Capture.PNG

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.
 
Last edited:
You must log in or register to reply here.

Similar threads

L
Send looping Email
Replies
3
Views
298
looma123
L
C
Inserting multiple rows into an email created by VBA
Replies
6
Views
194
ebs17
E
J
Mass Email - Based on Attachment - SMTP
Replies
5
Views
219
The_Doc_Man
The_Doc_Man
A
Solved Outlook Automation and Task Scheduler
Replies
4
Views
247
The_Doc_Man
The_Doc_Man
M
Query for multiple "not like" criteria on long text fields operating as "or", not "and".
Replies
9
Views
347
mike60smart
mike60smart

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom