+ Reply to Thread
Results 1 to 2 of 2

Error on macro

Hybrid View

  1. #1
    Registered User
    Join Date
    10-24-2011
    Location
    Orlando, FL
    MS-Off Ver
    Office 365 for Business
    Posts
    89

    Error on macro

    I have a macro that works sometimes when testing, but then gives users an error when activating it:
    Run-time error ‘1004’:
    Unable to set the Hidden property of the Range class

    Debug error:
    Rows("1:5").Hidden = True

    Macro is:
    Sub Emailstraightruckreq()
        
        Dim oApp, oMail As Object, _
        tWB, cWB As Workbook, _
        fileName, FilePath As String
    
        Application.ScreenUpdating = False
    
    'Set email id here, it may be a range in case you have email id on your worksheet
        
        Mailid = "[email protected]; [email protected]; [email protected]"
        
        
    'Write your email message body here , add more lines using & vbLf _ at the end of each line
        
        Body = "Straight truck requirements: " & vbLf _
        & vbLf _
        & "Truck/Driver comments: " & [B29].Value & " " & [B30].Value & vbLf _
        & vbLf _
        & "Thanks & Regards, " & vbLf _
        & [B7].Value
        
       
    'Copy Active Sheet and save it to a temporary file
    
        Set cWB = ActiveWorkbook
        ActiveSheet.Copy
        
        'Hide Rows 1 through 5
    
        Rows("1:5").Hidden = True
        Rows("10:25").Hidden = True
        Rows("31:45").Hidden = True
        
        Set tWB = ActiveWorkbook
        fileName = "Temp.xls" 'You can define the name
        FilePath = Environ("TEMP")
        
        On Error Resume Next
        Kill FilePath & "\" & fileName
        On Error GoTo 0
        Application.DisplayAlerts = False
        tWB.SaveAs fileName:=FilePath & "\" & fileName, FileFormat:=56
        Application.DisplayAlerts = True
         
    'Sending email through outlook
    
        Set oApp = CreateObject("Outlook.Application")
        Set oMail = oApp.CreateItem(0)
        With oMail
            .To = Mailid
            .Subject = [B6].Value & " Straight Truck request attached for " & [B8].Value
            .Body = Body & [B4].Value
            .Attachments.Add tWB.FullName
            .Display
        End With
         
    'Delete the temporary file and restore screen updating
        
        tWB.ChangeFileAccess Mode:=xlReadOnly
        Kill tWB.FullName
        tWB.Close SaveChanges:=False
        cWB.Activate
        Application.ScreenUpdating = True
        Set oMail = Nothing
        Set oApp = Nothing
        
    End Sub
    Last edited by wolfm; 01-30-2013 at 12:58 PM.

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Error on macro

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1