+ Reply to Thread
Results 1 to 4 of 4

Prevent macro working on all open workbooks

Hybrid View

Atik Prevent macro working on all... 12-29-2017, 12:23 AM
TMS Re: Prevent macro working on... 12-29-2017, 12:51 AM
Atik Re: Prevent macro working on... 12-29-2017, 12:57 AM
TMS Re: Prevent macro working on... 12-29-2017, 01:12 AM
  1. #1
    Registered User
    Join Date
    11-21-2014
    Location
    bangladesh
    MS-Off Ver
    2013
    Posts
    33

    Prevent macro working on all open workbooks

    Hi, thanks for viewing my post. I am using this code for the workbook named “Dashboard.xlsm” and the sheet is “Dashboard” to send mail if the condition met periodically (every after 15 minutes).
    It’s working fine but when opening two or more workbooks and working with them keep running workbook “Dahsboard.xlsm” background then the code reading values from another opened workbook. Although I have specified the sheet name. what’s wrong here?

    Sub send_email()
    Dim r As Long
    Dim DB As Worksheet
    Set DB = ThisWorkbook.Sheets("Dashboard")
    
    Set cdomsg = CreateObject("CDO.message")
    With cdomsg.Configuration.Fields
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 'NTLM method
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
    .Item("http://schemas.microsoft.com/cdo/configuration/smptserverport") = 587
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "dashboardnotification@gmail.com"
    .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "abcd1234~~~a"
    .Update
    End With
    
    For r = 13 To 17
       If DB.Cells(r, 9) > 0.25 Then
          With cdomsg
          .To = "atikrobian@gmail.com"
          .From = "dashboardnotification@gmail.com"
          .Subject = Cells(r, 1) & " profit margin is above 25%"
          .send
          End With
       End If
    Next
        Set cdomsg = Nothing
        
        Call Schedulesendemail
    End Sub
    Sub Schedulesendemail()
        TimeToRun = Now + TimeValue("00:15:15")
        Application.OnTime TimeToRun, "send_email"
    End Sub

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,596

    Re: Prevent macro working on all open workbooks

    Try changing this
          .Subject = Cells(r, 1) & " profit margin is above 25%"
    to this
          .Subject = DB.Cells(r, 1) & " profit margin is above 25%"
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    11-21-2014
    Location
    bangladesh
    MS-Off Ver
    2013
    Posts
    33

    Re: Prevent macro working on all open workbooks

    Hi, thanks for your help it works fine now.
    thank you.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,596

    Re: Prevent macro working on all open workbooks

    You're welcome. Thanks for the rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macro to open workbooks and resize columns not working
    By maym in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-08-2016, 09:39 PM
  2. open all workbooks not working
    By ammartino44 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-28-2015, 05:47 PM
  3. [SOLVED] Dir () , Workbooks.Open stopped working
    By vlookup. in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-28-2015, 05:25 PM
  4. Prevent new workbooks to open in same instance
    By c00ly81 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-27-2015, 07:42 AM
  5. [SOLVED] How to prevent macro from running in unintended workbooks
    By siray13 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-07-2012, 09:37 AM
  6. Macro to close all open workbooks save two not working
    By drajanm in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-18-2011, 05:58 AM
  7. [SOLVED] Problem with Macro working in new workbooks that I open
    By chris_rip in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-29-2005, 03:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1