Reply Form to Limit Possible Reply Strings

Printable View

08-27-2021, 10:50 AM
Jakson

Reply Form to Limit Possible Reply Strings

Howdy, I'm kinda a noobie to VBA and VBScript. I'm working with a small agency that needs some scheduling assistance. I've built a scheduler that takes some dates from Excel, runs some calculations, determines how many days away the given date is from today, then sends an employee an email. The employee must only respond with one of 5 possible options, basically a yes, no, or maybe.

I'm having a few difficulties with this, namely, getting the employees to specifically use the only "yes", "no", or "maybe" with exactness. No capitalizations, no punctuation... etc... This is due to the way I'm capturing their response with a string split.

I'm also having a very difficult time finding solid examples about this issue, or for something similar. I'm still too new to really know what I'm asking for. I've been recommended using a form to generate specific responses. I can't find my way through building a form and publishing it though. Help, please?

The problem:
I need to limit the number of possible ways for an employee to respond to an automated email. Their response MUST be exactly typed to get picked up by my string split macro. I want to use a form, but I'm not versed enough to know what I'm doing. (Or where to look for more information.)

TL;DR
Help, I'm a noobie and I don't know how to set up, then publish a form or get data out of the form!
08-28-2021, 01:45 AM
arnelgp

you can use a Fillable PDF (using combobox for their response).
08-28-2021, 07:03 AM
Logit

https://analystcave.com/word-making-...doc-automated/

You could use Survey Monkey: http://www.surveymonkey.com

Use of Outlook Forms : https://www.tek-tips.com/viewthread.cfm?qid=1231337 '<-- untested here. If this doesn't work, google for similar
Another Outlook Forms : https://www.quill.com/blog/tutorials...y-outlook.html
08-29-2021, 12:54 AM
gmayor

A fillable PDF as suggested by arnelgp is a possibility, but PDFs are much harder to interrogate for the results than Word documents. I would therefore suggest that you use a Word document attachment to create your form with content controls. A combo box control with your choice of values can be locked against deletion. You could use Extract data from forms (gmayor.com) to extract the date from the returned forms. There are other pages on my web site related to the extraction of data and https://www.gmayor.com/insert_content_control_addin.htm will help with he content controls
08-30-2021, 01:57 PM
Jakson

Quote:

Originally Posted by arnelgp

you can use a Fillable PDF (using combobox for their response).

I've mulled this over for a while and the big problem with using a form like that is that it's an attachment. Ideally this procedure will remove more steps than add steps to the reply process.

I'm realizing what I want is unrealistic for my skill set, but not impossible.

I've devised that I can generate a reply form, .oft, and passing them to every user in the circle. They'll have to be trained to know to use the reply forms to respond to the Excel sheet. :think:
08-30-2021, 02:16 PM
Jakson

Quote:

Originally Posted by Logit

You could use Survey Monkey:

Use of Outlook Forms : '<-- untested here. If this doesn't work, google for similar
Another Outlook Forms :

I really appreciate your providing some links for reading materials. I checked em' all out and some of the information was helpful. I tried out that untested HTML form method and it didn't work. I'm not sure why, and I don't know how to get it to work. I'm at a loss for words to dig for further documentation/help. LOL.

What I'm going to try out instead is the use of forms as a reply form... I turned my prism. I don't need a send form and a reply form, just the one form on the employee's computer with the response worded out.

Thank you very much for your help! : pray2:
08-30-2021, 02:21 PM
Jakson

Quote:

Originally Posted by gmayor

A fillable PDF as suggested by arnelgp is a possibility, but PDFs are much harder to interrogate for the results than Word documents. I would therefore suggest that you use a Word document attachment to create your form with content controls. A combo box control with your choice of values can be locked against deletion. You could use [link] to extract the date from the returned forms. There are other pages on my web site related to the extraction of data and [link] will help with he content controls

I checked out your links and it looks like a solid method, but it only adds to the complexity of the problem. One of my pressure's is to remove as many steps in the process as possible. The ideal solution would be a single page with all the relevant answers on it and a radio button to select the appropriate answer. I realize this is a little idealistic, and furthermore, beyond my abilities...

Instead, I'm going to try out using a form. I can load up a form on each of employee's computer and have them use the forms to reply instead of typing out their responses. Hopefully this should limit the number of steps and prevent accidental typos and errors.

Thank you for the info!!
08-31-2021, 04:49 PM
Jakson

Quote:

Originally Posted by Jakson

The problem:
I need to limit the number of possible ways for an employee to respond to an automated email. Their response MUST be exactly typed to get picked up by my string split macro. I want to use a form, but I'm not versed enough to know what I'm doing. (Or where to look for more information.)

TL;DR
Help, I'm a noobie and I don't know how to set up, then publish a form or get data out of the form!

UPDATE:
I stumbled my way through the internet enough to gather some more information about this process, many thanks to the helpful and generous fellows above this post. I discovered a method, I'm pretty sure that's the right word, for the MailItem object to replace the normal OFT blank email with a custom user generated OFT with form controls and such. (I've been using the wrong word, it's TEMPLATE not FORM!) This worked!!! SO happy!... I made it work a few times, however, when I tried to transfer this template to a test computer everything came falling apart- piecemeal.

To save the sob story, I've resolved all (but this tid-bit!) of my poor coding with fixes and now I'm stuck, this template won't update! I made a test template, then a true-to-needs template. I can't stop getting the test template instead of the new true-to-needs template. I've dived through the Outlook folders and cleared out the IPM.Note folders for the old test template, even the new template. I dove into the Outlook specific template folders and cleared those for references to the test template as well. Finally, I cleared all other templates through the Outlook client menus specifically.

For clarity, I have working code, it sends the OFT as an email correctly, I have a working test OFT and a working true-to-needs OFT with all the form controls I need laid out. I can't get my test Outlook client to send to my main account anything but the old test template! Doesn't matter if I make a new template, change the old template name, delete the IPM folders, replace the templates in the Outlook client through the menus... I'm completely at a loss and rather frazzled, please forgive any upset mood you get through the monitor...

Is there anything I'm not considering? Have I skipped a vital step? I keep getting this strange error I can hardly find any valuable information on... Its: " Run-time error '-2147287038' (80030002) " (Fingers crossed a very intelligent individual would happen to know this means I'm being silly and that they might know the resolution... like not being so silly.)

I could only find a few internet patrons who also experienced this error. (It seems like rather grave user error...) I realized I had misspelled the name of the .OFT file in my code when I originally got this error. I corrected the name and made sure to read over the folder path too for certainty. Both were correct, the error stopped, and yet-... I still get the old test template.

I'd be willing to post code if requested. I'm not sure that it'd help anyone so I'll refrain for now. :think::bug:
08-31-2021, 09:37 PM
gmayor

Outlook templates are by default stored in the user's Microsoft Word template folder. If you have not changed that folder enter or copy
%appdata%\Microsoft\Templates.
to the address window of Windows File Explorer and you will be taken to the appropriate (normally hidden) folder.
The error message suggests that you are trying to create a message with a template that doesn't exist in the folder on that particular PC.
You can call your templates using a macro to create the message using one or other of your named templates e.g. as follows

Code:

Sub CreateMsg() 'Graham Mayor - https://www.gmayor.com - Last updated - 01 Sep 2021 Dim olItem As MailItem Dim sPath As String, sFile As String Const sTemplate1 As String = "test.oft" 'the filename of the test template Const sTemplate2 As String = "true-to-needs.oft" 'the filename of the true to needs template start: sFile = InputBox("Enter 1, for 'test' template" & vbCr & _ "Enter 2, for 'true-to-needs' template") Select Case sFile Case Is = "1" sFile = sTemplate1 Case Is = "2" sFile = sTemplate2 Case Else MsgBox "Enter 1 or 2 only!", vbCritical GoTo start: End Select sPath = Environ("USERPROFILE") & "\AppData\Roaming\Microsoft\Templates\" 'the default template path If Dir(sPath & sFile) = "" Then MsgBox sPath & sFile & vbCr & "Template not found", vbCritical GoTo lbl_Exit End If Set olItem = CreateItemFromTemplate(sPath & sFile) With olItem .BodyFormat = olFormatHTML .Display End With lbl_Exit: Set olItem = Nothing Exit Sub End Sub
09-01-2021, 08:52 AM
Jakson

Quote:

Originally Posted by gmayor

Outlook templates are by default stored in the user's Microsoft Word template folder. If you have not changed that folder enter or copy
%appdata%\Microsoft\Templates.
to the address window of Windows File Explorer and you will be taken to the appropriate (normally hidden) folder.
The error message suggests that you are trying to create a message with a template that doesn't exist in the folder on that particular PC.
You can call your templates using a macro to create the message using one or other of your named templates e.g. as follows

Code:

Sub CreateMsg() 'Graham Mayor - https://www.gmayor.com - Last updated - 01 Sep 2021 Dim olItem As MailItem Dim sPath As String, sFile As String Const sTemplate1 As String = "test.oft" 'the filename of the test template Const sTemplate2 As String = "true-to-needs.oft" 'the filename of the true to needs template start: sFile = InputBox("Enter 1, for 'test' template" & vbCr & _ "Enter 2, for 'true-to-needs' template") Select Case sFile Case Is = "1" sFile = sTemplate1 Case Is = "2" sFile = sTemplate2 Case Else MsgBox "Enter 1 or 2 only!", vbCritical GoTo start: End Select sPath = Environ("USERPROFILE") & "\AppData\Roaming\Microsoft\Templates\" 'the default template path If Dir(sPath & sFile) = "" Then MsgBox sPath & sFile & vbCr & "Template not found", vbCritical GoTo lbl_Exit End If Set olItem = CreateItemFromTemplate(sPath & sFile) With olItem .BodyFormat = olFormatHTML .Display End With lbl_Exit: Set olItem = Nothing Exit Sub End Sub

Beautiful code, gmayor!! Thank you!

I'll have to give this a go Thursday to see if I can get over my grievous user-error. Thank you very much! :bow:
09-03-2021, 02:38 PM
Jakson

Quote:

Originally Posted by gmayor

Outlook templates are by default stored in the user's Microsoft Word template folder. If you have not changed that folder enter or copy
%appdata%\Microsoft\Templates.
to the address window of Windows File Explorer and you will be taken to the appropriate (normally hidden) folder.
The error message suggests that you are trying to create a message with a template that doesn't exist in the folder on that particular PC.
You can call your templates using a macro to create the message using one or other of your named templates e.g. as follows

Code:

Sub CreateMsg() 'Graham Mayor - https://www.gmayor.com - Last updated - 01 Sep 2021 Dim olItem As MailItem Dim sPath As String, sFile As String Const sTemplate1 As String = "test.oft" 'the filename of the test template Const sTemplate2 As String = "true-to-needs.oft" 'the filename of the true to needs template start: sFile = InputBox("Enter 1, for 'test' template" & vbCr & _ "Enter 2, for 'true-to-needs' template") Select Case sFile Case Is = "1" sFile = sTemplate1 Case Is = "2" sFile = sTemplate2 Case Else MsgBox "Enter 1 or 2 only!", vbCritical GoTo start: End Select sPath = Environ("USERPROFILE") & "\AppData\Roaming\Microsoft\Templates\" 'the default template path If Dir(sPath & sFile) = "" Then MsgBox sPath & sFile & vbCr & "Template not found", vbCritical GoTo lbl_Exit End If Set olItem = CreateItemFromTemplate(sPath & sFile) With olItem .BodyFormat = olFormatHTML .Display End With lbl_Exit: Set olItem = Nothing Exit Sub End Sub

Your code works elegantly and fulfills all of my needs! However, I'm still experiencing the issue related to the OP.

The more I test the more I believe I'm not understanding- or lacking something crucial that I haven't realized yet. I need my end user to see the same template email as is sent from the automation project. Your code causes the correct template to be brought up on the sender side - however, the receiving side does not see the same template. Is this another instance of user error? Have I skipped some vital set-up for the end user?

I'm not at all sure if this is related, but when I remove the true-to-needs.oft from the sending computer, the receiving computer can no longer open the email containing what should be the same template. The previously mentioned behavior persists when I remove the template from the sender's files, which, I suppose makes sense. How can it display something that doesn't exist anywhere?

Thoughts? Suggestions?

*****Edit:

I just tried the following and experienced the same issue as described above:
I sent the true-to-needs.oft file from the sender computer to the receiver computer via a little thumb drive. I loaded the template file into my receiver's outlook files, opened the template through the Outlook client menu, and then sent the template to the sender account. The sender account got the same old email template that the receiver was getting from the sender account. I'm not sure what about it is doing it but it will display the correct template on either computer, appear to send the correct template, then receive the wrong old template instead of the new one it had just displayed.

I tried sending the test.oft and it has no form controls or anything on it, it is effectively a blank email. That seems to send just fine! I'm so lost.

*********************Final Edit:

I figured out my mistakes! I'll highlight them in a new post so that anyone coming back to this thread won't get so mired in the back/forth...

I forgot that there is a distinction between COMPOSE and REVIEW. To make a "reply" template you have to fabricate the "REVIEW" view of the template file.

Thank you, thank you, thank you EVERYONE!

Especially you, gmayor!!! You helped me a ton!!!!!:cloud9:
09-03-2021, 03:41 PM
Jakson

Resolution:

Make a new template file: Developer > Design a form. Publish your form so Outlook can produce the necessary IPM.Note folder structure and files. Make necessary modifications to the Compose view, the initial view(when you are producing the email before first pressing send). Make necessary modifications to the Review view, this is achieved by clicking "Page" > "Edit Read Page"(when your end user is responding to your email).

Before finishing, you need to set up what Outlook does when the end user clicks "REPLY". To do this, click "(Actions)" in the edit form view. In the "(Actions)" tab, there are four possible response actions, Reply, Reply to All, Forward, and Reply to Folder. If you have a desired effect or form for each specific action, here is how you change those behaviors. For my purposes, I need the reply form to be identical to the compose form. In the third column, "Creates form of type", click Reply and you will be prompted to select a new form in place of Reply. Click the "Form name:" drop down and find your form. Double click or click "SELECT" in the menu. Click OK on the "Form Action Properties" window.

Click Publish > Publish Form to save over the newly created form. Click File, Save As, pick destination folder, change file type to .oft. Stick your .oft file in %appdata%\roaming\Microsoft\Templates on the end users' and senders' computers.

Once the .oft file is saved onto a new computer you can compose a new Form from the Design a Form menu in Outlook similarly to the method described above. Instead of creating a wholly new form template, click Design a Form. In "Look In" drop down, click "User Templates in File System". Identify your .oft file and select it. Outlook should now open the .oft file as though it were a complete and published form. Publish the form, save the new .oft file over the old one you imported for certainty.

Note: Be certain that both the sending computer and the receiving computer have the same .oft and IPM.Note folders and files, and that they are named exactly the same.

PLEASE PLEASE PLEASE!!!! If I am wrong, or misinforming, or am missing something, PLEASE TELL ME! I want this to be accurate and not at all misinforming or misleading.