Email cell information

  • I am somewhat new using VB codes. I am trying to set up a small spreadsheet for inventory,that way when a certain range falls below a reorder point, an email will be generated to reorder more.The only thing I need to know is how to email the contents of cell (example D2 thru G2)? The code I included is sending the email but only sends the amount the inventory is changed to.
    Below is what I have so far.
    Thanks for any help


    [vba]Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$H$2" Then
    If Target.Value < 6 Then
    If MsgBox("Confirm Reorder of Tooling", vbQuestion Or vbYesNo) = vbYes Then
    EmailNotify "Out of stock cutters ", Target.Value
    End If
    End If
    End If
    End Sub
    Public Sub EmailNotify(SubjectText As String, BodyText As String)
    Dim appOL As Object
    Dim objMail As Object
    Const EMAIL_ADDRESS = "Email address.com"

    Set appOL = CreateObject("Outlook.Application")
    Set objMail = appOL.CreateItem(0) ' olmailitem=0

    With objMail
    .Recipients.Add EMAIL_ADDRESS
    .Subject = reorder
    .Body = BodyText
    .Send
    End With

    Set objMail = Nothing
    Set appOL = Nothing
    End Sub[/vba]

  • Re: Email cell information


    There are several post that give you examples and code for exactly what your looking for on the forum. Try a search and look for similair titles and that should get you started.

    [SIZE=2]I should change my name to STUMBED![/SIZE]

  • Re: Email cell information


    You can create a string that holds the cells data. How you combine the cells will depend on their content and how you want them displayed in the e-mail. Here is one example to get started.
    [vba]Dim s As String
    s = Range("D2").Value & vbLf & Range("E2").Value & vbLf & Range("F2").Value & Range("G2").Value
    MsgBox s
    [/vba]

  • Re: Email cell information


    Derk,
    Thanks, I tried that code, however I rec'd a pop up,with the right information that I was looking for, but instead of the pop up notification is there a way to include the info in the body of the email? Or did I use the code in the wrong way?
    Thanks for any help.
    Scott

  • Re: Email cell information


    Now that you have the string the way you want it, pass it to your e-mail routine. So replace
    [vba] EmailNotify "Out of stock cutters ", Target.Value [/vba]
    with
    [vba] EmailNotify "Out of stock cutters ", s[/vba]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!