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
Bookmarks