Macro doesn't work always.

M

mehul13

New Member
Joined
Dec 3, 2014
Messages
6
Hi,

Was trying to get this working. Cell(17,2) has a date and is being compared to the present date. If Present date is greater than the date in Cell(17,2) it will shoot a mail.

Sometimes this code is working good but sometimes its not..

When runs it gives this error after completion
"Run-Time Error '287'
Application-defines or object-defined error..

Can anyone help me with this?


Code: 
Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object
    Dim StrBody As String
      
    If (CDate(Cells(17, 2).Value) < Now()) Then
                        
               StrBody = "This is line 1" & "
" & _
              "This is line 2" & "
" & _
              "This is line 3" & "


"
              
                Set rng = Nothing
                On Error Resume Next
                'Only the visible cells in the selection
                Set rng = Selection.SpecialCells(xlCellTypeVisible)
                'You can also use a fixed range if you want
                Set rng = Sheets("Checklist").Range("A2:B25").SpecialCells(xlCellTypeVisible)
                On Error GoTo 0


                With Application
                .EnableEvents = False
                .ScreenUpdating = False
                End With


                Set OutApp = CreateObject("Outlook.Application")
                Set OutMail = OutApp.CreateItem(0)


                On Error Resume Next
        
                With OutMail
             If Range("B3").Value = "Operation_Support" Then
             
                .To = "xy"
                
                .CC = "n"
                .BCC = ""
                .Subject = "Test Mail"
                .HTMLBody = StrBody & RangetoHTML(rng)
                .Attachments.Add ActiveWorkbook.FullName
                ' You can add other files by uncommenting the following line.
                '.Attachments.Add ("C:\test.txt")
                .Display
                '.Send
                End With
                Else
                
        .To = "ab"
                
                .CC = "p"
                .BCC = ""
                .Subject = "Test Mail"
                .HTMLBody = StrBody & RangetoHTML(rng)
                .Attachments.Add ActiveWorkbook.FullName
                ' You can add other files by uncommenting the following line.
                '.Attachments.Add ("C:\test.txt")
                .Display
                '.Send
       
        
        End If
    
    
End Sub


Function RangetoHTML(rng As Range)
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2016
    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim TempWB As Workbook


    TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"


    'Copy the range and create a new workbook to past the data in
    rng.Copy
    Set TempWB = Workbooks.Add(1)
    With TempWB.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial xlPasteValues, , False, False
        .Cells(1).PasteSpecial xlPasteFormats, , False, False
        .Cells(1).Select
        Application.CutCopyMode = False
        On Error Resume Next
        .DrawingObjects.Visible = True
        .DrawingObjects.Delete
        On Error GoTo 0
    End With


    'Publish the sheet to a htm file
    With TempWB.PublishObjects.Add( _
         SourceType:=xlSourceRange, _
         Filename:=TempFile, _
         Sheet:=TempWB.Sheets(1).Name, _
         Source:=TempWB.Sheets(1).UsedRange.Address, _
         HtmlType:=xlHtmlStatic)
        .Publish (True)
    End With


    'Read all data from the htm file into RangetoHTML
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    RangetoHTML = ts.readall
    ts.Close
    RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                          "align=left x:publishsource=")


    'Close TempWB
    TempWB.Close savechanges:=False


    'Delete the htm file we used in this function
    Kill TempFile


    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
End Function
 
Last edited:

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
Hi Mehul,

Right click and click on view code on sheet name tab in which you want to trigger the event procedure and paste this below code :

Code: 
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object
    Dim StrBody As String
    Dim wtch As Range
      
    Set wtch = Range("B17")
      
    If Not Application.Intersect _
            (Target, wtch) Is Nothing Then
        If CDate(Target.Value) < Now() And Not IsEmpty(Target) Then
               StrBody = "This is line 1" & ""
                Set rng = Nothing
                On Error Resume Next
                'Only the visible cells in the selection
                'Set rng = Selection.SpecialCells(xlCellTypeVisible)
                'You can also use a fixed range if you want
                Set rng = Sheets("Checklist").Range("A2:B25").SpecialCells(xlCellTypeVisible)
                On Error GoTo 0


                With Application
                .EnableEvents = False
                .ScreenUpdating = False
                End With

                Set OutApp = CreateObject("Outlook.Application")
                Set OutMail = OutApp.CreateItem(0)

                On Error Resume Next
        
                With OutMail
                
                If Range("B3").Value = "Operation_Support" Then
                
                .To = "xy"
                .CC = "n"
                .BCC = ""
                .Subject = "Test Mail"
                .HTMLBody = StrBody & RangetoHTML(rng)
                .Attachments.Add ActiveWorkbook.FullName
                ' You can add other files by uncommenting the following line.
                '.Attachments.Add ("C:\test.txt")
                .Display
                '.Send
            Else
                .To = "ab"
                .CC = "p"
                .BCC = ""
                .Subject = "Test Mail"
                .HTMLBody = StrBody & RangetoHTML(rng)
                .Attachments.Add ActiveWorkbook.FullName
                ' You can add other files by uncommenting the following line.
                '.Attachments.Add ("C:\test.txt")
                .Display
                '.Send
            End If
            End With
        End If
    End If
Application.EnableEvents = True
End Sub
Function RangetoHTML(rng As Range)
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2016
    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim TempWB As Workbook


    TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"


    'Copy the range and create a new workbook to past the data in
    rng.Copy
    Set TempWB = Workbooks.Add(1)
    With TempWB.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial xlPasteValues, , False, False
        .Cells(1).PasteSpecial xlPasteFormats, , False, False
        .Cells(1).Select
        Application.CutCopyMode = False
        On Error Resume Next
        .DrawingObjects.Visible = True
        .DrawingObjects.Delete
        On Error GoTo 0
    End With


    'Publish the sheet to a htm file
    With TempWB.PublishObjects.Add( _
         SourceType:=xlSourceRange, _
         Filename:=TempFile, _
         Sheet:=TempWB.Sheets(1).Name, _
         Source:=TempWB.Sheets(1).UsedRange.Address, _
         HtmlType:=xlHtmlStatic)
        .Publish (True)
    End With


    'Read all data from the htm file into RangetoHTML
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    RangetoHTML = ts.readall
    ts.Close
    RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                          "align=left x:publishsource=")


    'Close TempWB
    TempWB.Close savechanges:=False


    'Delete the htm file we used in this function
    Kill TempFile


    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
End Function
 
Upvote 0
Ombir said:
Hi Mehul,

Right click and click on view code on sheet name tab in which you want to trigger the event procedure and paste this below code :

Code: 
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object
    Dim StrBody As String
    Dim wtch As Range
      
    Set wtch = Range("B17")
      
    If Not Application.Intersect _
            (Target, wtch) Is Nothing Then
        If CDate(Target.Value) < Now() And Not IsEmpty(Target) Then
               StrBody = "This is line 1" & ""
                Set rng = Nothing
                On Error Resume Next
                'Only the visible cells in the selection
                'Set rng = Selection.SpecialCells(xlCellTypeVisible)
                'You can also use a fixed range if you want
                Set rng = Sheets("Checklist").Range("A2:B25").SpecialCells(xlCellTypeVisible)
                On Error GoTo 0


                With Application
                .EnableEvents = False
                .ScreenUpdating = False
                End With

                Set OutApp = CreateObject("Outlook.Application")
                Set OutMail = OutApp.CreateItem(0)

                On Error Resume Next
        
                With OutMail
                
                If Range("B3").Value = "Operation_Support" Then
                
                .To = "xy"
                .CC = "n"
                .BCC = ""
                .Subject = "Test Mail"
                .HTMLBody = StrBody & RangetoHTML(rng)
                .Attachments.Add ActiveWorkbook.FullName
                ' You can add other files by uncommenting the following line.
                '.Attachments.Add ("C:\test.txt")
                .Display
                '.Send
            Else
                .To = "ab"
                .CC = "p"
                .BCC = ""
                .Subject = "Test Mail"
                .HTMLBody = StrBody & RangetoHTML(rng)
                .Attachments.Add ActiveWorkbook.FullName
                ' You can add other files by uncommenting the following line.
                '.Attachments.Add ("C:\test.txt")
                .Display
                '.Send
            End If
            End With
        End If
    End If
Application.EnableEvents = True
End Sub
Function RangetoHTML(rng As Range)
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2016
    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim TempWB As Workbook


    TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"


    'Copy the range and create a new workbook to past the data in
    rng.Copy
    Set TempWB = Workbooks.Add(1)
    With TempWB.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial xlPasteValues, , False, False
        .Cells(1).PasteSpecial xlPasteFormats, , False, False
        .Cells(1).Select
        Application.CutCopyMode = False
        On Error Resume Next
        .DrawingObjects.Visible = True
        .DrawingObjects.Delete
        On Error GoTo 0
    End With


    'Publish the sheet to a htm file
    With TempWB.PublishObjects.Add( _
         SourceType:=xlSourceRange, _
         Filename:=TempFile, _
         Sheet:=TempWB.Sheets(1).Name, _
         Source:=TempWB.Sheets(1).UsedRange.Address, _
         HtmlType:=xlHtmlStatic)
        .Publish (True)
    End With


    'Read all data from the htm file into RangetoHTML
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    RangetoHTML = ts.readall
    ts.Close
    RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                          "align=left x:publishsource=")


    'Close TempWB
    TempWB.Close savechanges:=False


    'Delete the htm file we used in this function
    Kill TempFile


    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
End Function
Click to expand...


Hi Ombir,

Sure will try this n get back to you.. Thank you.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
VBA - Email specific range and attach Sheet1
Replies
4
Views
572
Clermont
C
G
  • Question
Email from Excel query
Replies
0
Views
199
gmazza76
G
A
  • Question
Ron de Bruin VBA
Replies
2
Views
1K
Alexfoots
A
J
  • Question
Kill Temp File
Replies
1
Views
378
ExcelToDAX
E
J
  • Question
Outlook Hypperlink of fila in email body will not open
Replies
1
Views
465
josros60
J

Forum statistics

Threads
1,215,862
Messages
6,127,386
Members
449,382
Latest member
DonnaRisso

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top