Excel email question

[regulus]

Simple question (with a not so simple answer I imagine).

How do I fire off an email from Excel with Outlook once a cell reaches a certain value?

So I want to re-order stock if, for argument's sake, stock levels reaches '30'.

Is it masses of code?

 

[hyperseven]

use sendmail function from vba

 

[wesimmo]

It's a few years since I did something similar but I had real issues with MS not letting you just fire off an email using VBA, there had to be some human intervention or you had to install some unofficial plug in type thing.

 

[Meeko]

As wesimmo above mentions, using the SendMail function MAY require user interaction (Basically clicking an 'Allow' button) depending on some security settings set by your system admin.

An alternative way is to utilise the Outlook object model from VBA.

In Excel, bring up the VBA window by pressing ALT+F11
Click on the Tools menu and then choose References
In the window that appears, tick the box for Microsoft Outlook 14.0 Object Library (version number may vary depending what version of Outlook is installed on your system)
Click OK
In the Project Explorer (at the left of the VBA window by default), double-click on the worksheet where you will be checking if the stock level is
Copy and paste the code below into the Code window

Private Sub Worksheet_Change(ByVal Target As Range)

    If Range("A1").Value <= 30 Then
        Set objOL = New Outlook.Application
        Set objMail = objOL.CreateItem(olMailItem)
        
        With objMail
            .To = "[email protected]"
            .Subject = "STOCK REMINDER"
            .Body = "Stock level has reached lower limit.  Please re-order"
            .Send
        End With
        
        Set objMail = Nothing
        Set objOL = Nothing
    Else
    End If

End Sub

Basically, the code above is checking to see if the value in cell A1 <=30 (change A1 as appropriate to where the stock level is on your own sheet) and if so, will send out an email to the address specified on the .To line

 

[GAM8LE]

As wesimmo above mentions, using the SendMail function MAY require user interaction (Basically clicking an 'Allow' button) depending on some security settings set by your system admin.

An alternative way is to utilise the Outlook object model from VBA.

In Excel, bring up the VBA window by pressing ALT+F11
Click on the Tools menu and then choose References
In the window that appears, tick the box for Microsoft Outlook 14.0 Object Library (version number may vary depending what version of Outlook is installed on your system)
Click OK
In the Project Explorer (at the left of the VBA window by default), double-click on the worksheet where you will be checking if the stock level is
Copy and paste the code below into the Code window

Private Sub Worksheet_Change(ByVal Target As Range)

    If Range("A1").Value <= 30 Then
        Set objOL = New Outlook.Application
        Set objMail = objOL.CreateItem(olMailItem)
        
        With objMail
            .To = "[email protected]"
            .Subject = "STOCK REMINDER"
            .Body = "Stock level has reached lower limit.  Please re-order"
            .Send
        End With
        
        Set objMail = Nothing
        Set objOL = Nothing
    Else
    End If

End Sub

Basically, the code above is checking to see if the value in cell A1 <=30 (change A1 as appropriate to where the stock level is on your own sheet) and if so, will send out an email to the address specified on the .To line

That would work, I had to do something for work but there will need to be a check in that to see if outlook is already open.

I did it like this:

'function to check if Microsoft Outlook is running, this is the generic bit
'but is called upon under Send email click
Function IsAppRunning(ByVal sAppName) As Boolean
Dim oApp As Object
On Error Resume Next
Set oApp = GetObject(, sAppName)

If Not oApp Is Nothing Then
Set oApp = Nothing
IsAppRunning = True
End If
End Function

Then called upon sending email(this can be done under anything like button click or the above code. This also checks if the senders mailbox is full or if outlook is even open

If IsAppRunning(sApp) = True Then
Set mail_object = CreateObject("outlook.application")

Set mail_single = mail_object.Createitem(olMailItem)

On Error GoTo Error_MailBoxFull

With mail_single
    .Subject = "enter subject here"
    .To = "enter who you want it to go to here"
    .cc = "need a CC recipient? enter it here"
    .HTMLBody = "body of email"
    .Send
'Error handling for full mailbox
Error_MailBoxFull:
Select Case Err.Number
    Case -2147219956
    MsgBox ("Your Mailbox is Full, Please Delete some Items from your Inbox/Sent items and try and send email again!")
End Select
End With
Else
MsgBox ("Please Open Microsoft Outlook before trying to send email!")
End If

 

[wesimmo]

Even when i used the Outlook Object reference I had to press "Allow" for the email to send.

I can't remember why i didn't just send a key press command, though i guess MS might have seen that workaround coming and done something about it.

 

[GAM8LE]

Even when i used the Outlook Object reference I had to press "Allow" for the email to send.

I can't remember why i didn't just send a key press command, though i guess MS might have seen that workaround coming and done something about it.

Really? that's strange, I never had any additional messages when I did that with the outlook reference.

 

[wesimmo]

Really? that's strange, I never had any additional messages when I did that with the outlook reference.

Like i say, it was a while ago so things may have changed since, it was probably pre 2007 Excel being used where i worked.

edit: Just checked it where i work at the moment, using Office 2010 and even with the reference library added you do have to press "allow" and key strokes won't work, you have to use the mouse. Must be security differences to where you are?

 

[andshrew]

Since Outlook 2007 it will automatically disable the application sending message on your behalf prompts if you have anti-virus installed which is correctly reporting that it is up-to-date and valid to the Windows and Office Trust Center.

If it doesn't then you can manually change the settings to force it not to warn you - see here for details.