VBA Unterordnersuche und Übernahme der Formatierung

[dachboden20]

Ich benötige wieder ein bischen Hilfe:

1. Code:

Dieser Code sucht mir in einem Ordner nach einer Datei und fügt sie mir in einer Zelle als anklickbares Symbol ein.

Sub aaa()
Dim rng As Range
Const strPfad = "Z:\testordner"
Set rng = Range("A7")
Do While rng <> ""
If Dir(strPfad & rng.Offset(, 6) & ".pdf", vbNormal) = "" Then
MsgBox "Die Datei gibt es nicht!", vbInformation, "Gebe bekannt..."
Else
rng.Offset(, 8).Select
ActiveSheet.OLEObjects.Add(Filename:=strPfad & rng.Offset(, 6) & ".pdf", Link:=False, _
DisplayAsIcon:=True, IconFileName:="T:\PFT\pft.ico", _
IconIndex:=0, IconLabel:="").Select
End If
Set rng = rng.Offset(1)
Loop
End Sub

Die Suche erfolgt nur im angegebenen Ordner, die Unterordner werden nicht duchsucht, wie kann ich das ändern? Außerdem hätte ich gern die gefundene Datei nicht als Objekt in einer Zelle eingefügt, sonder, jetzt zum 2. Code:

Option Explicit

Public Sub TableToMail()
Dim objOutlook As Object
Dim objMail As Object
Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.CreateItem(0)
With objMail
.to =
.Subject =
.HTMLBody = RangeToHTML(ActiveSheet, ActiveSheet.Range("D2:I7"))
.Display 'nur Anzeigen
' .Send 'direkt senden
End With
Set objMail = Nothing
Set objOutlook = Nothing
End Sub

Private Function RangeToHTML(objSheet As Worksheet, objRange As Range) As String
Dim strFilename As String
strFilename = Environ$("TEMP") & "/" & Format(Now, "dd-mm-yyyy_hh-mm-ss") & ".htm"
ActiveWorkbook.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=strFilename, _
Sheet:=objSheet.Name, _
Source:=objRange.Address, _
HtmlType:=xlHtmlStatic).Publish True
RangeToHTML = CreateObject("Scripting.FileSystemObject"). _
GetFile(strFilename).OpenAsTextStream(1, -2).ReadAll
Kill strFilename
End Function

Mit diesem Code wird eine E-Mail erstellt.
Aber:
- ich hätte gern den Betreff aus der Excel-Datei gezogen, Zelle B5
- als E-Mailtext wird mir der Bereich "D2:I7" ausgegeben, allerdings soll dieser Bereich variabel sein, der Code soll mich also jedes mal Fragen, welcher Bereich des Arbeitsblattes in der E-Mail stehen soll, geht sicher über Markierung oder!? - Außerdem soll dann noch meine Signatur in die Mail.
- nochmal zurück zum 1.Code: der eventuell gefundene Anhang soll als Anhang der E-Mail angefügt werden

Ich hoffe, mir kann jemand helfen!?

 

[Gnirsch]

Hallo, vielleicht hilft dir ja dieser Link aus der MS-Knowledgebase weiter beim durchsuchen der Unterverzeichnisse: https://support.microsoft.com/de-de/help/507404
Das ist glaube ich ein ähnliches Problem.

 

[Spike S.]

Bist du grundsätzlich mit der Programmierung vertraut?

Zum Problem 1)
Pfade mit Unterordnern durchsucht man in der Regel rekursiv. Also eine Liste von Ordnern die durchsucht werden soll wird abgearbeitet. Dabei fängt man mit einem ersten Ordner an und erweitert diese Ordnerliste immer um die Ordner die man im aktuell durchsuchten Verzeichnis findet. Also in etwa "Solange die Ordnerliste nicht abgearbeitet ist, nimm einen unbearbeiteten Eintrag daraus und schaue was du darin findest". Du müsstest also um deiner If Bedingung eine Schleife einfügen und nebenher eine Liste mitführen wo die Ordner gespeichert werden.
Es gibt wohl aber ein Objekt was das schon kann: Application.FileSearch
Damit habe ich noch nicht gerarbeitet, nur diesem Beispiel entnommen: http://www.herber.de/forum/archiv/1220to1224/1221650_Mit_VBA_Dateisuche_in_Ordnern.html

Zum Problem 2)
Auf einzelne Zellinhalte greift man mittels

ActiveSheet.Cells(2, 5).Value

zu. Erster Parameter ist die Zeilennummer, der zweite die Spaltennummer (es wird jeweils bei 1 angefangen zu zählen).

Einen Bereich kann man in Excel mittels

Application.InputBox

vom Nutzer abfragen. In der Excel-Hilfe gibt es ein Beispiel. Dabei als Parameter "Type" mit dem Wert "8" mitgeben.

Anhänge an eine Email können über die Eigenschaft "Attachments" hinzugefügt werden. Bei dir also, an der Stelle wo das objMail Objekt erstellt wird
.Attachments.Add "T:\PFAD\ZUR\DATEI"
innerhalb des With-Blocks einfügen.

Insgesamt gehe ich mal davon aus, dass du mit der Excel-Hilfe oder MSDN zurecht kommst

 

[cumulonimbus8]

Aus & mit durchaus eigener Erfahrung im Beruf mit so was (und selber gerne mit VBA am basteln) kann ich dir nur mit bestem Gewissen sagen:
Du packst das völlig falsch an.

• UserForms zur einfachen Bedienung
• Nutzen der Datei-Öffnen-Dialoge (›aus älterem Excel‹) was einem den Namen und Pfad einer Datei liefert
• Mit dieser Angabe kann ein Reintextinhalt aus der Datei entnommen werden und…
• → ShellX ergooglen
• → BLAT oder CMAIL ergooglen
• …der mit der Zieladresse und einem Betreff (u.U. wie oben aus Datei holen) das Abfeuern der Mail erlaubt.

Das sind natürlich mehr als eine Handvoll Zeilen, aber dieser Baukasten funktioniert und ist je nach dem auch sehr felxibel auch Listen abzuarbeiten. Dateinamen in Zellen zu tippen ist, einfach gesagt, grob fahrlässig. Und diese eingefügten Objekte braucht es dann auch nicht.

CN8

 

[dachboden20]

Ich danke für eure Hilfe. Bin die Sache jetzt aber etwas anders angegangen. Ich arbeite jetzt nicht mehr mit einem anklickbaren Objekt, sondern mit einem richtigen Dateianhang. Ich habe ein neues Thema erstellt mit neuen Problemen.