473,572 Members | 3,079 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

VBA Array to Lotus Notes email Looping Problem

Hello! I hope there is someone out there who can shed some light on
this for me. I have a module that is supposed to look at an access
table, pull out each bid record, link to another table to find all of
the people to send the email to and link to one more table to find who
should be cc'd on the note.

All of this works fine, except.... The arrays for the To and CC seem
to exclude names from the list if there are multiple recipients and I
can't pinpoint what I have done incorrectly. I am fairly new to arrays
and have never sent an email through Lotus Notes until this script so
please forgive some of my coding.

Here is the code I am using and I greatly appreciate any help.

Thanks in advance,

Nicole

Dim myDb As DAO.Database
Dim rs As DAO.Recordset
Dim rsTo As DAO.Recordset
Dim rsCC As DAO.Recordset
Dim project As String
Dim closedate As String
Dim messagesubject As String
Dim Copy As String
Dim sNames() As String
Dim ccNames() As String
Dim OnviaLink As String
' opens database and finds available records from query
Set myDb = CurrentDb
Set rs = myDb.OpenRecord set("select * from
OnviaDataConcat enatedEmailFiel ds")

lngRSCount = rs.RecordCount
If lngRSCount = 0 Then
MsgBox "No bid alerts to send.", vbInformation
Else
rs.MoveLast
lngRSCount = rs.RecordCount
rs.MoveFirst
Do Until rs.EOF
'determines the product family and determines information for subject
and body
If rs!ProductFamil y = "Truck" Then
project = rs!ProjectName
Else
project = rs!ProductFamil y
End If

'determines if an a is necessary for grammar in the message body
If Right(project, 1) = "s" Then
plural = ""
Else
plural = "a "
End If

'creates message subject
messagesubject = "Bid Alert/ " & rs!ProjectCity & "/ " & project

'finds if a close date is provided or not
If rs!SubmitDate <> "" Then
closedate = rs!SubmitDate
Else
closedate = "not available"
End If

' Opening Line
strbody = "Included below is a bid requst for " & rs!Agency & ", " &
rs!State & " for " & plural & project & ". " & _
"Close date is " & closedate & ". " & _
"Please see below for more detail and let me know if this alert has
helped" & _
vbCrLf & vbCrLf & _
"Regards," & _
vbCrLf & vbCrLf & _
"Nicole Mauser-Storer" & vbCrLf & "NACD Governmental Sales" & vbCrLf
& "(309)-494-1109" & vbCrLf & "Ma************ ********@cat.co m" & vbCrLf
& vbCrLf

'Bid Information

strbody = strbody & "General Information" & vbCrLf & vbCrLf

strbody = strbody & "Project Name: " & rs!ProjectName & vbCrLf
strbody = strbody & "Bid #: " & rs!BidNo & vbCrLf
strbody = strbody & "Agency: " & rs!Agency & vbCrLf
strbody = strbody & "Close Date: " & closedate & vbCrLf
strbody = strbody & "Contact Name: " & rs!contactName & vbCrLf
strbody = strbody & "Contact Phone: " & rs!Phone & vbCrLf
strbody = strbody & "Email: " & rs!Email & vbCrLf
strbody = strbody & "City: " & rs!ProjectCity & vbCrLf
strbody = strbody & "Zip: " & rs!Zip & vbCrLf
strbody = strbody & "Sector: " & rs!Sector & vbCrLf
strbody = strbody & "URL: " & rs!URL & vbCrLf
strbody = strbody & "Descriptio n: " & rs!Description

OnviaLink = rs!OnviaNo
'Declare To Array for Email message

strSQL = "SELECT ToCurrentBids.T oEmail,
OnviaDataConcat enatedEmailFiel ds.OnviaNo " & _
"FROM ToCurrentBids INNER JOIN
OnviaDataConcat enatedEmailFiel ds ON " & _
"ToCurrentBids. OnviaNo =
OnviaDataConcat enatedEmailFiel ds.OnviaNo WHERE ToCurrentBids.O nviaNo =
'" & OnviaLink & "';"

Set rsTo = myDb.OpenRecord set(strSQL)

lngRScount2 = rsTo.RecordCoun t
If lngRScount2 = 0 Then
MsgBox "No addresses in To Field.", vbInformation
Else
rsTo.MoveLast
lngRScount2 = rsTo.RecordCoun t
rsTo.MoveFirst
Do Until rsTo.EOF

ReDim Preserve sNames(1 To lngRScount2)
sNames(UBound(s Names)) = rsTo!TOEmail
rsTo.MoveNext
Loop

End If
rsTo.Close
Set rsTo = Nothing
Set strSQL = Nothing

'Declare CC Array for message

strSQL = "SELECT CCCurrentBids.C CEmail,
OnviaDataConcat enatedEmailFiel ds.OnviaNo " & _
"FROM CCCurrentBids INNER JOIN
OnviaDataConcat enatedEmailFiel ds ON " & _
"CCCurrentBids. OnviaNo =
OnviaDataConcat enatedEmailFiel ds.OnviaNo WHERE CCCurrentBids.O nviaNo =
'" & OnviaLink & "';"

Set rsCC = myDb.OpenRecord set(strSQL)

lngRScount3 = rsCC.RecordCoun t
If lngRScount3 = 0 Then
MsgBox "No addresses in CC Field.", vbInformation
Else
rsCC.MoveLast
lngRScount3 = rsCC.RecordCoun t
rsCC.MoveFirst
Do Until rsCC.EOF

ReDim Preserve ccNames(1 To lngRScount3)
ccNames(UBound( ccNames)) = rsCC!CCEmail
rsCC.MoveNext
Loop

End If
rsCC.Close
Set rsCC = Nothing
Set strSQL = Nothing
'Public Sub SendNotesMail(S ubject 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.

'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("N otes.NotesSessi on")
'Next line only works with 5.x and above. Replace password with
your password
'Session.Initia lize ("9707KB")
'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.UserNam e
MailDbName = Left$(UserName, 1) & Right$(UserName , (Len(UserName) -
InStr(1, UserName, " "))) & ".nsf"
'Open the mail database in notes
Set Maildb = Session.GETDATA BASE("", MailDbName)
If Maildb.ISOPEN = True Then
'Already open for mail
Else
Maildb.OPENMAIL
End If

'Set up the new mail document
Set MailDoc = Maildb.CREATEDO CUMENT
MailDoc.Form = "Memo"
MailDoc.sendto = sNames
MailDoc.CopyTo = ccNames
MailDoc.Subject = messagesubject
MailDoc.Body = strbody
MailDoc.SAVEMES SAGEONSEND = SaveIt
'Send the document
MailDoc.PostedD ate = Now() 'Gets the mail to appear in the sent
items folder
MailDoc.SEND 0, Recipient
Call MailDoc.Save(Tr ue, False)

'Clean Up
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj = Nothing
Set lngRSCount = Nothing
Set lngRScount2 = Nothing
Set lngRScount3 = Nothing

Erase sNames
Erase ccNames

rs.MoveNext
Loop
End If
rs.Close
myDb.Close
Set rs = Nothing
Set myDb = Nothing
Close

Nov 13 '05 #1
1 4473
It does not appear you are using the Notes Domino object model for
passing your email. Using the Domino Library you can use this code in
an Access module. The Domino object model will read an array
internally, you don't have to do any looping, just pass the array

Sub SendNotesMail(p _SendTo() As String, p_Subject As String, p_Body As
String, p_Path() As String, p_NotesPassword As String)
Dim n_Session As New NotesSession
Dim n_dir As NotesDbDirector y
Dim n_db As NotesDatabase
Dim n_doc As NotesDocument
Dim n_object As NotesEmbeddedOb ject
Dim n_rtitem As NotesRichTextIt em
Dim i As Integer
Call n_Session.Initi alize(p_NotesPa ssword)

Set n_dir = n_Session.GetDb Directory("")
Set n_db = n_dir.OpenMailD atabase
Set n_doc = n_db.CreateDocu ment
Call n_doc.AppendIte mValue("Form", "Memo")

Call n_doc.AppendIte mValue("SendTo" , p_SendTo())
Call n_doc.AppendIte mValue("Subject ", p_Subject)

Set n_rtitem = n_doc.CreateRic hTextItem("Body ")
n_rtitem.Append Text (p_Body & vbCrLf & vbCrLf)
For i = 0 To UBound(p_Path)
Set n_object = n_rtitem.EmbedO bject(EMBED_ATT ACHMENT, "", p_Path(i))
Next i
n_doc.SaveMessa geOnSend = True
Call n_doc.Send(Fals e)

Set n_db = Nothing
Set n_Session = Nothing
MsgBox "Email has been sent"
End Sub
the array p_SendTo() can take any email name or group name. Just pass
it to the routine. This routine also does attachments.
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
4995
by: NickBlooruk | last post by:
Hello, I have successfully linked a Lotus Notes server to our SQL Server database using an ODBC connection. This works fine when wanting to select records eg openquery(LOTUSNOTES2, 'select * from Person' ) The problem I have is when I try to update the record I get an error eg update openquery(LOTUSNOTES, 'select * from Person where
5
7369
by: Colin Anderson | last post by:
I discovered, with great excitement, this article http://www.davison.uk.net/vb2notes.asp when researching methods for emailing from Access via Notes. Unfortunatly, when I run this I get a Run-time error. When I run it on an XP machine it crashes, but on an NT box it just generates an unknown error, handled by the error handler. I have...
0
5604
by: PZ Fosbeck | last post by:
I'm not a Lotus Notes developer but thanks to this group's archives have successfully created a function for sending Lotus Notes emails from Access. The follow code works great except I want to remove my name from the 'Sent By' portion of the email. These messages are sent using my client session of Lotus Notes, using a database called...
2
1701
by: DeanL | last post by:
Hi Everyone, I'm not sure if this is even possible but I thought I'd ask anyway. I have an Access 97 database that contains construction projects and other associated details and is updated on a regular basis by construction engineers and the like. My problem is that ocassionally (actually, more like daily) emails are received from other...
1
13035
by: Joe | last post by:
HI Has anyone been able to work with lotus notes automation classes??? Can you post sample code of how to use these classes. I have setup in VB but I am not able to port to C# This is what I have so far - I cannot create a session and not sure how to setup From/Subject
3
3713
by: Pav | last post by:
Hi, I am developing a small Intranet web application which needs to send mails to our coporate Ids. I am using CDONTS, But my mails never leave Que folder. Not able to find out what the problem is . please help 1) I am very new to SMTP configiration on my own Windows XP PC. How do I configure to send mails. We Use Lotus Notes as our...
3
8255
by: =?Utf-8?B?SmFtZXNU?= | last post by:
I can create a message and send it via my btopenworld account but is the method the same when using Lotus Notes. I have no experience of Lotus Notes whatsoever. I have never seen it at all. the code I am using is: dim smtp as new smtpclient dim message as new mailmessage( from@cc.com, to@dd.com) message.subject = "test"
0
1778
by: kohligagan2 | last post by:
Hi, I am working on a scenario . And scenario is I am trying to send an Email using my Lotus notes Client Id ( Lotus notes :- is used for messaging and sending mails work as a middleware) I have an ASP .NET website running on a IIS Server with Lotus note is running at the machine where server is running. One client sends the request to...
2
3956
by: MarkStorer | last post by:
Hi All I need to email a report (with contains graphs) via Lotus Notes. I've tried the 'SendObjectSnp' method (which works with some Lotus Notes clients (but not many others)); so I used the code below: - Public Sub SEND_EMAILS() Dim session As Object Dim db As Object Dim doc As Object Dim rtitem As Object
0
7733
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7640
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8155
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
8000
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
5248
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3685
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3673
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2138
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
975
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.