Getting data from outlook and excecuting excel vba

Posted by John on January 09, 2002 3:35 AM

I would like to take an email that contains "lead" in the subject line when it comes in and save the text in a temporary text file and start a macro in excel that will strip it and reformat the information. I know how to write the code to stip the information once excel is started but I don't know how or if I can auto execute this from MS Outlook. Is this possible and if so do you have any advice or ideas on how to do this.

Any help would be appreciated,

John

Posted by DK on January 09, 2002 5:29 AM

Hi John,

Here's one way of doing it.

In Outlook open the VB editor and double click the ThisOutlookSession icon.

Paste this code into the code module:-

Option Explicit
Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbook

'YOU MUST SET A REFERENCE TO THE EXCEL OBJECT LIBRARY IN ORDER FOR THIS CODE TO WORK

Private Sub Application_NewMail()
Dim olFld As MAPIFolder, olMitem As MailItem
Dim lngUnread As Long

Set olFld = Application.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)

lngUnread = olFld.UnReadItemCount

If lngUnread = 0 Then Exit Sub 'No unread items. Shouldn't occur.

While lngUnread > 0
Set olMitem = olFld.Items.GetFirst
If InStr(1, olMitem.Subject, "lead") > 0 Then
ProcessMail olMitem
End If
lngUnread = lngUnread - 1
Wend

End Sub

Sub ProcessMail(olMailItem As MailItem)
Dim blnExcelRunning As Boolean
'Save the body of the message into C:\temp\lead.txt
olMailItem.Display
olMailItem.SaveAs "C:\temp\lead.txt", 0
olMailItem.Close olSave

'If Excel's running then use it, if not, create it.
Set xlApp = GetObject(, "Excel.Application")
If Err.Number = 0 Then
blnExcelRunning = True 'Excel is running
Else
Set xlApp = CreateObject("Excel.Application")
End If

Set xlWb = xlApp.Workbooks.Open("C:\temp\macroworkbook.xls")
xlApp.Run "macroworkbook.xls!yourmacro"

xlWb.Close False

If blnExcelRunning = False Then
xlApp.Quit
End If

End Sub

It isn't the most sophisticated way of doing it but it seems to work. You'll obviously have to make a few changes in order for it to suit your needs fully.

Let me know if you have any further questions.

Posted by DK on January 09, 2002 7:44 AM

Change needed to posted code...

Sub ProcessMail(olMailItem As MailItem)
Dim blnExcelRunning As Boolean
On error resume next

Make sure you add the line On Error resume next to your code. If not, and Excel doesn't happen to be open then you'll get a run time error with:-

Set xlApp = GetObject(, "Excel.Application")

Laters,
D.