Sending Lotus NOTS email - numerous times

K

kathleen

Active Member
Joined
Dec 16, 2002
Messages
295
I am trying to send 7 different emails in this sub, the sendnotes sub is not recognizing my GOLOOP value

Could somebody help me out please :oops:

As you can see I stole this code from this site. You all are terrific!

Sub SendFiles()
Sheets("Menu").Select
Range("C23").Select
Filename = ActiveCell.Value

GoLoop = "1"
SendNotesMail

GoLoop = "2"
SendNotesMail

GoLoop = "3"
SendNotesMail

GoLoop = "4"
SendNotesMail

GoLoop = "5"
SendNotesMail

GoLoop = "6"
SendNotesMail

GoLoop = "7"
SendNotesMail


End Sub

Sub SendNotesMail()
Stop


Dim Maildb As Object, MailDoc As Object, AttachMe As Object, Session As Object
Dim UserName As String, MaildDbName As String
Dim EmbedObj1 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(vbNullString, MailDbName)

If Not Maildb.IsOpen Then Maildb.OpenMail

Set MailDoc = Maildb.CreateDocument
MailDoc.Form = "Memo"

Select Case (GoLoop)
Case "1"
Mail.Doc.SendTo = "email@email.com"
MailDoc.Subject = "APR File"
MailDoc.Body = "Attached are the price changes for this reporting period. The baseline and standards have been reset. The direct supply APR as well as level 1 parts are included in this file. Please use this information and your monthly purchases to calculate your APR achievement. Please provide the APR$ and % of purchases by the 10th workday. "

Set AttachMe = MailDoc.CreateRichTextItem("Attachment 1")
Set EmbedObj1 = AttachMe.Embedobjecyt(1454, vbNullString, "C:\PCP\APR\" & "TMMAL " & Filename & ".xls", "Attachment")

Case "2"
Mail.Doc.SendTo = "kfesce@tmmna.com"
MailDoc.Subject = "APR File"
MailDoc.Body = "Attached are the price changes for this reporting period. The baseline and standards have been reset. The direct supply APR as well as level 1 parts are included in this file. Please use this information and your monthly purchases to calculate your APR achievement. Please provide the APR$ and % of purchases by the 10th workday. "

Set AttachMe = MailDoc.CreateRichTextItem("Attachment 1")
Set EmbedObj1 = AttachMe.Embedobject(1454, vbNullString, "C:\PCP\APR\" & "TMMBC " & Filename & ".xls", "Attachment")

Case "3"
Mail.Doc.SendTo = "kfesce@tmmna.com"
MailDoc.Subject = "APR File"
MailDoc.Body = "Attached are the price changes for this reporting period. The baseline and standards have been reset. The direct supply APR as well as level 1 parts are included in this file. Please use this information and your monthly purchases to calculate your APR achievement. Please provide the APR$ and % of purchases by the 10th workday. "

Set AttachMe = MailDoc.CreateRichTextItem
Set EmbedObj1 = AttachMe.Embedobject(1454, vbNullString, "C:\PCP\APR\" & "TMMC " & Filename & ".xls", "Attachment")

Case "4"
Mail.Doc.SendTo = "kfesce@tmmna.com"
MailDoc.Subject = "APR File"
MailDoc.Body = "Attached are the price changes for this reporting period. The baseline and standards have been reset. The direct supply APR as well as level 1 parts are included in this file. Please use this information and your monthly purchases to calculate your APR achievement. Please provide the APR$ and % of purchases by the 10th workday. "

Set AttachMe = MailDoc.CreateRichTextItem("Attachment 1")
Set EmbedObj1 = AttachMe.Embedobject(1454, vbNullString, "C:\PCP\APR\" & "TMMCA " & Filename & ".xls", "Attachment")

Case "5"
Mail.Doc.SendTo = "kfesce@tmmna.com"
MailDoc.Subject = "APR File"
MailDoc.Body = "Attached are the price changes for this reporting period. The baseline and standards have been reset. The direct supply APR as well as level 1 parts are included in this file. Please use this information and your monthly purchases to calculate your APR achievement. Please provide the APR$ and % of purchases by the 10th workday. "

Set AttachMe = MailDoc.CreateRichTextItem("Attachment 1")
Set EmbedObj1 = AttachMe.Embedobject(1454, vbNullString, "C:\PCP\APR\" & "TMMI " & Filename & ".xls", "Attachment")

Case "6"
Mail.Doc.SendTo = "kfesce@tmmna.com"
MailDoc.Subject = "APR File"
MailDoc.Body = "Attached are the price changes for this reporting period. The baseline and standards have been reset. The direct supply APR as well as level 1 parts are included in this file. Please use this information and your monthly purchases to calculate your APR achievement. Please provide the APR$ and % of purchases by the 10th workday. "

Set AttachMe = MailDoc.CreateRichTextItem
Set EmbedObj1 = AttachMe.Embedobject(1454, vbNullString, "C:\PCP\APR\" & "TMMK " & Filename & ".xls", "Attachment")

Case "7"
Mail.Doc.SendTo = "kfesce@tmmna.com"
MailDoc.Subject = "APR File"
MailDoc.Body = "Attached are the price changes for this reporting period. The baseline and standards have been reset. The direct supply APR as well as level 1 parts are included in this file. Please use this information and your monthly purchases to calculate your APR achievement. Please provide the APR$ and % of purchases by the 10th workday. "

Set AttachMe = MailDoc.CreateRichTextItem
Set EmbedObj1 = AttachMe.Embedobjecyt(1454, vbNullString, "C:\PCP\APR\" & "TMMWV " & Filename & ".xls", "Attachment")

End Select

MailDoc.SaveMessageOnSend = True
MailDoc.PostedDate = Now

On Error GoTo ErrorCheck
Call MailDoc.Send(False)

Set EmbedObj = Nothing: Set AttachMe = Nothing:
Set MailDoc = Nothing
Set Maildb = Nothing: Set Session = Nothing

Exit Sub

ErrorCheck:
Set EmbedOfj1 = Nothing: Set AttachMe = Nothing:
Set MailDoc = Nothing

Set Maildb = Nothing: Set Session = Nothing

End Sub

Edited by NPO: email address substituted
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
Hello Kathleen,

First, you're not marshalling your integer, which should look something like:

<font face=Courier New><SPAN style="color:darkblue">Sub</SPAN> tester()
<SPAN style="color:darkblue">Call</SPAN> tester2(3)
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN>

<SPAN style="color:darkblue">Private</SPAN> <SPAN style="color:darkblue">Sub</SPAN> tester2(<SPAN style="color:darkblue">ByVal</SPAN> myInt <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Long</SPAN>)
MsgBox myInt
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN>
</FONT>

2nd, loop is a specific process, I wouldn't use the word as you're not looping.

But, all of your cases are the same, what are you changing? I think, to streamline the code, if you're just changing one variable (like send to), I'd stack an array and pass your Integer to grab the element you'd like, it will dramatically streamline your procedure.

Edit: I see it now, file name, give me a second. :)
 
Upvote 0
I must be living "Right", I was praying that you Nate0 would respond as its your code I've stolen! If I could I would buy you a (y)

These files will all be sent out to seperate individuals. Right now I am sending to myself only to test. Thats why name is the same in all cases.

I'm afraid the array factor is way over my head. I originally started with a numeric value and when that didn't work I switched to the name GOLOOP.


Thanks I really appreciate you! (y)
 
Upvote 0
He he, I thought there were a few things I had noticed before. :)

How about a single procedure. Here's the array, flipping the file name as mentioned (it looked variable). You could also have a 2nd array within the same loop which flip the email addy's in a similar manner.

<font face=Courier New><SPAN style="color:darkblue">Private</SPAN> <SPAN style="color:darkblue">Sub</SPAN> SendNotesMail()

<SPAN style="color:darkblue">Dim</SPAN> Maildb <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Object</SPAN>, MailDoc <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Object</SPAN>, AttachMe <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Object</SPAN>, Session <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Object</SPAN>
<SPAN style="color:darkblue">Dim</SPAN> UserName <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">String</SPAN>, MailDbName <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">String</SPAN>
<SPAN style="color:darkblue">Dim</SPAN> EmbedObj1 <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Object</SPAN>
<SPAN style="color:darkblue">Dim</SPAN> myArr <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Variant</SPAN>, i <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Long</SPAN>
<SPAN style="color:darkblue">Dim</SPAN> myFil <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">String</SPAN>

<SPAN style="color:darkblue">Let</SPAN> myFil = Sheets("Menu").Range("C23").Value

<SPAN style="color:darkblue">Set</SPAN> Session = CreateObject("Notes.NotesSession")

UserName = Session.UserName
MailDbName = Left$(UserName, 1) & Right$(UserName, _
    (Len(UserName) - InStr(1, UserName, " "))) & ":.nsf"
    
<SPAN style="color:darkblue">Set</SPAN> Maildb = Session.GetDataBase(vbNullString, MailDbName)

<SPAN style="color:darkblue">If</SPAN> <SPAN style="color:darkblue">Not</SPAN> Maildb.IsOpen <SPAN style="color:darkblue">Then</SPAN> Maildb.OpenMail

<SPAN style="color:darkblue">Let</SPAN> myArr = Array("TMMAL ", "TMMBC ", "TMMC ", "TMMCA ", "TMMI ", "TMMK ", "TMMWV ")

<SPAN style="color:darkblue">For</SPAN> i = <SPAN style="color:darkblue">LBound</SPAN>(myArr) <SPAN style="color:darkblue">To</SPAN> <SPAN style="color:darkblue">UBound</SPAN>(myArr)
    <SPAN style="color:darkblue">Set</SPAN> MailDoc = Maildb.CreateDocument
    MailDoc.Form = "Memo"
    MailDoc.SendTo = "email@email.com"
    MailDoc.Subject = "APR File"
    MailDoc.Body = _
        "Attached are the price changes for this reporting period. " & _
        "The baseline and standards have been reset. " & _
        "The direct supply APR as well as level 1 parts are included in this file. " & _
        "Please use this information and your monthly purchases to calculate your " & _
        "APR achievement.  Please provide the APR$ and % of purchases by the 10th workday."
    
    <SPAN style="color:darkblue">Set</SPAN> AttachMe = MailDoc.CreateRichTextItem("Attachment 1")
    <SPAN style="color:darkblue">Set</SPAN> EmbedObj1 = AttachMe.Embedobjecyt(1454, vbNullString, "C:\PCP\APR\" & _
        myArr(i) & myFil & ".xls", "Attachment")

    MailDoc.SaveMessageOnSend = <SPAN style="color:darkblue">True</SPAN>
    MailDoc.PostedDate = Now
    <SPAN style="color:darkblue">Call</SPAN> MailDoc.Send(False)

<SPAN style="color:darkblue">Next</SPAN>
    
<SPAN style="color:darkblue">Set</SPAN> EmbedObj1 = Nothing:  <SPAN style="color:darkblue">Set</SPAN> AttachMe = <SPAN style="color:darkblue">Nothing</SPAN>
<SPAN style="color:darkblue">Set</SPAN> MailDoc = Nothing: <SPAN style="color:darkblue">Set</SPAN> Maildb = Nothing: <SPAN style="color:darkblue">Set</SPAN> Session = <SPAN style="color:darkblue">Nothing</SPAN>

<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>

This is untested.

Also, why can't you buy me a beer, too young? :LOL: (y)

Edit: Wow, a few mistakes on my part, I've updated this post.
 
Upvote 0
Thanks, I will try: Let me attempt to explain a few items:

Each month when the files are generated the month and year is appended to the file name: Hence this is why I am performing the following:

"C:\PCP\APR\" & "TMMBC " & Filename & ".xls"

Filename this month = TMMBC October 2004.xls

When populating the array would I just add these?

I am currently sending to myself, I will be sending to numerous users not email@email.com

Would I just load the array with their email addy's?

Thanks so much for your help!

Edited by NPO: changed e-mail address
 
Upvote 0
Just saw where you are adding Month & Year sorry about that. Am trying to include in code right now.
 
Upvote 0
Well, good question, I'm not quite clear yet as to how to answer.

Also, I'm going to pull the addresses so your account doesn't get harvested by 3rd parties.

Back to your quandary, do you have seven different files going to seven people. E.g.,

TMMAL - Recipient 1
TMMBC - Recipient 2

Or do they all get them? Can the attachments be combined if they all get all of the files?
 
Upvote 0
7 different files going to 7 different people.

have included in code and am receiving the following error:

Run Time Error 7000

Notes Error Database already contains a document with this ID (UNID)
 
Upvote 0
Hello again Kathleen, you'll want something like the following:

<font face=Courier New><SPAN style="color:darkblue">Private</SPAN> <SPAN style="color:darkblue">Sub</SPAN> SendNotesMail()

<SPAN style="color:darkblue">Dim</SPAN> Maildb <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Object</SPAN>, MailDoc <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Object</SPAN>, AttachMe <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Object</SPAN>, Session <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Object</SPAN>
<SPAN style="color:darkblue">Dim</SPAN> UserName <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">String</SPAN>, MailDbName <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">String</SPAN>
<SPAN style="color:darkblue">Dim</SPAN> EmbedObj1 <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Object</SPAN>
<SPAN style="color:darkblue">Dim</SPAN> flNmArr <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Variant</SPAN>, emailArr <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Variant</SPAN>, i <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Long</SPAN>
<SPAN style="color:darkblue">Dim</SPAN> myFil <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">String</SPAN>

<SPAN style="color:darkblue">Let</SPAN> myFil = Sheets("Menu").Range("C23").Value

<SPAN style="color:darkblue">Set</SPAN> Session = CreateObject("Notes.NotesSession")

UserName = Session.UserName
MailDbName = Left$(UserName, 1) & Right$(UserName, _
    (Len(UserName) - InStr(1, UserName, " "))) & ":.nsf"
    
<SPAN style="color:darkblue">Set</SPAN> Maildb = Session.GetDataBase(vbNullString, MailDbName)

<SPAN style="color:darkblue">If</SPAN> <SPAN style="color:darkblue">Not</SPAN> Maildb.IsOpen <SPAN style="color:darkblue">Then</SPAN> Maildb.OpenMail

<SPAN style="color:darkblue">Let</SPAN> flNmArr = Array("TMMAL ", "TMMBC ", "TMMC ", "TMMCA ", "TMMI ", "TMMK ", "TMMWV ")
<SPAN style="color:darkblue">Let</SPAN> emailArr = Array("Email1@y.com", "Email2@y.com", "Email3@y.com", "Email4@y.com", _
    "Email5@y.com", "Email6@y.com", "Email7@y.com")

<SPAN style="color:darkblue">For</SPAN> i = <SPAN style="color:darkblue">LBound</SPAN>(myArr) <SPAN style="color:darkblue">To</SPAN> <SPAN style="color:darkblue">UBound</SPAN>(myArr)
    <SPAN style="color:darkblue">Set</SPAN> MailDoc = Maildb.CreateDocument
    MailDoc.Form = "Memo"
    MailDoc.SendTo = emailArr(i)
    MailDoc.Subject = "APR File"
    MailDoc.Body = _
        "Attached are the price changes for this reporting period. " & _
        "The baseline and standards have been reset. " & _
        "The direct supply APR as well as level 1 parts are included in this file. " & _
        "Please use this information and your monthly purchases to calculate your " & _
        "APR achievement.  Please provide the APR$ and % of purchases by the 10th workday."
    
    <SPAN style="color:darkblue">Set</SPAN> AttachMe = MailDoc.CreateRichTextItem("Attachment 1")
    <SPAN style="color:darkblue">Set</SPAN> EmbedObj1 = AttachMe.EmbedObject(1454, vbNullString, "C:\PCP\APR\" & _
        myArr(i) & myFil & ".xls", "Attachment")

    MailDoc.SaveMessageOnSend = <SPAN style="color:darkblue">True</SPAN>
    MailDoc.PostedDate = Now
    <SPAN style="color:darkblue">Call</SPAN> MailDoc.Send(False)

<SPAN style="color:darkblue">Next</SPAN>
    
<SPAN style="color:darkblue">Set</SPAN> EmbedObj1 = Nothing:  <SPAN style="color:darkblue">Set</SPAN> AttachMe = <SPAN style="color:darkblue">Nothing</SPAN>
<SPAN style="color:darkblue">Set</SPAN> MailDoc = Nothing: <SPAN style="color:darkblue">Set</SPAN> Maildb = Nothing: <SPAN style="color:darkblue">Set</SPAN> Session = <SPAN style="color:darkblue">Nothing</SPAN>

<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>

Just be sure that you stack flNmArr and emailArr in the same respective order.

I suspect your error is from your trying to send yourself the same e-mail in such a short amount of time, Lotus sees this as odd behaviour and kills. You might need to either:

a) Find 7 friends to test this on
b) Take a leap of faith

:LOL:


Edit: Typo in code - fixed.
 
Upvote 0
:biggrin: Leap of faith ey.... I think I will try sending to 7 different friends.

Thanks again. You are the greatest and I really cannot begin to tell you how much I appreciate you!

(y) I will advise tommorrow of progress as I don't have seven different friends here right now I can ask to review.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
VBA to email cc using addresses in cells
Replies
7
Views
5K
Trevor G
Trevor G
Jak68
  • Question
Sending email through Outlook depending on date in cell
Replies
1
Views
362
mrbeanyuk
M
D
  • Question
[VBA] Create mail, attach files in Lotus Notes
2
Replies
10
Views
11K
AbdelRahman91
A
A
  • Question
Help with notes to outlook
Replies
1
Views
375
ansafar
A
H
  • Question
Lotus Notes - Embed image in place of body and attach file
Replies
0
Views
3K
HaydnMilton
H

Forum statistics

Threads
1,215,519
Messages
6,125,299
Members
449,218
Latest member
Excel Master

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top