Solved: Utilizing VBA in an Outlook Rule

[rsilberfarb]

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

[Killian]

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.

[rsilberfarb]

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

[Killian]

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.aspx?scid=KB;EN-US;825765
Then, I believe, you're good to go :thumb
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 : pray2:

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 :-)
'## 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

[rsilberfarb]

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 :thumb

[Killian]

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

[Killian]

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 daclarationsDim myCaseNum As String 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) 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

Now we can use it with the filename and the mail subject. Find where the wdFileName is assigned and change it to wdFileName = FinalReportFolder & "\ServiceCallLog_" & myCaseNum & ".doc" and likewise change the assignment of the mailitem subject property to something like .Subject = "Service Call Log " & myCaseNum
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 routineSub 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 !!! 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 codeShell "C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE c:\temp\temp.xls", 1 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... :)

[rsilberfarb]

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

[Killian]

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 readSub Test(Item As Outlook.MailItem)
Const TextFilePath As String = "C:\Documents and Settings\Killian\Desktop\txt\"
Item.SaveAs TextFilePath & Item.Subject & ".txt", olTXT
End SubNotice, 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 :thumb

[rsilberfarb]

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

[rsilberfarb]

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

[Killian]

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/default.asp?url=/library/en-us/dnout2k/html/oldigitalsignature.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.

[rsilberfarb]

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

[Killian]

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 mergewdApp.Quit the word document we created in itwdDoc = wdApp.Documents.Open 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 wdApp.Visible = True

[rsilberfarb]

ALright,

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

Any other suggestions.

[Killian]

you could try inserting this at the end of the routine, before the "release references" partSet wdApp = GetObject(, "Word.Application")
wdApp.Quitthough it still doesn't explain why it's not quitting (unless, somehow the wdApp reference is lost before it gets to the quit method)

[TonyJollans]

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?

[Killian]

Ahh yes... very good point!

[rsilberfarb]

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

[rsilberfarb]

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

[Killian]

Hi Ross

the text file was bought back as a result of the filesearch as object "ts" and as I read back through my own code I notice with some embarrassment that the lineSet ts = Nothing is conspicuous by it's absence. No great disaster since when the routine ends, the reference would be released anyway, but if you want to kill the file, you'll have to set that reference to nothing first.

[rsilberfarb]

Killian,


As I expected, you came thru for me again. That accomplished the task.

Thanks Again

Ross

[rsilberfarb]

Ok, I thought it was all good, but now an instance of Excel is staying open. Same deal as before, where the application isn't actually open, but if I look in the processess tab I see it.

WHen I run the process once it works fine, the second time around I get a message that the data source for the word mail merge doc is open.



Please Help,



Ross