VBA: Tabellenblätter abspeichern und per Mail versenden

[mexx189]

Hallo zusammen

Ich habe mir mit Mühe einen Code zusammengestellt (wird wohl ein wenig umständlich geschrieben sein) und bin auf zwei Probleme gestossen.
Der Code macht eigentlich nichts anderes, als die einzelnen Tabellenblätter in einer Datei temporär abzuspeichern und packt diese in eine E-Mail.

Nun zu meinen Problemen:

1. In der Quelldatei sind viele Formeln, welche ich nicht mitschicken möchte. Gibt es die Möglichkeit, dass nur die Werte kopiert werden?
2. Neben dem einzelnen Tabellenblatt, generiert es noch ein zweites welches 'Sheet1' heisst. Ist eigentlich nicht weiter schlimm. Wäre nur schön, wenn das nicht erscheint.

Kann mir jemand weiterhelfen? Ich komme nicht weiter...

Gruss
Mexx

Sub SendMailtoSCorEcPO()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
           Dim ws As Worksheet
           Dim AWS As Workbook
           Dim NameDatei As String
           Dim Pfad As String
                
           For Each ws In ThisWorkbook.Worksheets
          
           NameDatei = ws.Range("E7") & "_" & ws.Range("E4") & ".xlsx"
          
           If Not ws.Name = "Übersicht" Then
               Set objOL = CreateObject("Outlook.Application")
          
               Set objMail = objOL.CreateItem(0)
                  
                  
                    Set AWS = Workbooks.Add
                    Pfad = "C:\Users\" & Environ("Username") & "\Desktop\" & NameDatei
                    AWS.SaveAs Pfad
                    ws.Copy Before:=Workbooks(NameDatei).Sheets(1)
                    AWS.SaveAs Pfad
                    ws.UsedRange.Copy
                    xMailBody = "Please check file enclosed"
          
               With objMail
              
                   .To = ws.Range("H4")
                   .CC = ws.Range("H5")
                   .BCC = ""
                  
                   .Attachments.Add Pfad
                        
                   .Subject = "Cost" & " " & Range("E7") & " " & "-" & " " & Range("E4")
                  
                   .Body = xMailBody
          
          
                   .Display
                  
               End With
              
               Set objOL = Nothing
               AWS.Close
               Set AWS = Nothing
                End If
               Next
               Kill Pfad
           Application.DisplayAlerts = True
           Application.ScreenUpdating = True
End Sub

[schauan]

Hallöchen,

1) kopiere nach dem SaveAs in der temp. Datei den benutzten Bereich und füge anschließend die Werte ein
2) eine neue Mappe hat immer ein erstes Blatt. Lösche das Blatt nach dem Einfügen der Übersicht oder kopiere die Übersicht in eine neue Datei, dann brauchst Du Workbooks.Add nicht.

[RPP63]

Moin auch von mir!
Da nur die Werte gewünscht sind, wäre es das Einfachste, die einzelnen Blätter als PDF anzuhängen.
Dazu gibt es die Worksheet.ExportAsFixedFormat-Methode.

Gruß Ralf

[schauan]

Hi Ralf,

Da nur die Werte gewünscht sind, wäre es das Einfachste, die einzelnen Blätter als PDF anzuhängen.

ich schließe da nicht gleich draus, dass eine pdf für den Empfänger ausreicht. Ich ärgere mich gefühlt 100 Jahre meines Berufslebens über solche pdf, wenn ich die Daten später selber weiterverarbeiten will (... zumindest macht einen das nicht jünger )

Ansonsten ja, wäre sicher der einfachere und bessere Weg

[mexx189]

Hallo zusammen

Ich habe folgendes zum 1. Punkt probiert, aber irgendwie will es nicht so wie ich es will. Findet ihr den Fehler?

Sub SendMailtoSCorEcPO()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
           Dim ws As Worksheet
           Dim AWS As Workbook
           Dim NameDatei As String
           Dim Pfad As String
                
           For Each ws In ThisWorkbook.Worksheets
          
           NameDatei = ws.Range("E7") & "_" & ws.Range("E4") & ".xlsx"
          
           If Not ws.Name = "Übersicht" Then
               Set objOL = CreateObject("Outlook.Application")
          
               Set objMail = objOL.CreateItem(0)
                  
                  
                    Set AWS = Workbooks.Add
                    Pfad = "C:\Users\" & Environ("Username") & "\Desktop\" & NameDatei
                    AWS.SaveAs Pfad
                    ws.Copy Before:=Workbooks(NameDatei).Sheets(1)
                    ws.UsedRange.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
                         SkipBlanks:=False, Transpose:=False
                    AWS.SaveAs Pfad
                    ws.UsedRange.Copy
                    xMailBody = "Please check file enclosed"
          
               With objMail
              
                   .To = ws.Range("H4")
                   .CC = ws.Range("H5")
                   .BCC = ""
                  
                   .Attachments.Add Pfad
                
                   .Subject = "Cost" & " " & Range("E7") & " " & "-" & " " & Range("E4")
                  
                   .Body = xMailBody
          
          
                   .Display
                  
               End With
              
               Set objOL = Nothing
               AWS.Close
               Set AWS = Nothing
                End If
               Next
               Kill Pfad
           Application.DisplayAlerts = True
           Application.ScreenUpdating = True
End Sub

Ich muss die Datei leider unbedingt in Excel versenden, da der Empfänger diese weiterverwenden muss..

Gruss
Mexx

[schauan]

Hallöchen,

aber irgendwie will es nicht so wie ich es will.

was ist anders?

**************************

warum

ws.UsedRange.PasteSpecial

und nicht z.B.

ws.Range("A1").PasteSpecial

???

[mexx189]

Hallo schauan

Ich habe es mit ws.Range("A1").PasteSpecial versucht, aber es kommt folgender Fehler: "PasteSpecial method of Range class failed".
Jetzt wird eine neue Datei erstellt mit dem 1. Blatt, aber nicht im Mail angehängt und die Formeln hat es leider auch übernommen.

Gäbe es noch andere Möglichkeiten?

Gruss
mexx

[schauan]

Hallöchen,

mit Deinem code gibt es eigentlich ein paar Probleme in der Reihenfolge. Ein paar Kommentare würden das verdeutlichen

'Kopie des Blattes in eine andere Datei vor das Blatt 1
ws.Copy Before:=Workbooks(NameDatei).Sheets(1)
'Einfügen des benutzten Bereiches als Werte
ws.UsedRange.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
'"Speichern als" der anderen Datei
AWS.SaveAs Pfad
'Kopieren des benutzten Bereiches
ws.UsedRange.Copy


Schaue Dir speziell mal die Aktionen mit dem benutzten Bereich an. Ich würde den erst kopieren und dann einfügen und möglichst beides vor dem "Speichern als". Dann passt vermutlich auch die Alternative mit ..A1..

[mexx189]

Hallo nochmals

Ich habe nun ws.UsedRange.Copy durch ws.Range("A1).Copy abgeändert und über ws.Range("A1").PasteSpecial gesetzt. Nun macht das Makro wieder was es soll, danke schonmal!
Es fügt mir aber nach wie vor die Formeln ein und nicht die Werte... Gibt es da noch einen anderen Ansatz?

Gruss
mexx

[schauan]

Hallöchen,

das mit den copy hat schon gepasst. Nur beim Einfügen würde A1 reichen.
Das ist wie wenn Du so was manuell machst. Du markierst einen größeren Bereich, kopierst, und dann reicht es wenn Du zum Einfügen auf eine Zelle klickst.