HELP VB for Access 2007 to email Excel attachemnet via Lotus Notes

[nagiese]

I currently have VB code in two data bases that automatically appends specific data to an existing Excel report. Now I want to automate the process of email that report out once the final append is completed in the last database. I need an example of some code that emails an Excel file from an access data base through Lotus Notes. I would greatly appreciate it if someone could provide that sample code for me. I'm not sure how to set the code up so far I've only been able to find examples for outlook and where it emails reports and query data from the open database. I need to see examples of how to write the code when it has to go look for an Excel file out on a network address and then send it via Lotus Note. Thank you in advance for all of your assistance.



Nena Giese

[rpeare]

Try this:

http://www.fabalou.com/VBandVBA/lotusnotesmail.asp

[nagiese]

Hi, Thanks
I did find that example but it doesn't have an example of how to attach an excel file, in addition it has the code where you are opening up Lotus Notes and you have to code in a user name and pass word that will open the Lotus application. I want to write the code to assume that Lotus Notes is already open so there is no pass word and user name required. or does this code prompt the user to enter in their user name and pass word? Other users will be operating this database so I don't want to hard code a user name and password in to the code. I'm new to VB so I'm not sure how the attachment code is working here, how do you tell it to go get an Excel doc out in a network path and attach it in the body of the document. Where does that go in this code? That is why I need an example that actually does that.

[rpeare]

Well you see a bunch of code down towards the bottom where the mail message is actually getting built:

    MailDoc.sendto = Recipient
          MailDoc.Subject = Subject
          MailDoc.Body = BodyText

VB is an object oriented language so in this example MAILDOC is your object and if you want to add an attachment it is likely something like

MAILDOC.ATTACHMENT = strFileName

where strFileName is the full path and name of the file you're trying to attach, this is just a guess on my part but this code is very similar to outlook email generation so it's a fairly good guess.
The code does handle an embedded object though so you could play around with either method.

As far as the username and password, I would assume if lotus notes was already open it wouldn't bother with the login, but you would have to type in a PW if lotus notes was not open. I don't use/have never used lotus notes but the code assumes your login to your computer AND to your email system are the same (same username) so keep that in mind. Other than that you're going to have to work with the code and see what you can do, I can not duplicate your environment to try. Make sure you add the reference for lotus notes before you start testing as well or this code will flat out not work.

[nagiese]

ok I pasted the code into a new module and tweeked it for what I needed, but when I whent to test run it my data base didn't recognize the module at all it only gave me the option of runing my other module. Why isn't it recognizing the module? See code below.

code:

Public Sub 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")
'Next line only works with 5.x and above. Replace password with your password
Session.Initialize ("password")
'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 or using above password you can use other mailboxes.
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("nena_giese@vfc.com")
MailDoc.Subject = Subject("Item Count Report")
MailDoc.Body = BodyText("Good Day, Attached is the updated Item Count report for the current period. Please contact me if you have any questions")
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") = ("Q:\WWC Common\TAMPA Files\ItemIDCount.xlsx")
End If
'Send the document
MailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder
MailDoc.SEND 0, Recipient
'Clean Up
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj = Nothing

[rpeare]

How did you call your mailing function?

usually you have to do something like

Call SendNotesMail (Variable1, Variable2, Variable3, Variable4, Variable5) (your variables are subject, attachment, recipient, bodytext and saveit)

[nagiese]

I'm not sure, I thought that was the first line of the code? Is that not correct, do I need to change the Public Sub to Call SendNotes Mail and then add somthing else before that line?

[rpeare]

You've created a public function which you can call from pretty much anywhere in your database. Think of a function as a stand alone bit of code that does one specific thing. For instance there built in functions like ABS (for absolute value) which changes a negative number to a positive number. You are basically creating your on ABS function but you are telling that function that you will pass it 5 variables, those variables are listed in the parentheses at the end of your function declaration (Public Sub SendNotes Mail (expected variables here)). Your function is expecting you to pass it 5 variables so it can build your email. If you are not calling the function and passing it those variables it's going to fail.


So in your 'main' section of code (the section of code from which you're kicking off your email)

you'd have something like

sSubject = "OMG SUBJECT"
sAttachment = "c:\testfile.txt"
sRecipient = "omg@omg.com"
sBody = "omg this is the body"
vSaveit = True (not sure how you have your set up whether it's going to take true/false or what not)

call sendnotesmail (ssubject, sattachment, srecipient, sbody, vsaveit)

then when your function opens it uses the values you have passed it to create the mail

Note that the variables between a main section of code and a function do not have to be identical so when you put SSUBJECT in your call statement, when your email function gets it it's 'converting' it, for lack of a better word, to it's own internal SUBJECT variable.

[nagiese]

Thank you for your patience, I'm trying to get into a class as you can see I desperately need it. I've perchased a bunch of books but they aren't as helpful.

I was under the assumption that the Set up the new mail document and 'Set up the embedded object and attachment and attach it section of the code did that. Should I replace those sections with the code you listed or should I just add it to the Set up the new mail docement section?

[rpeare]

I do not understand your question. The code you listed generates an email, yes, but it relies on you passing it five variables. If you are not passing the function those five variables it will, in all likelyhood fail, because you've declared four strings, and if you don't supply them the function will assume they are null or empty strings so in essence you'd be attempting to send an email to "" which no email service in the world will be able to resolve.

[nagiese]

I'm confused, In the code I put in the send to, body, subject, attachment, I thought I was giving the system what it needed to send an email. Currently the system isn't recognizing my modual at all, this module is call SendNotesMail and when I go to run the module it doesn't even show up in the Macro Name window. VBE isn't recognizing that it exsists. I've added in your code and it still didn't work.

MailDoc.sendto = Recipient("nena_giese@vfc.com")
MailDoc.Subject = Subject("Item Count Report")
MailDoc.Body = BodyText("Good Day, Attached is the updated Item Count report for the current period. Please contact me if you have any questions")
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") = ("Q:\WWC Common\TAMPA Files\ItemIDCount.xlsx")

[rpeare]

Again, you set up your code as a FUNCTION (see: PUBLIC SUB). NOT as a MAIN MODULE. A FUNCTION is a subset of code that performs a specific set of instructions, mostly ones that you will use repeatedly in your MAIN MODULE. You are trying to run your FUNCTION as though it were a MAIN MODULE and it is not. If you want this code to perform as a MAIN MODULE then you have to create a module (new) and paste your code into a sub main/end sub section of code.

[nagiese]

I really appreciate your help, I've been a little confused as to why this hasn't worked, I started a new module and treated it as a Main Module and it still didn't work. But after I did that I rewrote the code and to the following and got it to work. The only issue I have is that it is not sending the email to my sent folder. That portion of the code I can't seem to get to work. New code is below

Public Sub SendNotesMail()
'Set up the objects required for Automation into lotus notes
    Dim MyConnect As String
    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)
    Dim Subject As String
    Dim Attachment As String
    Dim Recipient As String
    Dim BodyText As String
    Dim SaveIt As Boolean
       
    
    MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0; Persist Security Info = False;Data Source= Q:\WWC Common\TAMPA Files\Blank Inventory Analysis.accdb; User ID = Admin;"
    'Start a session to notes
    Set Session = CreateObject("Notes.NotesSession")
    'Next line only works with 5.x and above. Replace password with your password
    'Session.Initialize ("")
    '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 or using above password you can use other mailboxes.
    '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 = ("nena_giese@vfc.com")
    MailDoc.Subject = ("Item Count Report")
    MailDoc.Body = ("Good Day, Attached is the updated Item Count report for the current period.  Please contact me if you have any questions")
    MailDoc.SAVEMESSAGEONSEND = SaveIt
    Attachment = "Q:\WWC Common\TAMPA Files\ItemIDCount.xlsx"
    
    If Attachment <> "" Then
        Set AttachME = MailDoc.CREATERICHTEXTITEM("Q:\WWC Common\TAMPA Files\ItemIDCount.xlsx")
        Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", Attachment, "Attachment")
        MailDoc.CREATERICHTEXTITEM ("Q:\WWC Common\TAMPA Files\ItemIDCount.xlsx")
        Attachment = ("Q:\WWC Common\TAMPA Files\ItemIDCount.xlsx")
    
    End If
    'Send the document
    MailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder
    MailDoc.SEND 0, ("nena_giese@vfc.com")
    'Clean Up
    Set Maildb = Nothing
    Set MailDoc = Nothing
    Set AttachME = Nothing
    Set Session = Nothing
    Set EmbedObj = Nothing
End Sub

[rpeare]

Have you tried altering the order of your POSTDATED and SEND commands?

I do not know how lotus notes works but I would think you would be able to postdate something you haven't sent yet.

[nagiese]

Thanks so much for all your help, I remembered what you said about the Call comand, so I put Call in front of the POSTDATED and SEND commands and then I changed the MailDoc.SAVEMESSAGEONSEND = SaveIt to True and that worked. The only thing that would make this better would be if I could add multiple email addresses to the MailDoc.Sendto, haven't figured that out yet.

MailDoc.SAVEMESSAGEONSEND = True
Call MailDoc.ReplaceItemValue("PostedDate", Now())
Call MailDoc.SEND(False)