Consulting

Results 1 to 3 of 3

Thread: Solved: send email from Excel with cells as body

  1. #1
    VBAX Expert mperrah's Avatar
    Joined
    Mar 2005
    Posts
    738
    Location

    Post Solved: send email from Excel with cells as body

    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

    [vba]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[/vba]

    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.

    Mark

  2. #2
    VBAX Master
    Joined
    Jul 2006
    Location
    Belgium
    Posts
    1,284
    Location
    I usually go to Ron De Bruin's site : www.rondebruin.nl It could be worth a visit. If you don't know which client, you could use cdo.

  3. #3
    VBAX Expert mperrah's Avatar
    Joined
    Mar 2005
    Posts
    738
    Location
    That was just what I needed,
    Do you know a quick worksheet_change code to send when value in col(5)<5 ?
    Mark

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •