VBA Lotus Notes??? 10

[mbarnett]

Hi All,

I have an email I need to send every night, It contains the same name and is located in the same folder (it is created by a macro) is there a way to automatically send the email using VBA. I used to do it in my old job but that was with outlook. How can I do it or write it using Lotus Notes? Any Ideas????

 

[tbl]

Try this

'Public Sub SendNotesMail(Subject as string, attachment as string,
'recipient as string, bodytext as string,saveit as Boolean)
'This public sub will send a mail and attachment if neccessary to the
'recipient including the body text.
'Requires that notes client is installed on the system.

'Public Sub SendNotesMail(Subject As String, Attachment As String, Recipient As String, BodyText As String, SaveIt As Boolean)
Public Sub SendNotesMail(SavePathExcel, Title, FileType, Addressee)
'Set up the objects required for Automation into lotus notes
Dim Maildb As Object 'The mail database
Dim UserName As String 'The current users notes name
Dim MailDbName As String 'THe current users notes mail database name
Dim MailDoc As Object 'The mail document itself
Dim AttachME As Object 'The attachment richtextfile object
Dim Session As Object 'The notes session
Dim EmbedObj As Object 'The embedded object (Attachment)
'Start a session to notes
Set Session = CreateObject("Notes.NotesSession&quot
'Get the sessions username and then calculate the mail file name
'You may or may not need this as for MailDBname with some systems you
'can pass an empty string
UserName = Session.UserName
MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " &quot)) & ".nsf"
'Open the mail database in notes
Set Maildb = Session.GETDATABASE("", MailDbName)
If Maildb.IsOpen = True Then
'Already open for mail
Else
Maildb.OPENMAIL
End If
'Set up the new mail document
Set MailDoc = Maildb.CREATEDOCUMENT
MailDoc.Form = "Memo"
'MailDoc.sendto = Recipient
MailDoc.sendto = Addressee '"Richard"
'MailDoc.Subject = Subject
MailDoc.Subject = Title
'MailDoc.Body = BodyText
MailDoc.Body = "User Text............"
'MailDoc.SAVEMESSAGEONSEND = SaveIt
MailDoc.SAVEMESSAGEONSEND = False

'Set up the embedded object and attachment and attach it
Attachment = SavePathExcel & Title & FileType
If Attachment <> &quot;&quot; Then
Set AttachME = MailDoc.CREATERICHTEXTITEM(&quot;Attachment&quot
Set EmbedObj = AttachME.EMBEDOBJECT(1454, &quot;&quot;, Attachment, &quot;Attachment&quot
MailDoc.CREATERICHTEXTITEM (&quot;Attachment&quot
End If
'Send the document
MailDoc.SEND 0, Recipient
'Clean Up
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj = Nothing
End Sub

Have fun,

Richard

 

[mbarnett]

Thanks... I apologize for the next couple question, but if I have the attachment(an excel file) in my C:\Temp and my Lotus notes reciepient is michael.m.james@rrr.com how would I incorporate the above in your code. Sorry for the dumb question.

 

[tbl]

This should work if I have not made any errors in my editing.You can step through this one with F8 in the code module as usual to see what doesn't work.My original code was a total solution where one calls the code from another routine.
e.g.
SendByLotusNotes
Subject =&quot;something...&quot;
Addressee = &quot;Fred@Hotmail.com&quot;
Attachment = &quot;C:\ExcelFiles\Myfile.doc&quot;
BodyText = &quot;Message...&quot;
Saveit = False 'as Boolean
Call SendNotesMail(SavePathExcel, Title, FileType, Addressee,Saveit)

'Public Sub SendNotesMail(Subject as string, attachment as string,
'recipient as string, bodytext as string,saveit as Boolean)
'This public sub will send a mail and attachment if neccessary to the
'recipient including the body text.
'Requires that notes client is installed on the system.

'Public Sub SendNotesMail(Subject As String, Attachment As String, Recipient As String, BodyText As String, SaveIt As Boolean)

Just paste this bit and test it

Public Sub SendNotesMail()
'Set up the objects required for Automation into lotus notes
Dim Maildb As Object 'The mail database
Dim UserName As String 'The current users notes name
Dim MailDbName As String 'THe current users notes mail database name
Dim MailDoc As Object 'The mail document itself
Dim AttachME As Object 'The attachment richtextfile object
Dim Session As Object 'The notes session
Dim EmbedObj As Object 'The embedded object (Attachment)
'Start a session to notes
Set Session = CreateObject(&quot;Notes.NotesSession&quot
'Get the sessions username and then calculate the mail file name
'You may or may not need this as for MailDBname with some systems you
'can pass an empty string
UserName = Session.UserName
MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, &quot; &quot)) & &quot;.nsf&quot;
'Open the mail database in notes
Set Maildb = Session.GETDATABASE(&quot;&quot;, MailDbName)
If Maildb.IsOpen = True Then
'Already open for mail
Else
Maildb.OPENMAIL
End If
'Set up the new mail document
Set MailDoc = Maildb.CREATEDOCUMENT
MailDoc.Form = &quot;Memo&quot;
'MailDoc.sendto = Recipient
MailDoc.sendto = &quot;michael.m.james@rrr.com&quot;
'MailDoc.Subject = Subject
MailDoc.Subject = &quot;Title goes here&quot;
'MailDoc.Body = BodyText
MailDoc.Body = &quot;User Text............&quot;
'MailDoc.SAVEMESSAGEONSEND = SaveIt
MailDoc.SAVEMESSAGEONSEND = False

'Set up the embedded object and attachment and attach it
Attachment = &quot;C:\Excelfile.xls&quot;
If Attachment <> &quot;&quot; Then
Set AttachME = MailDoc.CREATERICHTEXTITEM(&quot;Attachment&quot
Set EmbedObj = AttachME.EMBEDOBJECT(1454, &quot;&quot;, Attachment, &quot;Attachment&quot
MailDoc.CREATERICHTEXTITEM (&quot;Attachment&quot
End If
'Send the document
MailDoc.SEND 0, Recipient
'Clean Up
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj = Nothing
End Sub

 

[mbarnett]

Thanks tbl for the code it works great, but I want to add more users I tried doing the following in the sendto area
&quot;mm@rr.com&quot; & &quot;,&quot; & &quot;jj@rr.com&quot; ---> it send the mail, but it only show jj@rr and does not show the .com section and the other reciepent does not get the mail. any suggestions. Sorry I'm getting back to so late. Many thanks. How do I give a star..

Michael

 

[xlbo]

Michael - to give a star, click on the &quot;MArk this post as a helpful / expert post&quot; at the bottom of the appropriate thread

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[tbl]

I can't remember offhand how I do this (I'm not in the office today so I can't check). I would try &quot;User1@Hotmail.com,User1@Hotmail.com&quot; and see if that works. There are differences between the versions of Lotus Notes. Outlook Express it is not !!!!!!!!

all the best,

Richard

 

[PJStephenson]

tbl,

have a star from me, a great bit of code, thanks

Peter

 

[mbarnett]

Thanks..I will give it a try

 

[PJStephenson]

As a follow-up try the following code for sending to multiple email addresses:

Recipient = Split(Addressee, &quot;,&quot
MailDoc.sendto = Recipient

this works in office 2000 when Addressee is &quot;User1@Hotmail.com,User1@Hotmail.com&quot;, don't use spaces between the commas or the inverted commas and the email addresses.

for sending multiple attachments my modification to the code is:

SplitTitle = Split(Title, &quot;,&quot
Dim SplitAttachment() As Variant
ReDim SplitAttachment(UBound(SplitTitle))
For TitleCount = LBound(SplitTitle) To UBound(SplitTitle)
SplitAttachment(TitleCount) = &quot;Attachment&quot; & TitleCount
Next

For TitleCount = LBound(SplitTitle) To UBound(SplitTitle)
Attachment = SavePathExcel & SplitTitle(TitleCount) & FileType
If Attachment <> &quot;&quot; Then
Set AttachME = MailDoc.CREATERICHTEXTITEM(SplitAttachment(TitleCount))
Set EmbedObj = AttachME.EMBEDOBJECT(1454, &quot;&quot;, Attachment, SplitAttachment(TitleCount))
MailDoc.CREATERICHTEXTITEM (SplitAttachment(TitleCount))
End If
Next

Again this can be modified to suit.

Peter

 

[mbarnett]

My Version of Lotus is 5.0.8 - I tried your suggestion tbl, but it did not work. Any suggestions for sending mail to more than one person

 

[PJStephenson]

Try This:

Addressee = &quot;User1@Hotmail.com,User1@Hotmail.com&quot;
Recipient = Split(Addressee, &quot;,&quot
MailDoc.sendto = Recipient

This works in office 2000 don't use spaces between the commas or the inverted commas and the email addresses if you use an earlier version you need to write your own split loop. Don't Dim Recipient, using split will make it an array and the sendto variable is an array. We use 5.0.4 and it works for us.

Peter

 

[mbarnett]

Hi All,

Thanks for the help. One stupid question. Would I dim Adressee as Variant or String??

 

[drctx]

i was having a problem with e-mailing attachements and this code works great. thanks

 

[PJStephenson]

In all the code shown Adressee has been a variant. When you debug it it should show up as Variant/String. Feel free to dim it as string and see what happens, it's up to you.

 

[fredk]

Thanks for this post tbl!!!!


Fred

 

[fredk]

I am using Access 97 ... I don't have the split function - I was not sure how to set this up on my own? If anyone can point me on this I would like to understand how I would do it -

In the meantime, I made this work by setting up a group in my address book in lotus notes and then referring to the group name in the code.

It worked.

Thanks!!!!

Fred

 

[foundryqa]

fredk,

From one Fred to another, there is a great faq for this here:

faq705-1823

Thanks,

Fred

 

[fredk]

Thanks Fred - I will check it out!!!!

Fred

 

[tbl]

The correct way to send mails to multiple recipients is to use an array.

Dim Recipient(10) as Variant
Recipient(0)="Fred"
Recipient(1) ="John" etc

Richard