Send email from form when inventory is low

[dcorleto]

I have created a SQL Query in Access that reports the number of remaining inventory as Expr1. When a command button is clicked on a form, I would like it to send an email to a person if the Expr1 is less than 4.

Can someone helps me start with the VBA code that I would need to acomplish this?

 

[rolaaus]

You must first make sure you have the reference to Microsoft Outlook (hopefully you are using 2003, or if you are using 2007, hopefully the code will work the same), but you need to add this refeence by going to Tools --> Preferences --> Microsoft Outlook XX.XX Objet Library

Then, drop the following code into your On_click event for the button.

Dim appOutlook As Outlook.Application
Dim olMailItem As Outlook.MailItem

Set appOutlook = CreateObject("Outlook.Application")
Set olMailItem = appOutlook.CreateItem(0)

With olMailItem
    .To = "Person@mailserver.com"
    .Subject = "Your Subect"
    .Body = "Place the body of your text here, to include any recordset info: " & me.expr1
    .Attachments.Add Source:="C:\io.sys" 'Place a file in here  - if you wish
    .Display 'This will dispaly the message giving the user the chance to send it
    'If you wish to automatically send the message, change .display to .send
End With

Set olMailItem = Nothing
Set appOutlook = Nothing

 

[dcorleto]

This is wonderful - thanks! But I guess I need the statements that runs the SQL query in the background first so that I can get the Me.expr1 - sorry but I am really new to all of this -

 

[rolaaus]

I think what you're wanting is to create a recordset that uses the same SQL as your existing query - this would allow you to use the recordset in the code I posted. Here is what I think you want, together with what I have already posted

Dim appOutlook As Outlook.Application
Dim olMailItem As Outlook.MailItem
Dim rsRecord as Recordset 'I would give this a more meaning name for you to know what is contained in the Recordset, like rsContacts, or rsGroceryList - just to name some examples.

Set appOutlook = CreateObject("Outlook.Application")
Set olMailItem = appOutlook.CreateItem(0)
Set rsRecord = currentdb.openrecordset("Select ... from ... where ...") 'This can be the same exact SQL statement from your existing uery.
rsRecord.movefirst 'this line of code may produce an error if there are no records, if you don't know how to trap errors, you may want to do a net search.
'now you can reference any fields in your recordset by referencing
'rsRecord("FieldName") and do IF or Select Case analysis, and placing values from these fields into msgbox's or other things like the olMailItem Subject or Body.


With olMailItem
    .To = "Person@mailserver.com"
    .Subject = "Your Subect"
    .Body = "Place the body of your text here, to include any recordset info: " & me.expr1
    .Attachments.Add Source:="C:\io.sys" 'Place a file in here  - if you wish
    .Display 'This will dispaly the message giving the user the chance to send it
    'If you wish to automatically send the message, change .display to .send
End With

Set olMailItem = Nothing
Set appOutlook = Nothing

If you need further assistance by all means keep a'posting and we'll finish this scenario out.

 

[dcorleto]

Dear rolaaus - I can't thank you enough for the quick response - I'll test this out and try to play around with it - I am taking a class in a couple of weeks in VBA for Access and can't wait to get started. This is so frustrating to not understand all this terminology and want to do these projects. I'll keep you posted on my progress with this. dan

 

[dcorleto]

One last time - I've gotten this far, but get a type mismatch error and can't seem to find why - can you please have a look and help me out - thanks!

Dim appOutlook As Outlook.Application
Dim olMailItem As Outlook.MailItem
Dim rsRecord As ADODB.Recordset 'I would give this a more meaning name for you to know what is contained in the Recordset, like rsContacts, or rsGroceryList - just to name some examples.
Dim sqlavail As Integer

Set appOutlook = CreateObject("Outlook.Application")
Set olMailItem = appOutlook.CreateItem(0)
sqlavail = "SELECT Count([TAG]) AS Avail From NOTEBOOKS "
sqlavail = sqlavail & "WHERE ONLOAN='Available' "
sqlavail = sqlavail & "AND ALIAS NOT IN ('USBKEY', 'INTERCALL')"
sqlavail = sqlavail & "And LOCATION Like 'NY*'"

Set rsRecord = CurrentDb.OpenRecordset(sqlavail) 'This can be the same exact SQL statement from your existing uery.
'rsRecord.MoveFirst 'this line of code may produce an error if there are no records, if you don't know how to trap errors, you may want to do a net search.
'now you can reference any fields in your recordset by referencing
'rsRecord("FieldName") and do IF or Select Case analysis, and placing values from these fields into msgbox's or other things like the olMailItem Subject or Body.
If rsRecord("Avail") < "4" Then

With olMailItem
.To = "Person@mailserver.com"
.Subject = "Your Subect"
.Body = "Place the body of your text here, to include any recordset info: " & rsRecord("Avail")
'.Attachments.Add Source:="C:\io.sys" 'Place a file in here - if you wish
.Display 'This will dispaly the message giving the user the chance to send it
'If you wish to automatically send the message, change .display to .send
End With
End If
Set olMailItem = Nothing
Set appOutlook = Nothing

 

[rolaaus]

When you reference getting an error - it is usually better to somehow signify where the error occured, but fortunately, I am betting it was stopped at this line

If rsRecord("Avail") < "4" Then

Whenever you use quote marks " it signifies text, so you will want to drop them and change it to
If rsRecord("Avail") < 4 Then

that should do it.

 

[dcorleto]

Thanks much it is working!!