email attachment via vb script as rtf shows as xls in Lotus Notes 1

[hartkea]

Created an attachment for e-mail using rtf. Lotus Notes is launched to send the e-mail and the attachment appears with .xls as format. Before I sent the document I opened it and it was is a mess.

Please advise

 

[lespaul]

where's your code for the email and attachment?

Leslie

 

[hartkea]

Thanks Leslie, any help is greatly appreciated!!

Code is as follows:

DoCmd.RunSQL "SELECT [z shell to create outfile by dealer].* INTO [outfile for report] FROM [z shell to create outfile by dealer] WHERE ((([z shell to create outfile by dealer].DEALER)= '" & vOfficeID & "'))", -1
DoCmd.SendObject acSendReport, "email dealer inventory/pipeline detail truck information", "RichTextFormat(*.rtf)", rs("Email_Address"), "", "", "Dealer Inventory Report", "Please find attached your dealer inventory, as well as trucks on order", True

I found this to be a known issue w/ lotus notes.

Microsoft resolution is to delete any orphaned files in my temp folder. Once I do that and I run the above code, the first couple of e-mails have the correct attachment but
then I get a msg: Lotus Notes Mailman Error

I click OK, get another exception error, click OK and the rest of the e-mails are generated with bad attachments.

I have no idea why the file is being generated in my temp folder.

Any suggestions?


Anne

 

[lespaul]

I'll admit, I haven't seen a Notes email sent in this manner. Most of the code that I've seen is more along the lines of:

Thread222-38494
Thread702-92097
Thread245-835128

Perhaps look into using one of these techniques? Or maybe some one can help you get SendObject to work.

Leslie

 

[mkov]

The below code is what I use to send automated e-mails through Lotus Notes.


Function SendTPAEmail(strRecipient As Variant, strPath1 As String, strPath2 As String)

On Error GoTo ErrHandler

Dim objNotesSession As Object
Dim objNotesDatabase As Object
Dim objNotesDocument As Object
Dim strText As String
Dim strMsg As String
Dim varRecipient As Variant

Dim objAttach As Object
Dim objEmbed As Object

Set objNotesSession = CreateObject("Notes.Notessession")
Set objNotesDatabase = objNotesSession.GETDATABASE("", "")
Call objNotesDatabase.OPENMAIL

Set objNotesDocument = objNotesDatabase.CREATEDOCUMENT
strText = ""

varRecipient = Split(strRecipient, ";", -1)

Call objNotesDocument.REPLACEITEMVALUE("SendTo", varRecipient)
Call objNotesDocument.REPLACEITEMVALUE("Subject", "Daily E-mail")
Call objNotesDocument.REPLACEITEMVALUE("Body", strText)

Set objAttach = objNotesDocument.CREATERICHTEXTITEM("Attachment")
Set objEmbed = objAttach.EMBEDOBJECT(1454, "", strPath1, "Attachment")
Set objEmbed = objAttach.EMBEDOBJECT(1454, "", strPath2, "Attachment")

objNotesDocument.SAVEMESSAGEONSEND = True
Call objNotesDocument.SEND(False)
Set objNotesSession = Nothing

ErrHandler:
If Err.Number <> 0 Then
MsgBox Err.Number & Err.Description, , "Message Alert"
End If

End Function


With this one in particular, I am attaching 2 files to a single e-mail. I pass the values into this function from another function that is looping through a table and gathering the data to send the e-mail. The files are exported to a temp directory and then attached to the e-mail from there.

I have a routine that sends out 200+ e-mails, which takes about 20 minutes to process. I have never seen a problem of the wrong file being attached to an e-mail.

Hopefully this will help you with your issue.

 

[mkov]

lespaul, you are correct, SendObject will not work.

 

[hartkea]

Thank you both very much.

Wow! I am very green regarding this depth of code.

In my little world. I had created a table of company codes and e-mail addresses. The report would run for each company code and e-mail the corresponding address from the table. This code was created by converting a macro to VB script.

mkov,
Would you mind adding some comments (spoon feeding) to your code for me?

Thank you so very much.

Anne

 

[lespaul]

Function SendTPAEmail(strRecipient As Variant, strPath1 As String, strPath2 As String)

On Error GoTo ErrHandler

Dim objNotesSession As Object
Dim objNotesDatabase As Object
Dim objNotesDocument As Object
Dim strText As String
Dim strMsg As String
Dim varRecipient As Variant

Dim objAttach As Object
Dim objEmbed As Object
[b]this sets the Notes Session[/b]
Set objNotesSession = CreateObject("Notes.Notessession")
[b]gets the local mail database[/b]
Set objNotesDatabase = objNotesSession.GETDATABASE("", "")
[b]opens the local mail database[/b]
Call objNotesDatabase.OPENMAIL
[b]creates a new document[/b]
Set objNotesDocument = objNotesDatabase.CREATEDOCUMENT
strText = ""
[b]splits the recipient array separated by ;[/b]
varRecipient = Split(strRecipient, ";", -1)
[b]replaces notes document items with who to send to, the subject[/b]
Call objNotesDocument.REPLACEITEMVALUE("SendTo", varRecipient)
Call objNotesDocument.REPLACEITEMVALUE("Subject", "Daily E-mail")
Call objNotesDocument.REPLACEITEMVALUE("Body", strText)
[b]creates a Rich text item attachment in the email[/b]
Set objAttach = objNotesDocument.CREATERICHTEXTITEM("Attachment")
[b]embeds the attachment in the object[/b]
Set objEmbed = objAttach.EMBEDOBJECT(1454, "", strPath1, "Attachment")
Set objEmbed = objAttach.EMBEDOBJECT(1454, "", strPath2, "Attachment")
[b]indicates to save the message[/b]
objNotesDocument.SAVEMESSAGEONSEND = True
[b]sends the email[/b]
Call objNotesDocument.SEND(False)
Set objNotesSession = Nothing

ErrHandler:
    If Err.Number <> 0 Then
        MsgBox Err.Number & Err.Description, , "Message Alert"
    End If

End Function

Here's another option from THread245-835128 where you assign the memo form type so it knows it's an email:

Set objNotesDatabase = session.GetDatabase(servername.Abbreviated, "mail.box")

Set objNotesDocument = New NotesDocument(objNotesDatabase)
objNotesDocument.Form = Memo

Leslie

 

[mkov]

Thanks Leslie, you beat me to it.

 

[lespaul]

well, you were obviously busy with something and I was waiting on information before I could continue with what I was doing!

Hopefully I translated everything correctly!

Leslie

 

[hartkea]

Thank you both very much. However, due to time constraints, I am going to have to create the reports as outfiles and have the user e-mail each one individually.

Hopefully, I will be able to get back to this project and learn from your code.

Thanks again,

Anne

 

[hartkea]

I have a few questions regarding the above code:

Do I have to export the reports?

Is "Attachment" the name of the report?

 

[lespaul]

Here's some information from the Notes Help:

Syntax
Set notesRichTextItem = notesDocument.CreateRichTextItem( name$ )
Parameters
name$
String. The name of the new rich text item

Set objAttach = objNotesDocument.CREATERICHTEXTITEM("Attachment")

So in this case, "Attachment" is the name of the rich text item in NOTES.

Again from the help:

Syntax
Set notesEmbeddedObject = notesRichTextItem.EmbedObject( type%, class$, source$, [ name$ ] )
Parameters
type%
Constant. Indicates if you want to create an attachment, an embedded object, or an object link. May be any of the following:
EMBED_ATTACHMENT (1454)
EMBED_OBJECT (1453)
EMBED_OBJECTLINK (1452)
Note The above constants are also used with the Type property in the NotesEmbeddedObject class.
class$
String.
If you are using EMBED_OBJECT and want to create an empty embedded object from an application, use this parameter to specify the name of the application (for example, "1-2-3 Worksheet") and specify an empty string ("") for source$. Case-sensitive.
If you are using EMBED_OBJECTLINK or EMBED_ATTACHMENT, specify an empty string ("").
source$
String.
If you are using EMBED_OBJECT and want to create an embedded object from a file, use this parameter to specify the name of the file, and specify an empty string ("") for class$.
If you are using EMBED_ATTACHMENT or EMBED_OBJECTLINK, use this parameter to specify the name of the file to attach or link.
name$
String. Optional. Name by which you can reference the NotesEmbeddedObject later. This parameter is only used for OLE/2 objects and does not include attachments.


So in this case:

Set objEmbed = objAttach.EMBEDOBJECT(1454, "", strPath1, "Attachment")

"Attachment" is an optional parameter.

Where are you creating the reports? What kind of reports are they?

leslie