Outlook XP VBA/Macro Problem

  • Thread starter Thread starter Mike MF
  • Start date Start date
M

Mike MF

I apologize ahead of time as I know that this question has
been asked and answered in part through out the group, but
I am not sure if my situation is the same as others.

I have a database built on access 2000 with links to open
a specific email form using the createitemfromtemplate
command

Set db = CurrentDb()
Set objoutlook = CreateObject("outlook.application")
Set objoutlookmsg = objoutlook.CreateItemFromTemplate
("c:\pdm\rma.oft")

With Outlook 2000 (and no security patches) I simply get
the macro warning and can enable this to open the form.
In the form, there is a drop-down box to look back to the
database to fill the form with information from the
database based on ticket number.

I have many users and some are now using Office XP/2003
and there are 2 issues now.

1. There is the message from outlook about another
program accessing outlook and would like to disable this.
Keep in mind that the form I am using, while I have it
published as a template in outlook, I am calling it
through the createitemfromtemplate which can only go to
the file system to find the form.

2. The macro's / dropdown field in the form does not work
at all any more. It no longer has the link to the access
database.

If I need to make changes to the database to make this
work, I am hoping that the changes will work for all users
(outlook 2000 and xp).

TIA
Mike
 
Sue,

I appreciate your reply and looked at the site, but I am
not sure in reading this how to get started. I tried
settig the "send form def" to no and this did not help.

I do not see how to use the MAPIFolder.Items.Add object.
I have the form published in outlook, but I do not know
how to have Access open the published form so that the
security warnings are not generated.

Another issue here is the drop down box which searchs back
through the Access table for reference. If I have Access
open the form, the dropdown is empty, but if I manually,
from Outlook, create a new message using the form, the
dropdown works fine. I am guessing this is part of the
active x blocking from an external program.

Again, thanks for your continued assistance.

mike
 
To create a new instance of a custom form programmatically, use the Add
method on the target folder's Items collection, e.g.:

Set objItem = objFolder.Items.Add("IPM.Note.MyMessageForm")

If it's a message form, you can use the Drafts folder as the target. If the
target is a default folder, you can use the Namespace.GetDefaultFolder
method to return it as a MAPIFolder object. Otherwise, you can use the code
at http://www.outlookcode.com/d/code/getfolder.htm to walk the folder
hierarchy and return the MAPIFolder corresponding to a given path string.

If you are using code to populate the dropdown box, that won't work unless
you're creating the items using a published form.

As for the security prompt about another program accessing Outlook, did you
look at the http://www.outlookcode.com/d/sec.htm page as I suggested. This
lists all your options.
--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
 
Sue,

Thanks again, but I am still confused here.

Let me give you my specifics and maybe you can provide the
specific changes I need.

In Access I have the following VBA that opens the mail
template, populates the to field (where concato is set to
the addresses);

Dim objoutlook As Outlook.Application
Dim objoutlookmsg As Outlook.MailItem
Dim objoutlookrecip As Outlook.Recipient
Dim db As Database, rs As Recordset
Set db = CurrentDb()
Set objoutlook = CreateObject("outlook.application")
Set objoutlookmsg = objoutlook.CreateItemFromTemplate
("c:\pdm\rma.oft")
With objoutlookmsg
Set objoutlookrecip = .Recipients.Add(concat)
objoutlookrecip.Type = olTo
.Importance = olImportanceHigh
.Subject = Msub
.Display
End With

So the form is located in the c:\pdm directory. It is
also published in outlook under the personal folder.

I have the following code for the form itself (only
partial as it quite a bit);

Set FSO = Application.CreateObject
("Scripting.FileSystemObject")
If FSO.FileExists("P:\engdb\servicedata.udl") Then
strUDLpath = "P:\engdb\servicedata.udl"
Set FSO = Nothing
Else
Set FSO = Nothing
Exit Function
End If
Set cnnSdata = Application.CreateObject
("ADODB.Connection")
'Open ADO connection using the UDL
cnnSdata.Open "File Name=" & strUDLpath
Set rsttick = Application.CreateObject("ADODB.Recordset")
rsttick.Open "Select * from qrytickinfo", cnnSdata,
adOpenKeyset
Set combopage = GetInspector.ModifiedFormPages("Message")
Set combobox1 = combopage.combobox1
Set combobox1 = combopage.combobox1

'Step through the recordset to fill the list box
i = 0
Do Until rsttick.EOF
combobox1.AddItem
combobox1.Column(0, i) = rsttick.Fields("tick#")
combobox1.Column(1, i) = rsttick.Fields("compname")
i = i + 1
rsttick.MoveNext
Loop
rsttick.MoveFirst

There is more to this code for the combo box and to set
other fields in the form based on the value of the combo
box (it goes back to the database to get the information
for each field).

Thanks again.

Mike
 
As I said earlier, if you want code on the form itself to run, you can't use
a template, i.e. an .oft file. You have to use a published form.

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers
 
Back
Top