VBA in excel and outlook

dlindamood2000 · November 17th, 2006, 02:44 PM

I want to write some code that will save an excel attachment in an open outlook email. Then I want the macro to activate excel, open the saved excel attachment, change the name and save under a new path.

some background info. As a teacher, I get an attendance report as an excel attachment everyday named: abs-rpt.xls I have some code that compares the abs-rpt to my class attendance. However, I want to automate the saving of the abs-rpt.xls from the email to a predetermined path.

maccas · November 20th, 2006, 08:18 AM

From the sounds of things you need a macro written in Outlook which automates Excel.

To put code in Outlook you need to open up the Outlook VBE (apologies if I'm telling you stuff you already know). You can do this through the tools menu or by pressing Alt+F11, in Outlook. From here you will need to go to the code window for ThisOutlookSession. Within this code window you need to put in a routine that fires off the NewMail event. Something along the lines of the following should work for you (NB I've not tested this so a bit of debugging may be required, this also requires a reference to the Excel object library to work):

Code:

Private Sub Application_NewMailEx(ByVal EntryIDCollection As String)

Dim MyMail As MailItem
Dim intInitial As Integer
Dim intFinal As Integer
Dim strEntryID As String
Dim intLength As Integer

Dim strFilename As String
Dim xlApp As Excel.Application
Dim xlWorkbook As Excel.Workbook

    ' Determine the Entry ID for the New MailItem
    intInitial = 1
    intLength = Len(EntryIDCollection)
    intFinal = InStr(intInitial, EntryIDCollection, ",")
    strEntryID = Mid(EntryIDCollection, intInitial, (intLength - intInitial) + 1)

    ' Set the new MailItem variable
    On Error GoTo Ender
    Set MyMail = Application.Session.GetItemFromID(strEntryID)
    On Error GoTo 0

    ' Check the attachments
    If MyMail.Attachments.Count > 0 Then
        If LCase(MyMail.Attachments.Item(1).FileName) = "abs-rpt.xls" Then

            ' Save the attachment file
            strFilename = "C:\Example - " & Format(Now, "d mmm yyyy") & ".xls"
            MyMail.Attachments.Item(1).SaveAsFile Path:=strFilename

            ' Open up Excel
            Set xlApp = New Excel.Application
            xlApp.Visible = True

            ' Open the attachment
            Set xlWorkbook = xlApp.Workbooks.Open(FileName:=strFilename, UpdateLinks:=False, ReadOnly:=True)

            ' Do stuff...

            ' Close the attachment
            xlWorkbook.Close SaveChanges:=False

            ' Quit Excel
            xlApp.Quit

            ' Dereference variables
            Set xlWorkbook = Nothing
            Set xlApp = Nothing

        End If
    End If

Ender:

End Sub

dlindamood2000 · November 20th, 2006, 03:06 PM

I tried this. I get a compile error. the Dim xlApp As Excel.Application line states that object type not defined or something to this effect.

dlindamood2000 · November 20th, 2006, 03:08 PM

The compile error states: user type not defined

maccas · November 21st, 2006, 04:18 AM

Like I said, you'll need to add a reference to the Excel Object library to get this to work. You can do this by selecting from the menus in the VBE: Tools -> References... This will get you to a dialogue box with lots of unchecked items in a list, scroll down to Microsoft Excel 11.0 Object Library and check it (NB the number may be different depending on what verions of Office you're running). Click ok and you should be good to go.

Maccas

dlindamood2000 · November 21st, 2006, 02:18 PM

OK, I was able to get this to work. However, I would like to keep excel open and work with excel files that are already open. What seems to be happening, is that a new instance of excel is opened. I have 2 excel programs running. I would like to be able to use CTRL f6 to toggle between the abs-rpt and my already opened file. When the macro changes the name and path of the excel file "abs-rpt" I want to save over an existing file that my open excel file links to.

maccas · November 22nd, 2006, 05:23 AM

I think its quite tricky to hook the instance of an already running Excel application object when automating from Outlook (altough I'm sure it must ultimately be possible if you hack into the WinAPI). The preferable solution by far would be to use the intiated Excel application object in the code to open up your linking file as well, you will then be able to toggle between the two.

dlindamood2000 · November 27th, 2006, 07:15 AM

Could I write a macro in excel that opens outlook and finds the email attachment?

maccas · November 28th, 2006, 07:59 AM

You could, however the initiating event that triggers the process (i.e. NewMail) is in Outlook not Excel. Depending on how you want the process to run this could cause problems. You have (at least) three options on how to structure the program flow.

1) If the e-mail is going to come in at a predictable time you could schedule your excel macro to run shortly after the anticipated arrival.
2) You could manually run the Excel macro every time you know that the new mail has arrived and you want to update.
3) You could automate via Outlook on the NewMail event.

The problem with 1) is that if the e-mail is delayed you may try to update with no new info. The problem with 2) is it will require a not fully automated process. If you are willing to live with either of these limitations (i.e. if you'd always planned to manually trigger an update from Excel) then you should be able code the program from Excel.

Maccas

maccas · November 28th, 2006, 08:05 AM

Actually you also have a fourth option, namely to hook Excel up to watch for Outlook events and use the NewMail event to trigger the code. However this is not worth contemplating as it will require Outlook and Excel to both be open all the time and also to have Excel permanently running a VBA routine waiting for the Outlook events - this would impact on machine performance and would stop you from running any other VBA in the same instance of Excel.

Maccas