Solved: Why does this fail?

[wnazzaro]

I'm trying to send email in Outlook from Word. The Word document is from a Mail Merge and the Subject, To:, and cc: fields are all in the body of the Word document. I can get VBA to copy everything to the right place, but it fails after the first email is sent.

Here's my code:

Option Explicit
Public Sub MailMerge()
Application.ScreenUpdating = False

Dim FileName As Document
Dim SecCounter As Long
Dim SentCounter As Long
Dim olApp As Object, olMsg As Object
Dim CurrPane As Pane
Dim CurrSec As Section
Dim MsgTxt As String
Dim Output As String

Set olApp = CreateObject("Outlook.Application")
Set olMsg = olApp.CreateItem(olMailItem)
Set FileName = ActiveDocument

Application.Windows(FileName).View = wdNormalView
Set CurrPane = Application.Windows(FileName).ActivePane

For SecCounter = 1 To CurrPane.Document.Sections.Count - 1
Output = ""
Set CurrSec = CurrPane.Document.Sections(SecCounter)

For SentCounter = 4 To 10
Output = Output + CurrSec.Range.Sentences(SentCounter)
Next

CurrSec.Range.Tables(1).Select
Selection.Copy

With CurrPane.Document
MsgTxt = .Sections(SecCounter).Range.Sentences(1)
MsgTxt = Left(MsgTxt, Len(MsgTxt) - 1)
olMsg.To = .Sections(SecCounter).Range.Sentences(2)
olMsg.CC = .Sections(SecCounter).Range.Sentences(3)
olMsg.Subject = MsgTxt
End With

olMsg.Body = Output
olMsg.Display

SendKeys "{Tab}", Wait:=True
SendKeys "^{End}", Wait:=True
SendKeys "^v", Wait:=True

Application.Options.WarnBeforeSavingPrintingSendingMarkup = False

'Replace this with .Send when you are ready to send
olMsg.Send
Next

Application.ScreenUpdating = True
End Sub


The first record will send, but I get an error for the second letter.

I get the message
A program is trying to automatically send an e-mail on your behalf. Do you want to allow this? I don't mind the message, but I would like to know how to get rid of it. I was hoping Application.Options.WarnBeforeSavingPrintingSendingMarkup = False would do the trick, but it doesn't.

This is where the Debugger stops when the program fails.
olMsg.To = .Sections(SecCounter).Range.Sentences(2)

The error message is:

Run-time error '-lots of numbers (some letters and numbers)':
Method 'To' of object'_MailItem' failed

All help is greatly appreciated.

[MOS MASTER]

Hi, :D

You cannot easily get rid of that message because it's a safety feature of Office (I though > Office 2000 SP3)

There are work arounds listed here with full discription off you're problem:
http://www.outlookcode.com/d/sec.htm

Enjoy! :thumb

[Killian]

Hi :hi: and welcome to VBAX,

I don't have Outlook here but looking at your code, I think your problem lies in the fact that you need to create a new mailitem at the start of each loop.
At the moment you set a reference, olMsg before the loop, it goes through fine the first time, then when you use the reference, olMsg for the first time in the second loop, it's still referring to the original you sent in the first one... if you see what I mean.
Moving the Set olMsg = ...... inside the loop should solve this.

The message is annoying, I don't think there's a setting in Outlook you can change (tho I haven't looked properly). If not, for sure there's a registry key you could change. I'll Google for it and post back later

[MOS MASTER]

If not, for sure there's a registry key you could change. I'll Google for it and post back later
Hi, :D

I've done that many times and hope you'll succeed because I'd like to have that registry hack to! :thumb

[Zack Barresse]

Just as a side note for any user's thinking of attempting a reg-hack, ensure you have a complete registry backup before you hack your registry. I've seen that one get too many people..

[MOS MASTER]

Just as a side note for any user's thinking of attempting a reg-hack, ensure you have a complete registry backup before you hack your registry. I've seen that one get too many people..Good advice! :thumb

[Killian]

Excuse my hopeless optimism... Outlook is actually more secure than I thought in this respect (surpisingly). Unfortunately, since Windows is an unsecure message based OS, it would be possible, I believe, to use a Win API function or two to deal with the message but you would have to be extra careful you don't operate on the wrong window.

Then I had a look at the link posted and someone's done it as a little freeware app (Express ClickYes). No point in re-inventing the wheel. I'd just download that!

[Ken Puls]

FYI,

If you're not stuck on using Outlook, you could adapt Ron DeBruin's Send mail using CDO (http://www.rondebruin.nl/cdo.htm)

This avoids the error message, since it doens't use Outlook to send. Because of that though, the side effect is that you don't get a message in your outbox either.

Just a thought!

[wnazzaro]

Hi :hi: and welcome to VBAX,

I don't have Outlook here but looking at your code, I think your problem lies in the fact that you need to create a new mailitem at the start of each loop.
At the moment you set a reference, olMsg before the loop, it goes through fine the first time, then when you use the reference, olMsg for the first time in the second loop, it's still referring to the original you sent in the first one... if you see what I mean.
Moving the Set olMsg = ...... inside the loop should solve this.

:friends: Glad to be here.

Moving the Set olMsg command insdie the loop makes sense. I'll try it when I get to work on Monday. When I change .send to .close (olsave) the program doesn't fail, but it only saves the last message. Your fix would explain that behavior as well, also why the program doesn't fail until I try to access the olmsg. I thought it had something to do with the sections or perhaps the timing was messing something up.

I also need to thank firefytr for getting me this far. This will be a great tool for sending email from Word once it's fixed (though I don't look forward to clicking Yes on 50+ security msgs, I suppose it could be worse).

Thanks for the help, I'll report back on Monday.

[MOS MASTER]

Excuse my hopeless optimism... Outlook is actually more secure than I thought in this respect (surpisingly). Unfortunately, since Windows is an unsecure message based OS, it would be possible, I believe, to use a Win API function or two to deal with the message but you would have to be extra careful you don't operate on the wrong window.

Then I had a look at the link posted and someone's done it as a little freeware app (Express ClickYes). No point in re-inventing the wheel. I'd just download that!Hi Killian, :D

Been there before so I know...

Click Yes works well. But if I want to Use Outlook's Object Model I usally just use "Outlook Redemption DLL" because that's also a great help in accessing Outlook within...

If it's only mail I'm after I also like using the CDO (Tip kpuls).

Al those are great tools in achieving a work around but wouldn't it be great if we could just use the Outlook Model again (Like Before all those Secuirity features)...That's why I was Applausing you're efforts in seaking the sollution in the machine's live line (The registry)..

Hope we do find the way to achieve full use off Outlook again with just the Outlook Object model in VBA. :thumb

[wnazzaro]

Option Explicit
' Declare Windows' API functions
Private Declare Function RegisterWindowMessage _
Lib "user32" Alias "RegisterWindowMessageA" _
(ByVal lpString As String) As Long

Private Declare Function FindWindow Lib "user32" _
Alias "FindWindowA" (ByVal lpClassName As Any, _
ByVal lpWindowName As Any) As Long

Private Declare Function SendMessage Lib "user32" _
Alias "SendMessageA" (ByVal hwnd As Long, _
ByVal wMsg As Long, ByVal wParam As Long, _
lParam As Any) As Long

Public Sub MailMerge()
Dim wnd As Long
Dim uClickYes As Long
Dim Res As Long

' Register a message to send
uClickYes = RegisterWindowMessage("CLICKYES_SUSPEND_RESUME")

' Find ClickYes Window by classname
wnd = FindWindow("EXCLICKYES_WND", 0&)

' Send the message to Resume ClickYes
Res = SendMessage(wnd, uClickYes, 1, 0)

Dim FileName As Document
Dim SecCounter As Long
Dim SentCounter As Long
Dim olApp As Object, olMsg As Object
Dim CurrPane As Pane
Dim CurrSec As Section
Dim MsgTxt As String
Dim Output As String

Set olApp = CreateObject("Outlook.Application")
Set FileName = ActiveDocument

Application.Windows(FileName).View = wdNormalView
Set CurrPane = Application.Windows(FileName).ActivePane

For SecCounter = 1 To CurrPane.Document.Sections.Count - 1

Set olMsg = olApp.CreateItem(olMailItem)
Set CurrSec = CurrPane.Document.Sections(SecCounter)
Output = ""

For SentCounter = 4 To 10
Output = Output + CurrSec.Range.Sentences(SentCounter)
Next

CurrSec.Range.Tables(1).Select
Selection.Copy

With CurrPane.Document
MsgTxt = .Sections(SecCounter).Range.Sentences(1)
MsgTxt = Left(MsgTxt, Len(MsgTxt) - 1)
olMsg.To = .Sections(SecCounter).Range.Sentences(2)
olMsg.CC = .Sections(SecCounter).Range.Sentences(3)
olMsg.Subject = MsgTxt
End With

olMsg.Body = Output
olMsg.Display

SendKeys "{Tab}", Wait:=True
SendKeys "^{End}", Wait:=True
SendKeys "^v", Wait:=True

olMsg.Send
Next

' Send the message to Suspend ClickYes
Res = SendMessage(wnd, uClickYes, 0, 0)
End Sub

Many thanks,
Bill

[Killian]

There you go... the WIN API can be a lot of fun (the fun part being getting around the limitations of the VBA object model)
Nicely executed, Bill :thumb

[Zack Barresse]

Cool beans Bill!!! Glad you got it working!! :yes

The experts here are truly amazing!

[Killian]

Bill,
not wishing to blatantly contravene the proven "don't fix what ain't broke" model of software development that I favor, I was reading somthing this weekend (while trying to fix some code that was most definately broke) about using API functions instead of SendKeys.
SendKeys can be horribly unreliable and since you seem happy to brave the murky waters of OS level commands in your code, I thought I'd mention it.
Let me know... the link may still be in my browser history folder

[wnazzaro]

Well, the API stuff was blatantly stolen from the ClickYes site. I really don't know what I am doing in the beginning of the program, I just copied and pasted.

I know SendKeys isn't a great way to go, but it's so easy to code, and being an absolute novice I fall back on it more than I would like to. It works right now, but I definitely want to learn other ways of doing things. I'm still at the point where at first I had my first For...Next loop run 53 times until I realized I could have the program count the sections for me.

If you post the link or email it to me, I'd be interested in learning more and better ways to do this.

This program really was a combination of firefytr's help, VBA for dummies code, and the additions from Killian and MOS Master. Thanks to all!

[MOS MASTER]

Hi, :D

You did an excellent job! (No more annoying question's for you from Outlook! ;) )

Enjoy. :thumb

[wnazzaro]

AAAAAAAAAHHHHHHHHGGGGGGGGGRRRRRRRRRHHHHHHHH!

So now I get an error message:
Your current security settings prohibit running ActiveX controls on this page. As a result, the page may not display correctly.

How do I get rid of this?

[MOS MASTER]

AAAAAAAAAHHHHHHHHGGGGGGGGGRRRRRRRRRHHHHHHHH!

So now I get an error message:

How do I get rid of this?
Huh??

Are you automating stuff in Internet Explorer? (Did you do a recent upgrade to XP SP2 perhaps?)

If not how and when is this happening?
Are you running code? (Does it get stuck somewhere?)

Enjoy! :whistle:

[wnazzaro]

AAAAAAAAAHHHHHHHHGGGGGGGGGRRRRRRRRRHHHHHHHH!

So now I get an error message:

How do I get rid of this?

Sorry. I changed the security settings (I should have thought of that). No more problem. I freaked because I tried to run the program and the messages were going out with no text. Quite the adreline rush trying to stop it (that should teach me to keep the error handler and fix the error)

Nothing to see here. Move along...move along.

[MOS MASTER]

Sorry. I changed the security settings (I should have thought of that). No more problem. I freaked because I tried to run the program and the messages were going out with no text. Quite the adreline rush trying to stop it (that should teach me to trust the error handler)

Nothing to see here. Move along...move along.
Haha...ok glad you've got it working now! :thumb

But can you please confirm you had an ActiveX warning? Instead of a Macro warning?

If so than it's probably from the ClickYes program but I'm used to this error more in the Internet Explorer enviroment.

Enjoy! :whistle:

[wnazzaro]

Haha...ok glad you've got it working now! :thumb

But can you please confirm you had an ActiveX warning? Instead of a Macro warning?

If so than it's probably from the ClickYes program but I'm used to this error more in the Internet Explorer enviroment.

Enjoy! :whistle:

Absolutely an ActiveX warning. The MsgBox that opened had the header "Internet Explorer." It was my security settings and sending email as HTML. That invokes the Security Zone setting of Outlook, which is set by IE. I had mine set to "Restricted Sites" instead of "Internet".

ClickYes worked too well. It sent my messages even though Outlook security wouldn't allow me to paste my text (I'm using a table now for the body instead of sentence counter because I can copy formatting with it and I couldn't with a string). So I sent a bunch of blank messages and had to stop the program (ESC!ESC! ctrl-alt-del!!!) and recall the messages.

The problem (now that I have had time to simmer down and think) was that the ActiveX warning happened before SendKeys could paste. ClickYes could still send, but I couldn't clear the ActiveX warning and allow the program to paste my table before it sent.

[MOS MASTER]

Absolutely an ActiveX warning. The MsgBox that opened had the header "Internet Explorer." It was my security settings and sending email as HTML. That invokes the Security Zone setting of Outlook, which is set by IE. I had mine set to "Restricted Sites" instead of "Internet".
Ah thanx,

Still wonder what ActiveX is called? (Must be one of ClickYes.) well....:dunno


ClickYes worked too well. It sent my messages even though Outlook security wouldn't allow me to paste my text (I'm using a table now for the body instead of sentence counter because I can copy formatting with it and I couldn't with a string). So I sent a bunch of blank messages and had to stop the program (ESC!ESC! ctrl-alt-del!!!) and recall the messages.
You should be able to do this with a string? (But have to now what you where doing in the first place and how) However if the current system works than it's fine to me...:rofl:

One more tip: STOP Endless loops or other code failures with CTRL+BREAK this is a lot better than the ctrl-alt-del method!


The problem (now that I have had time to simmer down and think) was that the ActiveX warning happened before SendKeys could paste. ClickYes could still send, but I couldn't clear the ActiveX warning and allow the program to paste my table before it sent.
Seams plausible to me!

Enjoy! :thumb