How to automatically send an email based upon query results

[dzirkelb]

I need to send an email based upon query results via vba or macro. Here is the steps taken:

1) user clicks a button
2) I need a query ran with the following syntax:
select HighAlert from Orders where (order = 123456)
3) I need to cycle through those results (seperate order lines) and if HighAlert ever equals 1, then I need to send an email to one of our managers notifying him of the alert.

My vba is not up to par, but here is how I would do it in vbscript:

strSendEmail = "No"
dbc = database connection properties

set rs = Server.CreateObject ("adodb.Recordset")
ssql = "SELECT HighAlert FROM Orders WHERE (order = 123456)
rs.Open ssql, dbc, adOpenForwardOnly, adLockReadOnly

do while not rs.eof
if rs("HighAlertPart") = 1 then
strSendEmail = "Yes"
end if
rs.moveNext
Loop

rs.close
set rs=nothing

dbc.close
set dbc=nothing

if strSendEmail = "Yes" then
code to send email
end if

So, can someone help me achieve this goal by clicking an access button?

 

[thechazm]

Thats pretty easy. Just use a macro and use the sendobject action. And you can customize what is in the send object action by using TempVars in the macro. I have a ton of vba code that takes care of mine but this way is far easier.

 

[dzirkelb]

I explained one portion incorrectly.

For every line that is tagged as high alert, an email will need to go out containting informaiton about that line. A seperate email for each line.

So, I think I need a programatic approach to run the results in a loop, and then send an email for each line when it sees it is tagged.

 

[thechazm]

Yes that is a correct statement. Look into Dao recordsets and you should be able to read the information and evaluate if its high or not. Then its just a matter of using some if then statements to have it work the way you want it to.

 

[dzirkelb]

I know how to do it, I know the pseudocode, I do not know the vba syntax. That is what I need help with.

 

[Trevor G]

Look at this thread I have placed in the VBA code which will help you.

http://www.access-programmers.co.uk/forums/showthread.php?t=212022

 

[dzirkelb]

Thanks a ton Trevor! From a quick scan of what you posted, that looks to be identical to what I need. I will work on this today and post back with any questions I may have.

 

[Trevor G]

Happy to help, hope it works for you.

 

[dzirkelb]

Ok, having a few issues so far. Here is the entire code for the button:

Private Sub CreateOrderFromTK850EditTable_OrderDetail_Click()
On Error GoTo Err_CreateOrderFromTK850EditTable_OrderDetail_Click

    Dim stDocName As String

    'stDocName = "CreateOrderFromTK850EditTable-OrderDetail"
    'DoCmd.OpenQuery stDocName, acNormal, acEdit

    'stDocName = "CreateOrderFromTK850EditTable-OrderDetailModFee"
    'DoCmd.OpenQuery stDocName, acNormal, acEdit
    
    Dim olapp As Outlook.Application
    Dim olmailmessage As Outlook.MailItem
    Dim olrecipient As Outlook.Recipient
    
    Dim StrMsg As String
    
    Dim rcdSQL As Variant
    
    Dim db As DAO.Database
    Dim rcd As Recordset
    
    Set db = CurrentDb()
    Set olapp = New Outlook.Application
    Set rcdSQL = db.OpenRecordset("SELECT ORDERS.[ORDER #], [CUSTOMER MASTER].[CUST NAME] AS CustName, INVENTORYMASTER1.HighAlertPartAssignedTo, [ORDER DETAILS].[PART_#] AS Part, [ORDER DETAILS].MFG_NAME AS Mfg, [ACCT ADMIN NAME LEGEND].[ACCT ADMINISTRATOR] AS InsideContact, [ACCT ADMIN NAME LEGEND].PrimaryEmail AS InsideEmail, [ACCT EXECUTIVE LEGEND].[ACCT EXECUTIVE] AS TM, [ACCT EXECUTIVE LEGEND].Email AS TMEmail FROM (((([CUSTOMER MASTER] INNER JOIN ORDERS ON [CUSTOMER MASTER].[CUST #] = ORDERS.[CUSTOMER #]) LEFT JOIN [ACCT EXECUTIVE LEGEND] ON [CUSTOMER MASTER].SALESPERSON = [ACCT EXECUTIVE LEGEND].[OUTSIDE TERR]) LEFT JOIN [ACCT ADMIN NAME LEGEND] ON [CUSTOMER MASTER].[INSIDE CONTACT] = [ACCT ADMIN NAME LEGEND].[INSIDE TERR]) INNER JOIN [ORDER DETAILS] ON ORDERS.[ORDER #] = [ORDER DETAILS].[ORDER #]) INNER JOIN INVENTORYMASTER1 ON [ORDER DETAILS].[PART_#] = INVENTORYMASTER1.[Part #] WHERE (((ORDERS.[ORDER #])=474263))", dbOpenDynaset, dbSeeChanges)
    
    Do Until rcdSQL.EOF
        If rcdSQL![HighAlertPartLockdown].VALUE = 1 Then
            Set olmailmessage = olapp.CreateItem(olMailItem)
            
            If Len(rcdSQL![PrimaryEmail].VALUE) > 3 Then
                olmailmessage.Recipients.Add (rcdSQL![PrimaryEmail].VALUE)
            End If
            
            If Len(rcdSQL![TMEmail].VALUE) > 3 Then
                olmailmessage.Recipients.Add (rcdSQL![TMEmail].VALUE)
            End If
            
            If Len(rcdSQL![HighAlertPartAssignedTo].VALUE) > 3 Then
                olmailmessage.Recipients.Add (rcdSQL![PrimaryEmail].VALUE & "@dee-inc.com")
            End If
            
            olmailmessage.Subject = "High Alert Part Notice - Order - Part # " & rcdSQL![Part].VALUE
            olmailmessage.Body = "Part Added to Order" & vbCrLf
            olmailmessage.Body = "Part: " & rcdSQL![Part].VALUE & vbCrLf
            olmailmessage.Body = "Mfg: " & rcdSQL![MFG].VALUE & vbCrLf
            olmailmessage.Body = "Inside Contact: " & rcdSQL![InsideContact].VALUE & vbCrLf
            olmailmessage.Body = "Cust Name: " & rcdSQL![CustName].VALUE & vbCrLf
            olmailmessage.Body = "Order #: 474263" & vbCrLf

            olmailmessage.Send
            Set olmailmessage = Nothing
        End If
    rcdSQL.MoveNext
    Loop
    
    Set olapp = Nothing
    
    
    
    
    
    
    
    
    
Exit_CreateOrderFromTK850EditTable_Order:
    Exit Sub

Err_CreateOrderFromTK850EditTable_OrderDetail_Click:
    MsgBox Err.Description
    Resume Exit_CreateOrderFromTK850EditTable_Order
    
End Sub

This creates an automatic crash of Access when clicking the button. I remove the "dbOpenDynaset" from the query and i get the error "Invalid Object" I remove the dbOpenDynaset, dbSeeChanges from teh query and i get "you must use the dbseechanges option with openrecordset when accessing a sql server table".

Every table involved in the query is a linked table to sql 2000 server.

I added the outlook 12 library and the Microsoft DAO 3.6 library.

What now? getting close

edit:
I have the query coded with a specific order number for now, and I will change that to be a variable later.

 

[Trevor G]

Very quick initial look at your reply.

You have declared

Dim rcdSQL As Variant


but not used the rcd anywere can I suggest you change

Dim rcdSQL As Recordset

 

[dzirkelb]

The same error messages happen when I changed the code to what you suggested.

 

[dzirkelb]

Ok, i got past the error, and initial testing returns no error, but no email either. I will see if the data I am using is bad and get back.

To fix my error, I had to add this to the end of the query code:

, dbOpenSnapshot, dbSeeChanges)

 

[dzirkelb]

Ok, I ahve everything working perfectly so far. I'll start to play around with how to grab the order number variable, I'll have to look at the application a bit more. But, in the code posted I have a couple lines commented out.

One quick question. Can I make the email HTML based anyhow? I have a link I need to put in there I would like them to just click.

 

[spikepl]

Use HTMLBody instead of Body

 

[Trevor G]

Ok, I ahve everything working perfectly so far. I'll start to play around with how to grab the order number variable, I'll have to look at the application a bit more. But, in the code posted I have a couple lines commented out.

One quick question. Can I make the email HTML based anyhow? I have a link I need to put in there I would like them to just click.

Change the following

Body To HTMLBody

Another tip for you, you can add break points into your code so when you step through the code if all working correctly to the break point then thats fine, if it falls over some where in between the break points it makes it easier to track the problem.

If you don't know about break points, place the mouse in the grey bar to the left of your code and click by the side of a line of code, you will see a circle and the line of code has a background colour.

Once you step through the code and all works to each break point you can click the circle to remove the break point.

 

[dzirkelb]

Awesome, thanks

I generally just put a msgbox("test") and move it around my code to see where it breaks

Everything is up and runnign smoothly. Thanks for your help!