Timed VBA Mail code

[vwsteven]

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?

[VBA]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[/VBA]


Thankx a lot

[mdmackillop]

Seems to work. Try it with a simpler code

[VBA]Sub MailObsdates()
MsgBox "test"
End Sub
[/VBA]

[vwsteven]

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]

Is all the code in a standard module?

[vwsteven]

yea everything is in standard mode

[mdmackillop]

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?

[GTO]

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]

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?

[GTO]

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]

[IMG]file:///C:/DOCUME%7E1/vanwinst/LOCALS%7E1/Temp/moz-screenshot.png[/IMG]Hey. 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]

and on the top it has General written

[vwsteven]

the code is written in a normal sheet, so not in a module or in "ThisWorkbook"

[GTO]

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]

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