Public
MYPATH
As
String
Option
Explicit
Sub
Start()
Worksheets(
"Auswahllisten"
).Range(
"F2:F105"
).Copy
Worksheets(
"Daten Umschläge"
).Range(
"A2"
).PasteSpecial xlPasteValues
Worksheets(
"Eingabetabelle"
).Range(
"H3:H105"
).Copy
Worksheets(
"Daten Umschläge"
).Range(
"B2"
).PasteSpecial xlPasteValues
Worksheets(
"Eingabetabelle"
).Range(
"I3:I105"
).Copy
Worksheets(
"Daten Umschläge"
).Range(
"C2"
).PasteSpecial xlPasteValues
Worksheets(
"Eingabetabelle"
).Range(
"D3:D105"
).Copy
Worksheets(
"Daten LLBB"
).Range(
"A2"
).PasteSpecial xlPasteValues
Worksheets(
"Eingabetabelle"
).Range(
"E3:E105"
).Copy
Worksheets(
"Daten LLBB"
).Range(
"B2"
).PasteSpecial xlPasteValues
Worksheets(
"Eingabetabelle"
).Range(
"F3:F105"
).Copy
Worksheets(
"Daten LLBB"
).Range(
"C2"
).PasteSpecial xlPasteValues
Worksheets(
"Eingabetabelle"
).Range(
"G3:G105"
).Copy
Worksheets(
"Daten LLBB"
).Range(
"D2"
).PasteSpecial xlPasteValues
Worksheets(
"Eingabetabelle"
).Range(
"J3:J105"
).Copy
Worksheets(
"Daten LLBB"
).Range(
"E2"
).PasteSpecial xlPasteValues
Worksheets(
"Eingabetabelle"
).Range(
"L3:L105"
).Copy
Worksheets(
"Daten LLBB"
).Range(
"F2"
).PasteSpecial xlPasteValues
Worksheets(
"Daten Umschläge"
).Range(
"$A$1:$C$105"
).RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes
Range(
"a3:a105"
).FormulaLocal =
"=WENN(NICHT(ISTLEER(B3));ANZAHL2($B$3:B3);"
")"
Call
MacroMitDeinemFormularSteuerelementVerknuepfen
End
Sub
Sub
MacroMitDeinemFormularSteuerelementVerknuepfen()
Dim
sText
As
String
MYPATH = Environ(
"temp"
)
sText =
"Sehr geehrte Damen und Herren,<br><br>"
sText = sText &
"anbei die Daten der heutigen XXX."
sText = sText &
""
Call
SendSheetOutlook( _
"XXX"
, _
"XXX"
, _
""
, _
sText)
End
Sub
Private
Sub
SendSheetOutlook(sSubject
As
String
, sTo
As
String
, sCC
As
String
,
ByVal
sText
As
String
)
Dim
olApp
As
Object
Dim
AWS
As
String
Dim
olOldBody
As
String
AWS = MYPATH &
"\" & Format(Date, "
YYYYMMDD
") & "
_
" & Format(Time, "
hhmmss
") & "
_" & _
WorksheetFunction.Substitute(ActiveWorkbook.Name,
".xlsm"
,
""
)
AWS = AWS
Worksheets(
"Daten LLBB"
).ExportAsFixedFormat Type:=xlTypePDF, Filename:=AWS, Quality:=xlQualityStandard, _
IncludeDocProperties:=
True
, IgnorePrintAreas:=
False
, OpenAfterPublish:=
False
AWS = AWS &
".pdf"
Set
olApp = CreateObject(
"Outlook.Application"
)
With
olApp.CreateItem(0)
.GetInspector.Display
olOldBody = .htmlBody
.
To
= sTo
.cc = sCC
.Subject = sSubject
.htmlBody = sText & olOldBody
.Attachments.Add AWS
End
With
AWS = AWS
ActiveWorkbook.SaveCopyAs
"DATEIPFAD"
& Format(Now,
"dd.mm.yyyy"
) &
".xlsm"
ThisWorkbook.Saved =
True
Application.Quit
End
Sub