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]

Hello,

1) The only way I know how to do this LN is to copy the Range as a picture and add the picture to the body. That code is in this thread, not sure how you missed it, but here it is, again:

http://www.mrexcel.com/board2/viewtopic.php?p=371210#371210

Note the following:

Range([b5], [b65536].End(3)).CopyPicture
Call UIdoc.GotoField("Body")
Call UIdoc.Paste

And:

Hiya Nate,

Is there any way to do this without it being an object?
{snipped}

It's acutally a graphic in the body, and nope, don't know of another way.

2) Nope, not a single cell. Put the address in multiple cells, then pass the Range's Value. Both Lotus Notes and Microsoft Outlook want arrays when you pass multiple recipients to the Senders input, not Strings.

And, having said all of this, my Lotus Notes days are over (hooray!!), so I can no longer test Lotus Notes interfaces with Lotus Notes.

But, I'll leave you with one last thought; my linked interface is via OLE and not Com. Good luck.

 

[noppojp]

THANK YOU!!

i'm not sure how i missed it either. This does wonders with pasting the range in the body of the message. Thank you very much.

> Put the address in multiple cells, then pass the Range's Value

Don't know how to do pass the range's value. Point me in the right direction for any code on this site that can get me going please, and i'll try that. i've found some code somewhere in this thread (me thinks), but not sure it's the right stuff (or way??) to do it, to wit:

MailDoc.Form = "Memo"
' Select range of e-mail addresses
Recipient = Sheets("E-Mail Addresses").Range("A2").Value
' Or send to a signle address
' Recipient = "yourname@isp.com"
MailDoc.SendTo = Recipient

But, all i see is one cell, A2, and not a range. Should it be A2:A10 or something like that?, and will this work paired with the code i have so far!!?

Many thanks again!
ray:

 

[NateO]

Passing a multi-cell range should work.

Otherwise, look up the Split Function in the Help File, it should work, as well.

 

[RiBaldi]

Sorry if my request could be not relevant. I am using VBA in a MS Access application and I need to do the same: adding text and an image in a Lotus message, but the image is not an Excel range of cells, but an... image, located on my pc. Or it could be a value like:
Forms!MyForm!MyImageField.Picture = "D:\MyPath\MyPic.jpg"
My problem is in the following:
Sub test()
Dim MyPic As Object
Application.ScreenUpdating = False
Set MyPic = ActiveSheet.Pictures.Insert( _
****"C:\Temp\My Picture.jpg")
Call SendMail(MyPic)
MyPic.Delete
Set MyPic = Nothing
Application.ScreenUpdating = True
End Sub
That is, I don't know how to turn it into something like:
Dim MyPicAs Object
Set MyPic= Forms!mElencoArt!immagine16.Picture
Call SendMail(MyPic)
Set MyPic= Nothing
My SendMail routine works well, apart from this, producing Lotus messages with formatted text in html style, but it misses the image.
Thanks.
Riccardo, Italy

 

[RiBaldi]

At the address
http://www-10.lotus.com/ldd/nd6forum.nsf/DateAllThreadedweb/dcbf91b97004f0af8525773e002867a9?OpenDocument
I found a solution to insert an image in the mail body. Here is the part of code I use:
Call stream.Open("<MY PATH IMAGE>")
Set body = MailDoc.CreateMIMEEntity '("memo")
Set richTextHeader = body.CreateHeader("Content-Type") '
Call richTextHeader.SetHeaderVal("multipart/mixed")
Set mimeImage = body.CreateChildEntity()
strImageType = "image/jpeg" 'Other formats are "image/gif" "image/bmp"
Call mimeImage.SetContentFromBytes(stream, strImageType, ENC_IDENTITY_BINARY)
Call stream.Close

Hope it is useful

 

[brenda nas]

hello Nateo,

I am new bie to excel /VBA.
I need your guidance on this..
may be simple for you...
request for your pursual...

refer
http://www.mrexcel.com/forum/showthread.php?t=607274&highlight=lotus

urs,
Brenda