Automating Email

[EliseFreedman]

Hi there

I am using the code below to send email through excel. It works like a dream apart from the fact that It opens up every email and waits for me to go into the message and hit the send button. I would like it to just send all the messages automatically without any intervention from the user. Can anyone tell me where im going wrong

Elise

Sub SendOutlookEmail()
Dim objol As New Outlook.Application
Dim objmail As MailItem
Set objol = New Outlook.Application

Dim r As Integer, x As Double
Sheets("Actions Tracking Report").Select
For r = 2 To 40
If Cells(r, 4).Text = "Actions Outstanding" Then

Set objmail = objol.createitem(olmailitem)
    With objmail
        .To = Cells(r, 7)
        .Subject = "Outstanding Actions from HRO Reference " & Cells(r, 5).Text
        .Body = "Dear " & Cells(r, 8) & _
            vbCrLf & vbCrLf & "I am emailing to advise you that you have the following action outstanding " & vbCrLf & vbCrLf _
            & Cells(r, 6).Text & vbCrLf & vbCrLf & _
            "Please complete the action as soon as possible" & vbCrLf & vbCrLf & _
            "Please ensure that on completing your action, you update the actions status in the Smarter Database by opening up the link below" & vbCrLf & vbCrLf & _
            "[URL unfurl="true"]http://appsvr1.collaborate.sharelnk.net/sMARTer2/"[/URL] & vbCrLf & vbCrLf & _
            "The action should be completed by " & Cells(r, 3).Text & vbCrLf & vbCrLf & _
            "Thank you for your cooperation " & vbCrLf & vbCrLf & _
            "Elise Freedman" & vbCrLf & vbCrLf & _
            "Health and Safety Department"
       
        .NoAging = True
   .display
    End With
    Set objmail = Nothing
    Set objol = Nothing
    SendKeys "%{s}", True 'send the email without prompts
    
    End If
Next r
End Sub

 

[SjrH]

Switch

.display

for

.Send

[small]have you turned it off and on again?[/small]

 

[EliseFreedman]

Hi There

Ive tried changing .display to .send but it now displays the outlook security prompt. I am trying to get the system so that It doesnt have any prompts etc and therefore doesnt require any intervention from the user.

Does anyone have any other suggestions?

 

[Grenage]

Absolutely. Go with SjrH's suggestion, that's definitely what you want. As for dealing with the security message, you can get round that with a free outlook utility:

http://www.mapilab.com/outlook/security/

We use this for a near-identical purpose.

"We can categorically state that we have not released man-eating badgers into the area" - Major Mike Shearer

 

[SjrH]

You can bypass outlook completely and use CDO to send your email directly through your SMTP host. You'll need to re-configure your script to do it, but its quite easy to do.

Just google 'cdo send email', theres tons of sample scripts you could modify.

[small]have you turned it off and on again?[/small]

 

[longhair]

you can also use blat.
you should be able to keep your scritp - you would just need a copy of blat (free off the net) and then a few lines of code in a public sub, then call the public sub at the end of your current script.
regards,
longhair