object required error for range of cells in copy in outlook mail

[sapxl]

Hello Friends,

I'm trying to send the outlook email, where i want to send the bodytext of email would be the value of cells range which needs to be copy into my email body. I tried to do so, but unable to figure out.

Is there anyone who can help me out with this issue... appreciate your help in this regard. Thanks,

code:

Sub Outlookmail()
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = Activate.Sheet1.Range("A1:H24").Value

''xMailBody = "Hi there" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2"

On Error Resume Next
With xOutMail
.To = "abcxyz@gmail.com"
.CC = ""
.BCC = ""
.Subject = Cells(cell.Row, "B8").Value & Space(1) & "(" & Cells(cell.Row, "D8").Value & Space(1) & Cells(cell.Row, "F8").Value & ")" & Space(1) & "**DO NOT REPLY TO THIS E-MAIL**"
.Body = xMailBody
.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub

 

[offthelip]

try something like this:

mailar = Activate.Sheet1.Range("A1:H24").Value
xMailBody = ""
 For i = 1 To 24
  For j = 1 To 8
  xMailBody = xMailBody & mailar(i, j)
  Next j
  xMailBody = xMailBody & vbNewLine
 Next i

 

[sapxl]

Hi offthelip,

Thanks for the code.. but it still not working... I included as below:

Sub Outlookmail()
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Dim mailar As String
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)

mailar = Sheet1.Range("A1:H24").Value
''xMailBody = Activate.Sheet1.Range("A1:H24").Value
With xOutApp.CreateItem(0)
xMailBody = ""
 For i = 1 To 24
  For j = 1 To 8
  xMailBody = xMailBody & mailar(i, j)
  Next j
  xMailBody = xMailBody & vbNewLine
 Next i
 End With
''xMailBody = "Hi there" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2"

On Error Resume Next
With xOutMail
.To = "abcxyz@gmail.com"
.CC = ""
.BCC = ""
.Subject = Cells(cell.Row, "B8").Value & Space(1) & "(" & Cells(cell.Row, "D8").Value & Space(1) & Cells(cell.Row, "F8").Value & ")" & Space(1) & "**DO NOT REPLY TO THIS E-MAIL**"
.Body = xMailBody
.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub

 

[offthelip]

do you have option explicit set,? if so you need to add:

dim mailar () as variant

 

[sapxl]

Excellent my dear friend.. superb... Thanks a ton... working very smooth...

 

[sapxl]

It was not working when i use option explicit, but when i removed, it is working smoothly.. Thanks once again..

 

[sapxl]

Hi offthelip,

I'm trying to automate the email sending process without clicking to send email.. if N5 cell has email id then it will automatically send email (N5 value) as soon as email id is entered in the cell N5. I'm trying the code, but no luck... could you pls help me on this..Appreciate your help in this regard.. Thanks,

VBA code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("N5").Value Like "?*@?*.?*" And Cells.SpecialCells(xlCellTypeConstants) Then
    Sheets("Invoice").Range("N5").Value.RefreshAll
    Call Outlookmail
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Application.Intersect(Range("N5"), Target) Is Nothing Then
        Exit Sub
        End If
    End If
End Sub

Sub Outlookmail()
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Dim mailar As Variant
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)

''On Error GoTo 0

''If cell.Value Like "?*@?*.?*" And Cells.SpecialCells(xlCellTypeConstants) Then

''If Sheets("Invoice").Range("N5").Value <> "" Then
''If cell.Value Like "?*@?*.?*" And LCase(Cells(cell.Row, "N5").Value) <> "" Then

mailar = Sheet1.Range("A1:H24").Value
With xOutApp.CreateItem(0)
xMailBody = ""
 For i = 1 To 24
  For j = 1 To 8
  xMailBody = xMailBody & mailar(i, j)
  Next j
  xMailBody = xMailBody & vbNewLine
 Next i
 End With
''xMailBody = "Hi there" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2"

On Error Resume Next
With xOutMail
''.To = "abcxyz@gmail.com"
.To = Sheets("Invoice").Range("N5").Value
.CC = ""
.BCC = ""
.Subject = "IMP. MESSAGE"
.Body = xMailBody
.Display 'or use .Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub

 

[offthelip]

I think you need to change the the worksheet change routine to this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Not (Intersect(Range("N5"), Target) Is Nothing) Then
    
    If Range("N5").Value Like "?*@?*.?*" Then
 '   Sheets("Invoice").Range("N5").Value.RefreshAll
        Call outlookmail
    End If
    End If
End Sub

I don't think your refreshall, statement is correct either see:
Workbook.RefreshAll method (Excel)

 

[sapxl]

Hi offthelip,

Thanks alot for your help, issue has been resolved with another method.. I have used the helper cell to do the task...

N3 cell in invoice sheet should have formula - IF(N5<>"","yes","no") & N5 ihas the email id cell... if the condition is yes, then the email is triggered automatically.... but it has disadvantage, whenever, i'm double-click on any part of sheet, it gets refreshed, and the email is triggering number of times on that email id which the cell contains... is there anything to stop running the code once successfully send to the email id...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim findText As Variant
findText = Application.Match("yes", Sheets("invoice").Range("N3"), 0)
If Not IsError(findText) Then
If Sheets("Invoice").Range("N3").Value = "yes" Then
Call Outlookmail
End If
End If
End Sub