Email Reports (97/SR2)

Topic

New Reply

I am attempting to email reports using Lotus Notes from MS Access. I have some code which opens a Notes session and sends an email with attachment if needed. The path of the file is passed to the procedure as a string. The trouble I am having is creating the file in the first place. The best I’ve come up with is to use the SaveAs argument of the RunCommand method. But this opens a dialogue box which requires input from an operator. Ideally, and it might not be possible, I’d like the code to create an rtf file from the report using the same file name and just overwriting the file each time. Then I could call the email procedure passing the required arguments one of which is the file path and name. I’ve looked through every help file and book I own and many web references without luck. Thanks for any suggestions.

Reply | Quote

Replies

Paul,
Is this thread of any use to you?

Reply | Quote

Thanks, Brian, I think I may have seen this in one of my searches. Unfortunately it does pretty much what my code does and it still requires the presence of a file before mailing. So I’m still missing that part of the automation piece. Thanks again for the response!

Reply | Quote

Hi Paul,

I don’t know if this will help, but I do a similar thing using Outlook instead of Lotus Note (you have my greatest sympathy if you’re required to use Lotus Notes). Anyway, the following VBA code is called by the “On Click” property in a command button. It automatically creates a file with the report in the Access “Snapshot” format and then opens up an e-mail message with the snapshot report attached.

Private Sub SendReport_Click()

DoCmd.SendObject acReport, “Report Name Here”, “SnapshotFormat(*.snp)”, “joeblow@somewhere.com”, “”, “”, “Snapshot Report”, “Enclosed is the latest report in SNP format”, False, “”

End Sub

You insert the report name where I show “Report Name Here” and the e-mail address where I show “joeblow@somewhere.com.” This writes the report out giving it the file name “Report Name Here.snp”. So I get the same report name each time. I suppose you could look up the syntax of the SendObject command to see what parameters are available for configuring and generating such a report.

Someone sent me the code, as I am not that skilled in VBA. However, it looks like one of the parameters dictates the format of the report, i.e. the “SnapshotFormat(*.snp)” parameter. I would assume that you could specify RTF or any other format in this command. The snapshot format requires the recipient to have the Microsoft Snapshot Viewer loaded on thier computer. With it the recipient can view and/or print the report. The snapshot format retains all of the Access report features whereas RTF and most other formats do not, e.g. horizontal lines may be left out of a non-snapshot format, etc. The snapshot viewer program is a free download from Microsoft. Once installed on the recipient’s machine it will automatically open a snapshot report when the operator double clicks on any snapshot file.

I hope some of this may be of help to you.

Good luck,

Reply | Quote

Thanks, Chuck, for your response and sympathies. Using the sendobject method is problematic with Notes. That’s why I went with this quasi object oriented approach. With Notes the sendobject method opens profile dialogue and password dialogues and others so you lose that automated feel.

However, I think I will pursue the snapshot format if I can figure out how to save the file in that format. I do lose some formatting with the rtf format. Thanks again for the suggestions!

Reply | Quote

I don’t know whether this is of any use to you, but this is the way I mix Excel and Notes:

Public Sub SendNotesMail()
Dim Recipients As String
Dim Message As String

server = “GBWIN01/GB/Myco” ‘define the Notes server & database to use
mailfile = “mailbrooke.nsf” ‘define the Notes server & database to use

Set session = CreateObject(“Notes.NotesSession”) ‘establish Notes session
Set db = session.GetDatabase(server, mailfile) ‘create a link to the database object
Set doc = db.CreateDocument
Set body = doc.CreateRichTextItem(“Body”) ‘create a link to the form’s body field

doc.Form = “Memo” ‘create a new “Memo” document for the database
doc.Subject = “TEST MESSAGE from NOTES” ‘give it a subject
doc.SendTo = “brooke@myco.com” ‘set the SendTo field to the list of recipients

‘create and append body details to the document
Call body.AppendText(Chr(10) & “Sample text to go in a Notes mail document ” & Chr(10) & Chr(10))
Call body.AppendText(Chr(10) & “That’s all folks!” & Chr(10))

‘Embeds the file noted as an attachment to the body field
‘Call body.EmbedObject(1454, “”, “k:dataexcelbuttons.xls”)

Call doc.SEND(False, doc.SendTo) ‘send the doc
Call doc.save(True, False) ‘save the doc in sent items

‘delete references to Notes objects
Set doc = Nothing
Set db = Nothing
Set session = Nothing

End Sub

Like I said, it may be of no help but I know how difficult it is to work it out from scratch. The above code came from L-soft – Charlotte mentioned ACCESS-L but I found this on EXCEL-L Both seem to have bits and pieces that might be useful. I also have code to automatically post to a lotus notes database if you need it. Note (sorry!) that the above will throw a password box at you if notes is closed.

HTH

Brooke

Reply | Quote

Thanks, Brooke, I’ll take a look at it. It looks interesting – somewhat similar to what I’m doing but different too. Thanks again!

Reply | Quote

Brooke,
I have been trying this out but get an “Active X componnet cant create object ” in the “Set session = CreateObject(“Notes.NotesSession”) ” line, I presume I need a reference to be there? I tried some of the Active X ones but unsuccessful. Any ideas?

Reply | Quote

The reference I have is “Lotus Notes Automation Classes”,and it’s location on my system is D:notesnotes32.tlb.

However, simple test code – sending a message to myself – will run without this checked. I just get error messages such as “can’t save in sent items” – though I do actually recieve the mail. so having run the simple test code to myself with the reference checked and without, I have two messages in my inbox but only one in sent items.

Reply | Quote

Brooke,
Thanks.
1) I have also since found that it will send as you have indicated as long as Notes is open. If notes is not I
get a “Cant create Active X object” error message. Whiloe I can check if NOtes is open , ultimately I would prefer Notes to startup and go to the login prompt.

2) The reference I have is “Lotus Domino Objects” and not “Lotus Notes Automation Classes”. As you would expect there seem to be similar objects. I suppose the difference in the name is just the version of Notes that is installed.

3) Multiple recepients
I have tried to modify the recepients area to allo for multiple names by the following but am not having much success i.e. mail is not received. Based on the sample code below, the sent box shows an email addressed to the following
To : joe@mysite.com,pete@mysite.com
… below but nothing is received.

SendToAddress2 = “joe@mysite.com”
SendToAddress1 = “pete@mysite.com”
Recipients = SendToAddress1 & “,” & SendToAddress2
doc.SendTo = Recipients ‘set the SendTo field to the list of recipients

I could send them individually using a loop … Any clues?

Reply | Quote

have a look at this thread – if you haven’t seen it already!

Reply | Quote

Brooke,
I have been trying the Array approch as per your suggestion with mixed results.

1) Using actual email addresses – this works as expected

‘Recepients = Array(“joe@mysite.com”, “jane@mysite.com”, “pete@mysite.com”)
..
doc.SendTo = Recepients
..
Call doc.SEND(False, Recepients)

2) Using variables to represent email addresses – this does not quite get there. The first names receives a mail but not the next 2. Strangely when I look in the sent folder the email is addressed to,
To : joe@mysite.com, jane@mysite.com, pete@mysite.com
BUT the only email that comes through shows the following,
To : joe@mysite.com, jane
The subsequent names are concatenated. Go figure!?!

SendToAddress1 = “pete@mysite.com”
SendToAddress2 = “joe@mysite.com”
SendToAddress3 = “jane@mysite.com”

Recepients = Array(SendToAddress1 & “, ” & SendToAddress2 & “, ” & SendToAddress3)
..
doc.SendTo = Recepients
..
Call doc.SEND(False, Recepients)

Reply | Quote

[indent]

---

Recepients = Array(SendToAddress1 & “, ” & SendToAddress2 & “, ” & SendToAddress3)

---

[/indent] try Recepients = Array(SendToAddress1 , SendToAddress2 , SendToAddress3)

I believe your code is creating an array of one, so only the first email address in the string will be recognised

Reply | Quote

Brooke,
Yes, thats on the money. Its humming….Many thanks for your time and patience getting me this far. If you could bear a little more I have some queries if you are familiar in this territory….

1) I wanted to get a further understanding and options of the Notes model, but will need to track down the help file for it. The lines for .Send and .Save…are you able to confirm the what the True,False parameter options mean?

Call doc.Send(False, Recepients) ‘send the doc
Call doc.Save(True, False) ‘save the doc in sent items

2) Do you know how to,
a) request a “return receipt”
Set Importance levels i.e. low,normal,high
c) Set delivery report i.e. only on failure, confirm on delivery

Reply | Quote

Call notesDocument.Send( attachForm [, recipients ] )

attachForm – Boolean. If True, the form is stored and sent along with the document. If False, it isn’t.
recipients – String or array of strings. Optional. The recipients of the document, which may include people, groups, or mail-in databases.Ignored if the document contains a SendTo item, in which case the document is mailed to recipients listed in SendTo. Required if the document does not contain a SendTo item.

Call notesDocument.Save( force, createResponse [ , markRead ])

force – Boolean. If True, the document is saved even if someone else edits and saves the document while the script is running. The last version of the document that was saved wins; the earlier version is discarded. If False, and someone else edits the document while the script is running, the createResponse argument determines what happens.
createResponse – Boolean. If True, the current document becomes a response to the original document (this is what the replicator does when there’s a replication conflict). If False, the save is canceled. If the force parameter is True, the createResponse parameter has no effect.
markRead – Boolean. If True, the document is marked as read. If False (default), the document is not marked as read.

and just in case you’re wondering, no, I don’t have that much experience. The initial code I posted was ripped from elswhere, and as it worked I didn’t meddle too much. I got the above from here and it took me a long time to find this. If you peruse this, you will see that it does mention .DeliveryReport, .DeliveryPriority and .ReturnReceipt, but doesn’t tell you what values to assign. I’ve had a good old scoot around the net and haven’t really got anywhere. Occaisionally you come across a snippet of info on how to do something but it seems to be very rarely.

Reply | Quote

Brooke,
OK. Have not come across that area. Some you may be interested in, if you have not found them,
http://www.lotus.com/developers/devbase.ns…K19991215044902]

http://www.lotus.com/developers/itcentral….76?OpenDocument

Sorry, I have yet used the “make a nice link” feature yet.

otk
http://linkhttp://link

Reply | Quote

Brooke,
I found some doco on the Lotus site that will probably help re integration with MS Office

http://www.lotus.com/developers/itcentral….76?OpenDocument

Reply | Quote

Paul,
I would be interested in the code you use to invke Notes. With respect to creating a report, I currently push reports to a common drive in two formats for people to read (rtf) or manipulate (excel).

for RTF which uses a report called
DoCmd.OutputTo acReport, “What Does That Equipment Do by Equipment Report”, acFormatRTF, Filepath & “What Does That Equipment Do.doc”, False, “”

for Excel which uses a query
DoCmd.OutputTo acQuery, “What Does That Equipment Do?”, “MicrosoftExcel(*.xls)”, Filepath & “What Does That Equipment Do.xls”, False, “”

Hope that helps.

Reply | Quote

Sorry for the slow response. For what it’s worth here’s the code I use. It is limited to one user at a time but it sounds like you’ve solved that – true? It will open a notes session if one isn’t already opened. Here ya go:

***Begin Code***

‘Public Sub SendNotesMail(Subject as string, attachment as string,
‘recipient as string, bodytext as string,saveit as Boolean)
‘This public sub will send a mail and attachment if neccessary to the
‘recipient including the body text.
‘Requires that notes client is installed on the system.

Public Function SendNotesMail(Subject As String, Attachment As String, _
Recipient As String, BodyText As String, SaveIt As Boolean)
‘Set up the objects required for Automation into lotus notes
Dim Maildb As Object ‘The mail database
Dim USERNAME As String ‘The current users notes name
Dim MailDbName As String ‘THe current users notes mail database name
Dim MailDoc As Object ‘The mail document itself
Dim AttachME As Object ‘The attachment richtextfile object
Dim Session As Object ‘The notes session
Dim EmbedObj As Object ‘The embedded object (Attachment)
‘Start a session to notes
Set Session = CreateObject(“Notes.NotesSession”)
‘Get the sessions username and then calculate the mail file name
‘You may or may not need this as for MailDBname with some systems you
‘can pass an empty string
USERNAME = Session.USERNAME
MailDbName = left$(USERNAME, 1) & Right$(USERNAME, (Len(USERNAME) – InStr(1, USERNAME, ” “))) & “.nsf”
‘Open the mail database in notes
Set Maildb = Session.GETDATABASE(“”, MailDbName)
If Maildb.IsOpen = True Then
‘Already open for mail
Else
Maildb.OPENMAIL
End If
‘Set up the new mail document
Set MailDoc = Maildb.CREATEDOCUMENT
MailDoc.Form = “Memo”
MailDoc.sendto = Recipient
MailDoc.Subject = Subject
MailDoc.Body = BodyText
MailDoc.SAVEMESSAGEONSEND = SaveIt
‘Set up the embedded object and attachment and attach it
If Attachment “” Then
Set AttachME = MailDoc.CREATERICHTEXTITEM(“Attachment”)
Set EmbedObj = AttachME.EMBEDOBJECT(1454, “”, Attachment, “Attachment”)
MailDoc.CREATERICHTEXTITEM (“Attachment”)
End If
‘Send the document
MailDoc.SEND 0, Recipient
‘Clean Up
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj = Nothing

End Function

***End Code***

I don’t remember where I got it from (it was several life times ago) so my apologies to the original author.

Reply | Quote

Paul,
Thx. I have something working but am missing the ability to start a notes session if its not open…which you have kindly included. I had tracked down some code from the Lotus site that I thought would do the trick but received an error (which is similar to what I get when I run what you have included).

The error occurs on the line,

Set Session = CreateObject(“Notes.NotesSession”)

Run time error 429
Active X component cant create object

The Help says “Creating objects requires that the object’s class be registered in the system registry and that any associated dynamic-link libraries (DLL) be available. ”

Within the references section I have “Lotus Domino Objects” (domobj.tlb) ticked so I am now guessing it must be an Active X listing that is not selected.
Additionally, from a Lotus.com download realting to “Lotus Domino and Toolkits for COM” it said “The Domino Objects, with their COM interface, are available on a machine where you have installed any of the following products, in version R5.0.2b or later:”, and to register the following dll, nlsxbe.dll.
I did no successfully using – regsvr32 C:Lotusnotesnlsxbe.dll

I dont know enough about this part of it but would suspect as it is a dll thats not registered that it be an Active X one. Any ideas? I’ll work my way thorugh the Active X ones and see….

otk.

Reply | Quote

The message means that it doesn’t recognize “Notes” as an automation server … that is, an application that allows itself to be automated from outside. The ActiveX part of the message confuses people because it sounds like it’s talking about a control when it isn’t. You may need to try “Domino” or to instantiate “Notes.Application” first in order to get it to work before you create a session object.

Reply | Quote

Charlotte,
Yes, that would figure. By instantiate I guess you mean “session.Initialize” below?

Dim session As Object
Set session = CreateObject(“Lotus.NotesSession”)
‘ Initialize the NotesSession
session.Initialize
MsgBox session.UserName

Reply | Quote

No, I was talking about the fact that you’re using late binding, so the code doesn’t know what a Lotus.NotesSession actually is, only that it’s an object of some sort. Here’s an example of the alternative when working with Word:

Dim objWord As Word.Document

Set objWord = GetObject(strTemplateDir & “” & strTmpltNm, “Word.Document”)

If you have a reference set to the object library, early binding like that is less confusing because Intellisense works with you instead of against you.

Reply | Quote