If statements in macros

[Dan Wood]

I have a macro to send an automated email when passwords need renewing. This
all works fine, but what i want to do is set up another macro to run
automatiocally at 00:00:01 to check if any passwords need changing.

What i have so far is this:-

Sub CheckDay()
Application.OnTime TimeValue("17:00:00"), "my_Procedure"
Range("D730").Select
If Range = 5 Then
Sub SendEmail()
Else
End If
End Sub

So what i want is if a cell in range D7 - D30 is 5 then sub the macro
SendEmail.

For some reason it is not workign and i can't work out why. Any help would
be much appreiciated.

 

[Mike H]

Hi,

Try this

Sub CheckDay()
Application.OnTime TimeValue("17:00:00"), "my_Procedure"
For Each c In Range("D730")
If c.Value = 5 Then
Call SendEmail
End If
Next
End Sub

Mike

 

[Dan Wood]

Genius!!

Worked first time. Thank you

Hi,

Try this

Sub CheckDay()
Application.OnTime TimeValue("17:00:00"), "my_Procedure"
For Each c In Range("D730")
If c.Value = 5 Then
Call SendEmail
End If
Next
End Sub

Mike

 

[Mike H]

Glad I could help and thanks for the feedback

Genius!!

Worked first time. Thank you

 

[Dan Wood]

Just two more quick (hopefully) questions:-

1 - I have the macro to ceate the email and input all the required details,
but can't get it to actually send. Is there an extra line i need at the
bottom to actually send?

2 - Can i get these macro's to run even if the sheet isn't open? From what i
have found i don't think i can, but the point of the spreadsheet is to alert
users when there passwords ar edue to expire, so in a perfect world it would
run every day shortly after midnight.

Thanks

 

[Dan Wood]

One more question. If i want the email to specify which system needs the new
password how would i do this?

Currently the email just has some text to say your password needs resetting.

The example of the sheet would be the systems in colum A, in colum B has
whether the password has expired. Therefore i would want the macro to run and
say systems A3, A6 and A9 for example have expired?

If this is very compicated it doesn't matter to much.

Thanks again

 

[Mike H]

Hi,

For sending email have a look here

http://www.rondebruin.nl/sendmail.htm

With regard to which system need the new mail you will have to pass a
parameter to your sendmail code like this. I gues the 5 is what triggers the
requirement so somewhere else and we'll assume column D you identify the
system so the code becomes


Public SySname
Sub CheckDay()

Application.OnTime TimeValue("17:00:00"), "my_Procedure"
For Each c In Range("D730")
If c.Value = 5 Then
SySname = c.Offset(, 1).Value
SendEmail SySname

End If
Next
End Sub

Sub SendEmail(SySname)
MsgBox SySname
End Sub

Mike

 

[Dan Wood]

Thanks for the reply. Dont think i have explained myself properly. The list
of systems is in column A, and the trigger for the email is in column D.

The important part of the macro to send the email is as follows:-

Msg = Msg & "Hi" & Cells(ActiveCell.Row, 6) & "," & vbCrLf & vbCrLf & "One
of your AS400 passwords is due to expire. Please log on and change your
password" & vbCrLf & vbCrLf & "Once you have done this please update the
spreadsheet to reflect the new password, and the date it was changed."

All on one line. I think there needs to be something inserted between the
two strings of proper text, which will just copy the system names.

I have tried adding an extra column on the end just to say the system name
if the password has expired using an IF formula. With this is have tried
using the following code:-

Range("G10")

I can't seem to get it to work with a proper range eg RANGE("G7:G30"). Not
sure why this won't pick up the range.

Thanks

 

[Mike H]

Dan,

You simply change the offset, this now returns column A

SySname = c.Offset(, -3).Value

Mike

 

[Dan Wood]

Im/You are very close to the macro working as i want now. It opens the
correct amount of emails for the systems, but how do i refer the second macro
SendEmail to the system name, either as the subject which is currently:-

Subj = Range("a1")

With the field A1 just being the title of the spreadsheet.

Thanks again for all your help it is much appreciated

 

[Dan Wood]

Please ignore the last comment i have worked it out. All i need to find out
now is how to send the email automatically.
Any clues?

Thanks