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

 

[Von Pookie]

Hey Natey

I've been working on this for the better part of an hour now and just can't get things to mesh. I've got another email that I'm trying to automate, and this time the code that pastes it into the email body as an object would be just fine.

Only problem is, it never puts anything in the "To" line. It's like it just skips over it completely, or reads it as a blank string or something. It fills in the subject and body just fine, though.

The last incarnation of the code here with the array works just fine for its purpose, so I was trying some way to combine the 2 to get the recipient and paste bits to both work, but it's just not flying.

Any clue as to how I can get it to paste the object in the email AND fill in the recipient line?

Edit: D'OH! Nebbermind. I just found this:
http://www.mrexcel.com/board2/viewtopic.php?p=444956#444956
Works fine now.

 

[NateO]

Hey Kristy Misty Poo-poo, :wink:

Glad to hear you have this sorted. I was having deja vu all over again!

<center>

</center>

 

[Von Pookie]

 

[Von Pookie]

Ok, I spoke too soon.

Do you know the fields for Cc and Bcc? I tried adding a couple of lines like the one for Subject and To, but after that it kept giving me an error 3000 on the line
Call WorkSpace.ComposeDocument(, , "Memo").

Once I commented out my attempts and left just the To and Subject fields, it ran without a hitch.

 

[NateO]

Hello again KMPP,

Call UIdoc.FieldSetText("EnterSendTo", "John Deere")

Call UIdoc.FieldSetText ("EnterCopyTo", “Jane Deere”)

Call UIdoc.FieldSetText ("EnterBlindCopyTo", “James Deere”)

:wink:

 

[Von Pookie]

Now darnit, I tried that. At least I thought I did. Hmm.

Of course, for some reason it's not even getting to that portion, since it keeps erroring out on the ComposeDocument line. I've got Notes open, even. I can see it on the taskbar switch to "New memo" so it opens the stupid thing, it just stops there. Yet if I step through the code, it works fine.

Edit: And yet when I just tried it again, it went through fine.

Stupid thing.

 

[NateO]

Notes gets screwed up if you error out on an interface. Even if you turn around do it proper-like, it doesn't work, well, at least as well as Notes can work... Try killing Notes, restarting it, then reinterface.

If that doesn't work, format your hard drive and install Outlook!

 

[Von Pookie]

Killing Notes. That sounds like a loverly option some days.

I've got Outlook on here, but we don't use it at work. For some weird reason

 

[noppojp]

Wow!!

It's lightening fast. This code is magnificent, and exactly what i need. It'd save tons of time. One simply checks the sent box for what went out. No messages or any bothersome stuff like that. Super.

i have 2 questions about it:

1) how can it be modified to copy a certain range & paste it into the body of the email, the range is: B8 to M46, and it's a graphic of who's in good and who's in bad status time reporting wise.

AND

2) rather than hard coding the recipients, point to a certain cell (cell B69 actually) that has all email addresses which is created from a concantenate formula (based on whether or not action is required).

I guess the next step would be to have it automated based on the date (as long as the file is open of course).

If you can point me in the right direction, that'd be great.

Highest regards from Tokyo

 

[noppojp]

Correction:
* it's NOT a graphic, but a colorful representation - no graphics at all, just colors

m(_ _)m