View Full Version : Solved: send email from Excel with cells as body

10-12-2007, 02:11 AM
Found this code in the forum and would like to modify it to send an email when inventory quantities are low.
The qty is in column D of the Parts sheet

Sub SendEmail()
'Uses early binding
'Requires a reference to the Outlook Object Library
Dim OutlookApp As Outlook.Application
Dim MItem As Outlook.MailItem
Dim cell As Range
Dim Subj As String
Dim EmailAddr As String
Dim Recipient As String
Dim Msg As String

'Create Outlook object
Set OutlookApp = New Outlook.Application

Subj = "Inventory Warning"
Recipient = "Mark"
EmailAddr = "me@yahoo.com"

'Compose message
Msg = "Dear " & Recipient & vbCrLf & vbCrLf
Msg = Msg & "Inventory is running low" & vbCrLf & vbCrLf
' if worksheets("Parts").column(4).value <=5 then list 'something like this?
Msg = Msg & " Auto sent from office"

'Create Mail Item and send it
Set MItem = OutlookApp.CreateItem(olMailItem)
With MItem
.To = EmailAddr
.Subject = Subj
.Body = Msg
' .Send
.Save 'to Drafts folder
End With
Set OutlookApp = Nothing
End Sub

Also, is there an easier way to build the email.
(using outlook express maybe, if the user has no outlook?)
This was posted to set up bulk email, but I only have one recipient, and the subject will be the same everytime,
only the body needs to change as inventory levels drop below 5.

Thanks in advance.


10-12-2007, 02:34 AM
I usually go to Ron De Bruin's site : www.rondebruin.nl (http://www.rondebruin.nl) It could be worth a visit. If you don't know which client, you could use cdo.

10-12-2007, 02:00 PM
That was just what I needed,
Do you know a quick worksheet_change code to send when value in col(5)<5 ?