View Full Version : Timed VBA Mail code
vwsteven
02-15-2011, 01:21 PM
Hi guys
I'm trying to write a code to send an automatic mail if some conditions are met and on a specific time. However when I call the sub which then is supposed to launch the mail sub, it keeps giving me a message that it can't find my MailObsdates code.
Can you help me with this please?
Sub MailObsdates()
'ActiveSheet.Calculate
Dim OutApp As Object
Dim OutMail As Object
Dim i As Integer
Dim z, a As String
Dim Subject As String
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
On Error GoTo cleanup
For i = 3 To 500
If Sheet1.Cells(i, 1) = Sheet1.Cells(i, 8) And Sheet1.Cells(i, 12) = "No" Then
w = Sheet1.Cells(i, 11)
z = Sheet1.Cells(i, 10)
a = Sheet1.Cells(i, 13)
b = Sheet1.Cells(i, 14)
c = Sheet1.Cells(i, 15)
d = Sheet1.Cells(i, 16)
strbody = "Dear "
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = w
.CC = a
.BCC = b
.Subject = "hello " & Sheet1.Cells(i, 3) & " "
.Body = strbody
.send
End With
On Error GoTo 0
Set OutMail = Nothing
Sheet1.Cells(i, 12) = "Yes"
Else
End If
Next i
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
Call TimeApp
End Sub
Sub TimeApp()
ActiveSheet.Calculate
Application.OnTime TimeValue("18:16:00"), ("MailObsdates")
End Sub
Thankx a lot
mdmackillop
02-15-2011, 01:34 PM
Seems to work. Try it with a simpler code
Sub MailObsdates()
MsgBox "test"
End Sub
vwsteven
02-15-2011, 01:37 PM
yea the code itself works and the timer works too now but when the timer executes, I keep getting a message that my Sub MailObsdates cant be found. Any ideas?
mdmackillop
02-15-2011, 01:43 PM
Is all the code in a standard module?
vwsteven
02-16-2011, 01:40 AM
yea everything is in standard mode
mdmackillop
02-16-2011, 02:12 AM
I tried your code on another PC with no issues and I cant't think why this should fail. Is your macro security set to High?
yea everything is in standard mode
Hi there,
I'm with Malcom, it sounds strange. I know you said "yea", but what do you mean "standard mode"? Is the code in a Standard Module, or might you accidently have it in a worksheet module, or other object or class module?
vwsteven
02-17-2011, 02:53 AM
Hey. so it worked once now with the timer but I was hoping it to run daily right. but this doesnt seem to work. any suggestions?
how do I see what mode it is in? on the top of my code it says general. this is what you mean right?
Hey. so it worked once now with the timer but I was hoping it to run daily right. but this doesnt seem to work. any suggestions? how do I see what mode it is in? on the top of my code it says general. this is what you mean right?
Why do you keep saying "mode"? Are you asking if the code is now in the correct type of module? Strip any personal/private data from the wb in question and attach it so that we can see.
vwsteven
02-17-2011, 03:25 AM
file:///C:/DOCUME%7E1/vanwinst/LOCALS%7E1/Temp/moz-screenshot.pngHey. this is what I have
Sub MailObsdates()
ActiveSheet.Calculate
Dim OutApp As Object
Dim OutMail As Object
Dim i As Integer
Dim z, a As String
Dim Subject As String
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
On Error GoTo cleanup
For i = 3 To 500
If Sheet1.Cells(i, 1) = Sheet1.Cells(i, 8) And Sheet1.Cells(i, 12) = "No" Then
w = Sheet1.Cells(i, 11)
z = Sheet1.Cells(i, 10)
a = Sheet1.Cells(i, 13)
b = Sheet1.Cells(i, 14)
c = Sheet1.Cells(i, 15)
d = Sheet1.Cells(i, 16)
strbody = "Dear "
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = w
.CC = a
.BCC = b
.Subject = "hello"
.Body = strbody
.send
End With
On Error GoTo 0
Set OutMail = Nothing
Sheet1.Cells(i, 12) = "Yes"
Else
End If
Next i
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
Call TimeApp
End Sub
Sub TimeApp()
ActiveSheet.Calculate
Application.OnTime TimeValue("11:00:00"), "Sheet1.MailObsdates"
End Sub
vwsteven
02-17-2011, 03:26 AM
and on the top it has General written
vwsteven
02-17-2011, 03:37 AM
the code is written in a normal sheet, so not in a module or in "ThisWorkbook"
the code is written in a normal sheet, so not in a module or in "ThisWorkbook"
Hi Steven,
The point is that those are all modules. When you say "written in a normal sheet", that means that you put the code in a worksheet's module, which can be referred to as an object module. Anyways, this is why Malcom was asking as to the Standard Module.
Insert a Standard Module, and you won't need to qualify the called procedure.
To attach a file (pictures are not all that helpful), look below the 'Quick Reply' box and press <Go Advanced>.
In the new window, scroll down and find <Manage Attachments>. It is self-explanatory thereafter.
In the below pic, see how in the properties window you can see that Module1 is selected? See how it's in a folder named Modules? This is where Standard Modules are kept. Hope that helps
Mark
vwsteven
02-17-2011, 08:49 AM
Allright. tahnk you for explaining.
I've written the following now. Do you think this is enough for a loop? what I'm doing by this heopefully is that one Sub always keeps calling the other one. Is that the way to do it??
Sub MailObsdates()
ActiveSheet.Calculate
Dim OutApp As Object
Dim OutMail As Object
Dim i As Integer
Dim z, a As String
Dim Subject As String
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
On Error GoTo cleanup
For i = 3 To 500
If Sheet1.Cells(i, 1) = Sheet1.Cells(i, 8) And Sheet1.Cells(i, 12) = "No" Then
w = Sheet1.Cells(i, 11)
z = Sheet1.Cells(i, 10)
a = Sheet1.Cells(i, 13)
b = Sheet1.Cells(i, 14)
c = Sheet1.Cells(i, 15)
d = Sheet1.Cells(i, 16)
strbody = "Dear "
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = w
.CC = a
.BCC = b
.Subject = "hi"
.Body = strbody
.send
End With
On Error GoTo 0
Set OutMail = Nothing
Sheet1.Cells(i, 12) = "Yes"
Else
End If
Next i
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
Application.OnTime TimeValue("11:05:00"), "Sheet1.TimeApp"
End Sub
Sub TimeApp()
ActiveSheet.Calculate
Application.OnTime TimeValue("11:00:00"), "Sheet1.MailObsdates"
End Sub
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.