DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 11 of 11

Thread: Can some one help me

  1. #1
    Join Date
    Jul 2008
    Posts
    5

    Can some one help me

    Am getting an from my Outlook macro

    "Object variable or With block not set"

    my vb be skills are in a mess
    can someonehelp me?
    Code:
    ' Declare Variables
     Dim ns As NameSpace         'The NameSpace is the object that gives you access to all Outlook's folders. In Outlook there is only one and it is called "MAPI" which is an acronym for Messaging Application Programming Interface.
     Dim Inbox As MAPIFolder     'We are going to be referring to a mail folder (a MAPIFolder object) which we have given the name Inbox but the macro won't know which actual folder that is until we tell it by giving the variable a value in the next step.
     Dim SubFolder As MAPIFolder 'Specify the subfolder in the inbox.
     Dim Item As Object          '
     Dim Atmt As Attachment      'Refer to the attachment objects we are looking for.
     Dim FileName As String      'This will be used to create a name and save path for each attachment as it is saved.
     Dim FilePath As String      'This will be used to create a name and save path for each attachment as it is saved.
     Dim FilePathDefault As String
     Dim i As Integer            'The integer variable i will be used as a counter to log the progress of the macro.
    
    ' Set values of Variables
     Set ns = GetNamespace("MAPI")
     Set Inbox = ns.GetDefaultFolder(olFolderInbox)
     Set SubFolder = Inbox.Folders("12_Weekly - Rubicon")
     FilePathDefault = "C:\Documents\_Reports\Weekly Pricing - NEW\_Rubicon\"     'Default location of the final folder
     FilePath = InputBox("Save attachments in the folder:", "Save Path", FilePathDefault)
     i = 0
    
    'Search the Folder for Attachments
    'We need an If Statement to check that there are messages in the Mail folder, and abandon the search if there are none.
    'The first line of the If Statement counts the number of items in the folder:
     If SubFolder.Items.Count = 0 Then
        MsgBox "There are no messages in the " & SubFolder, vbInformation, "Nothing Found"    'If there are no message in the Inbox folder the macro displays a message to the user and then exits (i.e. terminates).
        Exit Sub
     End If
    
    'Save Attachments
    'Using a pair of nested loops (i.e. one loop running inside the other)
    'If SubFolder.Items.Count > 0 Then
    '   For Each Item In SubFolder.Items
    '      For Each Atmt In Item.Attachments
    '         FileName = FilePath & Atmt.FileName & Item.Subject
    '         Atmt.SaveAsFile FileName
    '         i = i + 1
    '      Next Atmt
    '   Next Item
    'End If
    
    If SubFolder.Items.Count > 0 Then
       For Each Item In SubFolder.Items
          If Item.Subject = "MV: [R] RO_A_GDS_ALL Provincial Mercure Weekly Pricing_Report [Mercure Total Comp Set] (xls)" Then
             FileName = FilePath & "GDS_All Provincial Mercure Rubicon Weekly Pricing.xls"
             Atmt.SaveAsFile FileName
             i = i + 1
           ElseIf Item.Subject = "MV: [R] RO_A_GDS_Birmingham Weekly Pricing_Report [Area Midlands] (xls)" Then
              FileName = FilePath & "GDS_Birmingham Rubicon Weekly Pricing.xls"
              Atmt.SaveAsFile FileName
              i = i + 1
           ElseIf Item.Subject = "MV: [R] RO_A_GDS_Bristol Weekly Pricing_Report [Area Wales & M4 Corridor] (xls)" Then
              FileName = FilePath & "GDS_Bristol Rubicon Weekly Pricing.xls"
              Atmt.SaveAsFile FileName
              i = i + 1
           ElseIf Item.Subject = "MV: [R] RO_A_GDS_Cardiff Weekly Pricing_Report [Area Wales & M4 Corridor] (xls)" Then
              FileName = FilePath & "GDS_Cardiff Rubicon Weekly Pricing.xls"
              Atmt.SaveAsFile FileName
              i = i + 1
           ElseIf Item.Subject = "MV: [R] RO_A_GDS_Edinburgh Weekly Pricing_Report [Area Scotland & North of England] (xls)" Then
              FileName = FilePath & "GDS_Edinburgh Rubicon Weekly Pricing.xls"
              Atmt.SaveAsFile FileName
              i = i + 1
           ElseIf Item.Subject = "MV: [R] RO_A_WEB_London Centre Weekly Pricing_Report [Area London] (xls)" Then
              FileName = FilePath & "WEB_London Centre Rubicon Weekly Pricing.xls"
              Atmt.SaveAsFile FileName
              i = i + 1
           End If
       Next Item
    End If
    
    'Display a Summary Message
    If i > 0 Then
        MsgBox "I found " & i & " attached files." _
           & vbCrLf & "I have saved them into the folder " & FilePath _
           & vbCrLf & vbCrLf & "Have a nice day.", vbInformation, "Finished!"
     Else
        MsgBox "I didn't find any attached files in your mail.", vbInformation, _
        "Finished!"
    End If
    
    ' Clear the Memory
    Attach_RubiconWeekly_exit:
       Set Atmt = Nothing
       Set Item = Nothing
       Set ns = Nothing
       'Set Inbox = Nothing
       'Set SubFolder = Nothing
       Exit Sub
    'Handle Errors
    Attach_RubiconWeekly_err:
       MsgBox "An unexpected error has occurred." _
          & vbCrLf & "Please note and report the following information." _
          & vbCrLf & "Macro Name: Attach_4WeeksOTB" _
          & vbCrLf & "Error Number: " & Err.Number _
          & vbCrLf & "Error Description: " & Err.Description _
          , vbCritical, "Error!"
       Resume Attach_RubiconWeekly_exit
    End Sub
    Last edited by Hack; 07-22-2008 at 12:41 PM. Reason: Added Code Tags

  2. #2
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    Welcome to DevX

    I edited your post and added [code]your code goes here[/code] tags as it makes reading posted code a lot easier.

    What line of code is causing the error?

  3. #3
    Join Date
    Jul 2008
    Posts
    5

    Sos

    The problem is happening in the i loop

    the execution stops at the last

    "Atmt.SaveAsFile FileName" statement, which is just be for the 1st "End if"

  4. #4
    Join Date
    Mar 2008
    Location
    Lancashire, UK
    Posts
    156
    I think it's this bit:
    Code:
    If SubFolder.Items.Count > 0 Then
       For Each Item In SubFolder.Items
          If Item.Subject = "MV: [R] RO_A_GDS_ALL Provincial Mercure Weekly Pricing_Report [Mercure Total Comp Set] (xls)" Then
             FileName = FilePath & "GDS_All Provincial Mercure Rubicon Weekly Pricing.xls"
             Atmt.SaveAsFile FileName
             i = i + 1
    Just guessing here, but i think you need to do this:
    Code:
    If SubFolder.Items.Count > 0 Then
       For Each Item In SubFolder.Items
          If Item.Subject = "MV: [R] RO_A_GDS_ALL Provincial Mercure Weekly Pricing_Report [Mercure Total Comp Set] (xls)" Then
             FileName = FilePath & "GDS_All Provincial Mercure Rubicon Weekly Pricing.xls"
             Item.Attachments(0).SaveAsFile FileName
             i = i + 1

  5. #5
    Join Date
    Mar 2008
    Location
    Lancashire, UK
    Posts
    156
    Or you could set Atmt to be one of those attachments
    Code:
    Set Atmt = Item.Attachments(0)
    Atmt.SaveAsFile FileName

  6. #6
    Join Date
    Jul 2008
    Posts
    5
    it did not work Same error

    Esentially what the program does is rename the excel attachments after the email title in outlook, am no VB expert but I think its a very clever peice of coding. However if i can figure out how to change one am sure i can create a loop to do the others

  7. #7
    Join Date
    Mar 2008
    Location
    Lancashire, UK
    Posts
    156
    Ah! I understand your code now. The bit you commented out is what was orignially there. Try this

    Code:
    'at the top
    Dim j As Integer
    
    
    
    
    If SubFolder.Items.Count > 0 Then
       For Each Item In SubFolder.Items
          If Item.Subject = "MV: [R] RO_A_GDS_ALL Provincial Mercure Weekly Pricing_Report [Mercure Total Comp Set] (xls)" Then
             FileName = FilePath & "GDS_All Provincial Mercure Rubicon Weekly Pricing.xls"
             j = 0
             For Each Atmt In Item
                 j = j + 1
                 Atmt.SaveAsFile "Attachment " & CStr(j) & FileName
             Next Atmt
             i = i + 1
             
             ...

  8. #8
    Join Date
    Jul 2008
    Posts
    5

    Sos Vb

    I got a new error message

    "Object doesnt support this property or method"
    at the

    "For Each Atmt In Item"

    can you help

  9. #9
    Join Date
    Mar 2008
    Location
    Lancashire, UK
    Posts
    156
    Sorry, it should be
    Code:
    For Each Atmt In Item.Attachments

  10. #10
    Join Date
    Jul 2008
    Posts
    5

    Thanks A Mil Man!

    the code works
    just for my knowledge why the need for the extra look
    I dont undestand how it works
    can u explain

    And may be u can give me some links where I can get some begginers VBA programming

    Cheers

  11. #11
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    Quote Originally Posted by red65
    And may be u can give me some links where I can get some begginers VBA programming
    In what?

    Word?
    Excel?
    Access?
    All of the above?
    Something else entirely?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center