dabdowb
asked on
Send custom emails from Access 2000 using Lotus Notes 7
OK, welcome to the insanity! I have code below for an email routine I currently use in an Access 2000 database. It works just fine as is, however, my department approached me with some new ideas and I am just not quite certain how to tackle it. I checked a few other questions that have great solutions, just not quite what I need...I don't think.
Here is the task. An operator will enter the database, and choose an item from a drop down list that they need to edit. One item can have several emails identified with it (all to be stored in a table where there is one email per record in the table...each row consisting of item, location, customer, email address). So if the operator needs to edit data for just one of say 5 customers, the desire is to have an autogenerated email (attached via VBA code with the 'On Event' trigger of the form) to be sent to each of the other 4 email addresses associated with that particular item, with a predetermined body of text inserted, and the subject line listed as the corresponding item, location, customer data associated with that email address specifically. Instead of the email list below being hard coded as Recipient(0), Recipient(1),etc., I need this to be dynamic and fed from the results of a query to see what other contacts there are besides the one customer already edited.
I have seen a few entries on this site that showed how to have a formatted email sent to multiple addresses, but what I am looking for here is an email being sent to each address individually....so if there are 4 email addresses, I should have 4 emails in my sent folder when the process is done, with the subject line of each email being specific to that email address' item, location and customer per the master email table.
There is discussion of being able to attach a set file to each email as well (an .xls spreadsheet most likely), but I figured one step at a time. As mentioned above, the verbage in the actual body of the email will be set, so that at least can be as simple as what I have below, but just not wrapping my head around how to make the rest of it work.
Thoughts?
Thanks
Here is the task. An operator will enter the database, and choose an item from a drop down list that they need to edit. One item can have several emails identified with it (all to be stored in a table where there is one email per record in the table...each row consisting of item, location, customer, email address). So if the operator needs to edit data for just one of say 5 customers, the desire is to have an autogenerated email (attached via VBA code with the 'On Event' trigger of the form) to be sent to each of the other 4 email addresses associated with that particular item, with a predetermined body of text inserted, and the subject line listed as the corresponding item, location, customer data associated with that email address specifically. Instead of the email list below being hard coded as Recipient(0), Recipient(1),etc., I need this to be dynamic and fed from the results of a query to see what other contacts there are besides the one customer already edited.
I have seen a few entries on this site that showed how to have a formatted email sent to multiple addresses, but what I am looking for here is an email being sent to each address individually....so if there are 4 email addresses, I should have 4 emails in my sent folder when the process is done, with the subject line of each email being specific to that email address' item, location and customer per the master email table.
There is discussion of being able to attach a set file to each email as well (an .xls spreadsheet most likely), but I figured one step at a time. As mentioned above, the verbage in the actual body of the email will be set, so that at least can be as simple as what I have below, but just not wrapping my head around how to make the rest of it work.
Thoughts?
Thanks
Function EmailContacts()
Dim Recipient(13) As String
here2:
'For email notification subroutine:
Recipient(0) = "Matt Fiedler/matmgt/sigma/sial"
'Recipient(1) = "Dave Dresch/salesup/resrch/sial"
Subject = "Daily Updates Completed..."
Message = "The Daily Updates have been made. Please make Zbost file for planners."
GoSub SendMail
SendMail:
'************************************************************
'Sends Email Notification alerting group of update
'************************************************************
Dim Session As Object
Dim Database As Object
Dim Doc As Object
'session and database declared in general as object
Set Session = CreateObject("Notes.NotesSession") 'create notes session
Set Database = Session.GETDATABASE("", "") 'set db to database not yet named
Call Database.OPENMAIL 'Open the users mail database
recip = 0
For i = recip To 0
'i = 0
Set Doc = Database.CREATEDOCUMENT 'create a new document in mail database
Call Doc.replaceitemvalue("SendTo", Recipient(i)) 'create sendto field
Call Doc.replaceitemvalue("Subject", Subject) 'create subject field
Call Doc.replaceitemvalue("Body", Message) 'create body field
Call Doc.Send(False) 'send the message - if unsucessful, an error
Next
'Note: The Session object contains Database and Document. Destroying parent gets rid of others.
Set Session = Nothing ' close connection to free memory
'end of e-mail routine
'Return
DoCmd.SetWarnings True
End Function
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
objMailDocument.sendto = strLotusNotesUserID - Field Contact in your Case
objMailDocument.Subject = strMailTitle - in your case FCST_ID
When you are calling this function/sub pass the above two parameters and it should work fine.
strTextBody - you need to populate it before passing it to this function
objMailDocument.Subject = strMailTitle - in your case FCST_ID
When you are calling this function/sub pass the above two parameters and it should work fine.
strTextBody - you need to populate it before passing it to this function
ASKER
I am still alive...just been reassigned different projects, but this one is coming back to the top again, so I hope to try this code in the next week. I will post my discovery along the way.
Begging a thousand pardons for disappearing over the past weeks....I was also moving into a new home and getting engaged, so things have been completely NUTS!!!
Cheers
Begging a thousand pardons for disappearing over the past weeks....I was also moving into a new home and getting engaged, so things have been completely NUTS!!!
Cheers
ASKER
That is the part I don't understand....how do I pass those to fields from the query to the function call?
would I do something like this? (assuming the query is called qry_Pending_Items)
Public Sub SendLotusNotesMail(strMail
strLotusNotesUserID= [qry_Pending_Items].[Conta
strMailTitle= [qry_Pending_Items].[FCST_
would I do something like this? (assuming the query is called qry_Pending_Items)
Public Sub SendLotusNotesMail(strMail
strLotusNotesUserID= [qry_Pending_Items].[Conta
strMailTitle= [qry_Pending_Items].[FCST_
You can call the function like this
SendLotusNotesMail(param1,
SendLotusNotesMail(param1,
NB: param has to be replaced by actual data
SendLotusNotesMail(param1,
SendLotusNotesMail(param1,
NB: param has to be replaced by actual data
ASKER
OK, I understand that...I think. I am not familiar with sub functions, just functions, although my understanding is they are pretty much the same, even though the sub function would not let me step into in the VBA screen as I hoped, but maybe I am just doing something wrong...very likely actually.
BUT, let's say I create a button in a form that once clicked would trigger an event to call this subfunction, then the call would go as follows if I was just interested in the first two parameters right?
SendLotusNotesMail(([qry_P
The string going into the body is preset, I won't have any data to send to that part, it will just be hard coded into the programming, hence why I didn't pass it to the function call, because I have it programmed in the function itself.
Is there a reason this is a sub function as opposed to just a normal function? I never have understood the difference.
Thanks
BUT, let's say I create a button in a form that once clicked would trigger an event to call this subfunction, then the call would go as follows if I was just interested in the first two parameters right?
SendLotusNotesMail(([qry_P
The string going into the body is preset, I won't have any data to send to that part, it will just be hard coded into the programming, hence why I didn't pass it to the function call, because I have it programmed in the function itself.
Is there a reason this is a sub function as opposed to just a normal function? I never have understood the difference.
Thanks
The basic difference is that that Sub does not return anything to the parent calling method but Function has to return something to the parent.
Please post a new ticket as we are really going out of track
Please post a new ticket as we are really going out of track
ASKER
I guess I am confused as to why I need to post a new ticket when I can't get the code you offered me to work? I am just trying to get the verbage correct to make it run, but I am apparently not asking the question correctly, or understanding your answer correctly.
I have worked with functions before, and understand that if I was running a query, or typing code that called the function, I needed to include the fields of data for the function to work with. That seems to be what you are telling me to do, but when I tried it, the program did not work. That is what I was trying to understand from you two posts up when I put this
SendLotusNotesMail(([qry_P
Whenever I have run functions before and passed data to it, that was how I have done it, except typically I don't have to specify the query since I am in the query. So if I was in the qry_Pending_Items, I would type "SendLotusNotesMail([FCST_
Is this not what I typed in my original question...how do I load this data to a function from a query? Here is the snapshot from my original post...I am just trying to understand why a new post is needed for something I don't have a solution to in THIS post?
So if the operator needs to edit data for just one of say 5 customers, the desire is to have an autogenerated email (attached via VBA code with the 'On Event' trigger of the form) to be sent to each of the other 4 email addresses associated with that particular item, with a predetermined body of text inserted, and the subject line listed as the corresponding item, location, customer data associated with that email address specifically. Instead of the email list below being hard coded as Recipient(0), Recipient(1),etc., I need this to be dynamic and fed from the results of a query to see what other contacts there are besides the one customer already edited.
Thanks
I have worked with functions before, and understand that if I was running a query, or typing code that called the function, I needed to include the fields of data for the function to work with. That seems to be what you are telling me to do, but when I tried it, the program did not work. That is what I was trying to understand from you two posts up when I put this
SendLotusNotesMail(([qry_P
Whenever I have run functions before and passed data to it, that was how I have done it, except typically I don't have to specify the query since I am in the query. So if I was in the qry_Pending_Items, I would type "SendLotusNotesMail([FCST_
Is this not what I typed in my original question...how do I load this data to a function from a query? Here is the snapshot from my original post...I am just trying to understand why a new post is needed for something I don't have a solution to in THIS post?
So if the operator needs to edit data for just one of say 5 customers, the desire is to have an autogenerated email (attached via VBA code with the 'On Event' trigger of the form) to be sent to each of the other 4 email addresses associated with that particular item, with a predetermined body of text inserted, and the subject line listed as the corresponding item, location, customer data associated with that email address specifically. Instead of the email list below being hard coded as Recipient(0), Recipient(1),etc., I need this to be dynamic and fed from the results of a query to see what other contacts there are besides the one customer already edited.
Thanks
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
I never could figure out how to pass a parameter to the subfunction offered by AsishRaj, despite repeated attempts to get something beyond:
SendLotusNotesMail(param1,
SendLotusNotesMail(param1,
I know how to call a function AsishRaj, what I don't know, or at least trying to use the code you offered is how to insert into those parameters the value coming from a query, while not being inside the query itself...maybe that is the part of the statement I was not explaining correctly. The parameters would be based on the results of a query not attached to the user interface. I kept trying to provide example ideas and provided the name of the query being used and the fields to be passed, but nothing more than what is listed above was offere. I don't know, but I finally gave up and moved forward with an alternate idea that seems to be working just fine.
See the code I wrote below for the true answer to this question, although I will provide points to AsishRaj since it appears he is confident his code will loop through an unknown number of query results and send emails.
Thanks
SendLotusNotesMail(param1,
SendLotusNotesMail(param1,
I know how to call a function AsishRaj, what I don't know, or at least trying to use the code you offered is how to insert into those parameters the value coming from a query, while not being inside the query itself...maybe that is the part of the statement I was not explaining correctly. The parameters would be based on the results of a query not attached to the user interface. I kept trying to provide example ideas and provided the name of the query being used and the fields to be passed, but nothing more than what is listed above was offere. I don't know, but I finally gave up and moved forward with an alternate idea that seems to be working just fine.
See the code I wrote below for the true answer to this question, although I will provide points to AsishRaj since it appears he is confident his code will loop through an unknown number of query results and send emails.
Thanks
Option Compare Database
Function EmailContacts()
Dim db As DAO.Database
Dim Recipient, Subject, Message As String
Dim tbl As DAO.Recordset
Set db = DBEngine.Workspaces(0).Databases(0)
Set tbl = db.OpenRecordset("qryPendingItems", dbOpenDynaset)
tbl.MoveFirst
Message = "Whatever set verbage that Rhonda and Robin provide me :-)"
'For email notification subroutine:
GoSub SendMail
SendMail:
'************************************************************
'Sends Email Notification alerting group of update
'************************************************************
Dim Session As Object
Dim Database As Object
Dim Doc As Object
'session and database declared in general as object
Set Session = CreateObject("Notes.NotesSession") 'create notes session
Set Database = Session.GETDATABASE("", "") 'set db to database not yet named
Call Database.OPENMAIL 'Open the users mail database
'Loop through query results until EOF
Do
Recipient = tbl!Contact
Subject = tbl!FCST_ID
Set Doc = Database.CREATEDOCUMENT 'create a new document in mail database
Call Doc.replaceitemvalue("SendTo", Recipient) 'create sendto field
Call Doc.replaceitemvalue("Subject", Subject) 'create subject field
Call Doc.replaceitemvalue("Body", Message) 'create body field
Call Doc.SEND(False) 'send the message - if unsucessful, an error
tbl.MoveNext
Loop Until tbl.EOF
Set Session = Nothing ' close connection to free memory
'Note: The Session object contains Database and Document. Destroying parent gets rid of others.
'end of e-mail routine
DoCmd.SetWarnings True
End FunctionASKER
I don't understand why we could not communicate in a manner that would have led to your solution being used, but after repeated attempts to get more than textbook reply out of you on how to call a function, I finally gave up. I awarded you points because I like your code, and I think it can work, although I am positive it does not do all that I requested, nor did you offer the way to do it, but instead just persisted on how to call a function, which was not my question if you read more carefully. Thanks for your help though...it at least got my wheels spinning enough to solve the problem with my own code.
ASKER
Anyone looking for the final working solution to this question, please see my last post with the code I used to get the program to work as desired.
Cheers
Cheers
ASKER
objMailDocument.sendto = strLotusNotesUserID
objMailDocument.Subject = strMailTitle
Both of these will grab data from query results in the database, but I don't see where the code outlines grabbing those data points, so at present, it compiles fine, but it doesn't execute since there is no data for these lines.
To expand on the original idea, there will be one query that these two fields should feed from. Let's call the query, qry_Pending_Items. This query will have two fields in its results, one field called FCST_ID, which will be what should be inserted into the subject line, and the other field will be CONTACT, which will house the email address that particular record should be sent to.
As stated previously, I have a set Body Text, so I know to insert that I just put it in quotes on the line that says: objMailDocument.Body = strTextBody
It is the loading of data from the query part that is messing me up and that I need the most help with. The trick being, the function needs to loop for however many records are in the query results, which could be anywhere from 1 to ? different entries.
Thoughts?
Thanks,
Matt