Email from excel via Lotus Notes

[gaz_chops]

I managed to find this piece of code on this site, which I thought (hoped) I might be able to adapt, one huge problem is that I have never used vb???
What I need to do is send an email to each of the house no.s (as per the excel example, but without the leading alpha character) and populate the ????in the vb with the "Model/Item"......any help would be hugely welcome

Thanks
Gaz

Sub SendNotesMail()
Dim Maildb As Object, UserName As String, MailDbName As String
Dim MailDoc As Object, Session As Object
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 Maildb.IsOpen = True Then
Else: Maildb.OpenMail
End If
Set MailDoc = Maildb.CreateDocument
MailDoc.Form = "Memo"
MailDoc.SendTo = "0504" 'Nickname or full address
'MailDoc.CopyTo = 'whomever
'MailDoc.BlindCopyTo = Whomever
MailDoc.Subject = "Machine Change"
MailDoc.Body = _
Replace("As a result of a review of your AWP collections that I have carried out,@@I have asked Leisure Link to replace your ????? AWP.@@@@I or your Leisure Link Business Account Manager will try @@to phone you to discuss this within the next couple of days.@@However if you have any immediate comments,@@please do not hesitate to contact either of us." _
& Join(Application.Transpose(Range([b5], [b65536].End(3))), "@") _
& "@@With kind regards", "@", vbCrLf)
MailDoc.SaveMessageOnSend = True
MailDoc.PostedDate = Now
On Error GoTo Audi
Call MailDoc.Send(False)
Set Maildb = Nothing: Set MailDoc = Nothing: Set Session = Nothing
Exit Sub
Audi:
Set Maildb = Nothing: Set MailDoc = Nothing: Set Session = Nothing
End Sub

PCIMIS Notes.xls
	A	B	C	D
1	HseNo'	Model/Item	Recommendation
2	C0504	25NOTERUNNER	changetoJacktheKipper
3	C3223	25ROYALROULETTE	ANBdropping-changetoSuperstars/TopDog/HiFlyer
4	C1228	25HYPNOTIC	ChangeHypnotic-urgent
5	S5564	25TOPDOG	ChangetoBubble&Squeak/Jackpot&Beanstreak Changestilloutstanding
6	S5564	25CASHEXPLOSION	noedcsinceinstall-pleasecheck.Stillnoedcbeingreceivedpleasedealwith ChangetoMonopoly/Dambusters changestilloutstanding
7	S5564	25DO$HNPECKS	ChangeQualityStreak/Superstars/Dambuster
8	C3253	25CASHBANGWALLOP	ChangetoJumpinJokersorcashExplosionon82%
9	S9135	25JACKPOTANDTHEBEANSTREAK	ChangeANBdropping-injectionplease
Sheet1

 

[emitecaps]

hi,

this looks really useful - i'm trying to send emails to addresses in column a but i can't seem to get the code to work.

I've changed this
MailDoc.SendTo = Right$(cl, Len(cl) - 1)
but it doesn't seem to be working
the email addresses are in usual format of abc@def.com

anyhelp would be greatly appreciated!

thanks

 

[emitecaps]

apologies..have solved it ...by removing the -1
thanks

 

[NateO]

I would think you would just want:

MailDoc.SendTo = cl.value

Then... Not sure why I wrote this that way, three years later...

 

[emitecaps]

thanks...have found this code so very very useful.
my only remaining problem is that I have access to 2 databases, I want to use a specific one that is shared but I find that the mails are sent from my personal db which must be the default.
I've tried changing
MailDbName = "sharedmail\***.nsf" but this hasn't worked.


thanks...
barry

 

[NateO]

Just for the sake of having a certain 'sent from' address? Don't even mess around with that, see the following:

http://www.mrexcel.com/board2/viewtopic.php?p=922435#922435

I never found my source file and I no longer use Lotus Notes, but this certainly does work.