MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.

D.

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.