Sending Lotus NOTS email - numerous times

[kathleen]

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

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

 

[kathleen]

Type mismatch error on line

For i = LBound(myArr) To UBound(myArr)

Thanks again

 

[NateO]

Sorry, I changed the array names. Change:

myArr

to

flNmArr

 

[kathleen]

Got it! It works. Thank you, Thank You. PS can't buy the beer because I am in Kentucky! Would if I could and would make it Kentucky Bourbon instead!

 

[NateO]

Mmmm, Bourbon.

You are welcome.

 

[bill]

How is myArr defined?

 

[bill]

I assumed as much

 

[NateO]

I assumed as much

Assumed what again? Air code goof on my part. It was originally defined as a Variant, and then it sort of went away, but I missed it on the loop counter. :wink:

 

[bill]

Assumed it to be the 'looper'

I stumbled on this thead quite by accident however, it's pretty frigg'n neat as I too have to deal with LOTUS NOTES.

Tried to attach an excel file but the 'attach' failed
I've simplified it too!

    Set EmbedObj1 = AttachMe.Embedobjecyt(1454, vbNullString, "c:\Bill\Test.xls", "Attachment")

OOOOOH!!!!!
AttachMe.Embedobjecyt

had a typo!!!!

Kill the 'y'!!!

 

[bill]

Nate,
Why did you choose LATE over EARLY binding?

 

[NateO]

Because I like slow code.

I tend to use late binds on my distributed interfaces, in spite of Microsoft telling me not to do this.