Consulting

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

Thread: Solved: Utilizing VBA in an Outlook Rule

  1. #1

    Solved: Utilizing VBA in an Outlook Rule

    VBA Novice needs some help.

    First off, is it possible to link more than one Office app together in one VBA script?

    I currently have system generated e-mails come into my inbox from the same sender several times daily. I want to be able to either save the mail as text or copy the mail text (including message evelope) and paste/import into an excel file.

    From there I have a macro currently set up to extract the info I need and organize into rows and columns.

    Once spliced in Excel, I have a Word form that links to the Excel file to autopopulate a form.

    Once the form is populated I then want to attach it to an e-mail and send to the same address everytime.

    Not sure if this is just a pipe dream or if it can actually be done.

    I tried make it as simple as possible, but any and all opinions are welcomed.

    Thanks in advance.

    Ross

  2. #2
    VBAX Master Killian's Avatar
    Joined
    Nov 2004
    Location
    London
    Posts
    1,132
    Location
    Hi Ross
    What you're looking to do is no pipe dream but having the whole process running in one continuous stream may not be the best solution (although if your keen enough you can make it happen).
    I think the best way to manage the incoming mail is to stick with a rule in Outlook. What you can then automate is the data into Excel (which you seem to have already) then stream that into the word form, save that word doc and email back to the sender of the original mail. To do that we'd need to find a way to pass the senders address through with the data as well (unless it's always the same - is that what you meant?).
    If you post back with a basic example of the files (mail msg, excel and word) and the code your using at the moment I'm sure we can find a way to splice it all together. Also, let me know what office version(s) you're using.
    K :-)

  3. #3
    Killian,


    Thanks for the reply. I have attached the pieces to the puzzle. The marco I use in Excl is save in the file. The receipient of the mail is always the same, so no need to pass that info. I am using Office 2003.

    Let me know what you think.

    Thanks a Million.

    Ross

  4. #4
    VBAX Master Killian's Avatar
    Joined
    Nov 2004
    Location
    London
    Posts
    1,132
    Location
    OK, I've come up with something to get you started but there are a few tricky bits so I hope you're ready

    In your Excel file...
    First, in the VBEditor, you'll need to go to Tools|References and add references to Microsoft Word 11.0 Library and Microsoft Outlook 11.0 Library so we can use them.
    Add the code below in a new module, but first I should explain a few things:
    First I'll explain whats going on at the moment... What I've done here is a file search in a pre-defined folder for TXT files. For each one, it's opened, each line is passed into the excel sheet1, your original code is run to create the dataset, the word doc (report) is opened and it auto merges from the dataset, the doc is given a name and saved, a new mail item is created, filled in and the doc attached, the mail sent and then it loops back to start again with the next text file.
    Next, you'll need to change a few things in the code - the string constants for the folder/file locations need to be changed
    You'll probably want to customize the doc filename and email content - I've commented the code so you can see what I mean
    You may also need to reattach the datapath for the report doc if you chnage anything
    There's also a complication that had me scratching my head for a while: MS have closed a security loophole that prevents the SQL connect for the merge from being run from VBA. There's an registry hack to open it back up detailed here:
    http://support.microsoft.com/default...B;EN-US;825765
    Then, I believe, you're good to go
    I should point out I don't have Outlook at home so I haven't been able to test that aspect yet but it should be ok

    From here, I don't think its a massive leap to get this all running automatically using some fancy Outlook rule/script combination - when I get back to work next week I'll check it out

    Until then.. enjoy :-)
    [VBA]'## These string constants will need to be changed accordingly ##'
    Const TextFilePath As String = "C:\Documents and Settings\Killian\Desktop\txt"
    Const WordDocFilePath As String = _
    "C:\Documents and Settings\Killian\Desktop\Service Call Log Form Rev H.doc"
    Const ExcelFilePath As String = _
    "C:\Documents and Settings\Killian\Desktop\Service Call Inputs-RevB_K1.xls"
    Const WordDocFileName As String = "Service Call Log Form Rev H.doc"
    Const FinalReportFolder As String = _
    "C:\Documents and Settings\Killian\Desktop"
    Const strMailRecipient As String = "someone@somewhere.net"

    Sub BatchReportGen()

    'declare variables
    Dim fs As Object, fso As Object, ts As Object
    Dim i As Integer, r As Integer
    Dim wdApp As Word.Application
    Dim wdDoc As Word.Document
    Dim olApp As Outlook.Application
    Dim olNewMail As Outlook.MailItem
    Dim wdFileName As String

    'set objects required
    Set fs = Application.FileSearch
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set wdApp = New Word.Application
    Set olApp = New Outlook.Application

    With fs 'do a filesearch for all txt file in target folder
    .NewSearch
    .LookIn = TextFilePath
    .Filename = "*.txt"
    If .Execute > 0 Then
    For i = 1 To .FoundFiles.Count 'with each found txt file in turn...
    Set ts = fso.GetFile(.FoundFiles(i)).OpenAsTextStream(1, -2)
    r = 1
    Do Until ts.AtEndOfStream 'read each txt line into cell from A1 down
    ActiveWorkbook.Sheets("Sheet1").Cells(r, 1).Value = ts.Readline
    r = r + 1
    Loop
    'call the original routine to transfer the data
    Data_Transfer_New

    'open the mail merge doc and save it
    Set wdDoc = wdApp.Documents.Open(WordDocFilePath)
    'you may want to do something else with the filename so you don't get repeats
    'since you're in Excel, you could grab a job number from the data perhaps
    wdFileName = FinalReportFolder & "\ServiceCallLog_" & i & ".doc"
    wdDoc.SaveAs wdFileName
    wdDoc.Close

    'create a new mail item, fill in, attach doc and send
    Set olNewMail = olApp.CreateItem(olMailItem)
    With olNewMail
    .Recipients.Add strMailRecipient
    .Subject = "Subject string here"
    .Body = "Body text (if required)"
    .Attachments.Add wdFileName
    .Send
    End With
    Next i 'go to next found txt file and do it all again
    'finished - quit apps
    wdApp.Quit
    olApp.Quit
    Else
    MsgBox "No text files found."
    End If
    End With

    'release references
    Set fs = Nothing
    Set fso = Nothing
    Set wdDoc = Nothing
    Set wdApp = Nothing
    Set olNewMail = Nothing
    Set olApp = Nothing

    End Sub[/VBA]
    K :-)

  5. #5

    You Rock!!!!!!

    Killian,

    That worked great!!!

    I have a couple of questions.

    1. I would like to change the file name of the final report as you mentioned "you could grab a job number from the data perhaps." There is a "case number" that is unique and I would like to use that, I am just not sure how to do it.

    2. Along the same lines as number 1, I would like the subject of the e-mail to reflect the case number also.

    2. As the process goes now, I get the e-mail in only one time for each case. In order to utilize your code I need to save the mail as a text file in the same folder every time. This means that there will be multiple text files in this folder, and unless I missed something, every time the code is run it looks for all text files. This will create duplicate forms. My simple solution would be to have the code go back and delete the text file after it process the info. What do you think.

    It seems the only piece that is missing as you mentioned is connecting all this to an outlook rule. I am anxiously awaiting your reply.

    Thanks Again,

    Ross

  6. #6
    VBAX Master Killian's Avatar
    Joined
    Nov 2004
    Location
    London
    Posts
    1,132
    Location
    Hi Ross,
    glad it worked out - now for the fine-tuning...
    You can either strip the case number from the file name using string functions or, perhaps better, since it is pulled out of the data in your routine, assign it to a variable at that stage and pass that varible back to the calling routine for use with the filename and email subject.
    We should include some code to either delete or backup the source txt file.
    I'll have a look at that in the next couple of days. Also, I think it should be possible, once this is working 100%, to have it fire automatically when the mail comes in.

    You might also want to give some thought to whether you need to log all this activity when its automatic. Perhaps each row of data thats sorted in Excel should be saved to a worksheet so you can cross check any issue that may arise. Thinking about that makes me want to suggest error checking as well - what to do if a process fails and how to report that - important stuff with automation!

    I'll try to get a look at this again later when I get a bit more time
    K :-)

  7. #7
    VBAX Master Killian's Avatar
    Joined
    Nov 2004
    Location
    London
    Posts
    1,132
    Location
    Ok, so now I have a bit of time, the specifics:

    For the case number for the filename and mail subject line, declare a variable to hold the case number in the NEW module, up with the constatnt daclarations[VBA]Dim myCaseNum As String[/VBA] Then in your Data_Transfer_New routine, assign the case number value to it (its somewhere in there-here's the new line with the previous and next lines of code)[VBA] Range("A6").Select
    ActiveSheet.Paste
    'new line to assign the case number to our variable after its been pasted
    myCaseNum = Range("A6").Value
    Sheets("Sheet1").Select
    [/VBA]
    Now we can use it with the filename and the mail subject. Find where the wdFileName is assigned and change it to [VBA]wdFileName = FinalReportFolder & "\ServiceCallLog_" & myCaseNum & ".doc"[/VBA] and likewise change the assignment of the mailitem subject property to something like [VBA].Subject = "Service Call Log " & myCaseNum [/VBA]
    Now the fun part:
    Getting this all to run from Outlook is fairly straightforward. Open the Outlook VBEditor (Alt+F11), add a new module and paste in this routine[VBA]Sub Test(Item As Outlook.MailItem)
    Const TextFilePath As String = "C:\Documents and Settings\Killian\Desktop\txt"
    Item.SaveAs "c:\temp\" & Item.Subject & ".txt", olTXT
    End Sub[/VBA] !!! you'll need to update the TextFilePath constant to match the one you use in the excel code (just copy the whole constant declaration across). Now add a rule in Outlook (that applies to the email you want i.e. from a particular recipient) and the action for the rule needs to be "run a script". Click the underlined "script" link and select the sript you just added to Outlook. Now, when a mail comes in, a reference to that mailItem is passed to the script. The script saves it down as a text file.
    Once your happy things are as they should be you can use a shell command to launch the Excel code[VBA]Shell "C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE c:\temp\temp.xls", 1[/VBA] Note there are three part to this: in the quotes, separated by a space, are the path to the Excel exe and the fullpath and name of the file you want to open (your excel code file) and the "1" at the end determines how the Excel app will be displayed (see MS VBA help for "Shell" for more info)
    Now all you need to do is call the BatchReportGen routine in the WorkBook_Open event and it will all be automatic!
    Still some loose ends to tie up: you'll want delete/move the text file, quit Excel when it's done maybe save the row of data out to a separate workbook so you have a report log of all the processed files and, like I said before, some error checking might be a good idea.
    But that should keep you busy for a while...
    K :-)

  8. #8
    Killian,

    Thanks for the reply again. Sorry it took so long to get back, but I was waiting for an e-mail alert saying I got a new post and it never came.

    Anyway, I am having some difficulty executing your new code.

    First, I cannot get the case number to work right for either the subject or file name. I put the "Dim myCaseNum As String" up with the constants in the BatchReportGen code, but it does not seem to work. For the e-mail subject I only get Service Call Log_, any ideas

    Second, The code to save the mail as a text file is not working. I changed the Const TextFilePath, but errors on the next line, am I supposed to change "Item.SaveAs "c:\temp\" & Item.Subject & ".txt", olTXT" also.
    Third, where do I put the Shell Command?
    Forth, the regedit we did to remove security, seems to have removed my ability to save each form under a unique name a keep the mail merge info constant. In other words, there is no way to turn off the link. Any ideas.

    Thanks Again,

    Ross


  9. #9
    VBAX Master Killian's Avatar
    Joined
    Nov 2004
    Location
    London
    Posts
    1,132
    Location
    Hi Ross,
    1) I had both the routines in the same module, so declaring the myCaseNum at the top of the module would make it available to both. I'm assuming your routines are in separate modules, which is causing this problem (it's all about "scope" of variables, y'see). If you need your routines in separate modules, you'll need to declare myCaseNum as Public (replace "Dim" with "Public"), then all the routines in the project can use it.

    2) you're right, my mistake. that should read[VBA]Sub Test(Item As Outlook.MailItem)
    Const TextFilePath As String = "C:\Documents and Settings\Killian\Desktop\txt\"
    Item.SaveAs TextFilePath & Item.Subject & ".txt", olTXT
    End Sub[/VBA]Notice, I've put a "\" at the end of the path. Make sure you build a valid path\filename!
    3) The Shell command would go in the Outlook code, after you save the mail as text, to launch the Excel and the workbook with code

    4) The Registry change shouldn't be causing this behaviour - it simply bypasses the security check which prevents a SQL connect being made from VBA or script, so I'm not sure about that. You could test by deleting the key (or changing the value to 1) and seeing if that makes a difference to the file behaviour. I don't do too much with mailmerges (actually nothing at all) but I'm sure there must be a way of de-linking the final saved copy.
    What I suggest, is you get this working otherwise as you want, then post the mailmerge link issue separately in the Word forum where you can get some more ideas. (maybe link to this thread, so ppl can see the full story)
    Nearly there
    K :-)

  10. #10
    Killian,



    We are getting closer! Again thanks for your help on this. The shell command is still giving me problems. Is there a way to get the shell command to work when the path has spaces in it (c:\documents and settings...)?

    Ross

  11. #11
    Alright, I put double-double quotes ("") around the path and it worked. Now my issue is with security warnings in outlook. I get several during the process. Any way to programatically take care of this?


    Thanks Again,

    Ross

  12. #12
    VBAX Master Killian's Avatar
    Joined
    Nov 2004
    Location
    London
    Posts
    1,132
    Location
    Programatically... no. That's effectively what the security is protecting you from. My macro protection level is set to "Low" so I don't get these messages but that will leave you unprotected. Probably the best way is to self-sign your code so it will be a trusted source. More info here: http://msdn.microsoft.com/library/de...lsignature.asp
    You'll have to re-sign it if you make any changes but it should sort out the messages when running the outlook code.
    K :-)

  13. #13
    Killian,



    Thnkas Again!!



    I still have one issue. After the code runs (i think it is actually doing what I want it to), it seems to leave an instance of Word lingering in the background. The application is not open, but if I hit ctrl+alt+delete, and look at the processes tab I see winword still there. COnsequently, when I run the code a second time, a get a message that the word file is already open.



    Any Ideas.



    Ross

  14. #14
    VBAX Master Killian's Avatar
    Joined
    Nov 2004
    Location
    London
    Posts
    1,132
    Location
    No problem, Ross. Happy to help!
    interesting this... up there in the code somewhere there is a line that quits the word app we created for the merge[VBA]wdApp.Quit[/VBA] the word document we created in it[VBA]wdDoc = wdApp.Documents.Open[/VBA] and we do all kinds of other stuff with these objects so quitting it shouldn't be a problem.
    You may want to check your code as it executes to see exactly whats going on. You can step through it line by line with F8 or run to the cursor position with Ctrl+F8 or set some break points to pause it at a given point. Use the Locals widow to see whats going on with your objects and variables.
    It's important not to leave processes running as you've discovered... it might pay to display the apps you start until you've nailed the problem e.g [VBA]wdApp.Visible = True[/VBA]
    K :-)

  15. #15
    ALright,

    I tried everything within my means and I can't figure it out.

    Any other suggestions.

  16. #16
    VBAX Master Killian's Avatar
    Joined
    Nov 2004
    Location
    London
    Posts
    1,132
    Location
    you could try inserting this at the end of the routine, before the "release references" part[VBA]Set wdApp = GetObject(, "Word.Application")
    wdApp.Quit[/VBA]though it still doesn't explain why it's not quitting (unless, somehow the wdApp reference is lost before it gets to the quit method)
    K :-)

  17. #17
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    I haven't gone through all of this but are you sure the instance of Word is left over from your process? Or could you be using Word as your Outlook editor?
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  18. #18
    VBAX Master Killian's Avatar
    Joined
    Nov 2004
    Location
    London
    Posts
    1,132
    Location
    Ahh yes... very good point!
    K :-)

  19. #19
    Thanks for the new insight, that was part of the problem, but I have changed my outlook setting to not use word as the editior, and it still gets hung up.


    Any other ideas.

    Thanks

    Ross

  20. #20
    I think i have figured it out, if for some reason the code does not finish, it leaves the instances open. If the code runs from start to finish it seems to be OK.

    I have another question. I am trying to delete the text file from the e-mail, but I can't becasue it is still in use by the macro somewhere. I inserted a "kill" command that works independently of the rest of the code, but not when I put at the end the existing code.

    Is there some way to release to TXT file so I can delete it.

    Thanks,

    Ross

Posting Permissions

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