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

 

[bill]

OK, no one likes slow code, even the great NATEO dislikes slow code I imagine.

Is it more a compatibilty issue?
Or better yet, a propriatery issue where code is harded to figure out if props and methods are not exposed through early binding? Meaning, your clients are more dependant on you since they are less able to understand and therefore manipulate your solutions?

 

[NateO]

Not so much the latter, most of the people I work with:

1) Don't know VBA, independent of intellisense
2) Don't want to know VBA, for whatever reason

In a certain sense they're less dependant, as the code works. I have folks working with LN 4.x-6.x. And all sorts of Office, ADO, etc.. versions.

And, I don't want to be mucking around with old projects on a constant basis. So, I'll take the slight hit (in terms of absolute performance) and ship applications that work.

The code looks virtually the same as Early bound code, you just end up using coerced constants. The objects, methods and properties don't change.

 

[herpasymplex10]

I've been working off this code.
Could use some assistance.

I have created a userform.
I populate the fields
and have various command buttons on it
to perform certain functions.

One of the command buttons creates a seperate workbook and saves it.

For example My userform has:

Str Number - ()
PO Number - ()

the parentheses indicate where i enter the data.

(there are other fields, but these are the only two needed for what i need help with)

So. I've used my userform and created a seperate workbook. saved it and closed it.

What I'd like to do is have another command button that will
use the code from this thread (which works perfectly for emailing an attachment through Lotus Notes)
to email the workbook i created

In the above code, the file name is written in the code.
but in my example, the file name will change with each spreadsheet i need to email.

the path is:
G:\AFP\CRA\NewStore\

now my userform will have the rest of the information for the file path

so for example,
using my userform,
Str Number - 12
PO Number - 123456

so the path would be:
G:\AFP\CRA\NewStore\12\123456.xls

is this making sense?
and is it possible?

 

[NateO]

"G:\AFP\CRA\NewStore\" & TextBox1.Text & "\" & TextBox2.Text & ".xls"

*Untested*

 

[herpasymplex10]

"G:\AFP\CRA\NewStore\" & TextBox1.Text & "\" & TextBox2.Text & ".xls"

*Untested*

So far
So good.

Awesome.
Thanks so much!!!

 

[herpasymplex10]

Ok. I feel silly asking this one:

MailDoc.Subject = "PO Reset Request" & _
    ListBox1.Text & TextBox1.Text & _
    "PO Number" & TextBox2.Text
    MailDoc.Body = _
        "Please Reset this Purchase Order" & _
        "Thanks," & _
        "Christian"

In this code, the subject line should ideally look like this
PO Reset Request Store 1234 Purchaser Order 123456

Mine does that except there are no spaces


In the subject of the email, how would you make
Please Reset this Purchase order
Thanks,
Christian

all on seperate lines?

 

[herpasymplex10]

In this code, the attachement is seperated in Lotus notes by a line
which appears to be a page break.

is there any adjustment that can be done to the code
to eliminate that line/page break?

Thanks

 

[NateO]

Hello,

I'm not sure about your latest question...

But, with your 2nd most recent question, don't mistake a line-continuation character in VB[A] for string-formatting.

To add a space or a new line, you need to add those characters to your string, e.g.,

MailDoc.Subject = "PO Reset Request " & _
    ListBox1.Text & " " & TextBox1.Text & _
    " PO Number " & TextBox2.Text
MailDoc.Body = _
    "Please Reset this Purchase Order" & vbNewLine & _
    "Thanks," & vbNewLine & _
    "Christian"

*untested - I no longer have Lotus Notes*

 

[herpasymplex10]

Hello,

I'm not sure about your latest question...

But, with your 2nd most recent question, don't mistake a line-continuation character in VB[A] for string-formatting.

To add a space or a new line, you need to add those characters to your string, e.g.,

MailDoc.Subject = "PO Reset Request " & _
    ListBox1.Text & " " & TextBox1.Text & _
    " PO Number " & TextBox2.Text
MailDoc.Body = _
    "Please Reset this Purchase Order" & vbNewLine & _
    "Thanks," & vbNewLine & _
    "Christian"

*untested - I no longer have Lotus Notes*

Thanks for the help.

One more question.
Is there a way to have a message box pop up in Excel
to indicate the message has been sent?

 

[NateO]

You're welcome.

Yes, add the following line of code after you send the email:

msgbox "Hello World!!!"