D
Dion McKnight
Dear all,
Basically I have been working on sending a lotus notes mail from excel
which by and large I have achieved with little problem. Basically I am
using a userform to populate all the relevant mail field. I then use a
send button to establish an instance of notes then send the actual
information to the specified recipients.
The code works fine however when I try to include more than one
recipient in any of the SendTo, CopyTo etc fields only the first
person in each instance receives the mail. When I examine my sent
items in Lotus notes I can even see the other individuals names
appearing in the relevant fields however for some inexplicable reason
thay just do not appear to receive the mails. Does Excel97 in
conjunction with Lotus Notes only have the capacity to send a mail to
one person for each field in each instance. Please any assistance that
you could provide in relation to this issue would be greatly
appreciated. I just can not figure out why it is not working
Below is the code I am using:
Private Sub UserForm_Activate()
Application.ScreenUpdating = False
Me.txtSendTo.Value = "ExternalEmailAddress"
Me.txtCC.Value = "FirstCCMailrecipient,SecondCCMailRecipient"
Me.txtBCC.Value = ""
Me.txtSubject = "Subject"
Me.txtBody = "Body of Mail entered here"
Application.ScreenUpdating = True
End Sub
Private Sub cmdSend_Click()
Dim objNotesSession As Object
Dim objNotesDatabase As Object
Dim objNotesDocument As Object
' Dim objNotesField As Object
Dim objAttachment As Object
Dim objRichText As Object
Dim FullPath As String
Dim FileName As String
Dim Msg As String
Dim MoveIncrement As Integer
Dim Counter As Integer
Dim EmailCCTo
Const EMBED_ATTACHMENT = 1454 '1454 indicate a file attachment
'Establishes a Lotus Notes Session and Open Notes Database
Set objNotesSession = CreateObject("Notes.Notessession")
Set objNotesDatabase = objNotesSession.GETDATABASE("", "")
On Error GoTo ErrorHandler:
'Open a Mail Session within Lotus Notes
Call objNotesDatabase.OPENMAIL
If objNotesDatabase.isopen = False Then
Msg = "USER INFORMATION :" & vbCrLf & vbCrLf
Msg = Msg & "Cannot connect to Lotus Notes."
MsgBox Msg, vbCritical, Application.Caption
Unload Me
Exit Sub
End If
If objNotesSession.UserName = vbNullString Then
Msg = "USER INFORMATION :" & vbCrLf & vbCrLf
Msg = Msg & "Not logged in to Lotus Notes, please login
and retry."
MsgBox Msg, vbCritical, Application.Caption
Unload Me
Exit Sub
End If
Set objNotesDocument = objNotesDatabase.CreateDocument
Call objNotesDocument.ReplaceItemValue("Form", "Memo")
FileName = ActiveWorkbook.Name
FullPath = ActiveWorkbook.Path & "\" & FileName
' Assemble lotus notes email message
Set objRichText = objNotesDocument.CREATERICHTEXTITEM("Body")
Set objAttachment = objRichText.EmbedObject(EMBED_ATTACHMENT, "",
FullPath, FileName)
' EmailCCTo = Me.txtCC.Value
' Set objNotesField = objNotesDocument.APPENDITEMVALUE("CopyTo",
EmailCCTo)
With objNotesDocument
.Subject = Me.txtSubject.Text
.Body = Me.txtBody & objNotesSession.CommonUserName
.SendTo = Me.txtSendTo.Text
.CopyTo = Me.txtCC.Text
.BlindCopyTo = Me.txtBCC.Text
.SaveMessageOnSend = True ' Save in Sent folder
.Send (False)
End With
'Section of Code below used in animation of Mail Send
MoveIncrement = 1
For Counter = 1 To 90
Me.imgMailSend.Left = Me.imgMailSend.Left + MoveIncrement
DoEvents
' Specify time to pause Excel Execution in Sleep API function
'in milliseconds where a value of 1000 equates to 1 second
Sleep 10 '0.01 of a second
Next
Application.ScreenUpdating = True
Me.Hide
ExitHere:
'Clear out objects i.e. set them to nothing
Set objNotesSession = Nothing
Set objNotesDatabase = Nothing
Set objNotesDocument = Nothing
' Set objNotesField = Nothing
Set objAttachment = Nothing
Set objRichText = Nothing
Msg = "USER INFORMATION : " & vbCrLf & vbCrLf
Msg = Msg & "Your Lotus Notes message was successfully sent..."
MsgBox Msg, vbInformation, "STATUS ON EMAIL"
' ThisWorkbook.Close
Unload Me
Exit Sub
ErrorHandler:
Err.Raise Err.Number, Err.Source, Err.Description
Resume ExitHere
End Sub
Basically I have been working on sending a lotus notes mail from excel
which by and large I have achieved with little problem. Basically I am
using a userform to populate all the relevant mail field. I then use a
send button to establish an instance of notes then send the actual
information to the specified recipients.
The code works fine however when I try to include more than one
recipient in any of the SendTo, CopyTo etc fields only the first
person in each instance receives the mail. When I examine my sent
items in Lotus notes I can even see the other individuals names
appearing in the relevant fields however for some inexplicable reason
thay just do not appear to receive the mails. Does Excel97 in
conjunction with Lotus Notes only have the capacity to send a mail to
one person for each field in each instance. Please any assistance that
you could provide in relation to this issue would be greatly
appreciated. I just can not figure out why it is not working
Below is the code I am using:
Private Sub UserForm_Activate()
Application.ScreenUpdating = False
Me.txtSendTo.Value = "ExternalEmailAddress"
Me.txtCC.Value = "FirstCCMailrecipient,SecondCCMailRecipient"
Me.txtBCC.Value = ""
Me.txtSubject = "Subject"
Me.txtBody = "Body of Mail entered here"
Application.ScreenUpdating = True
End Sub
Private Sub cmdSend_Click()
Dim objNotesSession As Object
Dim objNotesDatabase As Object
Dim objNotesDocument As Object
' Dim objNotesField As Object
Dim objAttachment As Object
Dim objRichText As Object
Dim FullPath As String
Dim FileName As String
Dim Msg As String
Dim MoveIncrement As Integer
Dim Counter As Integer
Dim EmailCCTo
Const EMBED_ATTACHMENT = 1454 '1454 indicate a file attachment
'Establishes a Lotus Notes Session and Open Notes Database
Set objNotesSession = CreateObject("Notes.Notessession")
Set objNotesDatabase = objNotesSession.GETDATABASE("", "")
On Error GoTo ErrorHandler:
'Open a Mail Session within Lotus Notes
Call objNotesDatabase.OPENMAIL
If objNotesDatabase.isopen = False Then
Msg = "USER INFORMATION :" & vbCrLf & vbCrLf
Msg = Msg & "Cannot connect to Lotus Notes."
MsgBox Msg, vbCritical, Application.Caption
Unload Me
Exit Sub
End If
If objNotesSession.UserName = vbNullString Then
Msg = "USER INFORMATION :" & vbCrLf & vbCrLf
Msg = Msg & "Not logged in to Lotus Notes, please login
and retry."
MsgBox Msg, vbCritical, Application.Caption
Unload Me
Exit Sub
End If
Set objNotesDocument = objNotesDatabase.CreateDocument
Call objNotesDocument.ReplaceItemValue("Form", "Memo")
FileName = ActiveWorkbook.Name
FullPath = ActiveWorkbook.Path & "\" & FileName
' Assemble lotus notes email message
Set objRichText = objNotesDocument.CREATERICHTEXTITEM("Body")
Set objAttachment = objRichText.EmbedObject(EMBED_ATTACHMENT, "",
FullPath, FileName)
' EmailCCTo = Me.txtCC.Value
' Set objNotesField = objNotesDocument.APPENDITEMVALUE("CopyTo",
EmailCCTo)
With objNotesDocument
.Subject = Me.txtSubject.Text
.Body = Me.txtBody & objNotesSession.CommonUserName
.SendTo = Me.txtSendTo.Text
.CopyTo = Me.txtCC.Text
.BlindCopyTo = Me.txtBCC.Text
.SaveMessageOnSend = True ' Save in Sent folder
.Send (False)
End With
'Section of Code below used in animation of Mail Send
MoveIncrement = 1
For Counter = 1 To 90
Me.imgMailSend.Left = Me.imgMailSend.Left + MoveIncrement
DoEvents
' Specify time to pause Excel Execution in Sleep API function
'in milliseconds where a value of 1000 equates to 1 second
Sleep 10 '0.01 of a second
Next
Application.ScreenUpdating = True
Me.Hide
ExitHere:
'Clear out objects i.e. set them to nothing
Set objNotesSession = Nothing
Set objNotesDatabase = Nothing
Set objNotesDocument = Nothing
' Set objNotesField = Nothing
Set objAttachment = Nothing
Set objRichText = Nothing
Msg = "USER INFORMATION : " & vbCrLf & vbCrLf
Msg = Msg & "Your Lotus Notes message was successfully sent..."
MsgBox Msg, vbInformation, "STATUS ON EMAIL"
' ThisWorkbook.Close
Unload Me
Exit Sub
ErrorHandler:
Err.Raise Err.Number, Err.Source, Err.Description
Resume ExitHere
End Sub