Microsoft Excel

Excel und VBA: Formeln, Programmierung, Lösungen

Die Excel/VBA-Beispiele

Thema

Inhalt einer Arbeitsmappe als Mail senden (ohne die Mappe selbst)

Gruppe

Email

Problem

Wie kann man den Inhalt einer Excel-Tabelle per Mail versenden, ohne daß die Arbeitsmappe selbst versandt wird?

Lösung
Geben Sie den nachfolgenden Code in ein Standardmodul ein und weisen Sie ihn einer Schaltfläche zu.

StandardModule: basMain

Private Declare Function ShellExecute Lib "Shell32.dll" _
   Alias "ShellExecuteA" (ByVal hWnd As Long, _
   ByVal lpOperation As String, ByVal lpFile As String, _
   ByVal lpParameters As String, ByVal lpDirectory As String, _
   ByVal nShowCmd As Long) As Long

Private Sub Mail( _
   eMail As String, _
   Optional Subject As String, _
   Optional Body As String)
   Call ShellExecute(0&, "Open", "mailto:" + eMail + _
      "?Subject=" + Subject + "&Body=" + Body, "", "", 1)
End Sub

Sub MailVersenden()
   Dim rng As Range
   Dim sMail As String, sSubject As String
   Dim sBody As String
   Dim iRow As Integer, iCol As Integer
   sMail = "hans@herber.de"
   sSubject = "Excel-Daten"
   Set rng = Range("A1").CurrentRegion
   For iCol = 1 To rng.Columns.Count
      For iRow = 1 To rng.Rows.Count
         sBody = sBody & rng.Cells(iRow, iCol) & " "
      Next iRow
   Next iCol
   Call Mail(sMail, sSubject, sBody)
End Sub