Outlook Form Dump Into Database/Spreadsheet? 1

[jennuhw]

Is there a way with VBScript to have an Outlook form dump the information into an Access database or an Excel spreadsheet?

 

[jennuhw]

The reason why I have posted this in VBScript forum instead of Outlook is because to do this with code I have to use VBScript. I don't have a choice with Outlook forms. I would appreciate any suggestions! Thanks.

 

[PerlIsGood]

Here's a bit of code I used in an Outlook form:

Set fso = CreateObject("Scripting.FileSystemObject")
FileLoc = "c:\path\to\file.xls"

    'Check to see if Excel is already open
    If fso.FileExists(FileLoc) Then
        On Error Resume Next
        Set oExcel = GetObject(, "Excel.Application")
        If Err.Number <> 0 Then
            Err.Clear
            Set oExcel = CreateObject(&quot;Excel.Application&quot;)
        End If
    Else
        strErr = FileLoc & &quot; could not be found.&quot;
        MsgBox (strErr), vbCritical, &quot;Error: File Not Found&quot;
        Exit Function
    End If

Set oWrkBook = oExcel.Workbooks.Open (FileLoc)

oExcel.Worksheets(&quot;Sheet 1&quot;).Cells(1, 1).Value = var1
oExcel.Worksheets(&quot;Sheet 1&quot;).Cells(1, 2).Value = var2
oExcel.Worksheets(&quot;Sheet 1&quot;).Cells(1, 3).Value = var3
oExcel.Worksheets(&quot;Sheet 1&quot;).Cells(1, 4).Value = var4
oExcel.Worksheets(&quot;Sheet 1&quot;).Cells(1, 5).Value = var5
oExcel.Worksheets(&quot;Sheet 1&quot;).Cells(1, 6).Value = var6

oWrkBook.Save
oWrkBook.Close
oExcel.Visible = True
Set oWrkBook = Nothing
Set oExcel = Nothing
Set fso = Nothing

HTH Notorious P.I.G.

 

[jennuhw]

I will give that a try! Thanks!

 

[jennuhw]

Okay, I have tried this, but nothing is showing up on the spreadsheet. In the line: oExcel.Worksheets("Sheet 1&quot.Cells(1, 6).Value = var6 is var6 the name or the field in Outlook that I want to put in that cell?

 

[PerlIsGood]

Yes, var* should be whatever value you want from the outlook field to go into the database. For example:

Set username = Item.GetItem.ModifiedFormPages("THEPAGE").Controls("THEFIELD")

'Make sure that 'Sheet 1' is where you want everything
'to go, and make sure you're pointing to the right excel
'file...
oExcel.Worksheets("Sheet 1").Cells(1, 6).Value = username

If you used the script exactly as I posted before, then it's probably working correctly and adding var6 to the worksheet. Unfortunately, to the script var6 is 'undefined'.

Notorious P.I.G.

 

[PerlIsGood]

Doh. Correction: Item.GetInspector, not Item.

*need edit button* Notorious P.I.G.

 

[jennuhw]

Okay, I actually just ended up putting the name of the field not using a Set statement to define the variable. Thanks a bunch!

 

[jennuhw]

This is working great when the form is first opened up and sent. I put this info in the Item_Send function. But, when the form is forwarded, it doesn't work anymore. I put the code in Item_Forward, but it doesn't enter either function. Any ideas?

 

[PerlIsGood]

Can you expand on that a little more? Did you copy the code from the Item_Send function, making sure to get all the Set variable lines as well...? Sounds like you may have either missed something when you copied the code from Item_Send to Item_Forward, or the form definitions may not be set correctly...

I feel like I'm rambling ... so little to go on here You might want to just post that function here so we can take a look-see at it. Notorious P.I.G.

 

[jennuhw]

I copied all of the code from the Item_Send and modified what I needed to. I put a MsgBox right after the Item_Send and Forward to see if it was even entering either function when I forward the form. The MsgBoxes never showed, so I know that the code is not even being ran in either function.


Function Item_Send()
msgbox ("sending&quot
Set Esigncode = Item.UserProperties.Find ("Employee signature or code&quot
Set Ssigncode = Item.UserProperties.Find ("Supervisor signature or code&quot
Set Eackcode = Item.UserProperties.Find ("ackapprove&quot
Set Sackcode = Item.UserProperties.Find ("ackapprove2&quot
Set dept = Item.UserProperties.Find ("pif28&quot
Set eventt = Item.UserProperties.Find ("pif29&quot
'bunch more stuff
End Function

Function Item_Forward()
msgbox ("forward&quot
Set Esigncode = Item.UserProperties.Find ("Employee signature or code&quot
Set Ssigncode = Item.UserProperties.Find ("Supervisor signature or code&quot
Set Eackcode = Item.UserProperties.Find ("ackapprove&quot
Set Sackcode = Item.UserProperties.Find ("ackapprove2&quot
Set dept = Item.UserProperties.Find ("pif28&quot
Set eventt = Item.UserProperties.Find ("pif29&quot
'bunch more stuff
End Function

Neither message box ends up coming up! I have made sure that forwarding is enabled for the form, and I have received it with no problems. I think I am missing something really dumb! Thanks for your help!

 

[PerlIsGood]

Hi jennuhw,

I'm still not seeing anything. We could probably trade posts for the next week trying to figure this one out - if you want, we can save time (and posts) and you can email the form file to me (your_form.oft) and I can try and figure out what's going on.

Let me know if that sounds like an option... Notorious P.I.G.

 

[jennuhw]

I can forward you my form. I will have to change a couple of things first. What is your email address? Thanks!

 

[PerlIsGood]

No Prob. You can send it to perlisgood@hotmail.com

Try to remove only what's necessary from the code (company info, personal info, all that stuff). Notorious P.I.G.

 

[PerlIsGood]

Ok, a few things that I noticed that are causing problems [these might not be issues depending on how your files are setup]. For my test I used a blank .xls spreadsheet, deleted the Item_Forward function, and added numbering to the 1st column of my .xls file.

::::::::::::::::::::::::

Add data to Workbook|Send Function|Forward Function:

For n = 1 to rows
  val = Ucase( trim( Actv.Cells(n,1).Value ) )
  if val = var1 then
    'Add data to Excel spreadsheet

This block looks for the value in TextBox23, which is the forms tracking number, which in turn is what is assigned to val. Both Sending and Forwarding worked perfectly once I manually added the current form's number to the spreadsheet. I don't know if you pre-numbered the first column of your spreadsheet. If you did, then this shouldn't be an issue - but this sounds like it may have been your problem. I would recommend adding an ElseIf case to the If val = var1 then statement to handle the occasion when the form doesn't find the number, in turn telling the code to add a new row instead of 'doing nothing'.

Every time the form is opened TextBox23 is incremented - no matter what. This could make it difficult to track this same form as it gets passed around the office because every person that opens it is going to get a new form number, which will mean a new and separate entry in the spreadsheet. In Item_Open, you might want to try testing against one of the forms 'required fields' instead of the tracking number field, this way you'll know if it's a new form or one currently in use. Of course, ignore this if that's how you wanted it work.

Useless Code: I found that the Item_Forward function is useless unless you need it to do something different from the Item_Send function, or if you need the form to perform some actions between the point when the user clicks "Forward" and "Send".

Example:
User1 -> Creates and sends form to User2 -> Item_Send is triggered
User2 -> Modifies, Approves, and Forwards form to User3 -> Item_Send is still triggered

Having identical code in Item_Send and Item_Function just causes the program to repeat itself.

::::::::::::::::::::::::

General Form Observations|Concerns:

You seem to have allot of private/confidential data [authorization codes, etc.] hard-coded into the form. I don't know if you 'UN-password-protected' your form before you sent it to me, but even if you did there's a chance that a disgruntled (or just plain mischievous) employee with the right skills (or allot of free-time) could extract this information. Just a couple suggestions:

1. -> Password protect the form!
2. -> Perhaps store sensitive data in a separate file, maybe a password-protected
Access database. Haven't tried manipulating Access from Outlook yet so I
can't provide any practical examples, but this would probably be the most
secure location for this info. I'm sure you could do a search here on how
to query an Access database...

::::::::::::::::::::::::

That's it. I think I touched on everything. Hopefully this will help you iron-out the program. Great form by the way, nice work! Notorious P.I.G.

 

[jennuhw]

Yeah!!! Thanks for the suggestions. Yes, I did have the form password protected, but I undid it for you. I knew my code wasn't to wrong once you said taht it worked. I did a little research and figured out that I needed to change my message class to the form and not message. This enabled the form to perform the Item_Send function! Thank you so much for your help!