Results 1 to 10 of 10

Thread: Automatic emailing

  1. #1

    Automatic emailing



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 attached
    Smart ledger 1.0.xlsm

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,778
    Articles
    0
    Excel Version
    365
    Cut all the code in the Class1 code-module and paste it into Sheet1's code-module. Remove the Class1 code-module.

  3. #3
    I tried that. Still doesnt work

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,778
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by jrutt17 View Post
    I tried that. Still doesnt work
    So much information to go on.

  5. #5
    Quote Originally Posted by p45cal View Post
    So much information to go on.
    As in i'm still getting same exact errors.

  6. #6
    Acolyte jaslake's Avatar
    Join Date
    Aug 2011
    Location
    mineral city oh usa
    Posts
    81
    Articles
    0
    Excel Version
    2007;2010;MAc2011
    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.
    John

  7. #7
    Quote Originally Posted by jaslake View Post
    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.
    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.

  8. #8
    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!!!!

  9. #9
    Acolyte jaslake's Avatar
    Join Date
    Aug 2011
    Location
    mineral city oh usa
    Posts
    81
    Articles
    0
    Excel Version
    2007;2010;MAc2011
    Hi Jacob

    What will trigger running of the Code?
    John

  10. #10
    Quote Originally Posted by jaslake View Post
    Hi Jacob

    What will trigger running of the Code?
    Fixed it. This is solved. Thanks!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •