Open User Form from Template and populate message

[Tahlmorrah]

I agree with your logic, so maybe the difficulty is in the method of opening.
You stated:

first, I know one exception when my sample doesn´t work: The code won´t
be executed if OL is not running already and you´re opening the template
from within the file Explorer.

With Outlook running I am opening the .oft from file Explorere. That is the
only method I have been using. The user form is still not displaying when the
template is opened. What method would you suggest to open this template? I
have tried New, Choose Form, System, then the file location, that opens the
template but not the form.

I do not have access to script editors other than the buit in ones.

Jason

 

[Michael Bauer]

Hi Jason,

Private Sub Application_Startup()
Set Inspectors = Application.Inspectors
End Sub

1) Please set the cursor in the first line, containing
"...Application_Startup()". Press F8. The line is highlighted yellow
now, right? If so press F5. If not then there should be an error
message.

Private Sub Inspectors_NewInspector(ByVal Inspector As
Outlook.Inspector)
If Inspector.CurrentItem.Subject = TEMPLATE_SUBJECT Then
TSGRequest.Show 1
End If
End Sub

2) Now, without having done any changes after step #1(!), set the cursor
at the line starting with "If Inspector.CurrentItem..." and press F9 for
a breakpoint. The line is highlighted red. Now open your *.oft template
(New, Choose from... is ok). If you´re switching back to the VBA editor,
the line with the breakpoint should be highlighted yellow again.

If it´s not then I can´t help - I´ve never experienced that.

Otherwise, if it´s highlighted, the press F8 again. Which line is
highlighted now?

 

[Tahlmorrah]

It will not allow me to step into (F8 or draging the cursor to the location)
any of the following lines:
Private Sub Inspectors_NewInspector(ByVal Inspector As Outlook.Inspector)
If Inspector.CurrentItem.Subject = TEMPLATE_SUBJECT Then
TSGRequest.Show 1
End If
End Sub

I have tried this with the .oft open as well as closed

Jason

 

[Michael Bauer]

Jason,

where did I suggest to step into the function with F8?

 

[Tahlmorrah]

Got it finally. Was stepping into

Private Sub Application_Startup()
Set Inspectors = Application.Inspectors
End Sub

Had a syntax error in TEMPLATE_SUBJECT so the good news is that the user
form is not displaying...

....the bad news is that when Submit is clicked it does change the subject or
populate the message body.

What seems to be happening is that the .oft is double clicked the userform
displays in the backgroup, template does not displayed. Fill in userform,
select submit, template displays with default (non-userform) data.

Progress is a wonderful thing, can not thank you enough
Jason

 

[Michael Bauer]

Sorry Jason, that´s my fault. I´ve modified the sample a little bit. Now
it solves three points:

1) The Inspector is displayed before the UserForm.
2) Despite #1 the UserForm will be displayed modal.
3) The UserForm is showing on the top.

For that you need a timer. Please add the following modul
(name=modTimer) to the project:

<modTimer>
Option Explicit
Private Declare Function SetTimer Lib "user32.dll" (ByVal hWnd As Long,
_
ByVal nIDEvent As Long, ByVal uElapse As Long, _
ByVal lpTimerFunc As Long) As Long

Private Declare Function KillTimer Lib "user32.dll" (ByVal hWnd As Long,
_
ByVal nIDEvent As Long) As Long

Const WM_TIMER = &H113

Private hEvent As Long
Private m_oCallback As Object

Public Sub TimerProc(ByVal hWnd As Long, ByVal uMsg As Long, _
ByVal wParam As Long, ByVal lParam As Long _
)
If uMsg = WM_TIMER Then
m_oCallback.Timer
End If
End Sub

Public Function EnableTimer(ByVal msInterval As Long, oCallback As
Object) As Boolean
If hEvent <> 0 Then
Exit Function
End If
hEvent = SetTimer(0&, 0&, msInterval, AddressOf TimerProc)
Set m_oCallback = oCallback
EnableTimer = CBool(hEvent)
End Function

Public Function DisableTimer()
If hEvent = 0 Then
Exit Function
End If
KillTimer 0&, hEvent
hEvent = 0
End Function
</modTimer>

Then modifiy the NewInspector event:

Private Sub m_oInspectors_NewInspector(ByVal Inspector As Inspector)
If Inspector.CurrentItem.Subject = TEMPLATE_SUBJECT Then
modTimer.EnableTimer 40, Me
End If
End Sub

And add this method to ThisOutlookSession:

Public Sub Timer()
Dim oTSGRequest As TSGRequest
modTimer.DisableTimer
Set oTSGRequest = New TSGRequest
oTSGRequest.Show 1
End Sub

 

[Tahlmorrah]

Ok, one step forward one step back ...userform is not displaying again
Here is the entirity of the code as I can not see the syntax that I am
missing. I also removed much of the history of this message. It was getting a
bit long.

<Microsoft Outlook Objects>
<ThisOutlookSession>

Option Explicit
Private WithEvents Inspectors As Outlook.Inspectors

Private Sub Application_Startup()
Set Inspectors = Application.Inspectors
End Sub

Private Sub m_oInspectors_NewInspector(ByVal Inspector As Inspector)
If Inspector.CurrentItem.Subject = TEMPLATE_SUBJECT Then
modTimer.EnableTimer 40, Me
End If
End Sub

Public Sub Timer()
Dim oTSGRequest As TSGRequest
modTimer.DisableTimer
Set oTSGRequest = New TSGRequest
oTSGRequest.Show 1
End Sub

</ThisOutlookSession>
</Microsoft Outlook Objects>

<Forms>
<TSGRequest>

Private Sub CmdBtnSubmit_Click()
Set oMail = Application.ActiveInspector.CurrentItem

ReplaceCenter = "<TxtBxCenter>"
ReplaceHO = "<TxtBxHO>"
ReplaceHC = "<TxtBxHC>"
ReplaceTZ = "<TxtBxTZ>"
ReplaceTech = "<TxtBxTech>"
ReplacePhone = "<TxtBxPhone>"
ReplaceSev = "<TxtBxSev>"
ReplaceReason = "<TxtBxReason>"
ReplaceDate = "<TxtBxDate>"
ReplaceIssue = "<TxtBxIssue>"

oMail.Subject = Replace(oMail.Subject, ReplaceCenter, TxtBxCenter.Text)
oMail.Body = Replace(oMail.Body, ReplaceCenter, TxtBxCenter.Text)
oMail.Body = Replace(oMail.Body, ReplaceHO, TxtBxHO.Text)
oMail.Body = Replace(oMail.Body, ReplaceHC, TxtBxHC.Text)
oMail.Body = Replace(oMail.Body, ReplaceTZ, TxtBxTZ.Text)
oMail.Body = Replace(oMail.Body, ReplaceTech, TxtBxTech.Text)
oMail.Body = Replace(oMail.Body, ReplacePhone, TxtBxPhone.Text)
oMail.Body = Replace(oMail.Body, ReplaceSev, TxtBxSev.Text)
oMail.Body = Replace(oMail.Body, ReplaceReason, TxtBxReason.Text)
oMail.Body = Replace(oMail.Body, ReplaceDate, TxtBxDate.Text)
oMail.Body = Replace(oMail.Body, ReplaceIssue, TxtBxIssue.Text)

Unload TSGRequest
End Sub

Private Sub CmdBtnClear_Click()
TxtBxCenter = ""
TxtBxHO = ""
TxtBxHC = ""
TxtBxTZ = ""
TxtBxTech = ""
TxtBxPhone = ""
TxtBxSev = ""
TxtBxReason = ""
TxtBxDate = ""
TxtBxIssue = ""
End Sub

Private Sub TSGRequest_Activate()
TxtBxDate.Text = DateAdd("d", 1, Date)

End Sub
</TSGRequest>
</Forms>

<Modules>
<GlobalData>

Public Const TEMPLATE_SUBJECT As String = "TSG Dispatch Center <TxtBxCenter>"
Public Const REPLACE_SUBJECT As String = "<TxtBxCenter>"

</GlobalData>

<modTimer>
Option Explicit
Private Declare Function SetTimer Lib "user32.dll" (ByVal hWnd As Long,
ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As
Long
Private Declare Function KillTimer Lib "user32.dll" (ByVal hWnd As Long,
ByVal nIDEvent As Long) As Long

Const WM_TIMER = &H113

Private hEvent As Long
Private m_oCallback As Object

Public Sub TimerProc(ByVal hWnd As Long, ByVal uMsg As Long, _
ByVal wParam As Long, ByVal lParam As Long _
)
If uMsg = WM_TIMER Then
m_oCallback.Timer
End If
End Sub

Public Function EnableTimer(ByVal msInterval As Long, oCallback As Object)
As Boolean
If hEvent <> 0 Then
Exit Function
End If
hEvent = SetTimer(0&, 0&, msInterval, AddressOf TimerProc)
Set m_oCallback = oCallback
EnableTimer = CBool(hEvent)
End Function

Public Function DisableTimer()
If hEvent = 0 Then
Exit Function
End If
KillTimer 0&, hEvent
hEvent = 0
End Function

</modTimer>
</Modules>

<E-mail Subject>
TSG Dispatch Center <TxtBxCenter>
</E-mail Subject>

<E-mail Body>
Please dispatch Tech to center <TxtBxCenter> to complete <TxtBxIssue>
Technician must call <TxtBxTech> at <TxtBxPhone> after equipment is
installed, for further instructions.
Please dispatch at Severity <TxtBxSev>, <TxtBxReason>.
Equipment will arrive <TxtBxDate> by 10:30 <TxtBxTZ>
Center is open from <TxtBxHO> to <TxtBxHC> <TxtBxTZ>


-- Do not modify below this line dispatch template use only --

Center needs <TxtBxIssue>. Equipment will arrive <TxtBxDate> by 10:30
<TxtBxTZ>
Technician must call <TxtBxTech> at <TxtBxPhone> after equipment is
installed, for further instructions.
Pref: (Y/N) N
Address Verified:
Hours of Oprtn: <TxtBxHO> to <TxtBxHC> <TxtBxTZ>
H/W (Y/N): N
HHA Rcvd# (Y/N):
Multi System (Y/N): Y
Issue/Reason for SEV: Sev <TxtBxSev>, <TxtBxReason>
Esc. Approved By:
</E-mail Body>

Jason

 

[Michael Bauer]

Jason, IMHO you could have learned in the past two weeks, that a
statement like "it doesn´t work" isn´t qualified enough for me or
someone else to help you.

You know where to start now, which steps are important, and where you
can check them. In this modified sample please note, that you don´t set
a breakpoint between the calls of EnableTimer and DisableTimer.

Please check yourself what is going on. If you get an error or would
like to tell me which part isn´t executed then let me know.