Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Email Reports (97/SR2)

    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.

  2. #2
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email Reports (97/SR2)

    Paul,
    Is this thread of any use to you?

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email Reports (97/SR2)

    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!

  4. #4
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Email Reports (97/SR2)

    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. <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

    Good luck,

  5. #5
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email Reports (97/SR2)

    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!

  6. #6
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email Reports (97/SR2)

    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

  7. #7
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email Reports (97/SR2)

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

  8. #8
    Lounger
    Join Date
    Oct 2001
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email Reports (97/SR2)

    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?

  9. #9
    Lounger
    Join Date
    Oct 2001
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email Reports (97/SR2)

    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.
    <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

  10. #10
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email Reports (97/SR2)

    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.

  11. #11
    Lounger
    Join Date
    Oct 2001
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email Reports (97/SR2)

    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?

  12. #12
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email Reports (97/SR2)

    have a look at <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=xl&Number=75180&page=0&view =collapsed&sb=5&o=0&fpart=>this thread</A> - if you haven't seen it already!

  13. #13
    Lounger
    Join Date
    Oct 2001
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email Reports (97/SR2)

    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)

  14. #14
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email Reports (97/SR2)

    <hr>Recepients = Array(SendToAddress1 & ", " & SendToAddress2 & ", " & SendToAddress3)<hr>
    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

  15. #15
    Lounger
    Join Date
    Oct 2001
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email Reports (97/SR2)

    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"
    [img]/forums/images/smilies/cool.gif[/img] Set Importance levels i.e. low,normal,high
    c) Set delivery report i.e. only on failure, confirm on delivery

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •