Office Automation with VBA, Access, Word, Outlook

[Glenn Suggs]

I have a MS Access app that generates form letters and
one module is used to Email one of those letters to a
customer. There appears to be two problems. 1) While
testing the "send email" part, I get a message that says
that "A program is trying to access my outlook address
book. Do I want to allow this?" I don't want the users to
have to dismiss this message every time the code is run.
How do I turn it off? 2) Running the SendFormLetter
code seems to copy the contents of the Word document (the
form letter) to the body of the email ok, but when I run
it twice in a row, I get the message "Error 462 - The
remote server machine does not exist or is unavailable at
line #20." Any ideas would be appreciated.

Option Compare Database
Option Explicit
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim olApp As Outlook.Application
Dim olMessage As Object
Dim olAttachments As Object
Dim olRecipient As Recipient

Public Sub SendFormLetter(strFormLetter)
Dim F As Form
Dim rngSelectAll As Range

On Error GoTo ErrorHandler

Set F = Forms![GPC JOINT USE TRACKING]

10
Set wrdApp = New Word.Application
11
Set olApp = New Outlook.Application
12
wrdApp.Documents.Open (strFormLetter)
13
Set wrdDoc = wrdApp.Documents(strFormLetter)

15
wrdDoc.Activate
wrdDoc.Select

20
Set rngSelectAll = Selection.Range
21
rngSelectAll.WholeStory
22
rngSelectAll.Copy

25
Set olMessage = olApp.CreateItem(olMailItem)
Set olAttachments = olMessage.Attachments
Set olRecipient = olMessage.Recipients.Add
(F.txtContactEmail)
Set olRecipient = olMessage.Recipients.Add
("(e-mail address removed)")
olRecipient.Type = olCC

30
olMessage.Subject = "Message from My company"

33
olMessage.Body = Selection

35
Selection.PasteAndFormat Type:=wdFormatOriginalFormatting

60
olMessage.Display

SendFormLetter_Exit:

70
Set olMessage = Nothing
Set olAttachments = Nothing
Set olApp = Nothing

wrdApp.ActiveDocument.Close
wrdApp.Quit SaveChanges:=False
Set wrdDoc = Nothing
Set wrdApp = Nothing

Exit Sub

ErrorHandler:
Dim strMsg

' Construct an error message
strMsg = "Error # " & str(Err.Number) & " was generated
by " _
& Err.Source & Chr(13) & Err.Description & _
" at Line# " & Erl & "."

MsgBox strMsg, , "Error - modSendFormLetter",
Err.HelpFile, Err.HelpContext
GoTo SendFormLetter_Exit

End Sub

 

[Cheryl Fischer]

1) You are encountering the Outlook Security Prompt. Here is a link with
more information:

http://www.slipstick.com/outlook/esecup.htm

2) Here is a link which explains why setting an automation app to nothing
does not close it and what to do:

http://www.mvps.org/access/general/gen0017.htm



--
Cheryl Fischer
Law/Sys Associates
Houston, TX

I have a MS Access app that generates form letters and
one module is used to Email one of those letters to a
customer. There appears to be two problems. 1) While
testing the "send email" part, I get a message that says
that "A program is trying to access my outlook address
book. Do I want to allow this?" I don't want the users to
have to dismiss this message every time the code is run.
How do I turn it off? 2) Running the SendFormLetter
code seems to copy the contents of the Word document (the
form letter) to the body of the email ok, but when I run
it twice in a row, I get the message "Error 462 - The
remote server machine does not exist or is unavailable at
line #20." Any ideas would be appreciated.

Option Compare Database
Option Explicit
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim olApp As Outlook.Application
Dim olMessage As Object
Dim olAttachments As Object
Dim olRecipient As Recipient

Public Sub SendFormLetter(strFormLetter)
Dim F As Form
Dim rngSelectAll As Range

On Error GoTo ErrorHandler

Set F = Forms![GPC JOINT USE TRACKING]

10
Set wrdApp = New Word.Application
11
Set olApp = New Outlook.Application
12
wrdApp.Documents.Open (strFormLetter)
13
Set wrdDoc = wrdApp.Documents(strFormLetter)

15
wrdDoc.Activate
wrdDoc.Select

20
Set rngSelectAll = Selection.Range
21
rngSelectAll.WholeStory
22
rngSelectAll.Copy

25
Set olMessage = olApp.CreateItem(olMailItem)
Set olAttachments = olMessage.Attachments
Set olRecipient = olMessage.Recipients.Add
(F.txtContactEmail)
Set olRecipient = olMessage.Recipients.Add
("(e-mail address removed)")
olRecipient.Type = olCC

30
olMessage.Subject = "Message from My company"

33
olMessage.Body = Selection

35
Selection.PasteAndFormat Type:=wdFormatOriginalFormatting

60
olMessage.Display

SendFormLetter_Exit:

70
Set olMessage = Nothing
Set olAttachments = Nothing
Set olApp = Nothing

wrdApp.ActiveDocument.Close
wrdApp.Quit SaveChanges:=False
Set wrdDoc = Nothing
Set wrdApp = Nothing

Exit Sub

ErrorHandler:
Dim strMsg

' Construct an error message
strMsg = "Error # " & str(Err.Number) & " was generated
by " _
& Err.Source & Chr(13) & Err.Description & _
" at Line# " & Erl & "."

MsgBox strMsg, , "Error - modSendFormLetter",
Err.HelpFile, Err.HelpContext
GoTo SendFormLetter_Exit

End Sub

 

[Glenn Suggs]

Thanks for the help.

-----Original Message-----
1) You are encountering the Outlook Security Prompt. Here is a link with
more information:

http://www.slipstick.com/outlook/esecup.htm

2) Here is a link which explains why setting an automation app to nothing
does not close it and what to do:

http://www.mvps.org/access/general/gen0017.htm



--
Cheryl Fischer
Law/Sys Associates
Houston, TX

I have a MS Access app that generates form letters and
one module is used to Email one of those letters to a
customer. There appears to be two problems. 1) While
testing the "send email" part, I get a message that says
that "A program is trying to access my outlook address
book. Do I want to allow this?" I don't want the users to
have to dismiss this message every time the code is run.
How do I turn it off? 2) Running the SendFormLetter
code seems to copy the contents of the Word document (the
form letter) to the body of the email ok, but when I run
it twice in a row, I get the message "Error 462 - The
remote server machine does not exist or is unavailable at
line #20." Any ideas would be appreciated.

Option Compare Database
Option Explicit
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim olApp As Outlook.Application
Dim olMessage As Object
Dim olAttachments As Object
Dim olRecipient As Recipient

Public Sub SendFormLetter(strFormLetter)
Dim F As Form
Dim rngSelectAll As Range

On Error GoTo ErrorHandler

Set F = Forms![GPC JOINT USE TRACKING]

10
Set wrdApp = New Word.Application
11
Set olApp = New Outlook.Application
12
wrdApp.Documents.Open (strFormLetter)
13
Set wrdDoc = wrdApp.Documents(strFormLetter)

15
wrdDoc.Activate
wrdDoc.Select

20
Set rngSelectAll = Selection.Range
21
rngSelectAll.WholeStory
22
rngSelectAll.Copy

25
Set olMessage = olApp.CreateItem(olMailItem)
Set olAttachments = olMessage.Attachments
Set olRecipient = olMessage.Recipients.Add
(F.txtContactEmail)
Set olRecipient = olMessage.Recipients.Add
("(e-mail address removed)")
olRecipient.Type = olCC

30
olMessage.Subject = "Message from My company"

33
olMessage.Body = Selection

35
Selection.PasteAndFormat Type:=wdFormatOriginalFormatting

60
olMessage.Display

SendFormLetter_Exit:

70
Set olMessage = Nothing
Set olAttachments = Nothing
Set olApp = Nothing

wrdApp.ActiveDocument.Close
wrdApp.Quit SaveChanges:=False
Set wrdDoc = Nothing
Set wrdApp = Nothing

Exit Sub

ErrorHandler:
Dim strMsg

' Construct an error message
strMsg = "Error # " & str(Err.Number) & " was generated
by " _
& Err.Source & Chr(13) & Err.Description & _
" at Line# " & Erl & "."

MsgBox strMsg, , "Error - modSendFormLetter",
Err.HelpFile, Err.HelpContext
GoTo SendFormLetter_Exit

End Sub

.