Merge email - eliminate unwanted messages "allow access"

[Worksmart]

I'm using Word 2003 for merge documents. Excel 2003 is DataSource. Outlook
2003 for email. Windows Vista Home Premium.

I am not a programmer and am piecing together different macros.

I'm creating a dashboard (Word table) that will allow me to launch a macro
to create a set of documents via mail merge. Everything is working except the
following.

I've created a CommandButton to create mail merge email messages. This is
what happens:

Click CommandButton to start the merge
Are you sure? Yes
Choose Template to use in the merge
Choose DataSource
Enter Subject Line, OK
Choose Recipients, OK

Then this is where the problem comes in. This "Allow Access" box comes up
and I allow access for 10 minutes.

Then for every recipient, another box comes up with "A program is trying to
automatically send email on your behalf...."

I'm having to wait and click Yes for each name. There has to be a way to
stop all this.

Also, my subject line isn't showing up in the message.

When I create the message in Word without using the CommandButton...just
regular mail merge steps, none of this happens.

Here is my code (I'm not a programmer)

Private Sub CommandButton4_Click()
' Events_Email Macro
' Macro recorded 5/11/2008 by Peggy Duncan
'
If MsgBox(Prompt:="Are You Sure?", Buttons:=vbYesNo + vbQuestion, _
Title:="Mail Merge Document") = vbNo Then
Exit Sub
End If
' Check if Outlook is running. If it is not, start Outlook

On Error Resume Next
Set oOutlookApp = GetObject(, "Outlook.Application")
If Err <> 0 Then
Set oOutlookApp = CreateObject("Outlook.Application")
bStarted = True
End If

Dialogs(wdDialogFileOpen).Show
With ActiveDocument.MailMerge
' Show an input box asking the user for the subject to be inserted
into the email messages

Message = "Enter Your Subject Line for this Message." ' Set prompt.
Title = " Email Subject Input - Make it read like a headline" ' Set title.

' Display message, title

mysubject = InputBox(Message, Title)
.MailAddressFieldName = "Email"
Dialogs(wdDialogMailMergeRecipients).Display
.Destination = wdSendToEmail
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
'
' Close Outlook if it was started by this macro.

If bStarted Then
oOutlookApp.Quit
End If

'Clean up

Set oOutlookApp = Nothing
Source.Close wdDoNotSaveChanges
Maillist.Close wdDoNotSaveChanges

End Sub


Does anyone have any ideas about how to fix this. Thanks in advance.

 

[Doug Robbins - Word MVP]

See the article "Mail Merge to E-mail with Attachments" at

http://word.mvps.org/FAQs/MailMerge/MergeWithAttachments.htm

It will give you some information on the Express ClickYes utility that will
overcome the "Allow access" box and also shows how you need to incorporate
the use of the Subject into the code so that it appears in the email
messages.

However, as Word has the ability, built in, to execute a merge to email, I
am not sure why you are trying to re-invent the wheel.


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Worksmart]

Thank you Doug. I knew about the ClickYes and was hoping something had
changed and I wouldn't have to do this on different computers.

The Why: I'm setting up a document for senior citizens and other complete
novices who cannot figure out how to mail merge. I'm creating something
whereas all they'll have to do is click, click, click...much simpler than
trying to figure out Word's mail merge every time they need something new.

Thanks for your suggestion.

 

[Mike Clayton]

Peggy,

Something to bear in mind with ClickYes is that it could also allow any
viruses or trojans on the machine to send bulk emails as well while it's
active.

A more robust way might be to sign your macros with a certificate and then
have your users trust your certificate the first time they run your macros.
That way Outlook allows your macros to bypass the security dialog, and any
other programs still have to deal with it as per normal.

I found this article which gives a brief overview of how to sign your macro
code.

http://www.howto-outlook.com/howto/selfcert.htm

I hope it helps.

Regards,

Mike

 

[Mike Clayton]

Scratch that. Self-signed certificates can only be used on the machine they
were created on, according to the dialogs on that web page...

 

[Graham Mayor]

The dangers presented by ClickYes are very small in that you would only
activate it for the duration of the merge. The rest of the time it would be
turned off. Most people only merge for a very small proportion of their work
time.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>