Results 1 to 3 of 3

HELP, i am having an error and cant figure out whats wrong with my vba

Threaded View

  1. #1
    Registered User
    Join Date
    06-12-2012
    Location
    amsterdam
    MS-Off Ver
    Excel 2003
    Posts
    2

    HELP, i am having an error and cant figure out whats wrong with my vba

    Sub Mail_ActiveSheet()
    
    'Working in 2000-2007
        Dim sh As Worksheet
        Dim wb As Workbook
        Dim FileExtStr As String
        Dim FileFormatNum As Long
        Dim TempFilePath As String
        Dim TempFileName As String
        Dim OutApp As Object
        Dim OutMail As Object
    
        TempFilePath = Environ$("temp") & "\"
    
        If Val(Application.Version) < 12 Then
            'You use Excel 97-2003
        Else
            'You use Excel 2007
            FileExtStr = ".xlsm": FileFormatNum = 52
        End If
    
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
    
        Set OutApp = CreateObject("Outlook.Application")
        OutApp.Session.Logon
    
        For Each sh In ThisWorkbook.Worksheets
            
    
                sh.Copy
                Set wb = ActiveWorkbook
    
                TempFileName = "Request for the meeting room" & " " & sh.Range("I13").Value & " " & Format(Now, "dd-mmm-yy h-mm-ss")
    
    
    
    
                Set OutMail = OutApp.CreateItem(0)
                With wb
                    .SaveAs TempFilePath & TempFileName & FileExtStr, _
                            FileFormat:=FileFormatNum
                    On Error Resume Next
                    With OutMail
                        .To = "info@mymailadress"
                        .CC = ""
                        .BCC = ""
                        .subject = "Request for the meeting room:" & " " & sh.Range("I13").Value
                        .Body = "Dear Reception," & vbNewLine & vbNewLine & _
                                "Please find attached an request for a meeting room" & vbNewLine & vbNewLine & _
                                "Thanks and regards,"
                        .attachments.Add wb.FullName
                        'You can add other files also like this
                        '.Attachments.Add ("C:\test.txt")
                        .Display   'or use .Send
                    End With
                    On Error GoTo 0
                    .Close SaveChanges:=False
                End With
                Set OutMail = Nothing
    
                Kill TempFilePath & TempFileName & FileExtStr
            
        Next sh
    
        Set OutApp = Nothing
    
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
    End Sub
    Last edited by arlu1201; 06-12-2012 at 05:10 AM. Reason: Use code tags.

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