Office: [VBA] Zwei neue Dateien als Emailanhang erzeugen

Hallo zusammen, ich bastle gerade an meinem Code herum und komme nicht weiter.

Das Ziel:
In Tabelle1 liegen die Daten des ersten Blattes. Aus diesem Blatt soll eine neue Datei ohne Formeln, nur mit Werten & Formatierung entstehen, welche dann im weiteren Verlauf an eine Email gehängt werden soll. Das gleiche soll dann auch mit Tabelle4 passieren, sodass am Ende zwei *.xlsx Dateien an der Email hängen.

Es soll hier nur um das korrekte Erzeugen dieser beiden Dateien an eine Email gehen. Der Rest des Codes, der im Folgenden nicht angefügt ist, funktioniert tadellos.

Hinweis: Ich kopiere für beide Dateien die Daten zunächst in jeweils versteckte Blätter, um sie daraus dann in die neuen *.xlsx Dateien zu kopieren. Das ist wahrscheinlich Quark, daher bin ich sehr an einem effizienteren Weg interessiert.

Der Code:
Dim strDatei As String
Dim strDatei2 As String
Dim strPfad As String
Dim Mail As Object
Dim outApp As Object

Set outApp = CreateObject("Outlook.Application")
Set Mail = outApp.CreateItem(0)
strPfad = "C:\Users\Public"

With Tabelle1
.Unprotect
.Range("A1:H35").Copy
.Paste Destination:=Tabelle16.Cells(1)
With Tabelle16
.UsedRange.Value = ActiveSheet.UsedRange.Value
End With
.Protect
End With
Tabelle16.Copy
ActiveWorkbook.SaveAs strPfad & "\" & "Bestellung"
strDatei = ActiveWorkbook.FullName

With Tabelle4
.Unprotect
.Range("A1:E14").Copy
.Paste Destination:=Tabelle17.Cells(1)
With Tabelle17

.UsedRange.Value = ActiveSheet.UsedRange.Value​

End With
.Protect
End With
Tabelle17.Copy
ActiveWorkbook.SaveAs strPfad2 & "\" & "Transportauftrag"
strDatei2 = ActiveWorkbook.FullName​

-----------------------------------------------------------------------------------------
Platzhalter für dazwischenliegenden Code zur Erzeugung der Email
-----------------------------------------------------------------------------------------​

Workbooks("Bestellung.XLSX").Close SaveChanges:=False
Workbooks("Transportauftrag.XLSX").Close SaveChanges:=False

Das Ergebnis:
Die erste Datei (erzeugt aus Tabelle1) ist inhaltlich korrekt. Die zweite Datei (sollte Daten aus Tabelle4 enthalten) enthält Daten aus Tabelle1?!. das Anhängen an die Email klappt wiederum anstandslos.

Was mache ich falsch?

 

Hallo denpei
ich muss zugeben, in ExcelVBA bewege ich mich nicht so sicher, aber bist Du sicher, dass man With/EndWith-Abschnitte verschachteln kann?
Ich würde Deinen Code wohl eher so schreiben (keine Ahnung, ob das läuft)

Code:

Dim strDatei As String
Dim strDatei2 As String
Dim strPfad As String
Dim Mail As Object
Dim outApp As Object

    Set outApp = CreateObject("Outlook.Application")
    Set Mail = outApp.CreateItem(0)
    strPfad = "C:\Users\Public"

    With Thisworkbook.Tabelle1
        .Unprotect
        .Range("A1:H35").Copy
        .Paste Destination:=Tabelle16.Cells(1)
        ThisWorkbook.Tabelle16.UsedRange.Value = ActiveSheet.UsedRange.Value
        .Protect
    End With
    ThisWorkbook.Tabelle16.Copy
    ActiveWorkbook.SaveAs strPfad & "\" & "Bestellung"
    strDatei = ActiveWorkbook.FullName

    With ThisWorkbook.Tabelle4
        .Unprotect
        .Range("A1:E14").Copy
        .Paste Destination:=Tabelle17.Cells(1)
        ThisWorkbook.Tabelle17.UsedRange.Value = ActiveSheet.UsedRange.Value
        .Protect
    End With
    ThisWorkbook.Tabelle17.Copy
    ActiveWorkbook.SaveAs strPfad2 & "\" & "Transportauftrag"
    strDatei2 = ActiveWorkbook.FullName

    -----------------------------------------------------------------------------------------
    Platzhalter für dazwischenliegenden Code zur Erzeugung der Email
    -----------------------------------------------------------------------------------------

    Workbooks("Bestellung.XLSX").Close SaveChanges:=False
    Workbooks("Transportauftrag.XLSX").Close SaveChanges:=False

 

Hi,

natürlich kann man With-Blöcke ineinander verschachteln. Das steht sogar in der Online-Hilfe!

ThisWorkbook.Tabelle1.xxx funktioniert nicht. Den Codename kann man nur in der eigenen Datei (=ThisWorkbook) ansprechen. Daher reicht Tabelle1.xxx. Will man den Codename eines Blattes in einer anderen Datei verwenden, dann muss man eine Schleife über alle Sheets bilden und den Codename für jedes Blatt prüfen.

Zum ursprünglichen Code: Ich würde direkt das entsprechende Blatt kopieren, Formeln durch Werte ersetzen und dann speichern und schließen. Also ungefähr so:

Code:

Tabelle1.Copy
With ActiveWorkbook
    With Worksheets(1)
        .UsedRange.Value = .UsedRange.Value
    End With
    .SaveAs "Pfad\Dateiname"
    .Close
End With