XL Send Email - Suggestions

[longhair]

afternoon all,
i have an excel macro that sends multiple emails (multiple calls to my mailer sub - thanks for the FAQ - it was of much help). the multiple calls to the mailer sub will pass different arguments based upon info in the spreadsheet.
the issue is that the outlook object does not seem to always have focus when the sendkeys line is executed.
any suggestions? i've already played with appactivate to no avail.
the application.wait does help but still not all of the time.

...
Call Mailer(txtSubject, txtPath, txtSendTo)
...
Sub Mailer(txtSubject As String, txtPath As String, txtSendTo As String)
Sheets("dsgm862").Select
pathname = txtPath 'defines attachment
Dim objol As New Outlook.Application
Dim objmail As MailItem
Set objol = New Outlook.Application
Set objmail = objol.createitem(olmailitem)

With objmail
        .To = txtSendTo 'enter in here the email address
        '.cc = "whoever" 'enter in here the email address
        .Subject = txtSubject
        '.Body = "Please find attached the teste email" & _
         vbCrLf & "If you have any queries can you please let me know" & vbCrLf
        .Attachments.Add pathname 'adds attachment to email
        .display
    End With
    'Set objmail = Nothing
    'Set objol = Nothing
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 2
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
SendKeys "%{s}", True 'send the email without prompts
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 3
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
Set objmail = Nothing
Set objol = Nothing
AppActivate "Microsoft Excel"
End Sub

any and all suggestions are welcome
regards,
longhair

 

[firefytr]

Hi there,

Check out Outlook Redemption. SendKeys method is frail and error prone.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[CautionMP]

longhair,
Along the same lines, check out Collaboration Data Objects (CDO), same idea as Redemption but supplied my Microsoft (for better or worse).

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[firefytr]

Oh, and another great addition to Outlook is OutlookSpy. (And I'll second the CDO. )

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[JerryKlmns]

I have to use that ugly SendKeys "%{s}", which some times (when I click on explorer to see attached files travelling to a folder), adds an "s" to the body and doesn't send the bloody mail. In my process I exterminate objol on x steps of the loop, and every that moment I get prompt, Tab (selecting the No) Enter (mail travells).

But the post is not to share my annoying situation, just to suggest that you should add this line

objol.Quit

just before you destroy the objol

---------
longhair, you 'r' not alone out there

 

[longhair]

all,
since outlook redemption is not currently an option and i'd rather not use cdo (don't have the time to rewrite / learn / debug & it's "by microsoft for better or worse" just went with a solution already inhouse.
just a simple call to blat and no more problemos.
thanks for all the advice.
if anyone wants to see the code just post back.
regards,
longhair

 

[xlbo]

Have a look at "ClickYes"

small prog that sits in your tray and basically presses ok for you whilst its on - there is a FAQ with the url I think. I use it here and whilst it isn't a perfect solution its good for places like mine where Redemption isn't an option..

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[longhair]

xlbo,
"clickYes" isn't an option here. if you want i'll post the code to call blat - thinking about writing a FAQ for it. what's really nice is that when you call blat you can suppress the dos window and, therefor never lose focus of excel. the call to blat seems almost instant.
regards,
longhair