Email Named Range With Ron De Bruin's Example

  • I am trying to use http://www.rondebruin.nl/mail/folder3/message.htm program . Example 1


    Column A1- Company Name- Ed's Lighting
    Column B1 -Email [email protected]
    Column C1- Send- Yes
    Column D1- Range- Ed.....this is range (E1: G4:) which I named ED and wish to send this range to his email address.


    Column A5- Company Name- Bill's Plumbing
    Column B5 -Email Address- [email protected]
    Column C5- Send- Yes
    Column D5- Range- Bill.....this is range (E5: G10:) which I named Bill and wish to send.


    In Ron's program, the body only sends one line .


    Since the number of line Items varies, I need to send variable Named ranges identified in Column D.

  • Re: Email Named Range With Ron De Bruin's Example


    This code will return the range refered to by the name ED in your example
    [vba]
    msgbox thisworkbook.Names(Range("D1").value).RefersToRange.Address
    [/vba]

  • Re: Email Named Range With Ron De Bruin's Example


    I didn't do a good job of defining what I need.


    When I use [cell.Offset(0,2).names(range("d1").value)/] I get the range address but not the actual values.

  • Re: Email Named Range With Ron De Bruin's Example


    Did you use this code to build the body text from the cells in the range?
    [vba]
    Dim strbody As String
    For Each cell In ThisWorkbook.Sheets("Sheet1").Range("E1:E20")
    strbody = strbody & cell.Value & vbNewLine
    Next
    [/vba]

  • Re: Email Named Range With Ron De Bruin's Example


    That sends an email for everything in Range("E1:E20") to all who I have identified with yes in column C.


    What I need is to send only the range values identified by name in column D. Range(ED)


    Then since Billplumbing is identified as yes in column C, send his information using range name Bill.


    I will have about 100 emails sent to each vendor with different data identified by their individual range. Ed, Bill, Charlie, etc. This lets them know their items which they have low inventories.


    I would like to do a copy and paste then autofit to the emai body.
    The information now is in one column.

  • Re: Email Named Range With Ron De Bruin's Example


    Obviously using the code without change would send range E1:E20.
    Try combining it with the other code sample


    [vba]
    Dim strbody As String


    For Each cell In thisworkbook.Names(Range("D1").value).RefersToRange
    strbody = strbody & cell.Value & " "
    Next
    [/vba]

  • Re: Email Named Range With Ron De Bruin's Example


    Almost there!


    This is in range Ed:


    ITEM DESCRIPTION QTY
    B12 Switch Box 0
    C99 Cable for TV 10
    W16 Door Bell wire 2




    Now I get the data all on one line.


    Thanks for being patient, I am 64 years old and got several VB books for Christmas. Could not find it in any of them.

  • Re: Email Named Range With Ron De Bruin's Example


    This will place each row of cell content on a new line.


    [vba]
    Sub x()


    Dim strbody As String
    Dim rngRow As Range
    Dim rngCell As Range

    With ThisWorkbook.Names(Range("D1").Value).RefersToRange
    For Each rngRow In .Rows
    For Each rngCell In rngRow.Cells
    strbody = strbody & rngCell.Value & " "
    Next
    strbody = strbody & vbLf
    Next
    End With
    MsgBox strbody

    End Sub
    [/vba]

  • Re: Email Named Range With Ron De Bruin's Example


    I get the right names but the same data as "C2" .
    I tried to make the range names with a varible with "x". ("C" & x)
    I may have the code in the wrong place. Be sure to clear Column "J" of "sent" to re run the macro.



  • Re: Email Named Range With Ron De Bruin's Example


    Try this slight revision.
    I have made a function for constructing the body text from named range.


    [vba]
    Sub emailwithyes()


    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range
    Dim rngCell As Range

    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon

    On Error GoTo cleanup
    For Each cell In Sheets("Sheet1").Columns("B").Cells.SpecialCells(xlCellTypeConstants)
    If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 2).Value) = "yes" And cell.Offset(0, 8) <> "SENT" Then
    Set OutMail = OutApp.CreateItem(0)
    On Error Resume Next
    With OutMail
    .To = cell.Value
    .Subject = cell.Offset(0, -1) & " Inventory"
    .Body = "Dear " & cell.Offset(0, 7).Value & vbNewLine & vbNewLine & _
    "Your Inventory are:" & vbNewLine & "ITEM DESCRIPTION QTY" & vbNewLine & _
    BuildEmailBody(cell.Offset(0, 1).Value)

    'You can add files also like this
    '.Attachments.Add ("C:\test.txt")
    .display 'Send 'Or use Display
    End With
    On Error GoTo 0
    strdate = Format(Now, "mm-dd-yyyy-hh-mm")
    cell.Offset(0, 8) = "SENT"
    cell.Offset(0, 9) = strdate

    Set OutMail = Nothing
    End If


    Next cell


    cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
    End Sub


    Function BuildEmailBody(NamedRange As String) As String


    Dim rngRow As Range
    Dim strBody As String

    With ThisWorkbook.Names(NamedRange).RefersToRange
    For Each rngRow In .Rows
    For Each rngCell In rngRow.Cells
    strBody = strBody & rngCell.Value & " "
    Next
    strBody = strBody & vbLf
    Next
    End With

    BuildEmailBody = strBody


    End Function
    [/vba]


    You might want to check your named ranges as they appear to be out of sync with your data.

  • Re: Email Named Range With Ron De Bruin's Example


    Yes! Yes! I stand and appauld your expertiese.


    For others using the program, delete the space before Outlook
    in the statement:
    Set OutApp = CreateObject(" Outlook.Application")


    Thanks. now back to the VB books.

Participate now!

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