Outlook message prompting user for data

[JaimeZX]

Hi guys. I am both (a) new here and (b) new to VB altogether so I apologize if this seems like a stupid question.

In my office we have an email we send several times per day to numerous recipients with just minor variations. Once upon a time we had a .vbs (written by an old employee) that popped up a text box asking two or three simple questions like the date and an item number. Unfortunately my previous boss was confused by technology (and therefore the script) and he managed to delete not only the script but the several backups I made. Now he is gone and I'd like to recreate the script.

Ex:
Textbox 1: What is today's date? [User enters "19 July 07"]
Textbox 2: What is the item code? [User enters "ABC123"]

Outlook message opens with the 15 recipients (or whatever) already in the TO: box and the subject line comes out as
"19 July 07 - Update to item ABC123"

That's just a basic example of what I'm looking to do since I won't bore you with the minutae. Anyway, then we can just quickly look over the email and click "send."

I found this code to open a new message by searching:

Set MyApp = CreateObject("Outlook.Application")
Set MyItem = MyApp.CreateItem(0) 'olMailItem
With MyItem
    .To = "a@b.c"
    .Subject = "Subject"
    .ReadReceiptRequested = True
    .HTMLBody = "This is the message.<BR>This is line 2."
End With
MyItem.Display

but I'm not sure how to make the textbox prompts and add that data to the new message. Any suggestions y'all can make would be much appreciated, because we spend countless hours every week on these emails and I think we could shave 75% off of that if I can get this script working again.

Thanks in advance!!

Regards,

Jim

 

[Danp129]

Try this:
sDate = InputBox("What is today's date?", "Infomation needed")
sIcode = InputBox("What is the item code?", "Infomation needed")

 

[JaimeZX]

Awesome! That worked beautifully.

I'd also like to have it automatically open the "File open" dialogue so I can attach a report (whose name will vary unpredictably based on not only the date but also the type of activity involved so unfortunately I can't have it add automatically.)

I searched the forum and found some code I tried to work in:

 Function ChooseFile
  Set f = CreateObject("SAFRCFileDlg.FileOpen")
  f.OpenFileOpenDlg
  ChooseFile f.FileName
  Set f = Nothing
End Function

But it's not working for me. Any other suggestions? Thanks so much guys!

 

[PHV]

ChooseFile [!]=[/!] f.FileName

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JaimeZX]

SWEET. That worked great. Any idea how I can have it default to a different directory than "My Documents?"

(Believe it or not I *am* searching old posts while I do this.)

 

[dm4ever]

UserAccounts.CommonDialog allows you to specify a default directory.

http://www.microsoft.com/technet/scriptcenter/resources/qanda/mar05/hey0301.mspx

--------------------------------------------------------------------------------
dm4ever
My philosophy: K.I.S.S - Keep It Simple Stupid

 

[JaimeZX]

That's pretty slick. For some reason it won't let me add multiple attachments but I can work around that since that'd be uncommon.

Any comments?

' ############################################## Begin script ##########################################################
'
'  ***** Open new message window *****
Set MyApp = CreateObject("Outlook.Application")
Set MyItem = MyApp.CreateItem(0) 'olMailItem
' Clears the memory
Set report = nothing
'
'
' @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
'
'  *********** This is the file attachment function. Don't mess with it.
'  ******************** Unless you need to change the default directory.
Function ChooseFile
Set report = CreateObject("UserAccounts.CommonDialog")
report.Filter = "Reports|*.doc|All Files|*.*"
report.Flags = &H0200
report.FilterIndex = 1
report.InitialDir = "C:\Scripts"
intResult = report.ShowOpen
If intResult = 0 Then
    Wscript.Quit
Else
    arrFiles = Split(report.FileName, " ")
    For i = 1 to Ubound(arrFiles)
        strFile = arrFiles(0) & arrFiles(i)
        Wscript.Echo strFile
    Next
End If
End Function
' @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
'
'
'  *******  Boxes to input information in the email come from here.
sDate = InputBox("What is today's date?", "Infomation needed")
sIcode = InputBox("What is the item code?", "Infomation needed")
With MyItem
'
'
' $$$$$$$$$$$$ Put all email recipients inside the quotes, separated by semicolons
    .To = "a@b.c; b@c.d"
    .Cc = "e@f.g"
'
'
'
'  ******* Subject line is automatically generated. You probably shouldn't mess with this.
    .Subject = "Subject" & " " & sDate & " " & sIcode
    .ReadReceiptRequested = True

'
'
' $$$$$$$$$$$$ Body of email is in the text below. The string <br> is a line break (return/enter).
    .HTMLBody = "This is the message.<BR>This is line 2."
'
'
'   ******* Attach the file
ChooseFile
Set myAttachments = myItem.Attachments
 myAttachments.Add report.Filename
End With
'
' Clear memory
Set report = Nothing
' Show the email window
MyItem.Display
'
' ################################################ End of script ############################################################

 

[JaimeZX]

Okay, hopefully this pops the thread back up to the top?

The above script works fantastic for what I *thought* I needed, but now apparently we need to attach a certain Word file to the email.

I've figured out how to get it to open a new Word document, but how can I dynamically insert content into the Word document based on the above text-box inputs? Additionally, is there a way where I can add 10 years to the current date? So if I put 20070808 for "today's date," I would get 20170808 for the bottom line? Or should that be two text boxes, like "What is the year? (yyyy)" and "What is the month & day? (mmdd)"

Thanks again guys!

Example Word contents:
===============================
Date//
Item code//
(Date plus 10 years)//
===============================

 

[JaimeZX]

Wait; I figured out how to get the data into Word.

My questions are now much less. How can I make certain lines show up in red? Google has been less helpful here. I'd also like to automatically set the margins and use drop-down boxes for items where there are only 2-4 choices. For others I'd like to keep the text box but have a default answer already inside of it. I'll keep digging but if y'all have suggestions they WOULD be appreciated!

 

[luki08]

Hi Jim,
I am totally new to VBScript.
Please, can u give me advice, how can I get the data into Word? I am searching that, but still haven't succeed
I'd like to open word template and fill it with some data automatically. Or I can create the whole .doc (without using template), doesn't matter. Just still don't know, how to put some data into word document from VBScript.
Thanks in advance,
Jana

 

[luki08]

Hi guys,
I've found it, so u can forget the last post. Sorry.
have a nice day everybody!