Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old November 17th, 2006, 02:44 PM
Authorized User
 
Join Date: Nov 2006
Location: dayton, oh, USA.
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default VBA in excel and outlook

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.

Reply With Quote
  #2 (permalink)  
Old November 20th, 2006, 08:18 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: London, , United Kingdom.
Posts: 173
Thanks: 0
Thanked 3 Times in 3 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old November 20th, 2006, 03:06 PM
Authorized User
 
Join Date: Nov 2006
Location: dayton, oh, USA.
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
  #4 (permalink)  
Old November 20th, 2006, 03:08 PM
Authorized User
 
Join Date: Nov 2006
Location: dayton, oh, USA.
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The compile error states: user type not defined

Reply With Quote
  #5 (permalink)  
Old November 21st, 2006, 04:18 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: London, , United Kingdom.
Posts: 173
Thanks: 0
Thanked 3 Times in 3 Posts
Default

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

Reply With Quote
  #6 (permalink)  
Old November 21st, 2006, 02:18 PM
Authorized User
 
Join Date: Nov 2006
Location: dayton, oh, USA.
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
  #7 (permalink)  
Old November 22nd, 2006, 05:23 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: London, , United Kingdom.
Posts: 173
Thanks: 0
Thanked 3 Times in 3 Posts
Default

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.

Reply With Quote
  #8 (permalink)  
Old November 27th, 2006, 07:15 AM
Authorized User
 
Join Date: Nov 2006
Location: dayton, oh, USA.
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #9 (permalink)  
Old November 28th, 2006, 07:59 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: London, , United Kingdom.
Posts: 173
Thanks: 0
Thanked 3 Times in 3 Posts
Default

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

Reply With Quote
  #10 (permalink)  
Old November 28th, 2006, 08:05 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: London, , United Kingdom.
Posts: 173
Thanks: 0
Thanked 3 Times in 3 Posts
Default

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

Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
vba work outlook with excel sheet qunatphil Excel VBA 1 April 16th, 2008 01:30 PM
VBA to Outlook problem d12774 Access VBA 0 June 12th, 2007 04:04 PM
E-mail using VBA and Outlook cc16 Access VBA 6 December 4th, 2006 11:07 AM
VBA Excel, outlook createobject Willie Johnson Jr Excel VBA 0 March 10th, 2006 05:11 AM
Need help starting Outlook w/ VBA ArtDecade VB How-To 0 August 26th, 2004 12:09 PM



All times are GMT -4. The time now is 09:58 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.