macro/vba code error

M

mingandmong

Active Member
Joined
Oct 15, 2014
Messages
339
Hi

I posted this a week ago and unfortunatley it was never resolved sorry for re-posting or cross threading

I have a workbook with 20 sheets and only want the code to run when the sheet "Email" triggers the value, i'm receiving a compile error.. expected end of stament on the txt highlighted in red
The workbooks consist of a pivot table and the sheet Email has the formula that just looks at another sheet and uses the same value ie ='sheet1 B'!D2
When the Email sheet B2:B18 exceeds value 0.9 then it calls the vba module code in 2nd below and sends out an email from the Email sheet from column E of preset emails
I only want the first routine to trigger only from the Email sheet when the value exeeds 0.9, thankyou for any assistance and help..Dean

Code: 
Option Explicit

[COLOR=#ff0000]Private Sub Sheets "Email" _Calculate()[/COLOR]
    Dim FormulaRange As Range
    Dim NotSentMsg As String
    Dim MyMsg As String
    Dim SentMsg As String
    Dim MyLimit As Double

    NotSentMsg = "Not Sent"
    SentMsg = "Sent"

    'Above the MyLimit value it will run the macro
    MyLimit = 0.9

    'Set the range with Formulas that you want to check
    Set FormulaRange = Me.Range("B3:B18")

    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_outlook
                    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

Code: 
Option Explicit

Public FormulaCell As Range

Sub Mail_with_outlook()
'For mail code examples visit my mail page at:
'http://www.rondebruin.nl/sendmail.htm
    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, "E").Value
    strcc = ""
    strbcc = ""
    strsub = "Incorrect Lunch Swipes"
    strbody = "Hi " & Cells(FormulaCell.Row, "A").Value & vbNewLine & vbNewLine & _
              "You Have A ******* Employee Exceeding 6 Incorrect Lunch Swipes : " & Cells(FormulaCell.Row, "B").Value & _
              vbNewLine & vbNewLine & "Please Review And Action If Necessary"

    With OutMail
        .To = strto
        .CC = strcc
        .BCC = strbcc
        .Subject = strsub
        .Body = strbody
        'You can add a file to the mail like this
        '.Attachments.Add ("C:\test.txt")
        .Display    ' or use .Send
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
As far as the offending line goes (haven't looked at the rest of your code), you can't have spaces or quote marks in the name of a sub. Try:
Code: 
Sub SheetsEmail_Calculate()
 
Upvote 0
Hi Joe
This now calls in the macro and vba code, however the the sent/not sent sent does not change when i alter the value back under the 0.9 threshold, please see below the red txt, the test data for example Dean should read sent, because it exceeds 0.9 and the data for Richard is zero so should read not sent, any ideas what has now caused this ? i have included the full code with your mods below

Dean
1Not Sent1debra@something.abc
Tony 0Not Sent0debra@something.abc
Tony 0Not Sent0debra@something.abc
Don 0Not Sent0debra@something.abc
Lynsey0Not Sent0gord@something.abc
John0Not Sent0gord@something.abc
Rob0Not Sent0gord@something.abc
Phil 0Not Sent0gord@something.abc
Chris 0Not Sent0gord@something.abc
Richard
0Sent0gord@something.abc
Bill 0Not Sent0gord@something.abc
Darren 0Not Sent0gord@something.abc
Collin 0Not Sent0gord@something.abc
Dean0Not Sent0gord@something.abc
Gary0Not Sent0gord@something.abc
Brian 0Not Sent0gord@something.abc

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

Code: 
        Option Explicit

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

            NotSentMsg = "Not Sent"
            SentMsg = "Sent"

            'Above the MyLimit value it will run the macro
            MyLimit = 0.9

            'Set the range with Formulas that you want to check
            Set FormulaRange = Me.Range("B3:B18")

            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_outlook
                            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

Code: 
        Option Explicit

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

            NotSentMsg = "Not Sent"
            SentMsg = "Sent"

            'Above the MyLimit value it will run the macro
            MyLimit = 0.9

            'Set the range with Formulas that you want to check
            Set FormulaRange = Me.Range("B3:B18")

            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_outlook
                            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
 
Upvote 0
I'd like to help, but don't understand what you want to accomplish with your code or the layout of your sheet (row numbers and column letters are not shown in your post), I also don't see how your code can run with this line:

Set FormulaRange = Me.Range("B3:B18")

which seems to be an inappropriate use of the Me keyword.
 
Upvote 0
hi Joe
Thankyou for your imput, the origanal code is by Ron de bruin and here is a link to the origanl file,below is the origan code that i attempted to moddify Send a mail when a cell reaches a certain value
i opted to use the more than one valuechange tab, the html page states "Test this example macro to create/display a Outlook mail with a small text message.
You must copy this macro in a standard module and not in the worksheet module, see this page how.

Note: I use .Display in the code to display the mail, you can change that to .Send

Do not forget to change Call YourMacroName to Call Mail_small_Text_Outlook in the Change event."
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 = "Not Sent"
    SentMsg = "Sent"

    'Above the MyLimit value it will run the macro
    MyLimit = 200

    'Set the range with Formulas that you want to check
    Set FormulaRange = Me.Range("B3:B7")

    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

Code: 
Option Explicit

Public FormulaCell As Range

Sub Mail_with_outlook1()
'For mail code examples visit my mail page at:
'http://www.rondebruin.nl/sendmail.htm
    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 = "ron@something.abc"
    strcc = ""
    strbcc = ""
    strsub = "Customers"
    strbody = "Hi Ron" & vbNewLine & vbNewLine & _
              "The total Customers of all stores this week is : " & Cells(FormulaCell.Row, "B").Value & _
              vbNewLine & vbNewLine & "Good job"

    With OutMail
        .To = strto
        .CC = strcc
        .BCC = strbcc
        .Subject = strsub
        .Body = strbody
        'You can add a file to the mail like this
        '.Attachments.Add ("C:\test.txt")
        .Display    ' or use .Send
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Sub Mail_with_outlook2()
'For mail code examples visit my mail page at:
'http://www.rondebruin.nl/sendmail.htm
    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, "K").Value
    strcc = ""
    strbcc = ""
    strsub = "Your subject"
    strbody = "Hi " & Cells(FormulaCell.Row, "A").Value & vbNewLine & vbNewLine & _
              "Your total of this week is : " & Cells(FormulaCell.Row, "B").Value & _
              vbNewLine & vbNewLine & "Good job"

    With OutMail
        .To = strto
        .CC = strcc
        .BCC = strbcc
        .Subject = strsub
        .Body = strbody
        'You can add a file to the mail like this
        '.Attachments.Add ("C:\test.txt")
        .Display    ' or use .Send
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub
 
Upvote 0
mingandmong said:
hi Joe
Thankyou for your imput, the origanal code is by Ron de bruin and here is a link to the origanl file,below is the origan code that i attempted to moddify Send a mail when a cell reaches a certain value
i opted to use the more than one valuechange tab, the html page states "Test this example macro to create/display a Outlook mail with a small text message.
You must copy this macro in a standard module and not in the worksheet module, see this page how.

Note: I use .Display in the code to display the mail, you can change that to .Send

Do not forget to change Call YourMacroName to Call Mail_small_Text_Outlook in the Change event."
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 = "Not Sent"
    SentMsg = "Sent"

    'Above the MyLimit value it will run the macro
    MyLimit = 200

    'Set the range with Formulas that you want to check
    Set FormulaRange = Me.Range("B3:B7")

    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

Code: 
Option Explicit

Public FormulaCell As Range

Sub Mail_with_outlook1()
'For mail code examples visit my mail page at:
'http://www.rondebruin.nl/sendmail.htm
    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 = "ron@something.abc"
    strcc = ""
    strbcc = ""
    strsub = "Customers"
    strbody = "Hi Ron" & vbNewLine & vbNewLine & _
              "The total Customers of all stores this week is : " & Cells(FormulaCell.Row, "B").Value & _
              vbNewLine & vbNewLine & "Good job"

    With OutMail
        .To = strto
        .CC = strcc
        .BCC = strbcc
        .Subject = strsub
        .Body = strbody
        'You can add a file to the mail like this
        '.Attachments.Add ("C:\test.txt")
        .Display    ' or use .Send
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Sub Mail_with_outlook2()
'For mail code examples visit my mail page at:
'http://www.rondebruin.nl/sendmail.htm
    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, "K").Value
    strcc = ""
    strbcc = ""
    strsub = "Your subject"
    strbody = "Hi " & Cells(FormulaCell.Row, "A").Value & vbNewLine & vbNewLine & _
              "Your total of this week is : " & Cells(FormulaCell.Row, "B").Value & _
              vbNewLine & vbNewLine & "Good job"

    With OutMail
        .To = strto
        .CC = strcc
        .BCC = strbcc
        .Subject = strsub
        .Body = strbody
        'You can add a file to the mail like this
        '.Attachments.Add ("C:\test.txt")
        .Display    ' or use .Send
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub
Click to expand...

The code you have tried to modify is worksheet event code - not a standard module - and your modification is not appropriate. You may want to go back to the source (the link you posted) and follow the instructions there.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
How to combine similar Private Sub Worksheets?
Replies
3
Views
316
Rita9091
R
S
  • Solved
Insert Cell Value Into Email
Replies
2
Views
696
smythcounty
S
N
  • Solved
Excel VBA Add graphic signature block to automated email.
Replies
2
Views
332
NormThib
N
The Ruff Report
  • Question
Help adjusting Macro to email multiple cc addresses
Replies
1
Views
382
The Ruff Report
The Ruff Report
The Ruff Report
  • Question
Add multiple attachments from email list
Replies
1
Views
280
Trebor76
T

Forum statistics

Threads
1,215,050
Messages
6,122,868
Members
449,097
Latest member
dbomb1414

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