Excel VBA - adding attachment

hcl-bot · February 21, 2008, 1:03pm

Hello. I have an Excel macro that sends out email to my vendors on a weekly basis using a mail-in dbase so that their responses can be seen by everyone in my team. I am now in need of adding an attachment for them as well. The macro I have for some reason imbeds a rich text version of the spreadsheet instead of actually attaching the .xls file itself.

I was not able to get the background version of the macro I have to work with a mail-in dbase (although it sends the attachment properly), so I switched to the foreground version I’ve included here.

Can you please tell me what I am missing??

Also, can someone explain what the two double-quoted parameters in this line mean? I cannot find any info on it.

—> NotesUIDoc.CreateObject(“”, “”, “C:\temp\test.xls”)

Thanks, and I appreciate any help on this situation.

=========

Here is the code I have so far in case it helps anyone else out.

Dim workspace As Object

Dim NotesUIDoc As Object

Set workspace = CreateObject(“Notes.NotesUIWorkspace”)

sRecipient = “vendor@host.com”

ssubject = “Mail-In DB Test”

On Error Resume Next

Call workspace.OpenDatabase(“MyServer”, “mailin\mailindb.nsf”) ’

Set NotesUIDoc = workspace.COMPOSEDOCUMENT(“”, “”, “Memo”)

Call NotesUIDoc.FIELDSETTEXT(“EnterSendTo”, sRecipient)

Call NotesUIDoc.FIELDSETTEXT(“Subject”, ssubject)

NotesUIDoc.GOTOFIELD “Body”

NotesUIDoc.InsertText “Test Message”

Call NotesUIDoc.CreateObject(“”, “”, “C:\temp\test.xls”)

NotesUIDoc.SEND

NotesUIDoc.Close

AppActivate “Lotus Notes”

Application.SendKeys “y”

Set NoteUIDoc = Nothing

Set workspace = Nothing

hcl-bot · February 21, 2008, 1:26pm

Subject: Excel VBA - adding attachment

From the Notes designer help:

In a document in Edit mode, creates an OLE object in the current rich-text field.

Note CreateObject is not supported under OS/2, under UNIX, or on the Macintosh.

Defined in

NotesUIDocument

Syntax

Set handleV = notesUIDocument.CreateObject( [ name$ [ , type$ _

                     [ , filePath$ ] ] ] )

Parameters

name$

String. Optional. The name you want to use to refer to the object.

type$

String. Optional. The type of object that you want to create. The type that you specify must correspond to an entry in the OLE registry and is case-sensitive. The method creates an empty instance of the specified object type. If you specify a type, you must omit the filePath$ parameter.

filePath$

String. Optional. The path and file name of the source document that you want to use to create an object. The method creates an object from the file. If you specify a file path, you must specify an empty string (“”) for the type$ parameter.

Return value

handleV

Variant. A handle to the OLE object.

Usage

You must specify either a type$ or a filePath$, but not both. If you specify a filePath$, you create the object in a rich-text field. You must, however, code a GetObject statement to give LotusScript a handle on the object.

The current document must be open in Edit mode and the cursor must be in an editable rich-text field.

With no parameters, this method displays the Create Object dialog box, which allows the user to select the object to create.

hth.

brandt

hcl-bot · September 9, 2009, 12:40am

Subject: Excel VBA - adding attachment

If you are still looking for a way to add an attachment instead of a rich text version of the spreadsheet, I have figured out a way to do this. I used the function from this web site to copy the file to the clipboard and then used NotesUIDoc.Paste to paste the file into the note. Here’s the link:

And here is my code (after creating the document, setting the subject and recipients). Not sure if setting the edit mode to true is needed but it worked:

Call NotesUIdoc.gotofield(“Body”)

NotesUIdoc.editmode = True

ClipBdCopy = ClipboardCopyFiles(RptArray)

Call NotesUIdoc.Paste

hcl-bot · February 22, 2008, 5:07am

Subject: Excel VBA - adding attachment

Hello Derek,As per as the two “”,“” goes,

here is your answer:

Example

In a document in Edit mode, creates an OLE object in the current rich-text field.

Note CreateObject is not supported under OS/2, under UNIX, or on the Macintosh.

Defined in

NotesUIDocument

Syntax

Set handleV = notesUIDocument.CreateObject( [ name$ [ , type$ _

                     [ , filePath$ ] ] ] )

Parameters

name$

String. Optional. The name you want to use to refer to the object.

type$

String. Optional. The type of object that you want to create. The type that you specify must correspond to an entry in the OLE registry and is case-sensitive. The method creates an empty instance of the specified object type. If you specify a type, you must omit the filePath$ parameter.

filePath$

String. Optional. The path and file name of the source document that you want to use to create an object. The method creates an object from the file. If you specify a file path, you must specify an empty string (“”) for the type$ parameter.

Return value

handleV

Variant. A handle to the OLE object.

Usage

You must specify either a type$ or a filePath$, but not both. If you specify a filePath$, you create the object in a rich-text field. You must, however, code a GetObject statement to give LotusScript a handle on the object.

The current document must be open in Edit mode and the cursor must be in an editable rich-text field.

With no parameters, this method displays the Create Object dialog box, which allows the user to select the object to create.

Language cross-reference

CreateObject function in LotusScript language

Example