[Solved] Email : For Each doesn't work ????????

  • Hi, I am a novice VBAer so please bear with me on this one.


    I have a small database in an excel sheet.
    My goal is to allow a user to enter data, then select a range and have the data from the range inserted into the body of the email. I want to format the body of the mail so that the data from each row in the range is displayed on its own line.


    I am trying to get this code to work:


    Private Function SendMail() As Boolean

    Dim rngSelected As Range
    Dim strtemp As String
    Dim intRowCount As Integer

    'Let the user select a range
    Set rngSelected = Application.InputBox(Prompt:="Please select range you wish to send.", _
    Type:=8, Default:=Selection.Address)

    intRowCount = rngSelected.Rows.Count


    'I want to display the information from each row of the selected range
    'and display it on its own line in the message body
    'For some reason the For Each statement gives me an error

    Dim i As Integer
    For i = 1 To intRowCount
    For Each cell In rngSelected.Rows(i) ' gives error: can't find project or library
    strtemp = strtemp + " " + cell.Value
    Next
    strtemp = strtemp + vbCrLf 'start a new row
    Next

    'build my mail message
    Dim x As Outlook.Application
    Dim y As Outlook.MailItem
    Set x = New Outlook.Application
    Set y = x.CreateItem(olMailItem)
    With y
    .Subject = "Whatever"
    .To = "[email protected]"
    .Body = strtemp
    .Display
    End With


    Set x = nothing
    Set y = nothing

    End Function


    NOTE: When I enter the word "cell" it does not capitlize automatically like other key words.


    I have references to Outlook, Excel and Office 9.0 libraries and am using Excel 2000.


    Any help is greatly appreciated.

  • Hi Yellow Belly,


    The cell will not automatically convert to Cell because cell is not a reserved VBA word.
    It is just the name you have given to a undeclared variable.


    Try Adding Option Explicit to the very top of the code module.
    This will force you to declare all variables. But it will stop errors like this and typo's from occurring.


    I have added a line that declares Cell.


    The other problem was with the In rngSelected.Rows(i)
    This will work if you only select a single column of rows.
    You need to add .Cells to the end in order to process all cells selected.


    After this minor changes your code produced an email with the correct body text.


    Private Function SendMail() As Boolean

    Dim rngSelected As Range
    Dim strtemp As String
    Dim intRowCount As Integer
    Dim Cell As Range

    'Let the user select a range
    Set rngSelected = Application.InputBox(Prompt:="Please select range you wish to send.", _
    Type:=8, Default:=Selection.Address)

    intRowCount = rngSelected.Rows.Count

    'I want to display the information from each row of the selected range
    'and display it on its own line in the message body
    'For some reason the For Each statement gives me an error

    Dim i As Integer
    For i = 1 To intRowCount
    For Each Cell In rngSelected.Rows(i).Cells ' gives error: can't find project or library
    strtemp = strtemp + " " + Cell.Value
    Next
    strtemp = strtemp + vbCrLf 'start a new row
    Next

    'build my mail message
    Dim x As Outlook.Application
    Dim y As Outlook.MailItem
    Set x = New Outlook.Application
    Set y = x.CreateItem(olMailItem)
    With y
    .Subject = "Whatever"
    .To = "[email protected]"
    .Body = strtemp
    .Display
    End With

    Set x = Nothing
    Set y = Nothing


    End Function


    Cheers
    Andy

  • Thanks alot Andy,


    I just had to change "Cell.Value" to Cstr(Cell.Value ) and I got the result I was looking for. Probably because some cells contain dates and part numbers.


    Thanks again for all your help and the quick response. This does exactly what I wanted it to .


    Have a great day!