Macro Help


Dan Wood

My code is as follows:-

Sub SendEmail()
Dim Email As String, Subj As String
Dim Msg As String, URL As String
Email = Range("A5")
For Each c In Range("D7:D30")
If c.Value = 0 Then
SySname = c.Offset(, -3).Value
Subj = SySname

Msg = ""
Msg = Msg & "Hi" & Cells(ActiveCell.Row, 6) & "," & vbCrLf & vbCrLf &
"Your AS400 password is due to expire on the above mentioned system. 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."

'Replace spaces with %20 (hex)
Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")

'Replace carriage returns with %0D%0A (hex)
Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")

'Create the URL
URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg

'Execute the URL (start the email client)
ShellExecute O&, vbNullString, URL, vbNullString, vbNullString,

'Wait two seconds before sending keystrokes
'Application.Wait (Now + TimeValue("0:00:02"))
'Application.SendKeys "%s"

End If

However, when i try to run the script it flags up an error in the
ShellExecute part and i cannot figure out why.

Any help much appreciated

Dan Wood

Thanks for the advice but i don't really want to take that route as this
spreadsheet is going to be used as a template for lots of different users to
have to monitor passwords expiring.

The aim is for it to pick up the email address from cell A5.

Any ideas on what i need to change?


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question