Sheet Range Through Lotus Notes - Nate O.?

[stapuff]

The following code provided by Nate O. & Van Pookie (thank you by the way) works great, however, I need help on making a change. This sends all values in column b to an e-mail Lotus Notes format. How can it change it to send all values in a range A1:D65536.end ray:

Dim Maildb As Object, UserName As String, MailDbName As String
Dim MailDoc As Object, Session As Object
Dim myStr As Variant
Set Session = CreateObject("Notes.NotesSession")
UserName = Session.UserName
MailDbName = Left$(UserName, 1) & Right$(UserName, _
(Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
Set Maildb = Session.GetDatabase("", MailDbName)
If Not Maildb.IsOpen Then Maildb.OpenMail
Set MailDoc = Maildb.CreateDocument
MailDoc.Form = "Memo"
MailDoc.SendTo = "homer@simpson.com" 'Nickname or full address
'MailDoc.CopyTo = Whomever
'MailDoc.BlindCopyTo = Whomever
MailDoc.Subject = "Help Me"
With Range([B5], [B65536].End(3))
If IsArray(.Value) Then Let myStr = _
Join(WorksheetFunction.Transpose(.Value), "@") _
Else Let myStr = .Value
End With
MailDoc.Body = WorksheetFunction.Substitute( _
"Good afternoon!@@The following EO's are being sent to you@" _
& myStr & "@@Thank you and have a great weekend!", "@", vbCrLf)
MailDoc.SaveMessageOnSend = True
MailDoc.PostedDate = Now
Call MailDoc.Send(False)
Set Maildb = Nothing: Set MailDoc = Nothing: Set Session = Nothing
End Sub



Thanks,


Kurt

 

[stapuff]

Nate -

Using 6.0

Kurt

 

[NateO]

Then make the switch.

 

[stapuff]

Nate -


Got an error

Incorrect argument type: string expected


Kurt

 

[NateO]

Yeah, [a1] will need to house a string, a valid email address. try [a1].text or cstr([a1].value). Also, you might want to reference your sheet, e.g.,

sheets(1).[a1].text

 

[stapuff]

Nate -


Tried a lot of variations to this:

Call UIdoc.FieldSetText("EnterSendTo")sheets(E-Mail Addresses).[A2].Text

Getting all types of errors.

Kurt

 

[NateO]

I believe that. Try:

Call UIdoc.FieldSetText("EnterSendTo",sheets("E-Mail Addresses").[A2].Text)

 

[stapuff]

Nate -


It went through as it should! If I wanted to set a range of addresses [a2:a20] this code bombs out. Why? Is it because I do not have valid e-mail addresses in a3:a20 or will it only take 1 at a time? How can I tell the macro to send to everyone in a range on the sheet without hard coding it in? If I add an address or take one away - the code will fail correct?


Kurt

 

[NateO]

Air code:

Call UIdoc.FieldSetText("EnterSendTo",[transpose('E-Mail Addresses'!A2:A20)])

 

[stapuff]

Nate -


Back to the error we had before:

Incorrect argument type: string expected


Kurt

 

[NateO]

I've seen arrays work... Might be a difference between OLE and COM. Try:

Call UIdoc.FieldSetText("EnterSendTo", Join([transpose('E-Mail Addresses'!A2:A20)], ","))

More air code.

I hope you have Excel 2000 or greater for this...