vaRecipient = multiple names

[J.W. Aldridge]

Have this code that sends worksheet via lotus notes....

Tried placing a comma, semicolon, and every other trick i could think
of but cant get it to work.
Need to add more than one name.


vaRecipient = "jeremy aldridge"


thanx

 

[Dave Peterson]

I don't use Lotus Notes, but I'd try:

vaRecipient = array("jeremy aldridge", "next name here", "another")

vaRecipient is declared as a Variant, right???

 

[Ron de Bruin]

For the OP

See Dennis his site for Notes code
http://www.excelkb.com/?cNode=1X5M7A

 

[J.W. Aldridge]

Dave,

yes, it is declared as a variant.

However, still got an error...... "runtime error 13, type mismatch"

this line was highlighted.


Loop While vaRecipient = ""

 

[Dave Peterson]

Since vaRecipient is a Variant, it could be a string, a number, a boolean or an
array of anything.

If you're vaRecipient is a string (not an array), then that comparison will
work.

If you've already populated the vaRecipient with an array of addresses, maybe
you could use a different loop:

Dim iCtr as long
for ictr = lbound(vaRecipient) to ubound(vaRecipient)
'do something with that recipient
msgbox varecipient(ictr)
next ictr

But I don't know what you're really doing.

 

[J.W. Aldridge]

Tried implementing that but couldnt quite figure it out.....

Here's the original code.
Works with one recipient (only).
(I could even use a cc: field, just need to add more names)

Sub SendWithLotus()
Dim noSession As Object, noDatabase As Object, noDocument As
Object
Dim obAttachment As Object, EmbedObject As Object
Dim stSubject As Variant, stAttachment As String
Dim vaRecipient As Variant, vaMsg As Variant

Const EMBED_ATTACHMENT As Long = 1454
Const stTitle As String = "Active workbook status"
Const stMsg As String = "The active workbook must first be saved
" & vbCrLf _
& "before it can be sent as an attachment."
'Check if the active workbook is saved or not
'If the active workbook has not been saved at all.
If Len(ActiveWorkbook.Path) = 0 Then
MsgBox stMsg, vbInformation, stTitle
Exit Sub
End If
'If the changes in the active workbook have been saved or not.
If ActiveWorkbook.Saved = False Then
If MsgBox("Do you want to save the changes before sending?", _
vbYesNo + vbInformation, stTitle) = vbYes Then _
ActiveWorkbook.Save
End If
'Get the name of the recipient from the user.
Do
vaRecipient = "jeremy aldridge"
'Prompt:="Please add name of the recipient such as:" & vbCrLf
_
'& "(e-mail address removed) or just the name if internal mail within
Unity.", _
'Title:="Recipient", Type:=2)
Loop While vaRecipient = ""
'If the user has canceled the operation.
If vaRecipient = False Then Exit Sub
'Get the message from the user.
Do
vaMsg = "Hi, Please make the following adjustments to the DOR
for PCP.... Thanx"
'Prompt:="Please enter the message such as:" & vbCrLf _
'& "Enclosed please find the weekly report.", _
'Title:="Message", Type:=2)
Loop While vaMsg = ""

'If the user has canceled the operation.
If vaMsg = False Then Exit Sub
'Add the subject to the outgoing e-mail
'which also can be retrieved from the users
'in a similar way as above.
Do
stSubject = "PCP Out of Production Report"
'Prompt:="Please add a subject such as:" & vbCrLf _
'& "Weekly Report.", _
'Title:="Subject", Type:=2)
Loop While stSubject = ""
'Retrieve the path and filename of the active workbook.
stAttachment = ActiveWorkbook.FullName
'Instantiate the Lotus Notes COM's Objects.
Set noSession = CreateObject("Notes.NotesSession")
Set noDatabase = noSession.GETDATABASE("", "")
'If Lotus Notes is not open then open the mail-part of it.
If noDatabase.IsOpen = False Then noDatabase.OPENMAIL
'Create the e-mail and the attachment.
Set noDocument = noDatabase.CreateDocument
Set obAttachment = noDocument.CreateRichTextItem("stAttachment")
Set EmbedObject = obAttachment.EmbedObject(EMBED_ATTACHMENT, "",
stAttachment)
'Add values to the created e-mail main properties.
With noDocument
.Form = "Memo"
.SendTo = vaRecipient
.Subject = stSubject
.Body = vaMsg
.SaveMessageOnSend = True
End With
'Send the e-mail.
With noDocument
.PostedDate = Now()
.Send 0, vaRecipient
End With

'Release objects from the memory.
Set EmbedObject = Nothing
Set obAttachment = Nothing
Set noDocument = Nothing
Set noDatabase = Nothing
Set noSession = Nothing

'Activate Excel for the user.
AppActivate "Microsoft Excel"
MsgBox "The e-mail has successfully been created and
distributed.", vbInformation
End Sub

 

[Dave Peterson]

Dim vaRecipient() as string 'no longer a variant
dim resp as string
dim rCtr as long

rctr = 0
Do
resp = application.inputbox( _
Prompt:="Please add name of the recipient such as:" & vbCrLf _
& "(e-mail address removed) or just the name if internal mail within Unity.", _
Title:="Recipient", Type:=2)

if trim(resp) = "" then
'get out, the user is done!
exit do
else
'add the name to the array
rctr = rctr + 1
'preserve the previous names and resize the array
redim preserve varecipient(1 to rctr)
'add the name to the next element in the array
varecipient(rctr) = resp
Loop

'check to see if there were any names to send to
if rctr = 0 then
msgbox "hey, I can't send it to no one!"
exit sub 'or something else???
end if

======
Untested, uncompiled. Watch for typos!