Re: Open User Form from Template and populate message

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Thank you for letting me know that I had to have those items in
ThisOutlookSession. I do not consider that secure at all, but we can only
work within the limitations Microsoft has given us.

The Template named TSG Dispatch.oft has the following VBA code attached to it:
<Microsoft Outlook Objects>
<This Outlook Session>

Option Explicit
Private WithEvents Inspectors As Outlook.Inspectors

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

Private Sub Inspectors_NewInspector(ByVal Inspector As Outlook.Inspector)
If Inspector.CurrentItem.Subject = TEMPLATE_SUBJECT Then
TSGRequest.Show 1
End If
End Sub
</This Outlook Session>
</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>
<Module1>

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

</Module1>
</Modules>

Problem: Userform:TSGRequest does not automatically open when template TSG
Dispatch.oft is opened. Pretty sure I need a load or activate command
referancing userform:TSGRequest somewhere in the code so that when the .oft
is opened it pops up, but I am unsure of the command or where it should be
placed.

Believe or not this has been helpful. I get bits and pieces here and there
that help improve my knowledge of VBA and how it relates to Outlook, and as
you can see parts that help improve my code.

Jason


"Michael Bauer" wrote:

> Hi Jason,
>
> sorry, I understand less and less. The sample I´ve given to you must be
> executed in VBA, modul "ThisOutlookSession". You can´t move this code
> into your *.oft template.
>
> Whether you´re using VBScript in your template or not, because you´re
> also using an UserForm you have to enable VBA anyway.
>
> Or do you mean your *.oft template when talking about an UserForm? If so
> then it seems that you´d need help for VBScript, not VBA. That´s not my
> area.
>
> (IMHO VBA is more secure than using scripting in an template. It´s not
> easy to change or manipulate the project file (*.otm) - but very easy to
> change your *.oft file.)
>
> --
> Viele Grüße / Best regards
> Michael Bauer - MVP Outlook
>
>
> "Tahlmorrah" <Tahlmorrah@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:C1D9843D-59B1-4579-A144-BAF1300FEE30@xxxxxxxxxxxxxxxx
> > > Not sure, what you´re saying.
> >
> > I do not care that my security settings ask me to enable macros. This
> is
> > working as intended. I only want it to prompt when this .oft is opened
> as it
> > is more secure. It appears that I need to add something that is local
> to the
> > .oft rather than ThisOutlookSession. I only used ThisOutlookSession as
> I do
> > not know how to accomplish this so it is only contained in the .oft.
> >
> > All security settings are Custom based off of the Medium setting.
> >
> > The intention is to make a self contained macro in the template that
> only
> > activates when the template opens. Having macros that activate when
> Outlook
> > opens is not an option due to it compromising system security.
> >
> > Here is the desired sequence of events:
> > 1. User opens Outlook and works on it throughout the day
> > 2. User has need of template and opens template by clicking on the
> ..oft file
> > 3. Prompted to enable macros in the .oft
> > 4. Userform is displayed
> > 5. Completes userform and Submits
> > 6. Text on Template is changed with the entered information
> > 7. User can make additional changes as necessary
> > 8. E-mail is sent
> > 9. User continues to use Outlook as normal
> > 10. User has need of template and opens template
> > 11. See step 3.
> >
> > > This is not what I´ve suggested.
> > I understand that is not what you have suggested, but this is the only
> way I
> > have been able to get the userform to even show. If you can tell me
> how to
> > get the userform to show when the template opens, I will be more than
> glad to
> > change the code. This is my primary problem at this time.
> >
> > Jason
> >
> > "Michael Bauer" wrote:
> >
> > > > Started to get promted for macros after Outlook after closed and
> > > reopen
> > > > Outlook. If open template at that point, prompts for macros and
> opens
> > > > userform when changed
> > >
> > > Not sure, what you´re saying.
> > >
> > > As I mentioned earlier, you need to restart OL after making changes
> to
> > > the code or run Application_Startup manually. The execution of this
> > > function is important. If it´s done once and you don´t make more
> changes
> > > to the code then OL can stay opened till the end of the days.
> > >
> > > > Private Sub Application_Startup()
> > > > Set Inspectors = Application.Inspectors
> > > > TSGRequest.Show 1
> > >
> > > This is not what I´ve suggested.
> > >
> > > --
> > > Viele Grüße / Best regards
> > > Michael Bauer - MVP Outlook
> > >
> > >
> > > "Tahlmorrah" <Tahlmorrah@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > > news:97784E51-2A21-454D-AEB2-8C402C9910BB@xxxxxxxxxxxxxxxx
> > > > Started to get promted for macros after Outlook after closed and
> > > reopen
> > > > Outlook. If open template at that point, prompts for macros and
> opens
> > > > userform when changed
> > > >
> > > > <ThisOutlookSession>
> > > > Private Sub Application_Startup()
> > > > Set Inspectors = Application.Inspectors
> > > > TSGRequest.Show 1
> > > > End Sub
> > > > </ThisOutlookSession>
> > > >
> > > > However this is not accomplishing what I need to. Users need to
> keep
> > > Outlook
> > > > open at all times. When this template is selected they need the
> > > userform to
> > > > open automatically on top of the template.
> > > >
> > > > Progress is slow, but its progress
> > > > Thanks again
> > > > Jason
> > > >
> > > >
> > > > "Tahlmorrah" wrote:
> > > >
> > > > > Never hitting the breakpoint even with Outlook closed.
> > > > >
> > > > > Jason
> > > > >
> > > > > "Michael Bauer" wrote:
> > > > >
> > > > > > Hi Jason,
> > > > > >
> > > > > > > If Inspector.CurrentItem.Subject = TEMPLATE_SUBJECT Then
> > > > > >
> > > > > > please set a breakpoint (F9) in that line. Does the execution
> > > stops
> > > > > > there if you´re opening a new Inspector? If so then obviously
> the
> > > item´s
> > > > > > subject isn´t equal to the one defined in the constant.
> > > > > >
> > > > > > --
> > > > > > Viele Grüße / Best regards
> > > > > > Michael Bauer - MVP Outlook
> > > > > >
> > > > > >
> > > > > > "Tahlmorrah" <Tahlmorrah@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
> > > message
> > > > > > news:4D22D03C-00A5-4769-A919-26E4D729D057@xxxxxxxxxxxxxxxx
> > > > > > > Unfortunatly the user form is still not autoloading on open
> of
> > > the
> > > > > > template.
> > > > > > > What am I doing wrong?
> > > > > > >
> > > > > > > <ThisOutlookSession>
> > > > > > > Option Explicit
> > > > > > > Private WithEvents Inspectors As Outlook.Inspectors
> > > > > > >
> > > > > > > Private Sub Application_Startup()
> > > > > > > Set Inspectors = Application.Inspectors
> > > > > > > End Sub
> > > > > > >
> > > > > > > Private Sub Inspectors_NewInspector(ByVal Inspector As
> > > > > > Outlook.Inspector)
> > > > > > > If Inspector.CurrentItem.Subject = TEMPLATE_SUBJECT Then
> > > > > > > TSGRequest.Show 1
> > > > > > > End If
> > > > > > > End Sub
> > > > > > > </ThisOutlookSession>
> > > > > > >
> > > > > > > <TSGRequest>
> > > > > > > Private Sub CmdBtnSubmit_Click()
> > > > > > > Dim oMail As Outlook.MailItem
> > > > > > > Dim ReplaceHO As String
> > > > > > > Dim ReplaceHC As String
> > > > > > > Dim ReplaceTZ As String
> > > > > > > Dim ReplaceTech As String
> > > > > > > Dim ReplacePhone As String
> > > > > > > Dim ReplaceSev As String
> > > > > > > Dim ReplaceReason As String
> > > > > > > Dim ReplaceDate As String
> > > > > > > Dim ReplaceIssue As String
> > > > > > > Set oMail = Application.ActiveInspector.CurrentItem
> > > > > > >
> > > > > > > ReplaceHO = "<TxtBxHO>"
> > > > > > > ReplaceHC = "<TxtBxHC>"
> > > > > > > ReplaceTZ = "<TxtBxTZ>"
> > > > > > > ReplaceTech = "<TxtBxTech>"
> > > > > > > ReplacePhone = "<TxtBxPhone>"
> > > > > > > ReplaceSev = "<TxtBxSev>"
> > > > > > > ReplaceReason = "<TxtBxReason>"
> > > > > > > ReplaceDate = "<TxtBxDate>"
> > > > > > > ReplaceIssue = "<TxtBxIssue>"
> > > > > > >
> > > > > > > oMail.Subject = Replace(oMail.Subject, REPLACE_SUBJECT,
> > > > > > 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 TSGRequest_Activate()
> > > > > > > TxtBxHO = "0800"
> > > > > > > TxtBxHC = "1700"
> > > > > > > TxtBxTZ = "EDT"
> > > > > > > TxtBxTech = "Tech Ops"
> > > > > > > TxtBxPhone = "800.546.8008"
> > > > > > > TxtBxSev = "5"
> > > > > > > TxtBxReason = "Center is having difficulty meeting
> volume"
> > > > > > > TxtBxDate = Date + 1
> > > > > > > TxtBxIssue = "Hard drive swap and minor configuration"
> > > > > > > End Sub
> > > > > > > Private Sub CmdBtnClear_Click()
> > > > > > > TxtBxHO = ""
> > > > > > > TxtBxHC = ""
> > > > > > > TxtBxTZ = ""
> > > > > > > TxtBxTech = ""
> > > > > > > TxtBxPhone = ""
> > > > > > > TxtBxSev = ""
> > > > > > > TxtBxReason = ""
> > > > > > > TxtBxDate = ""
> > > > > > > TxtBxIssue = ""
> > > > > > > End Sub
> > > > > > >
> > > > > > > </TSGRequest>
> > > > > > >
> > > > > > > <Module1>
> > > > > > > Public Const TEMPLATE_SUBJECT As String = "Dispatch center <
> > > > > > TxtBxCenter >"
> > > > > > > Public Const REPLACE_SUBJECT As String = "<TxtBxCenter>"
> > > > > > >
> > > > > > > </Module1>
> > > > > > >
> > > > > > > Jason
> > > > > > >
> > > > > > >
> > > > > > > "Michael Bauer" wrote:
> > > > > > >
> > > > > > > > Hi Jason,
> > > > > > > >
> > > > > > > > the reason for the global constant TEMPLATE_SUBJECT is
> that
> > > both
> > > > > > > > modules, ThisOutlookSession and your UserForm, can use the
> > > same
> > > > > > subject
> > > > > > > > without having to define it twice. For a global constant
> you´d
> > > need
> > > > > > a
> > > > > > > > standard module, which you can add to the project
> > > (Insert/Module)
> > > > > > and
> > > > > > > > name it like you want to.
> > > > > > > >
> > > > > > > > After making changes to the code you´d need to restart OL
> or
> > > set the
> > > > > > > > cursor into Application_Startup and run it manually (press
> > > F5).
> > > > > > > >
> > > > > > > > --
> > > > > > > > Viele Grüße
> > > > > > > > Michael Bauer - MVP Outlook
> > > > > > > >
> > > > > > > >
> > > > > > > > "Tahlmorrah" <Tahlmorrah@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote
> in
> > > message
> > > > > > > > news:D9726C9C-C785-47FE-AA7A-878C8A818E31@xxxxxxxxxxxxxxxx
> > > > > > > > > Macros under Tools, Macros, Visual Basic is not
> displaying
> > > > > > > > GlobalData.bas so
> > > > > > > > > I am unsure of how to access this. Furthermore the form
> is
> > > still
> > > > > > not
> > > > > > > > popping
> > > > > > > > > up. I agree with your suggestions so now the code looks
.

Relevant Pages

  • Re: Open User Form from Template and populate message
    ... I do not have the tools to work in VBS, so I am working in VBA. ... course for Access) that only affects that document, but Outlook does not seem ... >> Private Sub Inspectors_NewInspector(ByVal Inspector As ... >> oMail.Body = Replace(oMail.Body, ReplaceCenter, TxtBxCenter.Text) ...
    (microsoft.public.outlook.program_vba)
  • Open User Form from Template and populate message
    ... Need to open a user form on open of Outlook Template and ... creaded from information entered in the UserForm. ... Problem: Have a standard template where the To: and CC: field are ... Private Sub ThisOutlookSession_Open ...
    (microsoft.public.outlook.program_vba)
  • Re: Open User Form from Template and populate message
    ... Private Sub Inspectors_NewInspector(ByVal Inspector As ... Now open your *.oft template ... Michael Bauer - MVP Outlook ... >> Private Sub Inspectors_NewInspector(ByVal Inspector As ...
    (microsoft.public.outlook.program_vba)
  • Re: Open User Form from Template and populate message
    ... The code behind an *.oft template isn´t VBA, ... > Private Sub Inspectors_NewInspector(ByVal Inspector As ... > oMail.Body = Replace(oMail.Body, ReplaceCenter, TxtBxCenter.Text) ... >>> opens is not an option due to it compromising system security. ...
    (microsoft.public.outlook.program_vba)
  • Re: Open User Form from Template and populate message
    ... Private Sub Inspectors_NewInspector(ByVal Inspector As ... ' It´s your template. ... Open now your UserForm. ... Michael Bauer - MVP Outlook ...
    (microsoft.public.outlook.program_vba)