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

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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

earthworm
  • Question Question
Autofit table in Outlook upon copying from Excel sheet using Macro
Replies
1
Views
145
KOKOSEK
KOKOSEK
M
  • Question Question
Email file VBA - change attachment from excel to PDF?
Replies
2
Views
232
MOB
M
D
  • Solved
VBA to paste table into Outlook
Replies
5
Views
678
Dani_LobP
D
L
  • Question Question
Sending Email with Table in Body
Replies
3
Views
509
Domenic
D
D
  • Question Question
VBA to Create Outlook Email Not Working for All Users
Replies
9
Views
282
Logit
Logit

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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