Automatic emailing

J

jrutt17

New member
Joined
Jun 30, 2015
Messages
6
Reaction score
0
Points
0
Hello,

I am trying to get excel to email a sales rep when their client account is nearing expiration. I have been through all of the forums and help sites trying to figure this out and I keep getting the same error, "error 91: object variable not set yet," I definitely did set it. Additioanlly, the mnacro deosnt recognize when the value of the specified cell goes above the limit i set it at. my code is reproduced below. I would appreciate any and all help. This is my second day ever dealing with VBA so thank you in advacne for your patience in dealing with me. I have attached the excel file as well.

regards,
Jacob
Code: 
Option Explicit



Private Sub Worksheet_Calculate()
    Dim FormulaRange As Range
    Dim NotSentMsg As String
    Dim MyMsg As String
    Dim SentMsg As String
    Dim MyLimit As Double


    NotSentMsg = "No"
    SentMsg = "Yes"


   
    MyLimit = 1


   
    Set FormulaRange = Me.Range("m4:m10")


    On Error GoTo EndMacro:
    For Each FormulaCell In FormulaRange.Cells
        With FormulaCell
            If IsNumeric(.Value) = False Then
                MyMsg = "Not numeric"
            Else
                If .Value > MyLimit Then
                    MyMsg = SentMsg
                    If .Offset(0, 1).Value = NotSentMsg Then
                        Call Mail_with_outlook2
                    End If
                Else
                    MyMsg = NotSentMsg
                End If
            End If
            Application.EnableEvents = False
            .Offset(0, 1).Value = MyMsg
            Application.EnableEvents = True
        End With
    Next FormulaCell


ExitMacro:
    Exit Sub


EndMacro:
    Application.EnableEvents = True


    MsgBox "Some Error occurred." _
         & vbLf & Err.Number _
         & vbLf & Err.Description



End Sub


and the email code is:
Code: 
Option Explicit

Public FormulaCell As Range




Sub Mail_with_outlook2()


    Dim OutApp As Object
    Dim OutMail As Object
    Dim strto As String, strcc As String, strbcc As String
    Dim strsub As String, strbody As String


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


    strto = Cells(FormulaCell.Row, "P").Value
    strcc = ""
    strbcc = ""
    strsub = "Your subject"
    strbody = ""


    With OutMail
        .To = strto
        .CC = strcc
        .BCC = strbcc
        .Subject = strsub
        .Body = strbody
        .Display
    End With


    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub


The worksheet is attachedView attachment Smart ledger 1.0.xlsm
 
Cut all the code in the Class1 code-module and paste it into Sheet1's code-module. Remove the Class1 code-module.
 
Hi Jacob

As p45cal suggested, move the Code to a Standard Module and ditch the Class Module.

Then change these three lines of Code:
Code: 
'Private Sub Worksheet_Calculate()
Sub Worksheet_Calculate()
Code: 
Set FormulaRange = Sheets("Sheet1").Range("m4:m10")
   'Set FormulaRange = Me.Range("m4:m10")
Code: 
If .Value = MyLimit Then
               'If .Value > MyLimit Then

Complete Code...
Code: 
Option Explicit

'Private Sub Worksheet_Calculate()
Sub Worksheet_Calculate()
   Dim FormulaRange As Range
   Dim NotSentMsg   As String
   Dim MyMsg        As String
   Dim SentMsg      As String
   Dim MyLimit      As Long
   NotSentMsg = "No"
   SentMsg = "Yes"

   MyLimit = 1

   Set FormulaRange = Sheets("Sheet1").Range("m4:m10")
   'Set FormulaRange = Me.Range("m4:m10")
   On Error GoTo EndMacro:
   For Each FormulaCell In FormulaRange.Cells
      With FormulaCell
         If IsNumeric(.Value) = False Then
            MyMsg = "Not numeric"
         Else
            If .Value = MyLimit Then
               'If .Value > MyLimit Then
               MyMsg = SentMsg
               If .Offset(0, 1).Value = NotSentMsg Then
                  Call Mail_with_outlook2
               End If
            Else
               MyMsg = NotSentMsg
            End If
         End If
         Application.EnableEvents = False
         .Offset(0, 1).Value = MyMsg
         Application.EnableEvents = True
      End With
   Next FormulaCell
ExitMacro:
   Exit Sub
EndMacro:
   Application.EnableEvents = True
   MsgBox "Some Error occurred." _
          & vbLf & Err.Number _
          & vbLf & Err.Description
End Sub
Works for me.
 
jaslake said:
Hi Jacob

As p45cal suggested, move the Code to a Standard Module and ditch the Class Module.

Then change these three lines of Code:
Code: 
'Private Sub Worksheet_Calculate()
Sub Worksheet_Calculate()
Code: 
Set FormulaRange = Sheets("Sheet1").Range("m4:m10")
   'Set FormulaRange = Me.Range("m4:m10")
Code: 
If .Value = MyLimit Then
               'If .Value > MyLimit Then

Complete Code...
Code: 
Option Explicit

'Private Sub Worksheet_Calculate()
Sub Worksheet_Calculate()
   Dim FormulaRange As Range
   Dim NotSentMsg   As String
   Dim MyMsg        As String
   Dim SentMsg      As String
   Dim MyLimit      As Long
   NotSentMsg = "No"
   SentMsg = "Yes"

   MyLimit = 1

   Set FormulaRange = Sheets("Sheet1").Range("m4:m10")
   'Set FormulaRange = Me.Range("m4:m10")
   On Error GoTo EndMacro:
   For Each FormulaCell In FormulaRange.Cells
      With FormulaCell
         If IsNumeric(.Value) = False Then
            MyMsg = "Not numeric"
         Else
            If .Value = MyLimit Then
               'If .Value > MyLimit Then
               MyMsg = SentMsg
               If .Offset(0, 1).Value = NotSentMsg Then
                  Call Mail_with_outlook2
               End If
            Else
               MyMsg = NotSentMsg
            End If
         End If
         Application.EnableEvents = False
         .Offset(0, 1).Value = MyMsg
         Application.EnableEvents = True
      End With
   Next FormulaCell
ExitMacro:
   Exit Sub
EndMacro:
   Application.EnableEvents = True
   MsgBox "Some Error occurred." _
          & vbLf & Err.Number _
          & vbLf & Err.Description
End Sub
Works for me.
Click to expand...

Wow. thank you. This ddefinitely works much better. Howevre, it still does not automaticcaly sense whether it should send an email or not. I still have to actually run the macro in order for it to notice and it only selects the first name on the list.
 
Ok. I fixed the selection error. Now I just need it run constantly in background without me having to manually run the macro. How can I do that? Thank you again for all of your help!!!!
 
You must log in or register to reply here.
Back
Top