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]

Still have my e-mail address Pook?

I'll take a look, post the result to the board. :wink:

 

[Von Pookie]

It appears that I do, actually (at least in the contacts of the site I used to check my home email while at work )

I'll ship 'em over

 

[Von Pookie]

Hooookay...slight change in plans.

The site that I use just went all wonky and I've been trying to deal with it since I posted (keeps logging me out for no apparent reason, etc., and now I can't get to it at all--I hate the "page cannot be displayed" error oh so very much).

So I'll send it from my Hotmail account. Just so you know

 

[NateO]

Howdy Pook, does the following look right?

<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">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> Maildb.IsOpen <SPAN style="color:darkblue">Then</SPAN>
    Else: Maildb.OpenMail
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">If</SPAN>
<SPAN style="color:darkblue">Set</SPAN> MailDoc = Maildb.CreateDocument
MailDoc.Form = "Memo"
MailDoc.SendTo = "John Deere" <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"
MailDoc.Body = _
    Replace("Good afternoon!@@The following EO's are being sent to you@" _
        & [b65536].End(3) & "@@Thank you and have a great weekend!", "@", vbCrLf)
MailDoc.SaveMessageOnSend = <SPAN style="color:darkblue">True</SPAN>
MailDoc.PostedDate = Now
<SPAN style="color:darkblue">On</SPAN> <SPAN style="color:darkblue">Error</SPAN> <SPAN style="color:darkblue">Resume</SPAN> <SPAN style="color:darkblue">Next</SPAN>
<SPAN style="color:darkblue">Call</SPAN> MailDoc.Send(False)
Session.Quit
<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>

Hope so.

Edit: This is going to require tinkering if you want to grab b5:b & lastrow on every transmission, it's only set up to grab the last cell in column B right now...

 

[Von Pookie]

Well, it's sending the mail, that's a start, at least!

Unfortunately, I'm having a bit of trouble trying to get it to copy more than that one cell. I just can't figure it out (spent the last half-hour or so trying).

I tried changing it from b65536 to B5:B30, and even just B5, but that only gave me the text that's in B4.

Even tried changing it completely to Range("B5").End(xlDown) but it still gives me only the last populated cell. Of course, I don't know what the End(3) bit you've got in there does, either.

I guess I just don't know enough VB to actually know what I'm doing on this one.

One other thing, while I'm at it: I'm assuming the '@' characters is obviously a carriage return. The email doesn't have the signature bit that I automatically have on the emails, so I just added it to the message text section. However, it doesn't like the '@' symbol in email addresses, either. I even tried using Chr(64), but it's still treating it as a return.

Any ideas?

Thanks again for putting up with me, Nate

 

[NateO]

Hello again Pook!

Yep, figured as much after rereading your initial post to the thread. 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">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> Maildb.IsOpen = <SPAN style="color:darkblue">True</SPAN> <SPAN style="color:darkblue">Then</SPAN>
    Else: Maildb.OpenMail
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">If</SPAN>
<SPAN style="color:darkblue">Set</SPAN> MailDoc = Maildb.CreateDocument
MailDoc.Form = "Memo"
MailDoc.SendTo = "John.Deere@hotmail.com" <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"
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)
MailDoc.SaveMessageOnSend = <SPAN style="color:darkblue">True</SPAN>
MailDoc.PostedDate = Now
<SPAN style="color:darkblue">On</SPAN> <SPAN style="color:darkblue">Error</SPAN> <SPAN style="color:darkblue">GoTo</SPAN> Audi
<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">Exit</SPAN> <SPAN style="color:darkblue">Sub</SPAN>
Audi:
<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>

I didn't see @ as being a problem in the address field during my tests... Hope this helps.

 

[Von Pookie]

Oh, sorry, Nate. I meant I was trying to get an '@' symbol in the email text. Not the "to" line

 

[Von Pookie]

Ohhh, nutbunnies

This was working earlier, darnit. Now when I try to run the code to send the email to myself, I'm getting an error from Notes.

Notes has been asked to execute the following action which does not fit within your security profile:

Action: NotesDatabase.Open
Signed by: -No Signature-
Not allowed: Ability to read other databases

If I click 'Execute Once,' it changes to

Action: NotesDocument.Send
Not allowed: Ability to send mail

Click 'Execute once' again, and it sends the email.

For the record, it did give me all of the items in column B.

But why would it work fine before and not now? I even tried it again with the original code and it's having the same results now.

I'm confuzzled.

 

[Von Pookie]

Straaaange...now it's working and sending the emails again.

Oh well. If it works, it works

Thanks again. Oh, and

Nate!