Fehler beim Kompilieren, Argument ist nicht Option
09.09.2016 16:28:00
Klaus
ich möchte aus Excel mit Lotus Notes mailen.
Habe eine Datei mit 8 Empfängern die gut funktioniert erweitert und weitere 9 Empfänger hinzugefügt.
Nun bekomme ich die Fehlermeldung "Fehler beim Kompilieren, Argument ist nicht Option"
Sind das zu viele Empfänger oder habe ich da was falsch gemacht?
Ich würde mich freuen wenn sich jemand die Zeit nimmt um die Datei zu durchforsten.
Danke im Voraus.
Sub AbsenderUebernehmen()
Cells(10, 42) = Cells(112, 1) ' Fax Nr Einsteller
Cells(11, 42) = Cells(113, 1) ' Email Absender
Cells(12, 42) = Cells(114, 1) ' Email Empfänger
Cells(13, 42) = Cells(115, 1) ' Email Team Versand
Cells(10, 57) = Cells(116, 1) ' Email Team Empfang
Cells(11, 57) = Cells(117, 1) ' Email Team Vorfallort
Cells(12, 57) = Cells(118, 1) ' Email V.CCS
Cells(13, 57) = Cells(119, 1) ' Email BTT
Cells(14, 57) = Cells(120, 1) ' y Saab/
Cells(15, 57) = Cells(118, 19) 'Absender
Cells(16, 57) = Cells(118, 19) 'L-E
Cells(17, 57) = Cells(118, 19) 'Jan
Cells(18, 57) = Cells(118, 19) 'Gabriele
Cells(19, 57) = Cells(118, 19) 'patrick.
Cells(20, 57) = Cells(118, 19) 'L-,
Cells(21, 57) = Cells(118, 19) 'Harald G
Cells(23, 57) = Cells(118, 19) 'Christoph F AG
Cells(25, 57) = Cells(118, 19) 'PK Regional
End Sub
Public Sub SendNotesMail(subject As String, Attachment As String, _
recipient As String, copyto As String, copyto2 As String, copyto3 As String, copyto4 As String, _
_
_
_
copyto5 As String, copyto6 As String, copyto7 As String, copyto8 As String, copyto9 As String, _
_
_
copyto10 As String, copyto11 As String, copyto12 As String, copyto13 As String, copyto14 As _
String, copyto15 As String, copyto16 As String, copyto17 As String, copyto18 As String, _
copyto19 As String, BodyText As String, SaveIt As Boolean)
'Variablen Dimensionieren, die benötigt werden, um das Mail zu senden
Dim Maildb As Object 'Die Datenbank
Dim UserName As String 'Der Benutzername
Dim MailDbName As String 'Der Datenbankname
Dim MailDoc As Object 'Das Maildokument selbst
Dim AttachME As Object 'Der Anhang (Richtext)
Dim Session As Object 'Die Notes Session
Dim EmbedObj As Object 'Ein eingebettetes Objekt (Anhang)
Dim recp(19) As Variant
Dim pos As Byte
recp(pos) = recipient: If recipient "" Then pos = pos + 1
If copyto "" And copyto "0" Then
recp(pos) = copyto$: pos = pos + 1
End If
If copyto2 "" And copyto2 "0" Then
recp(pos) = copyto2$: pos = pos + 1
End If
If copyto3 "" And copyto3 "0" Then
recp(pos) = copyto3$: pos = pos + 1
End If
If copyto4 "" And copyto4 "0" Then
recp(pos) = copyto4$: pos = pos + 1
End If
If copyto5 "" And copyto5 "0" Then
recp(pos) = copyto5$: pos = pos + 1
End If
If copyto6 "" And copyto6 "0" Then
recp(pos) = copyto6$: pos = pos + 1
End If
If copyto7 "" And copyto7 "0" Then
recp(pos) = copyto7$: pos = pos + 1
End If
If copyto8 "" And copyto8 "0" Then
recp(pos) = copyto8$: pos = pos + 1
End If
If copyto9 "" And copyto9 "0" Then
recp(pos) = copyto9$: pos = pos + 1
End If
If copyto10 "" And copyto10 "0" Then
recp(pos) = copyto10$: pos = pos + 1
End If
If copyto11 "" And copyto11 "0" Then
recp(pos) = copyto11$: pos = pos + 1
End If
If copyto12 "" And copyto12 "0" Then
recp(pos) = copyto12$: pos = pos + 1
End If
If copyto13 "" And copyto13 "0" Then
recp(pos) = copyto13$: pos = pos + 1
End If
If copyto14 "" And copyto14 "0" Then
recp(pos) = copyto14$: pos = pos + 1
End If
If copyto15 "" And copyto15 "0" Then
recp(pos) = copyto15$: pos = pos + 1
End If
If copyto16 "" And copyto16 "0" Then
recp(pos) = copyto16$: pos = pos + 1
End If
If copyto17 "" And copyto17 "0" Then
recp(pos) = copyto17$: pos = pos + 1
End If
If copyto18 "" And copyto18 "0" Then
recp(pos) = copyto18$: pos = pos + 1
End If
If copyto19 "" And copyto19 "0" Then
recp(pos) = copyto19$: pos = pos + 1
End If
copyto = ""
'Die Session starten
Set Session = CreateObject("Notes.NotesSession")
'Den Benutzernamen auslesen und den Dateinamen
'der MailDB errechnen
'Dies wird nicht überall benötigt. Auf manchen
'Systemen kann auch ein leerer String übergeben werden
UserName = Session.UserName
Set Maildb = "A041M\mailindb\app2\leocswtgerma.nsf"
'Datenbank öffnen
Set Maildb = Session.GETDATABASE("", MailDbName)
If Maildb.IsOpen = True Then
'Fertig zum mailen!
Else
Maildb.OPENMAIL
End If
'Ein neues Maildokument erstellen
Set MailDoc = Maildb.CREATEDOCUMENT
MailDoc.Form = "Memo"
MailDoc.copyto = copyto
MailDoc.sendto = recp
MailDoc.subject = subject
MailDoc.Body = BodyText
MailDoc.SAVEMESSAGEONSEND = SaveIt
'Eingebettete Objekte und Anhänge hinzufügen
If Attachment "" Then
Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")
Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", Attachment, _
"Attachment")
End If
'Senden
MailDoc.PostedDate = Now()
MailDoc.SEND 0, recp
'Aufräumen
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj = Nothing
End Sub
Public Sub Kunden_Verstaendigen()
'Wir prüfen nun wer, wie, wo und was zu verständigen ist...
' Wir speichern eine HTML generierte Transportverzögerungsdatei
Range("A1:AG64").Select
With ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
"C:\temp\CS_Sofo.htm", "Sofo", _
"$A$1:$AG$68", xlHtmlStatic, "Mappe_test_18813", "")
.Publish (True)
.AutoRepublish = False
End With
' Prüfen auf FaxNr.
Dim FaxNr As String
FaxNr = Cells(10, 42)
If FaxNr "" And FaxNr "0" Then ' Senden eines Faxes mit Anhang
SendNotesMail "Sofo", "c:\temp\CS_Sofo.htm", FaxNr + "@FAX", "", "", "", "", "", "", "", _
_
"", "", "", "", "", "", "", "", "", "", "", "", "", False
End If
' Bodytext
Dim Body As String
Body = "Sehr geehrte Damen und Herren," + Chr(10) + Chr(10)
Body = Body + "Bitte beachten sie angehängte Datei Sofo." + Chr(10) + Chr(10) + Chr(10)
Body = Body + "Für weitere Fragen bzw. Rückfragen zu diesem Schreiben nutzen sie bitte _
ausschließlich folgende Mailadresse:" + Chr(10) + Chr(10)
Body = Body + "x/Mail-In" + Chr(10) + Chr(10)
Body = Body + "Nur hierüber können wir sicherstellen, dass Ihre Anfrage schnellst möglich _
_
bearbeitet wird." + Chr(10) + Chr(10) + Chr(10)
Body = Body + "Mit freundlichen Grüßen" + Chr(10) + Chr(10)
Body = Body + "xxxxxxxxxxxxxxxx" + Chr(10)
' Prüfen auf Mail
If Cells(11, 42) "" Or Cells(12, 42) "" Or Cells(13, 42) "" Or Cells(10, 57) "" _
_
Or Cells(11, 57) "" Or Cells(12, 57) "" Or Cells(13, 57) "" Or Cells(14, 57) "" Or _
Cells(15, 57) "" Or Cells(16, 57) "" Or Cells(17, 57) "" Or Cells(18, 57) "" Or Cells(19, 57) "" Or Cells(20, 57) "" Or Cells(21, 57) "" Or Cells(23, 57) "" Or Cells(25, 57) "" Then
SendNotesMail "Sofo", "c:\temp\CS_Sofo.htm", Cells(11, 42), Cells(12, 42), Cells(13, 42) _
_
, Cells(10, 57), Cells(11, 57), Cells(12, 57), Cells(13, 57), Cells(14, 57), Cells(15, 57), _
Cells(16, 57), Cells(17, 57), Cells(18, 57), Cells(19, 57), Cells(20, 57), Cells(21, 57), Cells(23, 57), Cells(25, 57), Body$, False
End If
' Prüfen ob eine Kopie an den Empfänger der Sendung gewünscht ist
If Cells(112, 19) = True Then
' Fax Senden
FaxNr = Cells(10, 42)
If FaxNr "" Or FaxNr "0" Then
SendNotesMail "Sofo Empfänger-Kopie", "c:\temp\CS_Sofo.htm", FaxNr + "@FAX", "", "", _
_
"", "", False
Else
MsgBox "Eine Fax-Empfängerkopie konnte nicht gesendet werden da die Faxnummer ungü _
_
ltig ist."
End If
' Email Senden
'SendNotesMail "ChemSolution Transportverzögerung Empfänger-Kopie ", "c:\temp\ _
CS_Transportverzoegerung.htm", Cells(114, 19), Cells(115, 19), Cells(116, 19), Cells(117, 19), _
Cells(11, 57), False
End If
' Kopie in den Teambriefkasten...
Dim PreBody As String
PreBody = "Nachfolgende Email wurde an folgende Kontaktadressen übermittelt:" + Chr(10) + _
_
Chr(10)
PreBody = PreBody + FaxNr + Chr(10)
If Cells(11, 42) "" And Cells(11, 42) "0" Then
PreBody = PreBody + Cells(11, 42) + Chr(10)
End If
If Cells(12, 42) "" And Cells(12, 42) "0" Then
PreBody = PreBody + Cells(12, 42) + Chr(10)
End If
If Cells(13, 42) "" And Cells(13, 42) "0" Then
PreBody = PreBody + Cells(13, 42) + Chr(10)
End If
If Cells(10, 57) "" And Cells(10, 57) "0" Then
PreBody = PreBody + Cells(10, 57) + Chr(10)
End If
If Cells(11, 57) "" And Cells(11, 57) "0" Then
PreBody = PreBody + Cells(11, 57) + Chr(10)
End If
If Cells(12, 57) "" And Cells(12, 57) "0" Then
PreBody = PreBody + Cells(12, 57) + Chr(10)
End If
If Cells(13, 57) "" And Cells(13, 57) "0" Then
PreBody = PreBody + Cells(13, 57) + Chr(10)
End If
If Cells(14, 57) "" And Cells(14, 57) "0" Then
PreBody = PreBody + Cells(14, 57) + Chr(10)
End If
If Cells(15, 57) "" And Cells(15, 57) "0" Then
PreBody = PreBody + Cells(15, 57) + Chr(10)
End If
If Cells(16, 57) "" And Cells(16, 57) "0" Then
PreBody = PreBody + Cells(16, 57) + Chr(10)
End If
If Cells(17, 57) "" And Cells(17, 57) "0" Then
PreBody = PreBody + Cells(17, 57) + Chr(10)
End If
If Cells(18, 57) "" And Cells(18, 57) "0" Then
PreBody = PreBody + Cells(18, 57) + Chr(10)
End If
If Cells(19, 57) "" And Cells(19, 57) "0" Then
PreBody = PreBody + Cells(19, 57) + Chr(10)
End If
If Cells(20, 57) "" And Cells(20, 57) "0" Then
PreBody = PreBody + Cells(20, 57) + Chr(10)
End If
If Cells(21, 57) "" And Cells(21, 57) "0" Then
PreBody = PreBody + Cells(21, 57) + Chr(10)
End If
If Cells(23, 57) "" And Cells(23, 57) "0" Then
PreBody = PreBody + Cells(23, 57) + Chr(10)
End If
If Cells(25, 57) "" And Cells(25, 57) "0" Then
PreBody = PreBody + Cells(25, 57) + Chr(10)
End If
PreBody = PreBody + Chr(10) + Chr(10)
If Cells(112, 19) = True Then
PreBody = PreBody + "Zusätzlich wurde der Empfänger der Sendung informiert."
End If
' Kopie ans Zuständige Team
Dim TeamMail As String
If Cells(21, 9) "" And Cells(21, 9) "0" Then
TeamMail = Cells(21, 9)
Else
TeamMail = ""
End If
SendNotesMail "Sofo", "c:\temp\CS_Sofo.htm", "L-EOC-SWT-Germany/Mail-In", TeamMail, "", "", _
_
"", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", PreBody & Body$, False
MsgBox "Verständigung abgesetzt. Eine Kopie der Verständigung wurde in die ToDo-Liste _
gesendet."
End Sub