Excel sending email thru. Lotus Notes Client

[Shing]

Hi,

Is it possible to send a email message in Lotus Notes by Excel macro.

Thanks.
Shing (wsw...@hkstar.com)

[Bill Manville]

In article <eIw2l2Ul$GA.194@cppssbbsa05>, Shing wrote:
> Is it possible to send a email message in Lotus Notes by Excel macro.
>

If Notes provides a MAPI interface, yes.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

[Mark_F...@aep.com]

In article <eIw2l2Ul$GA.194@cppssbbsa05>,
"Shing" <wsw...@hkstar.com> wrote:
> Hi,

>
> Is it possible to send a email message in Lotus Notes by Excel macro.
>

> Thanks.
> Shing (wsw...@hkstar.com)
>

I wrote the following VBA code to get Excel to drive Lotus Notes,
creating an email message, populating it with formatted text, attaching
a file to the message, and sending it. Of course, the code should work
within Excel or Access just as well.

As it was rather difficult to figure out, I thought I'd share it to
avoid the potential reinvention of this particular wheel in the future.

The one thing I couldn't figure out was how to simulate a PasteSpecial
to get the spreadsheet to appear as a spreadsheet within the email
message (true OLE embedding), rather than being attached as an icon.
The icon is also just plain grey instead of being the proper icon for
the file type, and apparently there is no workaround for that.

- Mark

Sub SendViaNotes(theName As String)

Dim mySession As Object
Dim myDb As Object
Dim myDoc As Object
Dim myItem As Object
Dim myStyle As Object
EMBED_OBJECT = 1454

Set mySession = CreateObject("Notes.NotesSession")
Set myDb = mySession.getdatabase("DSML10R", "mail/mfreeman.nsf")
myDb.openmail
Set myDoc = myDb.createdocument
Set myItem = myDoc.AppendItemValue("Subject", "Your subject goes here")
Set myItem = myDoc.CreateRichTextItem("Body")
Set myStyle = mySession.CreateRichTextStyle
With myItem
myStyle.Bold = True
myStyle.FontSize = 12
Call myItem.AppendStyle(myStyle)
.Appendtext theName & ":"
.AddNewLine 2
myStyle.Bold = False
myStyle.FontSize = 10
Call myItem.AppendStyle(myStyle)
.Appendtext "Some text."
.AddNewLine 2
myStyle.Italic = True
Call myItem.AppendStyle(myStyle)
.Appendtext "Some more text. "
.Appendtext "Some text that appears on the same line as the
previous text."
myStyle.Italic = False
Call myItem.AppendStyle(myStyle)
.AddNewLine 2
.Appendtext "Even more text."
.AddNewLine 2
.Appendtext "Sincerely,"
.AddNewLine 1
.Appendtext "Mark Freeman"
.AddNewLine 1
.Appendtext "Y2K Assistance Team"
.AddNewLine 1
End With
'Attach the file. It says to embed it, but it only attaches it.
Set myItem = myDoc.CreateRichTextItem("Any text here works")
Call myItem.EmbedObject(EMBED_OBJECT, "Microsoft Excel
Worksheet", "c:\temp\" &
theName & ".xls", "Any text here works")
Call myDoc.send(0, theName)

Set myStyle = Nothing
Set myItem = Nothing
Set myDoc = Nothing
Set myDb = Nothing
Set mySession = Nothing
End Sub

Sent via Deja.com http://www.deja.com/
Before you buy.