[RESOLVED] How to Open Published Outlook Form through Word

[Kubull]

I am using a shared Outlook Calendar, and in previous instances we created Outlook Forms which are saved offline for the user to fill in and it then populate the online shared calendar, the below code works fine and dandy with that set-up, but this new form has vb code that activates a combo box that lists members of staff via Excel. This form and function doesn't work offline, so the only working form is the published one, which can be generated by the "ctrl - N"

Is there a way to grab the published form with VB and macro it into a button for users to use?

Sub KeyTextCreate()

On Error GoTo ErrorHandler
   
 'Sets the folder in which to post the Custom Form (public folders)
   Set ol = New Outlook.Application
    Set olns = ol.GetNamespace("MAPI")
    Set myFolder1 = olns.Folders("Public Folders")
    Set myFolder2 = myFolder1.Folders("All Public Folders")
    Set myFolder3 = myFolder2.Folders("All Local Folders")
    Set myFolder4 = myFolder3.Folders("All HQ Departments")
    Set myFolder5 = myFolder4.Folders("DLS")
    Set myFolder6 = myFolder5.Folders("Calendars")
    Set myfolderref = myFolder6.Folders("KeyTexts")
 
    
    'Creates item based on template in the above folder, i.e. myFolderRef
    Set myOlApp = CreateObject("Outlook.Application")
'   Dim myitem As Outlook.PostItem
        
    Set myitem = myOlApp.CreateItem("K:\Databases\Daily Lists\ClaimsAvailability.oft", myfolderref)
    myitem.Display

    Set ol = Nothing
    Set olns = Nothing
    Set myFolder1 = Nothing
    Set myFolder2 = Nothing
    Set myFolder3 = Nothing
    Set myFolder4 = Nothing
    Set myFolder5 = Nothing
    Set myFolder6 = Nothing
    Set myfolderref = Nothing
    Set myOlApp = Nothing
    Set myitem = Nothing

    Exit Sub

ErrorHandler:
MsgBox "Can not create item - Try later"
On Error GoTo ENDNOW
    Set ol = Nothing
    Set olns = Nothing
    Set myFolder1 = Nothing
    Set myFolder2 = Nothing
    Set myFolder3 = Nothing
    Set myFolder4 = Nothing
    Set myFolder5 = Nothing
    Set myFolder6 = Nothing
    Set myfolderref = Nothing
    Set myOlApp = Nothing
    Set myitem = Nothing
ENDNOW:
End Sub

This is the code in the form which doesn't work offline / unpublished

Function Item_Open()

set objPage = Item.GetInspector.ModifiedFormPages("Key Texts")
set objControl1 = objPage.Controls("cboBorrowerName")
set objControl2 = objPage.Controls("cboPassedTo")

dim Liste_oe
dim name_oe

set objExcel = Item.Application.CreateObject("Excel.Application")
objExcel.Workbooks.Open("K:\Databases\ComboBoxListOfLawyers.xls")
set objExcelBook = objExcel.ActiveWorkbook
set objExcelSheets = objExcelBook.Worksheets
set objExcelSheet = objExcelBook.Sheets(1)

For i = 1 to 44
Liste_oe = objExcelSheet.Cells(i,1).value
objControl1.Additem Liste_oe
objControl2.Additem Liste_oe
next

objExcelBook.close
objExcel.quit

End Function

[RobDog888]

You might want to check out my Office Faq's (link in signature) Outlook Forms section for some info on the subject. If I understand you correctly, you created a one-off form and do not want to publish it?

[Kubull]

Nope, the form is published I just want to get my hands on it and stick it on a button in Word, as the unpublished form doesn't work fully, Cheers

[RobDog888]

What do you mean by "fully work"?
Does the form appear and some features just not work? Getting any error messages?

[Kubull]

As I said in the first post, the new form uses two combo boxes which inturn uses vb script to activate a list of names from excel, the form and combo boxes will not work unpublished.

I can link the oft to a button for users to click but the combobox with the list of users will not activate, thus the form not working fully. I had a feeling that it isn't possible to use the published form rather then the saved oft.

Cheers anyways

[Kubull]

I've taken the Excel List of users out of the equation of the comboboxes and have just added the names into the Value in the design mode of the form, a bit laborious, but no other option I guess.

This option allows a button to be created within Word and the code in the above postings to work fine and dandy.

Cheers