Somewhere there is an error however me and my team cant find it
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
Bookmarks