Help modifying code (Lotus Notes email)

[Von Pookie]

For reference, this question is a product of my previous one at http://www.mrexcel.com/board2/viewtopic.php?t=59878


There is code in that thread that I am using to automatically populate one sheet with information that is entered in another. Which is working quite well.

However.

The numbers it captures for the sheet, I also have to send in an email (using Lotus Notes) to someone. I don't need an attachment, I just need the text that was just entered in the form via macro (does that make sense?).

I snagged the code from this thread ( http://www.mrexcel.com/board2/viewtopic.php?t=54735&highlight=lotus+notes ), thinking it sounded kind of like what I was wanting to do, and I could maybe figure something out.

But, no. I am completely and utterly confused by it. Nothing new.

I already have the first part of the email text entered, but then in the middle goes the numbers, then more email text at the bottom (I send the exact same email every week, save the numbers).

How can I take out the stuff that makes it an attachment and just copy the text from column B (starting from B5 down--and only the used cells) and just paste it in the middle of the email?

Or can I? No need to worry about saving the sheet, either.

Any thoughts appreciated, as usual--and I'll try to answer any questions as best as I can

 

[NateO]

Lotus doesn't work like it should.

So is it working as intended now?

Note that I'm replacing all @'s with vbcrlf in the message body, this allows for a reduction in string concatenation, which is a fairly slow operation in VB, to be avoided. You could pick a different replacement character if you want to use the @ char as text in your document body.

Thanks for the nice birthday wishes!

 

[Von Pookie]

Ooooh, so THAT'S what that is down there. I'll give it a shot.

 

[NateO]

Sorry Pook, should've explicitly stated this for you, it's right here:

<font face=Courier New>MailDoc.Body = _
    Replace("Good afternoon!@@The following EO's are being sent to you@" _
        & Join(Application.Transpose(Range([b5], [b65536].End(3))), "@") _
            & "@@Thank you and have a great weekend!", "@", vbCrLf)</FONT>

replace(String, char to be replaced, replacement char)

Helpful?

 

[NateO]

Hello again Pook, how about the following:

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

<SPAN style="color:darkblue">Dim</SPAN> Notes <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Object</SPAN>, db <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Object</SPAN>, WorkSpace <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Object</SPAN>
<SPAN style="color:darkblue">Dim</SPAN> UIdoc <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Object</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">Set</SPAN> Notes = CreateObject("Notes.NotesSession")

UserName = Notes.UserName
MailDbName = Left$(UserName, 1) & Right$(UserName, _
    (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"

<SPAN style="color:darkblue">Set</SPAN> db = Notes.GetDataBase(vbNullString, MailDbName)

<SPAN style="color:darkblue">Set</SPAN> WorkSpace = CreateObject("Notes.NotesUIWorkspace")
<SPAN style="color:darkblue">Call</SPAN> WorkSpace.ComposeDocument(, , "Memo")

<SPAN style="color:darkblue">Set</SPAN> UIdoc = WorkSpace.CurrentDocument
<SPAN style="color:darkblue">Call</SPAN> UIdoc.FieldSetText("SendTo", "John Deere") <SPAN style="color:green">'Recipient</SPAN>
<SPAN style="color:darkblue">Call</SPAN> UIdoc.FieldSetText("Subject", "Yer Numbers")

Range([b5], [b65536].End(3)).CopyPicture
<SPAN style="color:darkblue">Call</SPAN> UIdoc.GotoField("Body")
<SPAN style="color:darkblue">Call</SPAN> UIdoc.InsertText(WorksheetFunction.Substitute( _
    "Good afternoon!@@The following EO<SPAN style="color:green">'s are being sent to you@", _
    "@", vbCrLf))</SPAN>
<SPAN style="color:darkblue">Call</SPAN> UIdoc.Paste
<SPAN style="color:darkblue">Call</SPAN> UIdoc.InsertText(Application.Substitute( _
    "@@Thank you and have a great weekend!", "@", vbCrLf))
Application.CutCopyMode = <SPAN style="color:darkblue">False</SPAN>

<SPAN style="color:darkblue">Call</SPAN> UIdoc.Send(False)
UIdoc.<SPAN style="color:darkblue">Close</SPAN>

<SPAN style="color:darkblue">Set</SPAN> UIdoc = Nothing: <SPAN style="color:darkblue">Set</SPAN> WorkSpace = <SPAN style="color:darkblue">Nothing</SPAN>
<SPAN style="color:darkblue">Set</SPAN> db = Nothing: <SPAN style="color:darkblue">Set</SPAN> Notes = <SPAN style="color:darkblue">Nothing</SPAN>

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

?

 

[Von Pookie]

Hiya Nate,

I had time to try the code out today.

It's working when there is only one entry in the range, but a couple of things:

1) Is there any way to do this without it being an object? It's not a life-or-death thing, just a thought. I only ask because it's also picking up the borders when it pastes into the email.

2) I changed the "John Deere" to my name as I had it in the original code, but it won't send the email. It keeps saying "No names found to send mail to." Do I have to use the actual address, or should the nicknames work?

Edit: 3) When I close the email without sending it ("discard"), the code gives a Runtime error 300 on the line Call UIdoc.Send(False)

Thanks

 

[NateO]

Howdy Pook!

Hiya Nate,

I had time to try the code out today.

It's working when there is only one entry in the range, but a couple of things:

1) Is there any way to do this without it being an object? It's not a life-or-death thing, just a thought. I only ask because it's also picking up the borders when it pastes into the email.

Sure, try the following:

<font face=Courier New><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>, 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> MailDoc <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> myStr <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Variant</SPAN>
<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("", 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">Set</SPAN> MailDoc = Maildb.CreateDocument
MailDoc.Form = "Memo"
MailDoc.SendTo = "Jane C Doe" <SPAN style="color:green">'Nickname or full address</SPAN>
<SPAN style="color:green">'MailDoc.CopyTo = Whomever</SPAN>
<SPAN style="color:green">'MailDoc.BlindCopyTo = Whomever</SPAN>
MailDoc.Subject = "Yer Numbers"
<SPAN style="color:darkblue">With</SPAN> Range([b5], [b65536].End(3))
    <SPAN style="color:darkblue">If</SPAN> IsArray(.Value) <SPAN style="color:darkblue">Then</SPAN> <SPAN style="color:darkblue">Let</SPAN> myStr = _
        Join(WorksheetFunction.Transpose(.Value), "@") _
        <SPAN style="color:darkblue">Else</SPAN> <SPAN style="color:darkblue">Let</SPAN> myStr = .Value
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">With</SPAN>
MailDoc.Body = WorksheetFunction.Substitute( _
    "Good afternoon!@@The following EO<SPAN style="color:green">'s are being sent to you@" _
    & myStr & "@@Thank you and have a great weekend!", "@", vbCrLf)</SPAN>
MailDoc.SaveMessageOnSend = <SPAN style="color:darkblue">True</SPAN>
MailDoc.PostedDate = Now
<SPAN style="color:darkblue">Call</SPAN> MailDoc.Send(False)
<SPAN style="color:darkblue">Set</SPAN> Maildb = Nothing:    <SPAN style="color:darkblue">Set</SPAN> MailDoc = 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>

2) I changed the "John Deere" to my name as I had it in the original code, but it won't send the email. It keeps saying "No names found to send mail to." Do I have to use the actual address, or should the nicknames work?

Should work, just tested it out on my nickname.

Edit: 3) When I close the email without sending it ("discard"), the code gives a Runtime error 300 on the line Call UIdoc.Send(False)

I'm afraid you have lost me here. How could you close it without sending it?

Thanks

De nada.

 

[Von Pookie]

Howdy Pook!

1) Is there any way to do this without it being an object? It's not a life-or-death thing, just a thought. I only ask because it's also picking up the borders when it pastes into the email.

Sure...[/quote]

Cool, that seems to have done the trick. The nickname problem seems to be gone now, as well. Of course, I have no clue why it wasn't liking it last week...

I'm afraid you have lost me here. How could you close it without sending it?

Oh, sorry. I worded that badly. When I got the message that it couldn't find any names to send to, it wouldn't send the mail, so it would just be there on the screen as if I was typing a brand new email. So when I would close it and click "discard," it was giving me that weird error. *shrug*

 

[NateO]

Odd, it was working for me on a valid address, I did get a freeze on a bad nickname...

Glad to hear this is sorted, sounds like Miller time.

Edit: I think I have found the problem:

http://www.mrexcel.com/board2/viewtopic.php?t=77828&start=36

 

[Von Pookie]

Yeah, that sounds like the same issue to me.

And just for the record, I'm running this code with Notes 6.5 and it's working

I hate this new version, though. I think we had 5 before, and it was fine. The only problem with this one is that it's slower. I'm used to everything just popping right up after being clicked--save a large attachment. .

 

[NateO]

Howdy Pook,

Yeah, that sounds like the same issue to me.

And just for the record, I'm running this code with Notes 6.5 and it's working

I hate this new version, though. I think we had 5 before, and it was fine. The only problem with this one is that it's slower. I'm used to everything just popping right up after being clicked--save a large attachment. .

That's fantastic!! I use 4.X and hate the software (more than I hate Access! )! Nice to know the 'functionality' becomes more dysfunctional with 'upgrades.' Something to look forward to!