Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 27

Thread: Solved: Outlook Body Text to Text File, Scripting and Guard Issues

  1. #1
    VBAX Regular
    Joined
    May 2007
    Posts
    87
    Location

    Solved: Outlook Body Text to Text File, Scripting and Guard Issues

    Hi,

    I want to be able to configure Outlook to run a few simple batch files whilst I'm away from my desk (in meeting, email a few commands from a handheld, and PDF sent to my boss whilst I'm still in meeting).

    Most of this is working, except I need to approve the saveas/ file write to have access to my "Address book" each time it saves the text from an email. This defeats the object.

    I got that problem when I used this format:


    [vba]'------------------------------------------------
    Public Sub PRS(MyMail As MailItem)
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set aFile = fso.CreateTextFile(strTarget & "C:\PRS\REALLY_PAR.txt", True)
    parastring = MyMail.Body

    aFile.WriteLine (parastring)
    aFile.Close

    Set fso = Nothing
    Set aFile = Nothing

    End Sub

    '------------------------------------------------[/vba]



    Since then I've been through archieves and found a posting on this website, which flashed up the contents of a email (text only) as a MSGBOX string, I've modded that to the following. This *almost* works, but unfortunately when the email comes in with the code word in subject line, it doesn't auto select, so after the command comes in, it saves the text from the last email I was looking at to the text file. -No Good! -

    Here's the modded text macro, any ideas? :




    [vba]'------------------------------------------------
    Sub GetSelectedMailText()
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set aFile = fso.CreateTextFile(strTarget & "C:\PRS\PAR.txt", True)
    Dim myOlSel As Outlook.Selection
    Dim strText As String

    'check there's something selected
    If Application.ActiveExplorer.Selection.Count = 1 Then
    Set myOlSel = Application.ActiveExplorer.Selection
    strText = myOlSel.Item(1).Body
    'do something with the mail item body text

    aFile.WriteLine (strText)
    aFile.Close

    End If

    Set fso = Nothing
    Set aFile = Nothing
    End Sub

    '------------------------------------------------[/vba]



    Thanks in advance.

    -Andy S

  2. #2
    VBAX Regular
    Joined
    May 2007
    Posts
    87
    Location
    Anyone any ideas?

    Cheers.

    -Andy

  3. #3
    VBAX Regular
    Joined
    May 2007
    Posts
    87
    Location
    Anyone any ideas on this?

    I know I can do it by saving a textfile and opening that, but it kind of negates the object if its sooooo convoluted.

    HELP!

    -AS

  4. #4
    Knowledge Base Approver
    The King of Overkill!
    VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    Hi Andy,

    We can get this working for you.. the original PRS macro above looks like one you'd call from a Rule, so I'm guessing you have 2003? It is a lot less code using 2003 since you can send an email to a sub like that, the other thread is an example of how you could do it using only events (and I'd be happy to help modify that for you if you cant use the Rules).

    As for the 'need to approve the saveas/ file write to have access to my "Address book"', check out http://www.mapilab.com/outlook/security for a free add-in that will click that box for you automatically. You can also get a (free) standalone program called ClickYes to do the same, I've heard the mapilab addin is nicer since it integrates into outlook (I still use OL2000 -- no security popups )

    Also, to make it easier, FYI - if you enclose your code in [vba][/vba] tags then it will be formatted as per the VBIDE to improve readability.
    I'll take care of the code snippets above though
    Matt

  5. #5
    VBAX Regular
    Joined
    May 2007
    Posts
    87
    Location
    Quote Originally Posted by mvidas
    Hi Andy,

    We can get this working for you.. the original PRS macro above looks like one you'd call from a Rule, so I'm guessing you have 2003? It is a lot less code using 2003 since you can send an email to a sub like that, the other thread is an example of how you could do it using only events (and I'd be happy to help modify that for you if you cant use the Rules).
    I've got a modified form of the other thread working for saving a text file, so I think I can get this going now, though obviously I'm an engineer, not a programmer, so it'll be a little messy!

    I'll let you know how I get on!

    -Andy

  6. #6
    VBAX Regular
    Joined
    May 2007
    Posts
    87
    Location
    Yes, it's 2003 I'm doing this with, and I'm not sure how the IT dept would take it if I were to put a "Click Yes" bypass on that security measure.

    -AS

  7. #7
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    Though I haven't personally used it, I believe I read the MAPILab addin allows for some customization, ie. it would only Click Yes for certain procedures, and not all. So if you did get some kind of virus that tried to do harm it wouldn't click yes for that automatically, only your chosen macro.
    Matt

  8. #8
    VBAX Regular
    Joined
    May 2007
    Posts
    87
    Location
    OK, having looked further into it, I think I will need a little help here.

    I originally had a Rule that if the title contained the word "AutoStair" it would run this batch file from a command line.

    That batch file then calls a parameter file of the form:

    param.txt
    =================
    nosteps = 23
    width = 1000
    holes= 8
    height = 1200
    =================

    If this param.txt file was exactly the same as the contents of the email body, then I could basically finish the rest.

    I do like the routine you've done on the other thread, and as I said I'm probably not allowed a "Click Yes" application on a work machine, so if there was an easy alteration that could be done on the other text I think that would really KICK

    -AS

  9. #9
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    Having never had to deal with that security prompt I don't think there are any tricky ways around it (aside from the standalone program or outlook add-in).

    What you could do (unrelated to the security thing) is change that AutoStair rule to "Run a script" as the desired action, and for the macro choose this:[vba]Sub CheckMessageAgainstFile(vMsg As MailItem)
    Dim vFF As Long, vFile As String, FileContents As String
    vFile = "C:\param.txt" 'file to compare against

    'load file into string variable
    vFF = FreeFile
    Open vFile For Binary Access Read As #vFF
    FileContents = Space$(LOF(vFF))
    Get #vFF, , FileContents
    Close #vFF

    If FileContents = vMsg.Body Then
    'message has same text as file
    End If
    End Sub[/vba]
    That way each message that fits the rule will be sent to this sub, which will compare the text file to the contents of the message. Is that what you're looking to do, or did I read it wrong?
    Matt

  10. #10
    VBAX Regular
    Joined
    May 2007
    Posts
    87
    Location
    Quote Originally Posted by mvidas
    Having never had to deal with that security prompt I don't think there are any tricky ways around it (aside from the standalone program or outlook add-in).

    What you could do (unrelated to the security thing) is change that AutoStair rule to "Run a script" as the desired action, and for the macro choose this:
    Sorry, I was clear as mud last post, I just want to save the contents of an email body as a text file.

    What I'd been trying to say was if the email was already sent formatted, then there would be minimal processing, and a straight
    writeline (Mail.body)
    type arrangement would do the job!

    If the last routine you wrote in the other thread could be made to just copy the contents of the email body into a text file, the rest should run (with a little shoe-horning!)

    Thanks,

    -Andy.


    -AS

  11. #11
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    Well I still say you should use the Run As Script option in a rule, ie with something like[vba]Sub SaveMessageToFile(vMsg As MailItem)
    Dim vFF As Long, vFile As String
    vFile = "C:\saved.txt" 'file to save to

    'save message to text file
    vFF = FreeFile
    Open vFile For Output As #vFF
    Print #vFF, Msg.Body
    Close #vFF
    End Sub[/vba]But if you mean by that other thread that you want to check for a certain pattern in the message body before saving (to see if its formatted as you want it), we could add that to the script above too (regular expressions, which i used in the other thread, would make it simple). The other thread just gives a workaround to running a macro from a rule, as I wasn't sure what version he had
    Matt

  12. #12
    VBAX Regular
    Joined
    May 2007
    Posts
    87
    Location
    It's not that, it's more the fact your last one (maybe because it was formatting) didn't trigger the guard on my O2003, I've got this one running from a rule again, and I find it trips the guard.

    btw, had to make two minor alterations to the program before it ran:
    <vba>
    Public Sub SaveMessageToFile(vMsg As MailItem)
    Dim vFF As Long, vFile As String
    vFile = "C:\saved.txt" 'file to save to

    'save message to text file
    vFF = FreeFile
    Open vFile For Output As #vFF
    Print #vFF, vMsg.Body
    Close #vFF
    End Sub
    </vba>

  13. #13
    VBAX Regular
    Joined
    May 2007
    Posts
    87
    Location
    I've just cross-bred your two routines and come up with this nasty looking piece.

    It now (in combination with your last routines on other thread) saves the entire, unedited body of message as a text file in the root directory as savem2e.txt.

    I think I can tidy up and get this thing working, but if oyu have any tips, would be greatly appreciated.

    Thanks for your help, if you need any help with ProE, SldWKS, SldEdge or Inventor, let me know!

    -AS


    [vba]
    Private Sub vInbox_ItemAdd(ByVal Item As Object)
    If TypeName(Item) <> "MailItem" Then Exit Sub
    Dim RegEx As Object, vFF As Long, vFile As String, vBody As String

    'Set the output CSV file location here
    vFile = "C:\savem2e.txt"
    vBody = Item.Body
    Set RegEx = CreateObject("vbscript.regexp")
    With RegEx
    .IgnoreCase = True
    .Multiline = True
    .Pattern = "Name[^\n\r]*)[\n\r]*Telephone[^\n\r]*)[\n\r]*Asks for[^\n\r]*)"
    End With
    If RegEx.Test(vBody) Then
    vFF = FreeFile
    Open vFile For Output As #vFF
    Print #vFF, vBody
    Close #vFF
    'Open vFile For Append As #vFF
    'With RegEx.Execute(vBody).Item(0)
    ' Print #vFF, Trim(.SubMatches(0)) & "," & Trim(.SubMatches(1)) & "," & _
    ' Trim(.SubMatches(2))
    'End With
    'Close #vFF
    Item.UnRead = False 'Mark As Read
    End If
    Set RegEx = Nothing
    End Sub
    [/vba][vba]

  14. #14
    VBAX Regular
    Joined
    May 2007
    Posts
    87
    Location
    ....Saves entire body of email without alerting the guard, forgot to say that one important piece of info!

    Cheers,

    -Andy.

  15. #15
    VBAX Regular
    Joined
    May 2007
    Posts
    87
    Location
    ....still looks for the name: Telephone: Asks for:, though, so I'll keep working on it until it just accepts the trigger words and then saves the parameters.

    -AS

  16. #16
    VBAX Regular
    Joined
    May 2007
    Posts
    87
    Location
    Now I've modified your code to take the conditional "If/endif" statements out.

    I'll have a play with this and hopefully unveil the new code next week.

    Thanks for your help Matt.

    -Andy.

    [VBA]
    Option Explicit
    Dim WithEvents vInbox As Items
    Private Sub Application_Startup()
    Set vInbox = Application.Session.GetDefaultFolder(olFolderInbox).Items
    Call CheckInbox
    End Sub
    Private Sub Application_NewMail()
    If vInbox Is Nothing Then
    Set vInbox = Application.Session.GetDefaultFolder(olFolderInbox).Items
    Call CheckInbox
    End If
    End Sub
    Private Sub Application_Quit()
    Set vInbox = Nothing
    End Sub
    Function CheckInbox() As Boolean
    Dim vItems As Object, vItem As Object
    Set vItems = Application.Session.GetDefaultFolder(olFolderInbox).Items
    For Each vItem In vItems
    If TypeName(vItem) = "MailItem" Then
    If vItem.UnRead = True Then
    Call vInbox_ItemAdd(vItem)
    End If
    End If
    Next
    Set vItem = Nothing
    Set vItems = Nothing
    End Function
    Private Sub vInbox_ItemAdd(ByVal Item As Object)
    If TypeName(Item) <> "MailItem" Then Exit Sub
    Dim RegEx As Object, vFF As Long, vFile As String, vBody As String
    'Set the output Txt file location here
    vFile = "C:\paramsy.txt"
    vBody = Item.Body
    vFF = FreeFile
    Open vFile For Output As #vFF
    Print #vFF, vBody
    Close #vFF
    Item.UnRead = False 'Mark As Read
    Set RegEx = Nothing
    End Sub
    [/VBA][VBA]

  17. #17
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    Interesting.. I wonder why getting the .Body in one method triggers security but the other doesn't? Good to know at least.. maybe its the fact that it is an event? I'll have to take a closer look as to why..

    As for the sub, it will save every message to a text file, not just the ones with the certain subject (though you can use something like 'If Item.Subject = "AutoStair" Then'). But you can remove the RegEx references:[vba]Private Sub vInbox_ItemAdd(ByVal Item As Object)
    If TypeName(Item) <> "MailItem" Then Exit Sub
    Dim vFF As Long, vFile As String, vBody As String

    'Set the output CSV file location here
    vFile = "C:\savem2e.txt"
    vBody = Item.Body
    vFF = FreeFile
    Open vFile For Output As #vFF
    Print #vFF, vBody
    Close #vFF

    Item.UnRead = False 'Mark As Read
    End Sub[/vba]
    Just about anything can be done from VBA, so if you want to run the batch file too or anything just ask and I can show you how
    Matt

  18. #18
    VBAX Regular
    Joined
    May 2007
    Posts
    87
    Location

    Smile

    I'm calling the command line directly from the VB using "Shell" command.

    [vba]

    ChDir "C:\ProE_files\workingmodel\"
    Shell "C:\ProEng3\Wildfire3\bin\proe.exe -g:no_graphics C:\ProE_files\workingmodel\interactiveabsentee.txt", vbNormalFocus

    [/vba]

    Which I butchered from an Excel Program for ProE someone had written. (Edwin Muirhead, at Weatherfield, as published on Eng Tips ProE FAQ).

    shell "[Dos Command]", vbNormalFocus

    should all be on one line, and [dos command] can be anything you'd type at the command prompt (I believe).

    The actual command I'm calling here is for starting ProEngineer without showing anything on screen (works faster, the "-g:no_graphics" switch) and then the name and location of the TRAIL file, which is a macro recorded in ProE and saved as a text file.

    We won't go too deeply into the ProE Trail file, as basically it can do anything you want it to do in the industry standard solid modelling package, but it loads a set of parameters into the model and spits out a PDF, which is then sent out using Outlook to the recipients I want to receive it.

    n.b. "Shell" means two different things in VB and in ProE, but if you're attempting this in ProE, you should know that by now!!!

    If anyone reading this needs tips about ProE and MS Office Digital intergration, or other ProE automation stuff, give me a shout (reply to this message, it should alert me via email).

    Thanks for your help Matt, I'll leave credit where due in the code!

    Next step will be so it works from a text message (SMS) from a basic mobile phone, though I'm not sure I'm so enthusiastic about that!

    -AS

  19. #19
    VBAX Regular
    Joined
    May 2007
    Posts
    87
    Location
    I'll post relevant sections of the code here when it's finalised.

    I'll close thread then.

    -AS

  20. #20
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    No need for credit on my part, keep it in there and get some good credit yourself at work

    Not sure how long it takes for your PDF to generate, but there is a different kind of shell available to you as well in VBA, that does the same as VB's but also has a 3rd (optional) argument called "Wait On Return". Accepts true or false (default), if you put True in there it will wait until the called line is done running before proceeding in code.[vba] Dim WShell As Object
    Set WShell = CreateObject("wscript.shell")
    WShell.Run "Dos Command", "[WindowStyle]", "[WaitOnReturn]"[/vba]If you set a reference to Windows Scripting Host Object Library you could substitute "IWshRuntimeLibrary.WshShell" for "Object" above to see the intellisense; I just use late binding like above as its easier for distribution.


    As for SMS.... good luck! Heh.. In my opinion it is 100 times easier to find out the email address equivalent for the phone you want to send it to than to deal with SMS. It will work just as well and you won't have to hook up any special hardware or anything
    Matt

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •